How to Avoid Replicating Statements in MySQL

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:

[me@server ~]$ 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.

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

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.