MySQL started using system accounts to accept connections in version 5.7 using the auth_socket password plugin. It might be required to connect to the MySQL Server using the root account with a password using the mysql_native_password option. We can change the default behavior of the root account to use the native password using the commands as shown below.
# Login to MySQL sudo mysql
# Check password scheme of root user SELECT user,authentication_string,plugin,host FROM mysql.user;
# Note the password plugin of root user
+------------------+-------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | | auth_socket | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
# Change password plugin of root user ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<pw>';
# Apply changes flush privileges;
# Check password scheme of root user SELECT user,authentication_string,plugin,host FROM mysql.user;
# Note the password plugin of root user
+------------------+-------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+-------------------------------------------+-----------------------+-----------+
| root | *E5C4F73D963132CEF9BB4PA79LA818C08BAQC300 | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
This is how we can use the native password plugin for a MySQL user.
