mysql创建数据库表失败

问题

前段时间，开发人员找我咨询一个MySQL数据库的问题，问题大概是这样的，在MySQL数据库里创建一个应用访问用户，可是命令执行一直失败，创建用户的语法没有问题，从mysql.user表里查询，要创建的用户也不存在，很诡异，于是就找到了我。

这个问题其实很简单，不难，但是比较有意思，在这里写出来给大家分享一下

问题复盘

这个问题原因就是，在MySQL数据库的mysql.user表里的记录，与MySQL数据库实例内存中的acl_users数组数据不一致，而引起的问题。

那么在这里，先创建一个测试应用用户

[root@localhost] 14:58:26 [(none)]>ｓｅｌｅｃｔ user,host from mysql.user;+---------------+---------------+| user| host|+---------------+---------------+| monitor| % || repl| % || tony| % || mha| 192.168.112.% || mysql.session | localhost || mysql.sys | localhost || root| localhost |+---------------+---------------+7 rows in set (0.00 sec)[root@localhost] 15:01:49 [(none)]>create user ‘tim‘@‘%‘ identified by ‘tim1234‘;Query OK, 0 rows affected (0.28 sec)[root@localhost] 15:03:15 [(none)]>ｇｒａｎｔ ALL PRIVILEGES ON *.* TO ‘tim‘@‘%‘;Query OK, 0 rows affected (0.00 sec)[root@localhost] 15:03:20 [(none)]>ｓｅｌｅｃｔ user,host from mysql.user;+---------------+---------------+| user| host|+---------------+---------------+| monitor| % || repl| % || tim| % || tony| % || mha| 192.168.112.% || mysql.session | localhost || mysql.sys | localhost || root| localhost |+---------------+---------------+8 rows in set (0.00 sec)[root@localhost] 15:03:31 [(none)]>flush privileges;

在这里，测试用户tim已经创建好了，而且可以正常连接MySQL数据库。

删除测试用户tim

好了，到了关键步骤了，在这里要用ｄｅｌｅｔｅ语句来删除用户tim

[root@localhost] 15:05:07 [(none)]>ｄｅｌｅｔｅ from mysql.user where user=‘tim‘ and host=‘%‘;Query OK, 1 row affected (0.09 sec)[root@localhost] 15:05:17 [(none)]>ｓｅｌｅｃｔ user,host from mysql.user;+---------------+---------------+| user| host|+---------------+---------------+| monitor| % || repl| % || tony| % || mha| 192.168.112.% || mysql.session | localhost || mysql.sys | localhost || root| localhost |+---------------+---------------+7 rows in set (0.00 sec)

可以看到tim用户已经从mysql.user表里删除了，那么这里有一个猜想，这个时候tim用户还能正常登录吗？

[root@localhost] 15:22:06 [(none)]>ｄｅｌｅｔｅ from mysql.user where user=‘tim‘ and host=‘%‘;Query OK, 1 row affected (0.00 sec)[root@localhost] 15:24:22 [(none)]>quitBye[mysql@node131 ~]$ mysql -utim -ptim1234 --socket=/data/mysql/run/3306/mysql.sockmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.Commands end with ; or \g.Your MySQL connection id is 74Server version: 5.7.26-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.[tim@localhost] 15:24:47 [(none)]>

神奇吧，居然还能登录，这是因为MySQL数据库这个时候的用户权限验证，是从acl_users数组取的，而不是读取mysql.user表。

下面来验证一下能再次创建tim用户

[root@localhost] 15:05:21 [(none)]>create user ‘tim‘@‘%‘ identified by ‘tim1234‘;ERROR 1396 (HY000): Operation CREATE USER failed for ‘tim‘@‘%‘

此时，创建tim用户失败，这个报错也是开发人员创建用户失败的报错。

那如何能解决这个问题呢，其实很简单，用flush privileges即可解决。

flush privileges作用

该命令会清空acl_users数组，然后从mysql.user表中读取数据重新加载，重新构造一个acl_user数组。

总结

删除MySQL数据库用户时，不推荐使用ｄｅｌｅｔｅ方式，推荐使用ｄｒｏｐ user方式，ｄｒｏｐ user会自动重新加载mysql.user表中记录到acl_user数组中。