4.8.4 mysqlbinlog, Executing the queries from a binary log

You can examine the binary log file (see section 4.9.4 The Binary Log) with the mysqlbinlog utility.

shell> mysqlbinlog hostname-bin.001

will print all queries contained in binlog `hostname-bin.001', together with information (time the query took, id of the thread which issued it, timestamp when it was issued etc).

You can pipe the output of mysqlbinlog into a mysql client; this is used to recover from a crash when you have an old backup (see section 4.4.1 Database Backups):

shell> mysqlbinlog hostname-bin.001 | mysql

or

shell> mysqlbinlog hostname-bin.[0-9]* | mysql

You can also redirect the output of mysqlbinlog to a text file instead, modify this text file (to cut queries you don't want to execute for some reason), then execute the queries from the text file into mysql.

mysqlbinlog has the position=# options which will print only queries whose offset in the binlog is greater or equal to #.

If you have more than one binary log to execute on the MySQL server, the safe method is to do it in one unique MySQL connection. Here is what may be UNsafe:

shell> mysqlbinlog hostname-bin.001 | mysql # DANGER!!
shell> mysqlbinlog hostname-bin.002 | mysql # DANGER!!

It will cause problems if the first binlog contains a CREATE TEMPORARY TABLE and the second one contains a query which uses this temporary table: when the first mysql terminates, it will drop the temporary table, so the second mysql will report ``unknown table''. This is why you should run all binlogs you want in one unique connection, especially if you use temporary tables. Here are two possible ways:

shell> mysqlbinlog hostname-bin.001 hostname-bin.002 | mysql

shell> mysqlbinlog hostname-bin.001 >  /tmp/queries.sql
shell> mysqlbinlog hostname-bin.002 >> /tmp/queries.sql
shell> mysql -e "source /tmp/queries.sql"

Starting from MySQL 4.0.14, mysqlbinlog can prepare suitable input for mysql to execute a LOAD DATA INFILE from a binlog. As the binlog contains the data to load (this is true for MySQL 4.0; MySQL 3.23 did not write the loaded data into the binlog, so the original file was needed when one wanted to execute the content of the binlog), mysqlbinlog will copy this data to a temporary file and print a LOAD DATA INFILE command for mysql to load this temporary file. The location where the temporary file is created is by default the temporary directory; it can be changed with the local-load option of mysqlbinlog.

You can also use mysqlbinlog to read the binary log directly from a remote MySQL server.

mysqlbinlog --help will give you more information.

User Comments

Add your own comment.