mysql8设置远程访问You have an error in your SQL syntax

问题描述

docker中mysql8的容器无法用下面的命令来设置 root 远程访问

通过容器的 mysql 命令行工具连

docker exec -it mysql mysql -uroot -p123456

grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
flush privileges;

报错：

mysql> grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'root' with grant option' at line 1

---

解决方案：

mysql5和mysql8开启远程访问的方式不一样

mysql5

grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
flush privileges;

mysql8

mysql> use mysql;

mysql> update user set user.Host=’%’ where user.User=‘root’;

mysql> flush privileges;

mysql> select host,user from user;

遇到这样的报错不用管：Duplicate entry ‘%-root’ for key ‘user.PRIMARY’

mysql> update user set user.Host ='%' where user.User='root';
ERROR 1062 (23000): Duplicate entry '%-root' for key 'user.PRIMARY'

只要有一个名为root的用户host为%就行

mysql> select host, user from user where user = 'root';
+-----------+------+
| host      | user |
+-----------+------+
| %         | root |
| localhost | root |
+-----------+------+