MySQL 'Access denied for user 'root'@'localhost' 问题再探及求助

Hey Nick, let’s break down this MySQL access issue you’re hitting after switching from WAMP to Ubuntu’s LAMP stack. Since you’ve already tried all the standard password-reset fixes, we’ll focus on Ubuntu-specific quirks that often fly under the radar for folks coming from Windows environments.

1. Check if MySQL uses auth_socket for root (the #1 Ubuntu gotcha) #

Ubuntu’s default MySQL setup often uses the auth_socket plugin for the root user instead of password authentication. This means you can only log into MySQL as root via your system’s root account (using sudo), not with a password directly—total opposite of how WAMP works.

• First, log into MySQL using sudo (no password required):

  sudo mysql -u root
  

• Once inside, check which authentication plugin root is using:

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

• If the plugin shows auth_socket, switch it to password-based authentication:

  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your-preferred-password';
  FLUSH PRIVILEGES;
  

• Exit MySQL and try logging in normally:

  mysql -u root -p
  

2. Verify MySQL’s bind-address configuration #

Ubuntu’s MySQL config sometimes restricts connections to localhost only (which is correct for your use case, but misconfigurations can still block access):

• Open the main MySQL config file:

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

• Look for the line bind-address = 127.0.0.1—make sure it’s set to 127.0.0.1 (not commented out or set to another IP). This ensures MySQL accepts localhost connections.
• Restart MySQL to apply changes:

  sudo systemctl restart mysql
  

3. Fix file permissions for MySQL’s data directory #

Ubuntu is strict about file ownership, and MySQL needs proper access to its data folder to function correctly. WAMP handles this automatically, but Ubuntu doesn’t:

• Check the ownership of MySQL’s data directory:

  ls -ld /var/lib/mysql
  

• It should show mysql:mysql as the owner. If not, fix it with:

  sudo chown -R mysql:mysql /var/lib/mysql
  sudo chmod -R 755 /var/lib/mysql
  

• Restart MySQL again:

  sudo systemctl restart mysql
  

4. Confirm you’re targeting the correct root@localhost user #

In MySQL, root@localhost is a distinct user from root@127.0.0.1 or root@%. Sometimes Ubuntu’s setup can have missing or misconfigured entries:

• From the MySQL shell (logged in via sudo), list all root users:

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

• If root@localhost isn’t listed, create and grant it full privileges:

  CREATE USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your-password';
  GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
  FLUSH PRIVILEGES;
  

These steps address the Ubuntu-specific edge cases that generic password-reset guides miss. Let me know if any of these resolve your access issue!

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