mysql ERROR 1396 (HY000): Operation CREATE USER failed 解决办法

原因：MySQL账户表中已经存在这个要创建的用户

操作分析：

当创建新用户时会提示这个新用户创建失败，但是当解决创建失败的问题后再次重新创建这个新用户，则会报这个错误

# 创建新用户，提示root用户没有SYSTEM_USER权限，创建失败

mysql> create user 'proxysql'@'192.168.20.%' identified  with mysql_native_password by 'iD!^^EjU#Yxr5$p';

ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

# 解决root用户没有SYSTEM_USER权限的问题

mysql> grant system_user on *.* to 'root';

Query OK, 0 rows affected (0.00 sec)

# 再次创建该用户提示报错

mysql> create user 'proxysql'@'192.168.20.%' identified  with mysql_native_password by 'iD!^^EjU#Yxr5$p';

ERROR 1396 (HY000): Operation CREATE USER failed for 'proxysql'@'192.168.20.%'

# 实际情况是在第一次创建时，虽然提示root用户没有SYSTEM_USER权限，但是mysql.user表中已经有这个用户了

# 解决办法，删除这个用户信息，然后重新创建即可

mysql> drop user 'proxysql'@'192.168.20.%'

mysql> delete from mysql.user where user = 'proxysql'@'192.168.20.%';

mysql> delete from mysql.db where user = 'proxysql'@'192.168.20.%';

mysql> flush privileges;

mysql> create user 'proxysql'@'192.168.20.%' identified  with mysql_native_password by 'iD!^^EjU#Yxr5$p';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT USAGE,process,replication slave,replication client ON *.* TO 'proxysql'@'192.168.20.%' with grant option;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

巴特西

mysql ERROR 1396 (HY000): Operation CREATE USER failed 解决办法

最新文章

热门文章