MySQL Error: : 'Access denied for user 'root'@'localhost'

Question

Consider:

./mysqladmin -u root -p** '_redacted_'  

Output (including typing the password):

Enter password:

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

How can I fix this?

Answer 1

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

sudo mysql

-- for MySQL
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

-- for MariaDB
ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('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 log in with root. More information can be found in MySQL documentation or MariaDB documentation.

(Exit the MySQL console with Ctrl + D or by typing exit.)

Answer 2

None of the previous 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

Answer 3

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.

Answer 4

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 the blog post Solved: Error “Access denied for user ‘root’@’localhost’” of MySQL — codementor.tech (Medium).

Answer 5

For Ubuntu/Debian users

(It may work on other distributions, 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

Answer 6

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

sudo mysql -uroot -p

Answer 7

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 2018-09-12)

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 the MySQL service as "sudo service mysql restart". Now your MySQL is free of any authentication.
4. Connect to the 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 the service.

That's it.

Answer 8

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

Answer 9

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

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

Close the terminal and open a new terminal.

And the following worked in Linux, to set the 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 the first time. But on the second try, it was a success.)

Then log into 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

Answer 10

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

Answer 11

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

I had to do 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:

Log in 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. Log in 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: i.e., for "localhost", "127.0.0.1", and "::1"

In the future, when I go to RHEL 8 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.

Answer 12

Use sudo to alter your password:

sudo mysql

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

Source: Phoenixnap - Access denied for user root localhost

Answer 13

Fix for macOS

1. Install MySQL from https://downloads.mysql.com/archives/community/ (8.x is the latest as on date, but ensure that the version is compatible with the macOS 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>
   

Answer 14

If you are like me and all the information in previous answers failed, proceed to uninstall all versions of MySQL on your machine, search for all remaining MySQL files using the 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 lose all your data so weigh your options first.

Answer 15

My Station here:

• UBUNTU 21.04

• PHP 5.6.40-57

• MYSQL 5.7.37

let's config it

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

at the bottom, write this

skip-grant-tables

reload it

service mysql restart

Answer 16

Sometimes a default password is set when you install it - as mentioned in the documentation. This can be confirmed by the following command.

sudo grep 'temporary password' /var/log/mysqld.log

Answer 17

It can happen if you don't have enough privileges.

Type su, enter the root password and try again.

Answer 18

windows :

cd \Ampps\mysql\bin :

mysql.exe -u root -pmysql

after mysql start (you can see shell like this mysql> ) use this query :

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

try again access with root root

Answer 19

If you reached this page via Google like I did and none of the previous 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 am 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 application already.

Answer 20

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 set up the root password. Once set, it works for sure.

Answer 21

According to MariaDB official documentation, in MariaDB 10.4.3 and later, the unix_socket authentication plugin is installed by default.

In order to disable it, and revert to the previous mysql_native_password authentication method, add line below in [mysqld] section of my.cnf file:

[mysqld]
unix_socket=OFF

And then run:

mysql_install_db --auth-root-authentication-method=normal

And then start mysqld

This command will then work fine:

mysqladmin -u root password CHANGEME

For additional information, see Configuring mysql_install_db to Revert to the Previous Authentication Method.

Answer 22

I was trying to leverage Docker desktop on Mac to get 5.7.35 running and this docker-compose.yml configuration allowed it to work:

In particular it was the addition of the line...

command: --default-authentication-plugin=mysql_native_password

...that did the trick

version: '3.3'
services:
  mysql_db:
    image: mysql:5.7
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: 'your_password'
    ports:
      - '3306:3306'
    expose:
      - '3306'
    volumes:
      - ~/your/volume/path:/var/lib/mysql

Answer 23

After trying a lot with the following answer:

ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('root');

And similar answers, my terminal was still throwing me the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near...

So after researching on the web, this line solved my problem and let me change the root user password:

sudo mysqladmin --user=root password "[your password]"

Answer 24

One thing to check is the from-host filter. It may be "localhost" by default. Are you trying to connect from a remote client? Change this to "%".

Answer 25

The '-p' argument doesn't expect a space between the argument name and value.

Instead of

./mysqladmin -u root -p 'redacted'

Use

./mysqladmin -u root -p'redacted'

Or just

./mysqladmin -u root -p

which will prompt you for a password.