Change MySQL Server authentication plugin for root user

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!

Error when login to mysql root user from normal linux user account.

khanh@server:~$ mysql -uroot -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

But it is ok when we switch to linux root account

khanh@server:~$ sudo su -
khanh@server:~# mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.7.22-0ubuntu18.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

Let’s check the current authentication plugin that MySQL server is using.

mysql> SELECT plugin from mysql.user where User='root';
+-----------------------+
| plugin                |
+-----------------------+
| auth_socket           |
+-----------------------+

To be able to login with password, you have to change the plugin from auth_socket to 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;