当操作MySQL用户表出错:故障排除错误1396

几周前，我们在一个复制环境中遇到了一个问题管理服务客户:

LAST_ERROR_MESSAGE: Worker 2执行事务失败“UUID：GTID”在主宾格上。00123.45，end_log_pos 98765; Error ‘操作创建用户失败对于' test_user ' @ ' 10.10.10.10 "查询。默认数据库:' mysql '。查询:' CREATE USER ' test_user ' @ ' 10.10.10.10 ' IDENTIFIED WITH ' mysql_native_password ' AS ' ************ "

经过初步调查后，我们注意到副本中的用户并不存在!MySQL是不是疯了?但随后客户提到，在成功执行查询之前，他们在主服务器中出现了以下错误:

但是我们知道第一个命令不能被复制(因为失败的命令永远不会被记录到binlog中)，所以问题是，发生了什么，为什么MySQL在创建一个不存在的用户时遇到了麻烦?

如果你快速搜索一下，你会发现一些帖子提供了一些选择:

• 执行FLUSH权限。
• 删除用户，然后刷新特权。
• 很多人提到这是有报道的错误。

当然，如果你尝试了上面提到的方法，你最终会解决这个问题。尽管如此，我还是想更深入地了解为什么会发生这种情况，以及解决它的正确方法应该是什么(并理解为什么某些东西会解决它);所以我决定做一些测试，并为将来遇到类似问题的人写一篇文章，希望你也能理解它。

让我们从一个提醒开始:你不应该操纵mysql格兰特手动表，如果你这样做，你应该确保你知道你在做什么。首先，我们总结一下MySQL的特权是如何工作的。

MySQL特权的总结

在启动时，MySQL读取授权表并将其加载到内存中，因此每当需要检查用户是否被允许读取或连接时，MySQL可以更快地查找特权(从内存中读取)。

MySQL使用授权表，因此当我们在内部发出CREATE USER时，它转换为

• 插入到mysql.user
• 插入到mysql.db

如果我们GRANT或REVOKE，它将转换为

• 插入mysql.table_priv
• 在mysql.db中更新
• 在mysql.table_priv中删除。

等等......请注意这并不是一个精确的步骤;它只是MySQL需要执行的一些内部事情的一个例子。

最后，在每一个帐户管理报表， MySQL将新的特权读入内存，并应用更改。

有了这个，MySQL让我们可以很容易地操作特权;如果您需要删除一个用户，而不是手动删除每个授权表中的每一行，您可以执行DROP user，就是这样。

尽管这种方式更简单，但有时用户决定采取“艰难的方式”，手动操作特权:从授予表执行DELETE而不是DROP USER。但是，正如前面提到的，MySQL不会知道这些更改，因为已经读取了授权表，并且权限在内存中。

让我们回顾一下上面提到的问题

第一步:在主服务器上创建用户:

第二步:经过一些测试后，他们想删除用户并重新创建它，但他们还是手动删除了用户:

到目前为止，如上所述，MySQL并不知道用户test_user被删除(MySQL没有将授权表重新读入内存，因为他们没有执行FLUSH PRIVILEGES);这就是为什么当他们试图再次创建用户时，失败了。

因为他们找不到用户，所以他们做了我们都会做的事情:再次重试，并希望这次命令能神奇地工作。

它成功了，用户被创建了，一切又恢复正常了。为什么它能起作用?因为使用第一个CREATE USER(即使命令失败)，MySQL重新加载授予的权限。

下面是第一次CREATE的情况:

1. MySQL检查它的内存表，发现用户已经存在。
2. 拒绝新用户，因为它不能复制用户。
3. MySQL重新加载内存中的表(无论是否成功，都会发生这种情况)。

因此，即使命令失败，MySQL也会重新加载内存中的表和用户test_user不存在(因为用户之前被手动删除);这就是第二次CREATE成功的原因。

第二个CREATE USER执行成功，它转到binlog和副本。

现在，从副本的角度来看，到目前为止，它已经复制了DELETE语句和ONE CREATE USER，并回到了它开始的地方，副本中的错误:

LAST_ERROR_MESSAGE:工作人员2执行事务' UUID:GTID '在主binlog失败。0012345, end_log_pos 98765;在查询' test_user ' @ ' 10.10.10.10 '时，操作CREATE USER失败。默认数据库:' mysql '。查询:' CREATE USER ' test_user ' @ ' 10.10.10.10 ' IDENTIFIED WITH ' mysql_native_password ' AS ' ************ "

通过上面解释的事情，我认为更容易理解这里发生了什么:这个CREATE语句是“第一个”，因为主进程中的第一个语句失败了，所以没有到达副本，所以为了“修复”这个问题，我们只启动了复制，这就做到了，这次CREATE工作了，因为它已经重新加载了内存中的表。

在结束之前，我想测试更多的东西，以了解为什么有时简单的FLUSH PRIVILEGES可以工作，为什么有时需要完全DROP USER;简单的回答:这取决于你手动操作拨款表的“好”程度。

当FLUSH PRIVILEGES起作用时

因为create用户只插入了mysql。用户，it was the only table where the user exists, so the flush privileges command was successful.

当FLUSH特权不够用时

有很多授权表，因此在手动删除用户时，很容易漏掉其中一个用户，从而导致FLUSH PRIVILEGES无法解决的错误。

GRANT命令也会在mysql.db表中插入一条记录

然后如果我手动删除，但只从mysql。用户table, the record in mysql.db will be there until it’s cleaned.

因此，即使我尝试FLUSH命令，我将无法创建用户(由于用户存在于mysql.db)

正确的方法是执行DROP命令，让MySQL清理任何需要的表(你可以在下面看到MySQL .db表也被清理了)。

现在CREATE工作了:

“如果不存在”条款呢?

使用相同的场景进行复制;首先，在主服务器中创建用户。

在此之后，手动删除mysql中该用户。用户表。

我们知道CREATE USER命令会失败并重新加载内存中的表;让我们看看如果我们添加if NOT EXISTS子句会发生什么:

没有错误，但有一个警告:

并且用户不存在，正如预期的那样(它是手动删除的，但是没有FLUSH特权，所以MySQL不知道)。

但是，这一次命令被记录在binlog中:

并且被复制没有问题，以及，下面是接力日志事件:

最后要记住的是，这一次帐户管理报表执行后没有重新加载授权表(我们可以知道这是因为第二次执行没有创建用户，就像执行create user时不带子句一样，参见下面的警告):

因此，IF NOT EXISTS子句可以避免出现错误(在复制环境中也没有问题)，但与往常一样，一定要检查警告以了解发生了什么，否则，您仍然得不到用户所需的信息。

最终的想法

当你想要修改MySQL中的特权/用户时，你一定要使用帐户管理报表，这样你就不必担心所有的细节。

我想与您分享这个特定的场景，希望它能让您更清楚地了解MySQL如何管理内部特权，以及在手动修改任何内容时，内存中的表是一个重要的东西。

最后，记住这一点的文档你的朋友是:

如果直接使用INSERT、UPDATE或DELETE(不建议使用)等语句修改授权表，则更改对权限检查没有影响，除非您告诉服务器重新加载表或重新启动表。因此，如果您直接更改授权表，但忘记重新加载它们，则更改直到重新启动服务器才会生效。这可能会让你想知道为什么你的改变似乎没有什么不同!