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

Question

I'm setting up a new server and keep running into this problem.

When I try to login to the MySQL database with the root user, I get the error:

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

It doesn't matter if I connect through the terminal(SSH), through PHPMyAdmin or a MySQL Client, e.g. Navicat. They all fail.

I looked in the mysql.user table and get the following:

+------------------+-------------------+
| user             | host              |
+------------------+-------------------+
| root             | %                 |
| root             | 127.0.0.1         |
| amavisd          | localhost         |
| debian-sys-maint | localhost         |
| iredadmin        | localhost         |
| iredapd          | localhost         |
| mysql.sys        | localhost         |
| phpmyadmin       | localhost         |
| root             | localhost         |
| roundcube        | localhost         |
| vmail            | localhost         |
| vmailadmin       | localhost         |
| amavisd          | test4.folkmann.it |
| iredadmin        | test4.folkmann.it |
| iredapd          | test4.folkmann.it |
| roundcube        | test4.folkmann.it |
| vmail            | test4.folkmann.it |
| vmailadmin       | test4.folkmann.it |
+------------------+-------------------+

As you can see, root should have access.

The Server is quite simple, as I have tried to troubleshoot this for a while now..

It's running Ubuntu 16.04.1 LTS with Apache, MySQL and PHP, so that it can host websites, and iRedMail 0.9.5-1, so that it can host mail.

Login in to the MySQL database works fine before I install iRedMail. I also tried, just installing iRedMail, but then root, also doesn't work...

If someone could tell me how I fix my MySQL login problem or how I install iRedMail, on top of an existing MySQL install. And yes I tried the Installation Tips and I can't find those variables in the config files.

Answer 1

Some systems like Ubuntu, mysql is using by default the UNIX auth_socket plugin.

Basically means that: db_users using it, will be "auth" by the system user credentias. You can see if your root user is set up like this by doing the following:

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------------------+
| User             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

As you can see in the query, the root user is using the auth_socket plugin

There are 2 ways to solve this:

1. You can set the root user to use the mysql_native_password plugin
2. You can create a new db_user with you system_user (recommended)

Option 1:

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ sudo service mysql restart

Option 2: (replace YOUR_SYSTEM_USER with the username you have)

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY 'YOUR_PASSWD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ sudo service mysql restart

Remember that if you use option #2 you'll have to connect to mysql as your system username (mysql -u YOUR_SYSTEM_USER)

Note: On some systems (e.g., Debian stretch) 'auth_socket' plugin is called 'unix_socket', so the corresponding SQL command should be: UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER';

Update: from @andy's comment seems that mysql 8.x.x updated/replaced the auth_socket for caching_sha2_password I don't have a system setup with mysql 8.x.x to test this, however the steps above should help you to understand the issue. Here's the reply:

One change as of MySQL 8.0.4 is that the new default authentication plugin is 'caching_sha2_password'. The new 'YOUR_SYSTEM_USER' will have this auth plugin and you can login from the bash shell now with "mysql -u YOUR_SYSTEM_USER -p" and provide the password for this user on the prompt. No need for the "UPDATE user SET plugin" step. For the 8.0.4 default auth plugin update see, https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

Answer 2

NEW Version of MYSQL does it this way.

In the new my-sql if the password is left empty while installing then it is based on the auth_socket plugin.

The correct way is to login to my-sql with sudo privilege.

$ sudo mysql -u root -p

And then updating the password using:

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

Once this is done stop and start the mysql server.

$  sudo service mysql stop
$  sudo service mysql start

For complete details you can refer to this link.

Answer 3

No need of sudo

The database is initialised with 2 all-privilege accounts: the first one is "root" which is inaccessible and the second one with your user name (check with command whoami).

To enable access to root account, you need to login with your user name

mysql -u $(whoami)

and manually change password for root

use mysql;
set password for 'root'@'localhost' = password('YOUR_ROOT_PASSWORD_HERE');
flush privileges;
quit

Login as 'root'

mysql -u root -p

Answer 4

I was having this issue on an Debian 8 VM that I was interacting with through Putty on my Windows 10 desktop.

I tried the various suggestions on here but nothing quite worked and I am running MariaDB on the Debian host. In the end I found that I couldn't start the db server in safe mode but I didn't need to and the following commands actually worked for me i.e. allowing a newly created MySql user to log into the MySql/MariaDB server:

