Mysql Binlog quickly traverses the search record and the binlog data view method

Goals, developers say a puzzling added data, but we don't know where it was added, and application functions should not add the data, in order to find out the source, so I'll get ready to go binlog found inside, but binlog number for several months, I so 1 each mysqlbinlog bottom go to, also not way, so think of to use script loop to operate.

1, go to binlog directory and copy all binlog to temporary directory /tmp/bl/

cp /home/data/mysql/binlog/mysql-bin.* /tmp/bl

2. Write script traversal

[root@wgq_idc_dbm_3_61 tmp]# vim find_guolichao.sh 
#!/bin/bash
 Enter temporary directory 
cd /tmp/bl
#  Start the loop through the directory 
for path in `ls . |grep mysql-bin.0`
do
# record 1 Some basic information, such as the current traversal mysqlbinlog The log 
echo "" >> z_grep.log
echo "begin ..."
echo $path >> z_grep.log
#  I need to search out AD_ADVERTISEMENT The table AD_LINK The value of the field is ad_init_user Record, record to the total log z_grep.log
/usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v |grep AD_ADVERTISEMENT |grep AD_LINK |grep ad_init_user >> z_grep.log
# Record the search results in a separate log, alone because if you do it too many times, 1 The list to see also do not know whether to search for, first look z_grep_single.log If you have a record, go again z_grep.log I'm going to retrieve which one mysqlbinlog Just inside. 
/usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v |grep AD_ADVERTISEMENT |grep AD_LINK |grep ad_init_user >> z_grep_single.log
echo "end." >>z_grep.log
done

3. Execute the search script and view the results

[root@wgq_idc_dbm_3_61 tmp]# bash -x find_guolichao.sh 
[root@wgq_idc_dbm_3_61 tmp]# ll ./bl/z_grep*
-rw-r--r--. 1 root root 33534 1 month  27 15:59 ./bl/z_grep.log
-rw-r--r--. 1 root root 0 1 month  27 15:59 ./bl/z_grep_single.log
[root@wgq_idc_dbm_3_61 tmp]# 

Look./bl/ z_grep_single.log size is 0, which is obviously not recorded in the current 2 month binlog log. It should be the data entered two months ago, so we can only go to the backup record of the historical backup record to find it

How to view binlog data for MySQL

binlog introduction

binlog, or base 2 log, records all changes on the database.

When the EXECUTION of an SQL statement that changes the database ends, a record is written at the end of binlog and the statement parser is notified that the statement is executed.

binlog format

Based on statements, there is no guarantee that all statements will execute successfully from the library, such as update... limit 1;

On a row-based basis, write each change as 1 line in binlog. The row-based format has advantages when performing a particularly complex update or delete operation.

Log on to mysql to see binlog

View only the contents of the first binlog file

show binlog events;

View the contents of the specified binlog file

show binlog events in 'mysql-bin.000002';

View the binlog file that is currently being written

show master status\G

Gets the list of binlog files

show binary logs;

View with the mysqlbinlog tool

Note:

Do not view the binlog file that is currently being written

Do not add the --force parameter to enforce access

If binlog format is in row mode, add the -ES85en parameter

The local view

Based on start/end time

mysqlbinlog --start-datetime='2013-09-10 00:00:00' --stop-datetime='2013-09-10 01:01:01' -d  The library  2 A binary file 

Based on the values of pos

mysqlbinlog --start-postion=107 --stop-position=1000 -d  The library  2 A binary file 

Remote viewing

Specify the start/end time and redirect the results to the local t.binlog file.

mysqlbinlog -u username -p password -hl-db1.dba.beta.cn6.qunar.com -P3306 \
--read-from-remote-server --start-datetime='2013-09-10 23:00:00' --stop-datetime='2013-09-10 23:30:00' mysql-bin.000001 > t.binlog