mysqlbinlog --server-id before MySQL 5.1?
Oct 12, 2008 / By Sheeri Cabral
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
awk 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:
#!/bin/sh for binlog in `ls mysql-bin.*` do mysqlbinlog $binlog > $binlog.tmp && awk '/server id 1/,/server id 227/' $binlog.tmp > $binlog.sql done
This reduced the size of the binary log files from 40G to about 62M, which is MUCH more easy to copy and apply!
* = 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.