MysqlBinlog: how to quickly traverse search records and view binlog data _ MySQL

This article describes how to use MysqlBinlog to quickly traverse search records and how to view binlog data. if you need to use MysqlBinlog, you can refer to the following target. the developer said that some data has been added inexplicably, but I do not know where to add the data, and the application function should not add such data. in order to find out the source, I am going to find it in binlog, however, the number of binlogs is several months. it is not a way for me to execute mysqlbinlog one by one. Therefore, I want to use a script loop to perform operations.

1. copy all binlogs from the binlog directory to the 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 the temporary directory cd/tmp/bl # start looping through the directory for path in 'ls. | grep mysql-bin.0 'do # record some basic information, such as the current mysqlbinlog echo ""> z_grep.logecho "begin... "echo $ path> z_grep.log # Records whose AD_LINK field value is ad_init_user need to be searched, record 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 in a separate log, the reason is that if the number of times is too large I don't know if I can find the record. check z_grep_single.log and retrieve the mysqlbinlog file in z_grep.log. /Usr/local/mysql/bin/mysqlbinlog -- base64-output = DECODE-ROWS-v | grep AD_ADVERTISEMENT | grep AD_LINK | grep ad_init_user> begin "end."> z_grep.logdone

3. execute the search script to view the result.

[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 --. 1 root 33534 January 27 15:59. /bl/z_grep.log-rw-r -- r --. 1 root 0 January 27 15:59. /bl/z_grep_single.log [root @ wgq_idc_dbm_3_61 tmp] #

Check that the size of./bl/z_grep_single.log is 0. Obviously, this value is not recorded in the binlog of the current two months. It should be the data entered two months ago. in this way, you can only search for the backup records of historical backup records.

How to View binlog data of MySQL

Binlog introduction

Binlog is a binary log that records all changes in the database.

When the SQL statement execution of the database is changed, a record will be written at the end of the binlog, and the statement parser will be notified to complete the statement execution.

Binlog format

Based on statements, it cannot be ensured that all statements are successfully executed in the slave database, such as update... limit 1;

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

Log on to mysql to view binlog

Only view the content of the first binlog file

show binlog events;

View the content of the specified binlog file

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

View the binlog file currently being written

show master status\G

Obtain the binlog file list

show binary logs;

View with mysqlbinlog

Note:

Do not view the binlog file currently being written

Do not add the -- force parameter to force access

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

Local View

Based on start/end time

Mysqlbinlog -- start-datetime = '2017-09-10 00:00:00 '-- stop-datetime = '2017-09-10 01:01:01'-d Library name binary file

Based on pos value

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

Remote View

Specify the start/end time and redirect the result 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

The above is the Mysql Binlog quick search record and binlog data viewing method _ MySQL content. For more information, please follow the PHP Chinese network (www.php1.cn )!