Troubleshooting MySQL Access Denied for ‘root’@’localhost’ on Ubuntu 22.04 LTS

Resolve 'Access denied for user 'root'@'localhost' using password: YES' on Ubuntu 22.04 LTS. Learn about `auth_socket` plugin and how to reset MySQL root password.

When managing a MySQL database on Ubuntu 22.04 LTS, encountering an "Access denied" error for the root user trying to connect from localhost with a password can be a frustrating experience. This guide will walk you through the common causes and provide precise, step-by-step solutions to regain control of your MySQL instance.

Symptom & Error Signature

The typical symptom is the inability to log into the MySQL server as the root user, even when you believe you are using the correct password. This usually manifests in the terminal when using the mysql client or in application logs.

Terminal Output:

$ mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Application Log (Example – PHP/Laravel):

SQLSTATE[HY000] [1045] Access denied for user 'root'@'localhost' (using password: YES) (Connection: mysql, SQL: select * from `users`)

Root Cause Analysis

The "Access denied for user 'root'@'localhost' (using password: YES)" error, particularly on modern Ubuntu installations, usually points to one of the following core issues:

1. Incorrect or Forgotten Password: The most straightforward reason. The password you are providing for the root user is simply not the one configured in MySQL. This can happen after initial setup, password changes, or migration.
2. auth_socket Plugin Configuration: Ubuntu's default MySQL/MariaDB installation often configures the root@localhost user to authenticate using the auth_socket plugin. This plugin allows passwordless access to MySQL only when the connecting operating system user matches the MySQL user. For instance, if you are logged into your Ubuntu server as the root OS user, you can connect to MySQL as root@localhost without a password. However, if you attempt to connect as root@localhost with a password while auth_socket is active (and no mysql_native_password or caching_sha2_password is explicitly set or takes precedence), MySQL might reject the connection because the password method is not expected or is overridden.
3. skip-grant-tables Left Active: If you previously enabled skip-grant-tables for maintenance or password reset and failed to properly restart MySQL without this option, it can lead to authentication inconsistencies once re-enabled. While typically you'd get different errors (or passwordless access), it's a potential factor to consider.
4. MySQL Service Not Running: Though it would manifest as a "Can't connect to local MySQL server through socket" error rather than "Access denied," it's always the first thing to rule out.
5. Corrupted Grant Tables: A rare but possible scenario where the MySQL system tables responsible for user privileges become corrupted.

Step-by-Step Resolution

Follow these steps carefully to diagnose and resolve the "Access denied" error. We will prioritize the most common scenarios first.

1. Verify MySQL Service Status

Before diving into password resets, ensure your MySQL service is actually running.

sudo systemctl status mysql

If the output shows active (running), proceed. If it's inactive or failed, try starting it:

sudo systemctl start mysql

2. Reset MySQL Root Password Using auth_socket (If You Have sudo Access)

This is often the easiest method on Ubuntu if your root@localhost user is configured with auth_socket and you have sudo privileges on the system.

1. Connect as root (OS user) to MySQL (passwordless):

   sudo mysql -u root
   

   If this successfully logs you into the MySQL prompt, it confirms auth_socket is active for root@localhost and you can proceed. If it fails, proceed to the skip-grant-tables method.

2. Check root user's authentication plugin: At the MySQL prompt, execute:

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

   If plugin is auth_socket, this confirms the default setup.

3. Change root user's authentication method and set a new password: To allow root@localhost to connect with a password from any OS user (not just root OS user), you need to change its authentication plugin to mysql_native_password or caching_sha2_password and set a password.

   Replace 'Your_New_Strong_Password' with a secure, unique password.

   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Your_New_Strong_Password';
   FLUSH PRIVILEGES;
   EXIT;
   

   mysql_native_password is widely compatible. caching_sha2_password is more secure and the default for new MySQL 8.x users, but older clients might not support it. If you encounter client compatibility issues later, you can switch back to mysql_native_password.

4. Test the new password:

   mysql -u root -p
   

   Enter 'Your_New_Strong_Password' when prompted. You should now be able to log in.

3. Reset MySQL Root Password Using skip-grant-tables (The Fallback Method)

If the auth_socket method didn't work, or you don't have sudo access to connect as the root OS user, this method allows you to bypass authentication temporarily to reset the password.

1. Stop the MySQL service:

   sudo systemctl stop mysql
   

2. Start MySQL with skip-grant-tables: This command starts MySQL without loading the grant tables, effectively disabling all security.

   While skip-grant-tables is active, anyone can connect to your MySQL server without a password. Do this only for the duration of the password reset and ensure no external access is possible.

   sudo mysqld_safe --skip-grant-tables --skip-networking &
   

   The & puts it in the background, and --skip-networking prevents remote connections, making it safer. You should see a process ID (PID) displayed.

3. Connect to MySQL as root (passwordless):

   mysql -u root
   

   You should now get a MySQL prompt without being asked for a password.

4. Reset the root user's password: At the MySQL prompt, execute these commands.

   Replace 'Your_New_Strong_Password' with a strong, new password.

   -- Change authentication plugin and set password for root@localhost
   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Your_New_Strong_Password';
   
   -- Apply changes
   FLUSH PRIVILEGES;
   
   EXIT;
   

5. Stop the mysqld_safe process: First, find the PID of the mysqld_safe process. You can use ps aux | grep mysqld_safe. It's typically the PID you saw when you started it, or look for the process owned by mysql.

   sudo kill $(pgrep mysqld_safe)
   

   Wait a few seconds for it to fully shut down.

6. Start the MySQL service normally:

   sudo systemctl start mysql
   

7. Test the new password:

   mysql -u root -p
   

   Enter 'Your_New_Strong_Password' when prompted. You should now be able to log in successfully.

4. Check for External Factors (Less Common for root@localhost)

While highly unlikely for root@localhost connections, consider these if previous steps fail:

• Firewall Rules: Ensure no firewall (e.g., UFW) is blocking local connections to port 3306.

  sudo ufw status
  

  If UFW is active, ensure rules exist to allow access, e.g., sudo ufw allow 3306/tcp.

• MySQL Configuration (my.cnf): Verify that bind-address in your my.cnf (e.g., /etc/mysql/mysql.conf.d/mysqld.cnf) is not set to only an external IP, preventing localhost connections. It should typically be 127.0.0.1 or 0.0.0.0 (for external access, use with caution).

  sudo grep "bind-address" /etc/mysql/mysql.conf.d/mysqld.cnf
  

  If bind-address is present and set to anything other than 127.0.0.1 or 0.0.0.0, it could be a factor. Comment it out or change it to 127.0.0.1 if you only need local access, then restart MySQL.

By following these detailed steps, you should successfully resolve the "Access denied for user 'root'@'localhost' (using password: YES)" error and regain full control of your MySQL database on Ubuntu 22.04 LTS. Remember to always use strong, unique passwords for database users.