[mysql] MySQL Error: : 'Access denied for user 'root'@'localhost'

$ ./mysqladmin -u root -p 'redacted'
Enter password:

mysqladmin: connect to server at 'localhost' failed error:
'Access denied for user 'root'@'localhost' (using password: YES)'

How can I fix this?

I tried many steps to get this issue corrected. There are so many sources for possible solutions to this issue that is is hard to filter out the sense from the nonsense. I finally found a good solution here:

Step 1: Identify the Database Version

$ mysql --version

You'll see some output like this with MySQL:

$ mysql  Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using  EditLine wrapper

Or output like this for MariaDB:

mysql  Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Make note of which database and which version you're running, as you'll use them later. Next, you need to stop the database so you can access it manually.

Step 2: Stopping the Database Server

To change the root password, you have to shut down the database server beforehand.

You can do that for MySQL with:

$ sudo systemctl stop mysql

And for MariaDB with:

$ sudo systemctl stop mariadb

Step 3: Restarting the Database Server Without Permission Checking

If you run MySQL and MariaDB without loading information about user privileges, it will allow you to access the database command line with root privileges without providing a password. This will allow you to gain access to the database without knowing it.

To do this, you need to stop the database from loading the grant tables, which store user privilege information. Because this is a bit of a security risk, you should also skip networking as well to prevent other clients from connecting.

Start the database without loading the grant tables or enabling networking:

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

The ampersand at the end of this command will make this process run in the background so you can continue to use your terminal.

Now you can connect to the database as the root user, which should not ask for a password.

$ mysql -u root

You'll immediately see a database shell prompt instead.

MySQL Prompt

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

MariaDB Prompt

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Now that you have root access, you can change the root password.

Step 4: Changing the Root Password

mysql> FLUSH PRIVILEGES;

Now we can actually change the root password.

For MySQL 5.7.6 and newer as well as MariaDB 10.1.20 and newer, use the following command:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

For MySQL 5.7.5 and older as well as MariaDB 10.1.20 and older, use:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

Make sure to replace new_password with your new password of choice.

Note: If the ALTER USER command doesn't work, it's usually indicative of a bigger problem. However, you can try UPDATE ... SET to reset the root password instead.

[IMPORTANT] This is the specific line that fixed my particular issue:

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('new_password') WHERE User = 'root' AND Host = 'localhost';

Remember to reload the grant tables after this.

In either case, you should see confirmation that the command has been successfully executed.

Query OK, 0 rows affected (0.00 sec)

The password has been changed, so you can now stop the manual instance of the database server and restart it as it was before.

Step 5: Restart the Database Server Normally

The tutorial goes into some further steps to restart the database, but the only piece I used was this:

For MySQL, use: $ sudo systemctl start mysql

For MariaDB, use:

$ sudo systemctl start mariadb

Now you can confirm that the new password has been applied correctly by running:

$ mysql -u root -p

The command should now prompt for the newly assigned password. Enter it, and you should gain access to the database prompt as expected.

Conclusion

You now have administrative access to the MySQL or MariaDB server restored. Make sure the new root password you choose is strong and secure and keep it in safe place.

Ugh- nothing worked for me! I have a CentOS 7.4 machine running mariadb 5.5.64.

What I had to do was this, right after installation of mariadb from yum;

# systemctl restart mariadb
# mysql_secure_installation

The mysql_secure_installation will take you through a number of steps, including "Set root password? [Y/n]". Just say "y" and give it a password. Answer the other questions as you wish.

Then you can get in with your password, using

# mysql -u root -p

It will survive

# systemctl restart mariadb

The Key

Then, I checked the /bin/mysql_secure_installation source code to find out how it was magically able to change the root password and none of the other answers here could. The import bit is:

do_query "UPDATE mysql.user SET Password=PASSWORD('$esc_pass') WHERE User='root';"

...It says SET Password=... and not SET authentication_string = PASSWORD.... So, the proper procedure for this version (5.5.64) is:

login using mysql -u root -p , using the password you already set.
Or, stop the database and start it with:
mysql_safe --skip-grant-tables --skip-networking &

