1

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.

Tom
  • 113
  • 1
  • 6

1 Answers1

1

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.

RLazar
  • 101
  • 3
  • 1
    The last quote is to wrap the message 'Operation DROP USER failed for 'mysql_user'@'localhost'' – Tom Jul 27 '17 at 13:54
  • Also, wouldn't it be possible to just disable mysql replicating the users table? – Tom Jul 27 '17 at 15:04
  • Disabling replication of *mysql* database is not an option if you are using replication for backup purposes, but yes, it can help avoid such situations. This way or another, `DROP USER` stopped on slave thus stopping replication. – RLazar Jul 27 '17 at 15:13
  • Okay, so do you want to answer the question by putting how to disable replicating the mysql database? – Tom Jul 28 '17 at 09:19
  • I have just added another paragraph to original answer hoping it will be useful. – RLazar Jul 28 '17 at 11:28