deepin/ubantu下 mysql 1698错误解决

deepin/ubantu下 mysql 1698错误解决

~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user ‘root’@‘localhost’

1. 首先切换deepin的超级管理员用户

   sudo su
   

2. MySQL启动之后就可以不用密码登陆了。

   mysql -u root
   

3. 查看一下user表，错误的起因就是在这里， root的plugin被修改成了auth_socket，用密码登陆的plugin应该是mysql_native_password。

   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)
   
   

4. 关于auth_socket，在官方有说明： https://dev.mysql.com/doc/mysql-security-excerpt/5.5/en/socket-authentication-plugin.html ，反正现在暂时不用它， 那就把这里改了。

   mysql> UPDATE mysql.user SET authentication_string=PASSWORD('123456'), 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)
   

5. 切换到普通用户并登录

	 su 用户
	 user@user-PC:~/Desktop$ mysql -uroot -p
	 Enter password: 
	 
	 Welcome to the MySQL monitor.  Commands end with ; or \g.
	 Your MySQL connection id is 2
	 Server version: 5.7.21-1 (Debian)
	
	 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>