mysqlbinlog --server-id before MySQL 5.1?

Posted in: MySQL, Technical Track

Recently I had an interesting issue crop up. Due to an unfortunate migration incident in which involved master/master replication and not checking to see if replication was caught up, we ended up with an infinite replication loop of a number of SQL statements. awk helped immensely in the aftermath cleanup.

The basics of the replication infinite loop were that the binary logs contained statements from three different server-ids. Since there were only 2 machines after the migration completed, the statements from the third machine kept being applied to one server, written to the binary log and replicated to the other. Lather, rinse, repeat.*

The nitty gritty was that the looping statements were coming from server-id 227, and the server-id values of the two masters were 16 and 127. We recognized this was a problem because we saw that 2 other slaves were not keeping up with replication — each slave had fewer available resources than the two masters.

After fixing the base issue by stopping writes to the database and resetting the master logs on both masters, we had to deal with the problem of how to apply 40G of logfiles to the slave servers so they could catch up. The problem is that most of the logfile was the looping queries, which is a waste to send over, as each 1.1G binary log was taking about 2 hours to apply.

In MySQL 5.1, mysqlbinlog has a --server-id parameter that allows you to extract only a specified server-id. However, there is no indication that you can extract more than one server-id with this method, and this was on MySQL 5.0, and while I could copy the logs to a 5.1 machine for processing, or perhaps copy the 5.1 mysqlbinlog program to the machine, there was an easier way, guaranteed to work with more than one server-idawk to the rescue!

Each query in the query log starts with some metadata, commented out, such as:

# at 39026
#081011  9:11:08 server id 127  end_log_pos 39026   Query   thread_id=12678 exec_time=1 error_code=0


# at 39054
#081011  9:11:08 server id 227  end_log_pos 39054   Query   thread_id=82    exec_time=30822     error_code=0

In awk you can retrieve the text on lines between two strings (including the lines the strings themselves are on) with:

awk '/string1/,/string2/' file

And so, in order to get items from server-id 16 and server-id 127, all I had to do was get all the lines between the strings “server id 1” and “server id 227”. So I wrote a little shell script to help me out:


for binlog in `ls mysql-bin.*`
mysqlbinlog $binlog > $binlog.tmp && awk '/server id 1/,/server id 227/' $binlog.tmp > $binlog.sql

This reduced the size of the binary log files from 40G to about 62M, which is MUCH more easy to copy and apply!

You can read more information about awk or learn its various uses in An Awk Primer.

* = The way MySQL prevents loops in circular replication is that it stores the server-id in the binary log and a server does not apply statements from its own server-id. So an update from server1 will replicate to server2, then to server3, then back to server1 — but that second time around, server1 says “Hey, this is from me!” and does not run the update. Thus the second time around, the statement is not logged to server1’s binary log, and thus not replicated a second time.

Interested in working with Sheeri? Schedule a tech call.

2 Comments. Leave new

Yea this goes to show once again that circular replication (master-master with more than 2 servers) only seems like a good idea, until there is a failover situation. And taking out a server-id like you had, that might be the ultimate nuisance ;-)
Thanks for that.

Lars Thalmann
April 16, 2009 8:35 pm

To solve this problem, we have, in MySQL 6.0, introduced a mechanism to filter by server id in replication.



Leave a Reply

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