MySQLBinLogConverter Specification

MySQLBinLogConverter

The MySQLLogConverter is a Java utility for reading a MySQL binlog text file, and converting it to a SQL script. The utility takes a text file generated from the mysqlbinlog utility, and converts it to a SQL script file. This version handles only Statement-based logging, and will be improved in the future to support Row-based and Mixed log formats.

MySQL Binary Log Text Format The MySQL Binary Log has the following general format for each "event" in the log:

# at 573
#120220 18:57:35 server id 12  end_log_pos 738 	Query	thread_id=54935512	exec_time=0	error_code=0
SET TIMESTAMP=1329789455/*!*/;
UPDATE space SET c1 = 0 WHERE c2 = 'xxx'
/*!*/;

An event always starts with: # at , indicating the position in the log file (always on its own line).

An entry ends with /*!*/; which can be on its own line, or at the end of a command.

There can be multiple statements in a a single event, such as:

# at 166
#120220 18:57:35 server id 12  end_log_pos 487 	Query	thread_id=54935512	exec_time=0	error_code=0
use sampledb/*!*/;
SET TIMESTAMP=1329789455/*!*/;
UPDATE t1 SET c1 = 'xxx' WHERE c2 = 'yyy'
/*!*/;

Here is an example INSERT statement:

# at 1378
#120220 18:57:35 server id 12  end_log_pos 1406 	Intvar
SET INSERT_ID=506802481/*!*/;
# at 1406
#120220 18:57:35 server id 12  end_log_pos 1933 	Query	thread_id=54935936	exec_time=0	error_code=0
SET TIMESTAMP=1329789455/*!*/;
INSERT INTO t1 (a,b,c) VALUES ('xxx','yyy',NOW())
/*!*/;

There are special commands used to provide information that is calculated in the SQL statement:

SET INSERT_ID=506802481/*!*/;

This provides a generated auto increment column ID, when a value is not included in the INSERT statement itself.

SET TIMESTAMP=1329789455/*!*/;

This provides a timestamp value, used to substitute for any NOW() function that may be in the SQL statement.

These special commands always precede the statement they affect.

Here is an example BEGIN statement, starting a transaction:

#120220 18:57:35 server id 12  end_log_pos 573 	Query	thread_id=54935512	exec_time=0	error_code=0
SET TIMESTAMP=1329789455/*!*/;
BEGIN

This is a corresponding COMMIT statement, ending a transaction:

# at 1284
#120220 18:57:35 server id 12  end_log_pos 1311 	Xid = 143578775
COMMIT/*!*/;

Converter Output The converter generates a SQL script file, in the format:

[statement]
;

All statements have the special SET INSERT_ID and SET TIMESTAMP commands are added as separate statements in the file. They are rewritten as:

SET OSP_AUTO_INCREMENT_ID 12345
;
SET OSP_TIMESTAMP 34567890
;

These must be interpreted by any consumer of the log file, to generate appropriate SQL statements.