WombatNation

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.

10Gen put on another excellent MongoDB conference last Friday, this time at Microsoft Research in Mountain View. Like Mongo SF, there was a good balance between intro and advanced material, as well as between 10Gen presenters and third party presenters, like myself. Registration was smooth, sessions ran on time, they made it easy on presenters, presentations were videotaped, audio was recorded with a direct feed, food was adequate (though putting squeaky bags of chips in rooms was not a good idea), 10Gen people were really helpful and the after party at Tied House was excellent.

And best of all, I talked my way out of an undeserved traffic ticket while leaving Microsoft Research. The car in front of me pulled into the street and then stopped to wait for a car to go by. I pulled up to the stop sign and stopped, since he was already in the street. When he finally pulled away, so did I. Fortunately, the cop accepted my side of the story and let me go. He was actually pretty nice about it.

Below are the slides from my presentation on Logging Application Behavior to MongoDB. If you’re interested in logging to MongoDB from Java, Python, Ruby, PHP and/or C#, I hope you’ll find them useful.

I’m currently on the agenda to present on the same topic at the January 18 San Francisco MongoDB Meetup. By then, I plan to have more detailed info on analyzing data that has been logged to MongoDB.