ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Some systems like Ubuntu, MySQL is using the UNIX auth_socket plugin by default.
Basically it means that: db_users using it, will be "authenticated" by the system user credentials. 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 it was a 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 two ways to solve this:
- You can set the root user to use the
mysql_native_password
plugin - You can create a new
db_user
with yousystem_user
(recommended)
Option 1:
sudo mysql -u root # I had to use "sudo" since it was 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 9 (Stretch)) the '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';
From @andy's comment it 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 authentication plugin and you can log in from the Bash shell now with "mysql -u YOUR_SYSTEM_USER -p" and provide the password for this user on the prompt. There isn’t any need for the "UPDATE user SET plugin" step.
For the 8.0.4 default authentication plugin update, see https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/
A 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.
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