MySQL重载(reload)失败但重启正常，寻求排查与解决方法

Problem Context #

When running sudo service mysql reload, I get a socket connection failure error, but restart and status checks work fine. Restarts take ~20 seconds, which makes me suspect a timeout issue. I've already tried creating a local .my.cnf for the root user to rule out password problems, and updated all localhost entries in the mysql.user and mysql.db tables to 127.0.0.1 (due to a skip-name-resolve warning), but the reload issue persists.

Exact reload error:

# sudo service mysql reload
* Reloading MySQL database server mysqld
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

Troubleshooting & Fix Steps #

Let's walk through targeted fixes to get reload working again:

1. Verify the MySQL Socket File
   First, check if the socket file exists and has the right permissions:

   ls -l /var/run/mysqld/mysqld.sock
   

   • If it's missing, confirm the socket path in your MySQL config (usually /etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf) matches this location.
   • Create the directory and set ownership if needed:

     mkdir -p /var/run/mysqld
     chown mysql:mysql /var/run/mysqld
     

   • Restart MySQL and check if the socket file is generated.

2. Force mysqladmin to Use TCP Instead of Socket
   Since you have skip-name-resolve enabled, socket connections might be misbehaving. Test a reload using TCP directly:

   mysqladmin -h 127.0.0.1 -u root -p reload
   

   If this works, the issue is with the init script using socket connections by default. Edit the MySQL init script (typically /etc/init.d/mysql) and replace instances of mysqladmin reload with mysqladmin -h 127.0.0.1 reload (you can omit -p if your .my.cnf has the password configured).

3. Adjust Reload Timeout in Init Script
   The 20-second restart time suggests MySQL needs more time to process reloads. Check for a timeout setting in the init script:

   grep -n "RELOAD_TIMEOUT" /etc/init.d/mysql
   

   Increase the value (e.g., from 10 to 30 or 60 seconds) and save the script. Test the reload again.

4. Confirm Root User's TCP Connection Permissions
   Even after updating localhost to 127.0.0.1, double-check the root user has proper TCP access:

   SELECT user, host FROM mysql.user WHERE user = 'root';
   

   If root@127.0.0.1 isn't present, create it with full privileges:

   CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'your_secure_password';
   GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
   FLUSH PRIVILEGES;
   

5. Check Error Log for Reload-Specific Issues
   After a failed reload, check the MySQL error log for detailed clues (e.g., config syntax errors, resource limits):

   tail -n 50 /var/log/mysql/error.log
   

   Look for entries that appear right after you run the reload command—these will often point to the root cause.

内容的提问来源于stack exchange，提问作者Kyle Anderson