我有一台主写入服务器 (server1)，它将数据复制到两台查询服务器 (server2 和 server3)。我将把所有数据库复制到这些服务器，包括 MySQL 数据库。
当我执行如下 GRANT 时，复制工作完美。
GRANT execute,select ON database1.* TO `user1`@`host` IDENTIFIED BY 'password';
但是，如果我执行相同的 GRANT 来更改现有用户的权限，而没有使用 IDENTIFIED 子句，复制就会中断。
Error 'Can't find any matching row in the user table' on query. Default database: 'mysql'. Query: 'GRANT execute,select ON database1.* TO `user`@`host`'
如果我尝试手动运行查询，我会收到同样的错误。
服务器 1：
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------------------------------------+
| protocol_version | 10 |
| version | 5.0.77-log |
**my.cnf**
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
symbolic-links=0
max_allowed_packet = 100M
log-bin = /var/lib/mysql/logs/borg-binlog.log
max_binlog_size=50M
expire_logs_days=7
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
服务器2：
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------------------------------------+
| protocol_version | 10 |
| version | 5.0.77-log |
我的cnf
server-id=12
master-host=x
master-user=x
master-password=x
master-connect-retry=60
relay-log=/var/lib/mysql/borg-relay.log
relay-log-index=/var/lib/mysql/borg-relay-log.index
谢谢参观
编辑：目前它运行良好，直到您进行破坏它的授权......
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.128.0.5
Master_User: repli-ragnarok
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: borg-binlog.002730
Read_Master_Log_Pos: 4375760
Relay_Log_File: borg-relay.005489
Relay_Log_Pos: 4375899
Relay_Master_Log_File: borg-binlog.002730
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4375760
Relay_Log_Space: 4375899
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
编辑：从历史记录中显示奴隶状态
+----------------------------------+-------------+----------------+-------------+---------------+--------------------+---------------------+-------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+-------------+----------------+-------------+---------------+--------------------+---------------------+-------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 10.128.0.5 | repli-valhalla | 3306 | 60 | borg-binlog.002729 | 40429793 | borg-relay.005486 | 40311514 | borg-binlog.002729 | Yes | No | | | | | | | 1133 | Error 'Can't find any matching row in the user table' on query. Default database: 'mysql'. Query: 'GRANT execute,select ON auth_tracker.* TO `mail-sin1`@`%.sin1.netline.net.uk` IDENTIFIED BY 'mail-sin1666'' | 0 | 40311375 | 40429932 | None | | 0 | No | | | | | | NULL |
+----------------------------------+-------------+----------------+-------------+---------------+--------------------+---------------------+-------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.06 sec)
答案1
我刚刚遇到了同样的问题。就我而言，当我设置 SLAVE 服务器时，我只是导入了我的应用程序数据库转储，因此在默认 MySQL 数据库中创建的用户不在我的 SLAVE db 服务器上，然后 GRANT 语句失败。
在我看来，现在我要么手动创建用户，要么从头开始将默认数据库导入到我的 SLAVE 服务器。
答案2
我要检查以下几件事：
Flush Privileges在所有三台服务器上（可能不会执行任何操作但不会造成伤害）。- 确保主服务器上的 mysql.user 表与两个从服务器上的表相同。
- 似乎有一个非常相似的MySQL 错误报告这可能与您的问题相关。
- 检查服务器模式看看该问题是否确实与 NO_AUTO_CREATE_USER 模式有关。
- 尝试直接在从属服务器上运行 GRANT 或手动查询，看看是否得到相同或不同的结果。在进行任何操作之前，请注意这将如何复制和备份。