MySQL重建某个用户账号

概述

在MySQL5.7中删除用户信息后无法重建ymcc用户，提示报错ERROR 1396 (HY000): Operation CREATE USER failed

查询数据库用户表

通过查询用户发现已无ymcc用户

mysql> select Host,User from mysql.user;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| localhost | admin         |
+-----------+---------------+

1 rows in set (0.00 sec)

新建ymcc用户

mysql> create user "ymcc"@"%" identified by "JKLASDJKL";
ERROR 1396 (HY000): Operation CREATE USER failed for 'ymcc'@'%'

由于之前删除ymcc用户时是通过delete命令删除的

mysql> delete from mysql.user where user="ymcc" and host ="%";
mysql> flush privileges;

删除ymcc用户

查了下资料，发现如果要彻底不使用某个用户，推荐使用drop命令进行删除

mysql> drop user ymcc@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

DROP USER不会自动中止已连接的用户会话，也就是说被删的用户如果在删前已经连接上了服务器，并且连接尚未中断，那它此时还能继续执行一定的操作，只是它的身份已经变成了黑户。

重建ymcc的用户

mysql> create user "ymcc"@"%" identified by "JKLASDJKL";
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host from mysql.user;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| %         | ymcc          |
| localhost | admin         |
+-----------+---------------+
2 rows in set (0.00 sec)

END