Start from MySQL Server 5.7, if we do not provide a password to root user during the installation, it will use auth_socket plugin for authentication. With this configuration, MySQL won’t care about your input password, it will check the user is connecting using a UNIX socket and then compares the username. If it is match, you are authenticated!
[email protected]:~$ mysql -uroot -p
But it is ok when we switch to linux root account
[email protected]:~$ sudo su -
Let’s check the current authentication plugin that MySQL server is using.
mysql> SELECT plugin from mysql.user where User='root';
To be able to login with password, you have to change the plugin from
mysql_native_password. Following is the command to do that.
mysql> UPDATE mysql.user SET plugin = 'mysql_native_password', authentication_string = PASSWORD('changeme') WHERE User = 'root';
mysql> FLUSH PRIVILEGES;