Thursday, August 11, 2016

Can't access MySQL 5.7 as root - Error 1698 (28000)

MySQL 5.7+ isn't allowed to login as root user without sudo because that secure model has been changed. The results of login appears ERROR 1698 (28000) as following output;

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

Skip grant tables to login

Stop the MySQL service for starting mysqld_safe.

$ sudo service mysql stop

Start the MySQL as safe mode with --skip-grant-tables to login without verification.

$ sudo mysqld_safe --skip-grant-tables

Connect to the MySQL as root.

$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 94
Server version: 5.7.14 MySQL Community Server (GPL)

Update mysql.user

It should be that the Plugin of root user sets auth_socket. The Plugin value will be modified to mysql_native_password for compatible former MySQL.

mysql> SELECT User, Host, Plugin FROM mysql.user;
+-----------+-----------+-----------------------+
| User      | Host      | Plugin                |
+-----------+-----------+-----------------------+
| root      | localhost | auth_socket           |
| mysql.sys | localhost | mysql_native_password |
+-----------+-----------+-----------------------+
2 rows in set (0.00 sec)
mysql> UPDATE mysql.user SET authentication_string = PASSWORD ('[root-password]'), Plugin = 'mysql_native_password' WHERE User = 'root';
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Restart the MySQL

Restart the MySQL service and then enter via mysql-client after typed FLUSH PRIVILEGES. It should be solved.

$ mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 94
Server version: 5.7.14 MySQL Community Server (GPL)

That's it!

No comments:

Post a Comment