sudo service mysql restart
sudo mysql # logs in automatically into MariaDB
use mysql;
update user set plugin='' where user='your_user_name';
flush privileges;
exit;
sudo service mysql restart # restarts the mysql service

If the above doesn't quite work for you, follow the steps outlined in zetacu's post above (zetacu) then follow my steps.

Now you should be able to use a remote terminal client and securely log into mysql using the command:

mysql -u your_user_name -p

*type in the password when prompted

Answer 5

After hours of struggle with no solution here, this worked for me then I found a youtube video where it says the password column is now called authentication_string . So I was able to change my password as follows: First get into mysql from terminal

sudo mysql

then inside mysql type whatever after mysql>

mysql> use mysql
mysql> update user set authentication_string=PASSWORD("mypass") where user='root';
mysql> flush privileges;
mysql> quit;

at this point you are out of mysql back to your normal terminal place. You need to restart mysql for this to take effect. for that type the following:

sudo service mysql restart

Refer to this video link for better understanding

Answer 6

step 1. sudo mysql -u root -p

step 2. USE mysql;

step 3. ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin';

Here 'admin' is your new password, yo can change it.

step 4. exit

Thanks. You are done.

Answer 7

I would suggest to remove the Mysql connection -

UPDATE-This is for Mysql version 5.5,if your version is different ,please change the first line accordingly

sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-5.5 mysql-client-core-5.5
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

And Install Again But this time set a root password yourself. This will save a lot of effort.

sudo apt-get update
sudo apt-get install mysql-server

Answer 8

That worked for me:

mysql --user=root mysql
CREATE USER 'some_user'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'some_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Answer 9

There is a good and regularly updated guide on how to set a new password for the latest MySQL.

https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04

It would be best to read whole topic from the link above but in short, this maybe could help,

Run the security script:

sudo mysql_secure_installation

Detailed Info for "mysql_secure_installation"

After that, you can change password by following the next steps

sudo mysql

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

Detailed Info for changin root user password

If you have a problem maybe you will need to reinstall MySql.

Answer 10

First step: go to /etc/phpmyadmin/config.inc.php then uncomment lines where you find AllowNoPassword . Second step: login to your mysql default account

mysql -u root -p
use mysql;
update user set plugin="" where user='root';
flush privilege;

and that's all!

Answer 11

I also faced the same issue at the first time.

Now it is fixed:

First, you copy the /etc/mysql/mysql.conf.d/mysqld.cnf file and past in to /etc/mysql/my.cnf.

You can do it by command:

sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/my.cnf

Now let's Rest the password:

Use the following commands in your terminal:

sudo service mysql stop 
sudo service mysql start
sudo mysql -u root

Now you are inside the mysql console.

Then let's write some queries to reset our root password

USE mysql
update mysql.user set authentication_string=password('newpass') where user='root' and Host ='localhost';
update user set plugin="mysql_native_password"; 
flush privileges;
quit

Now we can clean /etc/mysql/my.cng

Open the above file in your editor and remove the whole lines inside the file.

After that let's restart mysql:

sudo mysql service restart 

Now let's use mysql with newly created password:

sudo mysql -u root -p

Finally enter your newly created password.

Answer 12

os:Ubuntu18.04

mysql:5.7

1. add the skip-grant-tables to the file end of mysqld.cnf

2. cp the my.cnf

sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/my.cnf

2. reset the password

(base) ➜  ~ sudo service mysql stop 
(base) ➜  ~ sudo service mysql start
(base) ➜  ~ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed, 3 warnings
mysql> update mysql.user set authentication_string=password('newpass') where user='root' and Host ='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> update user set plugin="mysql_native_password"; 
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4  Changed: 0  Warnings: 0

mysql>  flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

3. remove theskip-grant-tables from my.cnf

(base) ➜  ~ sudo emacs /etc/mysql/mysql.conf.d/mysqld.cnf 
(base) ➜  ~ sudo emacs /etc/mysql/my.cnf                 
(base) ➜  ~ sudo service mysql restart

4. open the mysql

(base) ➜  ~ mysql -uroot -ppassword 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> 

5. check the password policy

mysql> select @@validate_password_policy;
+----------------------------+
| @@validate_password_policy |
+----------------------------+
| MEDIUM                     |
+----------------------------+
1 row in set (0.00 sec)


mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
6 rows in set (0.08 sec)！

6. change the config of the validate_password

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.05 sec)

