mysql 8 设置允许远程连接 You are not allowed to create a user with GRANT

向右看齐 2022-02-28 00:18 549阅读 0赞

**1.登录mysql : **  mysql -u root -p

**2.输入密码：**    Enter password:  xxxxx

------ Server version: 8.0.15 MySQL Community Server - GPL

**3.进入mysql数据库：**use mysql;

**4.设置允许远程用户访问：**

MySQL \[mysql\]> GRANT ALL ON \*.\* TO 'root'@'%'

出现问题：ERROR 1410 (42000): You are not allowed to create a user with GRANT

原因：当前user表中没有root - %记录； 可以更新root - localhost 为 root - %

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L214c2t5bXg_size_16_color_FFFFFF_t_70][]

MySQL \[mysql\]> update user set host = '%' where user = 'root';  
出现问题：ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'

原因显示：host+user 应该是联合主键，冲突了

**5.解决方法：**

MySQL \[mysql\]> update user set host = '%' where user = 'root' and host='localhost';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L214c2t5bXg_size_16_color_FFFFFF_t_70 1][]

**6.再次给用户root授权**

MySQL \[mysql\]> GRANT ALL ON \*.\* TO 'root'@'%'

MySQL \[mysql\]> flush privileges;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L214c2t5bXg_size_16_color_FFFFFF_t_70 2][]

此时用navicat连接还是报错：Client does not support authentication protocol requested by server;

原因是mysql8默认的加密方式为caching\_sha2\_password 与mysql5的加密方式mysql\_native\_password 不同

**7.解决方法-更新用户加密方式：**

MySQL \[mysql\]> ALTER USER 'root'@'%' IDENTIFIED WITH mysql\_native\_password BY '密码';

查询一下修改结果：MySQL \[mysql\]> select host,user,plugin from user;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L214c2t5bXg_size_16_color_FFFFFF_t_70 3][]

**其它：如果需要支持 root - localhost可以使用插入语句**

MySQL \[mysql\]> insert user (user, host, ssl\_cipher, x509\_issuer, x509\_subject) values('root', 'localhost', '', '', '');

再查看：（注意 ssl\_cipher, x509\_issuer, x509\_subject这几个字段没有默认值，不设置会提示错误）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L214c2t5bXg_size_16_color_FFFFFF_t_70 4][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L214c2t5bXg_size_16_color_FFFFFF_t_70]: /images/20220228/c198a514af8c470e9b83ca4330e00513.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L214c2t5bXg_size_16_color_FFFFFF_t_70 1]: /images/20220228/95c3feaa928d45b5bfece60241535f2d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L214c2t5bXg_size_16_color_FFFFFF_t_70 2]: /images/20220228/f2e72ea79481438bb1000c659373dd2c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L214c2t5bXg_size_16_color_FFFFFF_t_70 3]: /images/20220228/db82bb81336641e09605aecd9ee564a9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L214c2t5bXg_size_16_color_FFFFFF_t_70 4]: /images/20220228/46344bafc5c74e36bf8360a867fd01d8.png