Mysql Binlog a quick way to traverse search records and binlog data viewing _mysql

Target, the developer said that there is a data inexplicably added, but do not know where to add, and the application function should not add such data, in order to find out the source, so I was ready to go to binlog inside, but Binlog have several months, I am so mysqlbinlog down, is not the way, so think of preparing to use a script loop to operate.

1, go to the Binlog directory to replicate all Binlog to the temp 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
into temp directory
cd/tmp/bl
# Start looping through directory
for Path in ' ls. |grep mysql-bin.0 '
do
#记录一些基础信息, such as the currently traversed Mysqlbinlog log
echo "" >> Z_grep.log
echo "Begin ..."
echo $path >> z_grep.log
# You need to search for records in the Ad_link field of the Ad_advertisement table that are ad_init_user and log 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
#记录搜索结果到单独的日志里面, alone because if there are too many times, one to see whether or not to find out, first look at Z_grep_single.log if there is a record, then go to Z_ Grep.log inside the search under which mysqlbinlog inside can be.
/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 see 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 January 15:59/bl/z_grep.log
-rw-r--r--. 1 root root 0 January 15:59./bl/z_grep_single.log

Look. The/bl/z_grep_single.log size is 0, and it is clear that this value is not recorded in the current 2-month Binlog log. Should be the data entered 2 months ago, so you can only go to historical backup records of backup records to find

How to view MySQL's binlog data

Binlog Introduction

Binlog, the binary log, records all changes on the database.

When the SQL statement of the database is changed, a record is written at the end of the Binlog, the statement parser is executed, and the statement completes.

Binlog format

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

Based on rows, each change is recorded as a row in Binlog. The line-based format has an advantage when performing a particularly complex update or delete operation.

Login to MySQL view 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

Get Binlog file List

Show binary logs;

View with the Mysqlbinlog tool

Attention:

Do not view Binlog files that are currently being written

Do not force access with--force parameters

If the Binlog format is in row mode, add the-VV parameter

View locally

Based on start/end time

Mysqlbinlog--start-datetime= ' 2013-09-10 00:00:00 '--stop-datetime= ' 2013-09-10 01:01:01 '-D library name binaries

Based on POS value

Mysqlbinlog--start-postion=107--stop-position=1000-d Library name binary file

Remote View

Specifies the start/end time and redirects 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