How to Avoid Replicating Statements in MySQL

By | December 29, 2010

If a replication slave gets out of sync with the master, you can bring them back in sync by running statements that don’t execute on every server in the replication chain. There are sane and insane ways to do this.

The right way is to execute SET SESSION sql_log_bin=0; on your current connection before running the statements you don’t want replicated. Then, either execute SET SESSION sql_log_bin=1; or close the connection.

The crazy way is to execute the statements while the default database is set to a database that is not replicated. Many DBAs configure MySQL servers so that the mysql database is not replicated, since it may contain user and host info that is specific to a server instance. When the default database is set to a database that is not replicated, mysqld will not replicate statements affecting any database. Feature or bug, you be the judge.

In my example scenario below, db1 is the master and db2 is the slave.

First, create a table in the test database and verify it is replicated. Here’s an example create statement.

mysql> CREATE TABLE test.rs (a INT);

Then, use the MySQL CLI to connect to the master database server (in this case, db1), set the default database to mysql (-D mysql) and execute an INSERT statement. The short version of this is:

[[email protected] ~]$ mysql -u me -D mysql -h db1 -e "INSERT INTO test.rs VALUES (1);"

Then, verify that the row was added on the master, but not on the slave.

[[email protected] ~]$ mysql -u me -h db1 -e "SELECT * FROM test.rs;"
+------+
| a |
+------+
| 1 |
+------+
[[email protected] ~]$ mysql -u me -h db2 -e "SELECT * FROM test.rs;"
[[email protected] ~]$

And if you haven’t already guessed, the insane way is frequently the accidental source of many out-of-sync situations. Use the sane way to fix the damage.

Of course, the easy way to sync up tables on out of sync servers in a replication chain is to use mk-table-sync.

2 thoughts on “How to Avoid Replicating Statements in MySQL

  1. Robert Post author

    Thank you, past self. I think this is the third time I’ve searched for my own blog post. Amazing how many bloggers reverse bin and log in the name of that system variable.

    Reply
  2. Robert Post author

    Seems like MySQL 5.5.32 may have fixed the issue that allowed the insane technique for not replicating statements. The release notes include:

    Now, if there is no current database, a statement using fully qualified table names is always written to the binary log. (Bug #11829838, Bug #60188)

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *