A master-slave replication interruption caused by MySQL cross-database operations.

A master-slave replication interruption caused by MySQL cross-database operations.

Today, all MySQL master-slave replication on the server is abnormally interrupted. log on to one of the platforms and execute show slave status \ G. the following error is found:
--
Last_Error: Error 'Operation drop user failed for 'guest '@ 'localhost' on query. Default database: 'work'. Query: 'drop user' guest' @ 'localhost''
--
That is to say, it is caused by the drop user 'guest '@ 'localhost' command, and such operations are generally only performed on the Master, this operation should only affect the system database "mysql. Previously, this operation was performed many times. Why is this operation the only one having problems?
After some investigation, the root cause of the problem was found,
"Binlog-do-db, binlog-ignore-db, replicate-do-db, replicate-ignore-db" is not as reliable as you think!

In general, we will assume that as long as the above parameters are set, MySQL master-slave replication will only take effect for the database we set. But in fact, MySQL is not determined based on the content, but is determined by a dummies based on the database you have executed the "use work" or specified during the initial connection.
This time, before executing the drop user operation, we need to select some data from the "work" database and then use work to enter the work database, as we all know, when executing the drop user operation, you do not need to enter the system database "mysql". Therefore, you directly run the drop user command, however, because the MySQL judgment is executed after use work, we think that the operations for the "work" database will be synchronized, there are no users like guest @ localhost in the service, which leads to errors and master-slave replication interruption.

Therefore, in the MySQL server environment with a master-slave replication architecture, we should try to avoid such cross-database operations to ensure that the command is executed after the correct use dbname is executed.

This type of fault recovery solution is simple, that is, skipping this SQL statement.
--
Stop slave;
Set global SQL _slave_skip_counter = 1;
Start slave;
Show slave status \ G
--

References:
Http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/