重置MySQL密码及终端连接时遇ERROR 2002套接字错误求助

Hey there, let's break down this frustrating MySQL socket error and fix it properly so it doesn't pop up again. First, let's understand why this happens in the first place.

Root Cause Analysis #

The /var/run/mysqld/mysqld.sock file is a Unix socket that MySQL uses for local connections—you shouldn't have to create this manually. When you manually create it and set permissions, it works temporarily, but that's just a band-aid. The real issue is that your MySQL server isn't starting correctly, so it can't generate this socket file on its own. Common reasons include:

• The MySQL service isn't running at all, or crashed on startup
• The /var/run/mysqld directory has incorrect permissions, so the mysql user can't create the socket file
• Misconfigured settings in your MySQL config file (like wrong socket path)
• Insufficient disk or memory space (since /var/run is often a tmpfs mounted in memory)

Permanent Fix Steps #

Let's go through the proper troubleshooting and fix process step by step:

1. Check if MySQL Service is Running #

First, verify the status of your MySQL service to confirm it's active:

systemctl status mysql

If it shows failed or inactive, check the service logs to find the exact startup error:

journalctl -u mysql

This log will tell you if it's a config syntax error, permission issue, or missing dependency.

2. Fix Socket Directory Permissions (The Right Way) #

Instead of manually creating the .sock file, fix the directory permissions so MySQL can generate it automatically:

1. Stop the MySQL service first:

   systemctl stop mysql
   

2. Create the /var/run/mysqld directory if it doesn't exist:

   mkdir -p /var/run/mysqld
   

3. Assign full ownership to the mysql user and group:

   chown -R mysql:mysql /var/run/mysqld
   

4. Start the MySQL service again:

   systemctl start mysql
   

Now check if the socket file was generated automatically:

ls -l /var/run/mysqld/

You should see mysqld.sock listed, owned by mysql:mysql.

3. Verify MySQL Configuration File #

Double-check your MySQL config to ensure the socket path is set correctly:

1. Open the main config file (Ubuntu uses this path):

   nano /etc/mysql/mysql.conf.d/mysqld.cnf
   

2. Confirm these lines are present and correct:

   socket = /var/run/mysqld/mysqld.sock
   pid-file = /var/run/mysqld/mysqld.pid
   

3. Save the file and restart MySQL if you made changes:

   systemctl restart mysql
   

4. Check System Resources #

If /var/run is a tmpfs (memory-mounted filesystem), it might be full. Check its usage:

df -h /run

If it's close to 100%, clean up temporary files or adjust the tmpfs size in your /etc/fstab file. Also, check overall disk space with df -h to ensure no partitions are full.

5. Correct Password Reset Flow (Avoid Service Issues) #

If your password reset attempts caused service problems, use this reliable Ubuntu-specific process:

1. Stop MySQL:

   systemctl stop mysql
   

2. Start MySQL in safe mode (skipping password checks):

   mysqld_safe --skip-grant-tables &
   

3. Connect to MySQL without a password:

   mysql -u root
   

4. Switch to the mysql database and update the root password (for MySQL 5.7+):

   use mysql;
   UPDATE user SET authentication_string=PASSWORD('your-new-secure-password') WHERE User='root';
   FLUSH PRIVILEGES;
   exit;
   

5. Stop the safe mode process and start MySQL normally:

   pkill mysqld_safe
   systemctl start mysql
   

6. Test the new password:

   mysql -u root -p
   

Final Note #

Remember: manually creating the .sock file is just a temporary fix. The real solution is ensuring MySQL can start successfully on its own, which will generate the socket file automatically. By following the steps above, you'll address the root cause and prevent this error from recurring.

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