0%

ubuntu下mysql5.7安装后无法远程登录问题

Posted on Edited on In

在ubuntu18.04上安装mysql5.7后通过workbench连接和×mysql -u root -p 命令连接均无法连接。命令行显示如下错误：

1
2
3
lazycece@cc-ubuntu:~$ mysql -u root -p 
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

通过 sudo mysql进行访问，进入命令行查看用户信息：

1
2
3
4
5
6
7
8
9
10
mysql> select user,host,plugin from mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | localhost | auth_socket           |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| debian-sys-maint | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)

可以看到此时root用户的认证方式为auth_socket，与其他不一样，原因是在使用ubuntu安装时，mysql默认使用了UNIX auth_socket plugin插件，也就是说着当db_users使用数据库时，将会通过系统用户认证表进行认证。，可参考官方文档

那么如何解决呢？可参考ERROR 1698 (28000): Access denied for user ‘root’@’localhost’,有如下两种方案，设置后，service mysql restart即可。

  • 修改root用户的认证方式 
1
2
3
4
5
mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
mysql> \q
  • 创建一个与系统用户一致的新的数据库用户 
1
2
3
4
5
6
mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY '';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> \q;