html

When you see [Warning] IP address 'xxx.xxx.xxx.xxx' could not be resolved: Name or service not known in your MySQL logs, it means MySQL is attempting reverse DNS lookup on incoming connections and failing. This typically happens when:

• The IP doesn't have a valid PTR record
• Your DNS resolver isn't properly configured
• You're receiving connection attempts from suspicious sources

While this warning itself isn't dangerous, frequent unresolved IP warnings (especially from foreign IP ranges) often indicate:

2023-05-15T14:22:17.123456Z 12 [Warning] IP address '45.142.120.XX' could not be resolved
2023-05-15T14:22:18.234567Z 13 [Warning] IP address '185.163.45.XX' could not be resolved
2023-05-15T14:22:19.345678Z 14 [Warning] IP address '103.172.32.XX' could not be resolved

These are likely automated bots scanning for vulnerable MySQL instances.

1. Disable reverse DNS lookups:

# Add to my.cnf under [mysqld]
skip-name-resolve=1

2. Implement proper firewall rules:

# Example iptables rules
iptables -A INPUT -p tcp --dport 3306 -s 192.168.1.0/24 -j ACCEPT  # Allow internal network
iptables -A INPUT -p tcp --dport 3306 -j DROP  # Block everything else

For production Master/Slave setups:

# MySQL user creation with IP restrictions
CREATE USER 'repl_user'@'192.168.1.%' IDENTIFIED BY 'complex_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.%';

# Enable SSL for replication
CHANGE MASTER TO MASTER_SSL=1;

Set up a dedicated log parser to track connection attempts:

#!/bin/bash
# Monitor MySQL auth failures
tail -f /var/log/mysql/error.log | grep --line-buffered "Host '.*' is not allowed" | while read line
do
    ip=$(echo $line | grep -oE '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+')
    echo "$(date) - Blocked connection attempt from $ip" >> /var/log/mysql_auth_attempts.log
    # Optional: Block IP immediately
    # iptables -A INPUT -s $ip -j DROP
done

Reverse DNS lookups can impact performance. After implementing skip-name-resolve, monitor query response times:

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Aborted_connects';
SHOW GLOBAL STATUS LIKE 'Connection_errors%';

---

When you see the warning [Warning] IP address 'xxx.xxx.xxx.xxx' could not be resolved: Name or service not known in your MySQL logs, it means the server is attempting reverse DNS lookups on connecting IP addresses but failing to resolve them to hostnames. This is common in environments where:

• IP addresses don't have reverse DNS records
• Your DNS resolver is slow or unreliable
• Connections originate from countries (like China) where reverse DNS isn't consistently configured

MySQL performs these lookups for:

1. Hostname-based authentication in grant tables
2. Logging and monitoring purposes
3. Security auditing (identifying suspicious connections)

While this warning isn't critical for database operations, it can cause:

• Minor connection delays (each lookup attempt takes time)
• Cluttered log files making troubleshooting harder
• Potential performance impact if you receive many connections

Add this to your my.cnf/my.ini under the [mysqld] section:

[mysqld]
skip-name-resolve

This tells MySQL to skip DNS lookups and use IP addresses exclusively. After changing, restart MySQL:

sudo systemctl restart mysql

When using skip-name-resolve:

1. All MySQL user privileges must be granted using IP addresses instead of hostnames
2. Update your grant tables accordingly:

-- Instead of:
GRANT ALL ON db.* TO 'user'@'hostname.example.com';

-- Use:
GRANT ALL ON db.* TO 'user'@'192.168.1.100';

To complement your firewall plan, implement MySQL-side restrictions:

-- Create a restricted user that only allows local connections
CREATE USER 'safeuser'@'localhost' IDENTIFIED BY 'strongpassword';

-- Or limit to specific IP ranges
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'apppassword';

For MySQL 5.7+, use the performance_schema to track connections:

SELECT * FROM performance_schema.hosts;
SELECT user, host, db, command FROM information_schema.processlist;

For connections originating from China, consider:

-- Geo-based restrictions (requires MySQL Enterprise or proxy solution)
-- Or implement a connection delay for unknown IP ranges:

[mysqld]
connect_timeout = 10
wait_timeout = 60
interactive_timeout = 60

This bash script helps analyze connection patterns:

#!/bin/bash
# Count unique IPs generating DNS warnings
grep "could not be resolved" /var/log/mysql/error.log | \
awk -F"'" '{print $2}' | \
sort | uniq -c | sort -nr