On 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 a 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 it was a 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 MySQL 8.0.4: New Default Authentication Plugin: caching_sha2_password.