MySQL MariaDB

MySQL Access Denied for User ‘root’@’localhost’

“One of the most common errors encountered by MySQL users is “MySQL access denied for user ‘root’@’localhost’.” And whether you are a new MySQL user or a newb, knowing the various techniques for resolving it is advantageous.

Using this guide, you will discover three methods of resolving this error.”

What Does This Error Mean?

The best way to understand this error is to break it down into single components. This error occurs when you attempt to login into your MySQL instance on your local machine, hence ‘localhost.’

An example is as shown:

$ mysql -u root -p

The components of the error are as follows:

  1. ERROR 1045 (28000) – refers to the ERROR CODE and SQLSTATE, respectively. For example, in MySQL and MariaDB, the error code 1045 means access denied for that username with the specified password.
  2. The second is the message string. The error message follows the format: Access denied for user ‘%s’@’%s’ (using password: %s).

NOTE: The first %s in the error message refers to the username, while the second refers to the host.

Solution 1: Sudo Into MySQL

The first and most common method to resolve this error is to sudo into your MySQL instance and set the auth_plugin to mysql_native_password.

Open your terminal and run:

$ sudo mysql

Enter the password for the account and proceed.

Next, change the auth_plugin and set a new password for the root account as shown:

ALTER USER '[email protected]' IDENTIFIED WITH mysql-native_password BY 'password';

Changing the authentication plugin will allow you to log in to your server.

Next, flush the privileges to update the grant tables:

FLUSH PRIVILEGES;

Finally, exit the terminal and attempt your login:

$ mysql -u root -p

Solution 2- Manually Edit the Configuration File

If the error persists, you may need to edit the MySQL configuration file. In most cases, it is under the name my.cnf in the /etc or /etc/mysql directories.

Locate this file in the MySQL installation folder for Windows and Mac users.

If the file does not exist, create it.

Open the file with your text editor and locate the mysqld block.

$ nano /etc/mysql/my.cnf

Under the [mysqld] block, add the following entry.

skip-grant-tables

Close and save the file.

Next, restart the MySQL server and log in.

$ sudo service mysql restart

$ mysql -u root -p

Once logged in, flush the privileges and set a new password:

FLUSH PRIVILEGES;

ALTER USER '[email protected]' IDENTIFIED BY 'password';

Ensure to replace the command above with a secure password.

Once completed, edit my.cnf file and remove the entry we added earlier.

Restart the MySQL server and log in with your new password.

Solution 3 – Start MySQL in Safe Mode

MySQL provides you with the mysqld_safe utility for starting the MySQL server. This utility allows us to specify safe features when starting and restarting the server for diagnosing errors.

Start by stopping the MySQL Server.

$ sudo service mysql stop

Next, start MySQL with no authentication as;

$ mysqld_safe --skip-grant-tables &

The command will start the server in the background as a job.

Once the daemon is running (with authentication disabled), open a new terminal and run:

$ mysql -u root -p

The command above should allow you to log in without a password.

Once logged in, flush the privileges and set a new password as:

FLUSH PRIVILEGES;

ALTER USER '[email protected]' IDENTIFIED BY 'password';

Close the session and restart MySQL.

Now log in to your server with your new password.

Closing

In this article, we covered three main methods of resolving the

"mysql access denied for user '[email protected]'" error.

We hope this tutorial helped you resolve this error.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list