大网数据广告活动
服务器运维问答

MySQL Replication failing on `mysql`.`user` changes

· 52CE 我爱测

I have a simple master - slave setup with MySQL 5.6 that keeps failing whenever I make changes to the mysql.user table. The master is set to replicate two databases, neither of which are the mysql database. The binlog_format is s…

问题原文

原帖链接 · Tom · CC BY-SA 3.0

I have a simple master - slave setup with MySQL 5.6 that keeps failing whenever I make changes to the mysql.user table. The master is set to replicate two databases, neither of which are the mysql database. The binlog_format is set to ROW.

I have a selection of MySQL users set up on the master for various colleagues to make changes to the database on, but on the slave I only have one user, which is the web server.

When I tried to delete a redundant user from the master, the slave failed with the following message:

Error 'Operation DROP USER failed for 'mysql_user'@'localhost'' on query. Default database: ''. Query: 'DROP USER 'mysql_user'@'localhost''

I don't want to have to keep skipping the errors, because every now and again I'll make changes to the mysql.user table and it's easy to forget to check the replication is still going. I don't have any monitoring setup for the replication, so a couple of times I've made changes to the mysql table and no one has noticed for a couple of hours, which impacts our production web server.

Any help much appreciated.

已采纳解答

原帖链接 · RLazar · CC BY-SA 3.0

Pay attention to the part of your error message: 'mysql_user'@'localhost''

There are two quotes after localhost word. When this statement replicates, a slave database server cannot find a user and replication stops. MySQL 5.7 has DROP USER IF EXISTS user form, but in MySQL 5.6 you better do not make such mistakes or prefer direct edits of mysql database.

UPDATE: When you are not interested in replicating MySQL accounts from a master server to slaves, you may want to add replicate-ignore-db=mysql to [mysqld] sections on each slave. I prefer filtering at slaves because having changes in binary logs of a master have potential value for troubleshooting in the future.

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2017-07-27)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。