mysqlbinlogutility. Extracting CRUD statements and DDL is relatively straightforward, but not for statements like
LOAD DATA INFILE.The actual data file is embedded within the binary log, and not very visible to the naked eye. But there is an easy way to decipher the binary log and extract the file to load manually.
As an example, I have taken a simple text file of numbers and loaded it into a fictitious table abc using the
LOAD DATA LOCAL INFILEcommand. To see where in the binary log that command would reside, the
mysqlbinlogutility is used:
$ mysqlbinlog mysqld-bin.000003 | grep -i -B7 "load data"
# at 174
#100921 21:42:10 server id 1136902037 end_log_pos 218
#Begin_load_query: file_id: 1 block_len: 21
# at 218
#100921 21:42:10 server id 1136902037 end_log_pos 432 Execute_load_query thread_id=5 exec_time=0 error_code=0
LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-1-2' INTO TABLE `abc` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`)
You can see that from the command above that the command, including the load file is contained between positions 174 and 432 of the binary log. Now that the start/stop positions are known, it is possible to extract the data file to load manually into your database (or into another database):
mysqlbinlog --start-position=174 --stop-position=432 --local-load=/var/tmp mysqld-bin.000003
--local-loadoption specifies what directory to store the actual load file. And you can take the file and use the
LOAD DATAcommand above (changing the directory name as needed) and load that data back into your database or use it to seed another database.