Resolving MySQL Error 1285: Addressing –skip-name-resolve Configuration

by

Understanding the Error

MySQL Error 1285, SQLSTATE HY000 (ER_WARN_HOSTNAME_WONT_WORK), indicates that the MySQL server has been started with the --skip-name-resolve option. This option disables DNS hostname resolution and causes MySQL to only use IP addresses for client authentication. If you try to grant privileges using hostnames rather than IP addresses while this option is enabled, you’ll encounter this error.

Diagnosing the Issue

To confirm that the server is running with the --skip-name-resolve option, you can check the MySQL server variables. Connect to the MySQL server and execute the following command:

SHOW VARIABLES LIKE 'skip_name_resolve';

If the value is ON, then the server is indeed started with the --skip-name-resolve option.

Solutions to Fix Error 1285

Restart MySQL Without –skip-name-resolve

To resolve this error, you will need to restart the MySQL server without the --skip-name-resolve option. This can be done by editing the MySQL configuration file (my.cnf or my.ini, depending on your operating system) and commenting out or removing the line that includes this option:

# skip-name-resolve

After making the change, save the file and restart the MySQL server. On a Linux system, the command to restart MySQL might look like this:

sudo systemctl restart mysql

Using IP Addresses for Grants

If you prefer to keep the --skip-name-resolve option for performance reasons or because of a policy that avoids DNS lookups, you will need to use IP addresses instead of hostnames when granting privileges. For example:

GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'192.0.2.1' IDENTIFIED BY 'password';

Replace database_name, user_name, 192.0.2.1, and password with your actual database name, username, client IP address, and password.

Wildcard IP Addresses

You can also use wildcard characters in IP addresses if you want to grant privileges to a range of IP addresses. For example:

GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'192.0.2.%' IDENTIFIED BY 'password';

This will grant privileges to any user connecting from an IP address that starts with 192.0.2..

Checking User Table for Hostnames

If you have existing user accounts that use hostnames, you’ll need to update them to use IP addresses. You can view the current user accounts with:

SELECT user, host FROM mysql.user;

Look for any entries in the host column that are not IP addresses and update them accordingly.

Conclusion

MySQL Error 1285 is a clear indication that your MySQL server’s --skip-name-resolve configuration is conflicting with the use of hostnames in your privilege grants. Depending on your needs, you can either restart MySQL without the --skip-name-resolve option or adjust your grant statements to use IP addresses. Always ensure that your server’s configuration aligns with your operational requirements and security policies.

Leave a Comment