mysql> set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_number_count=3;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_special_char_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=3;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_dictionary_file    |       |
| validate_password_length             | 3     |
| validate_password_mixed_case_count   | 0     |
| validate_password_number_count       | 3     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 0     |
+--------------------------------------+-------+
6 rows in set (0.00 sec)

note you should know that you error caused by what? validate_password_policy?

you should decided to reset the your password to fill the policy or change the policy.

Answer 13

I found my solution after hours of research here.

Stop MySQL

sudo service mysql stop

Make MySQL service directory.

sudo mkdir /var/run/mysqld

Give MySQL user permission to write to the service directory.

sudo chown mysql: /var/run/mysqld

Start MySQL manually, without permission checks or networking.

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

Log in without a password.

 mysql -uroot mysql

update password

UPDATE mysql.user SET authentication_string=PASSWORD('YOURNEWPASSWORD'), plugin='mysql_native_password' WHERE User='root' AND Host='%';
EXIT;

Turn off MySQL.

sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown

Start the MySQL service normally.

sudo service mysql start

Answer 14

The first

sudo mysql -u root -p

SHOW VARIABLES LIKE 'validate_password%';

we will see something like this:

+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+

we need to change these rows

1. validate_password.length
2. validate_password.number_count
3. validate_password.policy
4. validate_password.special_char_count

SET GLOBAL validate_password.policy=LOW;
SET GLOBAL validate_password.length=4;
SET GLOBAL validate_password.number_count=0;
SET GLOBAL validate_password.special_char_count=0;

SHOW VARIABLES LIKE 'validate_password%';

// we will see

+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 4     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 0     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 0     |
+--------------------------------------+-------+

now exit from MySQL

exit;
sudo mysql -u root -p

and now you can write your password 4 or more only letters.

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

exit;

sudo mysql -u root -p

your new password is in DB for user 'root';

Answer 15

You want to access MySQL with root user but you're not providing root's correct password.

If you need to set a new password for root, MySQL's site has great documentation on how to do it: http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

I'll not show the process in here because MySql documentation on the above link it's clear and concise.

Answer 16

This has happened to me as well. The problem is with the mysql repo that comes already with the linux distro. So when you simply do: $ sudo apt install mysql-server it installs mysql from their default repo which gives this problem. So to overcome that you need to uninstall that installed mysql $ sudo apt remove mysql* --purge --auto-remove

Then download mysql repo from official mysql website MySQL APT Repo Follow their documentation on how to add repo and install it. This gives no issue. Also as answered by @zetacu, you can verify that mysql root now indeed uses mysql_native_password plugin

Answer 17

in my case,

dev@Dev-007:~$ mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I am sure my password was correct otherwise error code would be ERROR 1045 (28000): Access denied for user

so i relogin using sudo,

dev@Dev-007:~$ sudo mysql -u root -p

this time it worked for me . see the docs

and then change root password,

mysql> alter user 'root'@'%' identified with mysql_native_password by 'me123';
Query OK, 0 rows affected (0.14 sec)

mysql> 

then restart server using sudo /etc/init.d/mysql restart

Answer 18

I have done the following steps to get rid of this issue. Login into the MySQL in your machine using (sudo mysql -p -u root) and hit the following queries.

1. CREATE USER 'jack'@'localhost' IDENTIFIED WITH mysql_native_password BY '<<any password>>';

2. GRANT ALL PRIVILEGES ON *.* TO 'jack'@'localhost';

3. SELECT user,plugin,host FROM mysql.user WHERE user = 'root';
+------+-------------+-----------+
| user | plugin      | host      |

+------+-------------+-----------+

| root | auth_socket | localhost |

+------+-------------+-----------+

4. ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<<any password>>';

5. FLUSH PRIVILEGES;

Please try it once if you are still getting the error. I hope this code will help you a lot !!

Answer 19

for anyone that tried the solution here and nothing works, make sure you are using the correct command, sudo sudo mysql -u root -p and not mysql mysql -u root -p, you'll need to enter two passwords the one of the current user and the root one.

Answer 20

Let me solve it for ya.

• Get into the sql.
• Enter the following thing.

SET GLOBAL validate_password.length = 4;
SET GLOBAL validate_password.mixed_case_count = 0;
SET GLOBAL validate_password.number_count = 0;
SET GLOBAL validate_password.policy = LOW;
SET GLOBAL validate_password.special_char_count = 0;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

Only for testing purposes.