原因: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)