From the mysql> prompt:

use mysql;
select host,user,password from user where user = 'root';
(observe your existing passwords for root).
UPDATE mysql.user set Password = PASSWORD('your_new_cleartext_password') where user = 'root' AND host = 'localhost';
select host,user,password from user where user = 'root';
flush privileges;
quit;

kill the running mysqld_safe. restart mariadb. Login as root: mysql -u -p. Use your new password.

If you want, you can set all the root passwords at once. I think this is wise:

mysql -u root -p
(login)
use mysql;
select host,user,password from user where user = 'root';
UPDATE mysql.user set Password = PASSWORD('your_new_cleartext_password') where user = 'root';
select host,user,password from user where user = 'root';
flush privileges;
quit;

This will perform updates on all the root passwords: ie, for "localhost", "127.0.0.1", and "::1"

In the future, when I go to RHEL8 or what have you, I will try to remember to check the /bin/mysql_secure_installation and see how the guys did it, who were the ones that configured mariadb for this OS.

If you are like me and all the above suggestions failed, proceed to uninstall all versions of mysql on your machine, search for all remaining mysql files using this command sudo find / -name "mysql" and rm -rf every file or directory with the mysql name attached to it (you should skip files related to programming language libraries). Now install a fresh version of MySQL and enjoy. NB: You will loose all your data so weigh your options first.

Although a old one, I was getting the same error while setting up the mysql-8 zip version. Finally, switched to installer version which worked seamlessly. During installation, there is a prompt to setup the root password. Once set, it works for sure.

Solution: Give up!

Hear me out, I spent about two whole days trying to make MySQL work to no avail, always stuck with permission errors, none of which were fixed by the answers in this thread. It got to the point that I thought if I continued I'd go insane.

Out of patience for making it work, I sent the command to install SQLite, only using 450KB, and it worked perfectly right from the word go.

If you don't have the patience of a saint, go with SQLite and save yourself a lot of time, effort, pain, and storage space..!

Fix for Mac OS

1. Install MySQL from https://downloads.mysql.com/archives/community/ (8.x is latest as on date but ensure that the version is compatible with the Mac OS version)

