MySQL8授权用户远程连接失败,提示ERROR 1410 (42000): You are not allowed to create a user with GRANT

标签:#mysql# 时间:2022/07/31 12:55:02 作者:小木

原来直接用root账户授权远程访问失败,最新的MySQL8不允许直接创建并授权用户远程访问权限,必须先让自己有GRANT权限,然后创建用户,再授权。

正确做法如下:


# 先确保自己有授权权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

# 更新一下权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

# 查询自己是否有权限
mysql> SELECT host,user,Grant_priv,Super_priv FROM mysql.user;
+---------------+------------------+------------+------------+
| host          | user             | Grant_priv | Super_priv |
+---------------+------------------+------------+------------+
| localhost     | root             | Y          | Y          |
+---------------+------------------+------------+------------+
7 rows in set (0.00 sec)

# 创建新用户
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE USER 'root'@'1.1.1.%' IDENTIFIED BY '你的密码';
Query OK, 0 rows affected (0.13 sec)

# 授予新用户权限
mysql> grant all on *.* to 'root'@'1.1.1.%;
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
欢迎大家关注DataLearner官方微信,接受最新的AI技术推送