Mysql
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:
- 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 is new installation
mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;
$ 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 '';
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;
$ 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
Comments