mysqlbinlog Tips and Tricks

Posted in: MySQL, Technical Track

So, you have a binlog. You want to find out something specific that happened inside of it. What to do? mysqlbinlog has some neat features, which I thought we would look at here.

I should first explain what mysqlbinlog really is. It is a tool that lets you analyze and view the binlogs/relaylogs from mysql, which are stored in binary format. This tool converts them to plaintext, so that they’re human-readable.

For the first tip, let’s start with the --read-from-remote-server option, which allows you to examine a binlog on a master server in order, perhaps, to dump it onto your slave and compare master/slave logs for potential problems*.

$ mysqlbinlog --read-from-remote-server -uwesterlund -p mysql-bin.000001 -h -P 3306 | head -5
Enter password: 
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 4
#080815 19:25:23 server id 101  end_log_pos 107 	Start: binlog v 4, server v 6.0.5-alpha-log created 080815 19:25:23 at startup

Pretty useful!

Now, let’s assume we have a binlog that is 94 lines long*:

$ mysqlbinlog --read-from-remote-server -uwesterlund -p mysql-bin.000001 -h -P 3306 | wc -l
Enter password: 

If we know that we have correctly replicated all data until position 932, we can make less output for our plaintext file*:

$ mysqlbinlog --read-from-remote-server -uwesterlund -p mysql-bin.000001 -h -P 3306 --start-position=932 | wc -l
Enter password: 

And even better if we know between which two locations we need to look, but one problem is that we need to know both the start position and stop position, we can’t just insert a random value.

First looking at the binlog and show what it would look like:

# at 1132
#080815 19:25:25 server id 101  end_log_pos 1217     Query    thread_id=1    exec_time=8    error_code=0
SET TIMESTAMP=1218821125/*!*/;
flush privileges
# at 1217
#080815 19:34:41 server id 101  end_log_pos 1307     Query    thread_id=8    exec_time=1    error_code=0
SET TIMESTAMP=1218821681/*!*/;
create database blogs

then trying to start at position 1200: (just like inserting a random value instead of an actual position):

$ mysqlbinlog mysql-bin.000001 --start-position=1200
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
# at 4
#080815 19:25:30 server id 102  end_log_pos 107     Start: binlog v 4, server v 6.0.5-alpha-log created 080815 19:25:30 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1818850921, event_type: 115
# End of log file
ROLLBACK /* added by mysqlbinlog */;

So the error we see here is:

ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1818850921, event_type: 115

We can pinpoint specific locations this way*:

$ mysqlbinlog --read-from-remote-server -uwesterlund -p mysql-bin.000001 -h -P 3306 --start-position=932 --stop-position=1132 | wc -l
Enter password: 

But what if your developer says, “I ran a statement at exactly 19:34:55 and I can’t remember what the statement was, can you help me find out?” Sure, you can look at which binlog contains that one, and dump the whole thing, then search for 19:34:55 (granted, the clocks have to be synchronized, it won’t be pretty if the developer’s clock is three minutes off). But, there’s also another way:

$ mysqlbinlog --read-from-remote-server -uwesterlund -p mysql-bin.[0-9]* -h -P 3306 --start-datetime="2008-08-15 19:34:55" --stop-datetime="2008-08-15 19:34:56"
Enter password: 

#080815 19:34:55 server id 101  end_log_pos 1426 	Query	thread_id=8	exec_time=0	error_code=0
use blogs/*!*/;
SET TIMESTAMP=1218821695/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table t1 (a int auto_increment primary key)
# at 1426
#080815 19:34:55 server id 101  end_log_pos 1454 	Intvar
# at 1454
#080815 19:34:55 server id 101  end_log_pos 1567 	Query	thread_id=8	exec_time=0	error_code=0
SET TIMESTAMP=1218821695/*!*/;
insert into t1 values (NULL), (NULL), (NULL)
# End of log file
ROLLBACK /* added by mysqlbinlog */;

So this introduced us to --start-datetime and --stop-datetime , both of which accept DATETIME or TIMESTAMP entries, and which together set the start/stop of what kind of information we’re interested in.

Also, if you have a problem on your master, but your slaves are fine, and you need to restore the master, you can use --skip-write-binlog to avoid the point-in-time recovery to replicate to the slaves, which would cause more problems.

Another point that I touched on above is the option to use certain regexps, i.e., instead of listing mysqlbinlog.01, .02, .03 and so on, you can use mysqlbinlog.[0-9]* to match all of them.