2. Give password for root (let <root-password> be the password) during installation (Don't forget to remember the password!)

3. Select Use Legacy Password Encryption option (that is what I had used and did not try for Use Strong Password Encryption option)

4. Search and open MySQL.prefPane (use search tool)

   1. Select Configuration tab
   2. Click Select option of Configuration File
      1. Select /private/etc/my.cnf

5. From terminal open a new or existing file with name /etc/my.cnf (vi /etc/my.cnf) add the following content:

    [mysqld]
    skip-grant-tables
   

6. Restart mysqld as follows:

   1. ps aux | grep mysql
   2. kill -9 <pid1> <pid2> ... (grab pids of all mysql related processes)

7. mysqld gets restarted automatically

8. Verify that the option is set by running the following from terminal:

    ps aux | grep mysql
   
    > mysql/bin/mysqld ... --defaults-file=/private/etc/my.cnf ... (output)
   

9. Run the following command to connect (let mysql-<version>-macos<version>-x86_64 be the folder where mysql is installed. To grab the actual folder, run ls /usr/local/ and copy the folder name):

    /usr/local/mysql-<version>-macos<version>-x86_64/bin/mysql -uroot -p<root-password>
   

For Ubuntu/Debian users

(may work on other distros especially debian based ones)

Run the following to connect as root (without any password)

sudo /usr/bin/mysql --defaults-file=/etc/mysql/debian.cnf

If you don't want to add --defaults-file each time you want to connect as root, you can copy /etc/mysql/debian.cnf into your home directory:

sudo cp /etc/mysql/debian.cnf ~/.my.cnf

and then:

sudo mysql

For new linux users this could be a daunting task. Let me update this with mysql 8(the latest version available right now is 8.0.12 as on 12-Sep-2018)

1. Open "mysqld.cnf" configuration file at "/etc/mysql/mysql.conf.d/".
2. Add skip-grant-tables to the next line of [mysql] text and save.
3. Restart mysql service as "sudo service mysql restart". Now your mysql is free of any authentication.
4. Connect to mysql client(also known as mysql-shell) as mysql -u root -p. There is no password to be keyed in as of now.
5. run sql command flush privileges;
6. Reset the password now as ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPassword';
7. Now let's get back to the normal state; remove that line "skip-grant-tables" from "mysqld.cnf" and restart service.

That's it.

All solutions I found were much more complex than necessary and none worked for me. Here is the solution that solved my problem. No need to restart mysqld or start it with special privileges.

sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

With a single query we are changing the auth_plugin to mysql_native_password and setting the root password to root (feel free to change it in the query)

Now you should be able to login with root. More information can be found in mysql documentation

(exit mysql console with Ctrl + D or by typing exit)

In your MySQL workbench, you can go to the left sidebar, under Management select "Users and Privileges", click root under User Accounts, the in the right section click tab "Account Limits" to increase the max queries, updates, etc, and then click tab "Administrative Roles" and check the boxes to give the account access. Hope that helps!

In my experience, if you run without sudo it will not work. So make sure your command is;

sudo mysql -uroot -p

After trying all others answers, this it what finally worked for me

sudo mysql -- It does not ask me for any password

-- Then in MariaDB/MySQL console:
update mysql.user set plugin = 'mysql_native_password' where User='root';
FLUSH PRIVILEGES;
exit;

I found the answer in this post : https://medium.com/@chiragpatel_52497/solved-error-access-denied-for-user-root-localhost-of-mysql-programming-school-6e3611838d06

In my case under Debian 10, the error

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

was solved by (GOOD WAY)

sudo mysql -u root -p mysql

BAD WAY:

mysql -u root -p mysql

None of the above answers helped me with this problem, so here's the solution I found.

The relevant part:

In Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. This allows for some greater security and usability in many cases, but it can also complicate things when you need to allow an external program (e.g., phpMyAdmin) to access the user.

In order to use a password to connect to MySQL as root, you will need to switch its authentication method from auth_socket to mysql_native_password. To do this, open up the MySQL prompt from your terminal:

sudo mysql

Next, check which authentication method each of your MySQL user accounts use with the following command:

SELECT user,authentication_string,plugin,host FROM mysql.user;

Output

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

In this example, you can see that the root user does in fact authenticate using the auth_socket plugin. To configure the root account to authenticate with a password, run the following ALTER USER command. Be sure to change password to a strong password of your choosing, and note that this command will change the root password you set in Step 2:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Then, run FLUSH PRIVILEGES which tells the server to reload the grant tables and put your new changes into effect:

FLUSH PRIVILEGES;

Check the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin:

SELECT user,authentication_string,plugin,host FROM mysql.user;

Output

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *3636DACC8616D997782ADD0839F92C1571D6D78F | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

You can see in this example output that the root MySQL user now authenticates using a password. Once you confirm this on your own server, you can exit the MySQL shell:

exit

Okay, I know this is an old thread but if you reached this page via Google like I did and none of the above solutions worked, what turned out to be the error was 100% foolishness on my end. I didn't connect to the server. Once connected everything was smooth sailing.

In case it helps to know my setup, I'm using Sequel Pro and trying to connect to it with Node using the NPM package, mysql. I didn't think I needed to actually connect (other than run Sequel Pro) because I was doing that from my app already.

It can happen if you don't have enough privileges. Type su, enter root password and try again.

I did this to set my root password in initial set up of MySQL in OSx. Open a terminal.

sudo sh -c 'echo /usr/local/mysql/bin > /etc/paths.d/mysql'

Close the terminal and open a new terminal. And followings are worked in Linux, to set root password.

sudo /usr/local/mysql/support-files/mysql.server stop
sudo mysqld_safe --skip-grant-tables

(sudo mysqld_safe --skip-grant-tables : This did not work for me in first time. But second try, out was success.)

Then login to MySQL

mysql -u root

FLUSH PRIVILEGES;

Now change the password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

Restart MySQL:

sudo /usr/local/mysql/support-files/mysql.server stop
sudo /usr/local/mysql/support-files/mysql.server start