When it comes to --start-position, --start-datetime, –-stop-position and --stop-datetime, they can be used together. So if you know that at 10:00:01 someone dropped your main database, and your last backup has a start position of 1454, you can do this . . .

mysqlbinlog binlog.02 --start-position=1454 --stop-datetime="2008-08-15 10:00:00"

. . . to recover from that point to a second before your crash. (Unless you used the earlier method to find out the exact position of the drop, in which case: recover to it, then skip one entry on your slave, and continue from there.)

Now for the last of these gems. What do you do when you have 500mb of binlog that check for corruption? Do you dump it to a plaintext and read through it looking for errors? No. What you want to do is run mysqlbinlog binlog.000003 binlog.000004 > /dev/null. That will return an error if there’s any problem with the binlog, for example, “Event too big”.

* For those of you unfamiliar with Unix tools, wc -l returns a count of lines from its input, a file or stream; head shows you only the top few lines of its input. back

Interested in working with Nicklas? Schedule a tech call.

9 Comments. Leave new

Alex Gorbachev
August 19, 2008 11:27 pm

Nice. Good to know about MySQL analog of Oracle LogMiner.
Thanks Nick!


Realy its very helpful. Thanks Buddy

Nicklas Westerlund
August 30, 2008 4:55 pm

Thanks Alex and Abhishek — is there any other topic you’d like me to explain in further detail?



Hi Nick
Your article is great
When I run the Master MySql server in Linux and the Slave in Windows I have an issue of maintaing case sensitive of Table Column names. IS there a way out.
Would appreciate a reply.


0 * * * * mysqlbinlog –read-from-remote-server -h -uwesterlund -p -P 3306 mysql-bin.* –start-datetime=”`date +%Y-%m-%d’ ‘%H:00:00 -d\”1 hour ago\”`” –stop-datetime=”`date +%Y-%m-%d’ ‘%H:00:00`” | awk ‘/tbl_company/,/;/’ | replace tbl_company db.tbl_company | mysql -h -uusrname -ppasswd mydb

I have set a crontab that will check the bin-logs every hour for entries related to a particular table and update it on some other server. I use this method when I want to update only one table and don’t need replication.

set global sql_slave_skip_counter = 1
is the line I need to run if I want to skip one line on slave.

And yes, Unix knowledge does help :)

Nicklas Westerlund
September 4, 2008 11:38 am


Thanks, I appreciate it.
Not really, although I would recommend you to set lower_case_table_names=1 (on both machines, unless you have table1 and Table1). This will store all tables as lowercase, and will also convert it for you (So SELECT a,b FROM C; will be SELECT a,b FROM c;)

However, you’d have to convert all your tables to lowercase on linux first.

Another option might be to set it to 0 on linux and 2 on windows, but there’s some drawbacks to that as well.

For a more descriptive walk through, please visit:
which has use cases as well.

Yes, you are right that unix knowledge does help — unless you’re a windows DBA I guess. (But even then, having some unix knowledge would be beneficial). Thanks for commenting!

Krishna Chandra Prajapati
November 3, 2008 8:11 am

Hi Nick,

I would like to know that whether mysqlbinlog –read-from-remote-server can be used to replicate data to a slave mysql server using script. If yes is it successfull. Although there will be a delay, depending on the cron executing the script.

I have a situation, where i need to get data from different mysql server on server called report server. Actually, i am working on this.

Please give your comments.

Krishna Chandra Prajapati

Nizar Yousri M. Azmi
June 5, 2013 6:54 am

Well, that was so helpful, but i still have a question if you’d thankfully help me with… I am trying to read a binlog from a remote host everything works fine but during the read operation i get

Got error reading packet from server: Lost connection to MySQL server during query
# End of log file
ROLLBACK /* added by mysqlbinlog */;

I am not quite familiar with what actually happened. Has the read operation been Rolled back. I mean has it been undone. If that’s the case how can i get rid of this error. What i think happened is the connection timed out during the read operation, also if that’s the case how can i configure it right to not time out during this operation.?!

Any help would be appreciated. Thanks

Martijn Heemels
November 3, 2015 10:44 am

Thanks for the insight. I’ve been trying to find out if I can use the binlog to replay only the transactions for a certain database. Our MySQL server hosts databases for many customers and I’d like a way to fix one of them for example dropping a table. I have nightly backups of each database separately so I guess I could restore that dump and replay any transactions for that database from the time of the backup up to the drop command. Any thoughts?


Leave a Reply

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