Mysql Binlog quickly traverses search records and how to view binlog data, mysqlbinlog

Mysql Binlog quickly traverses search records and how to view binlog data, mysqlbinlog

Target: The developer said that there was a data addition, but he did not know where to add it. In addition, the application function should not add such data. In order to find out the source, so I am going to find it in binlog, but the binlog has several months. It is not a way for me to execute mysqlbinlog one by one, so I want to use a script loop to operate it.

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

Articles you may be interested in:

• Instructions on binlog processing by mysql
• MySQL database recovery (using the mysqlbinlog command)
• Migration of Mysql Data DIRECTORY And Binlog directory
• Mysql binlog binary log details
• How to manually delete BINLOG in mysql
• Mysql problems-solutions for a large number of binlog dump records in slow log
• [MySQL binlog] How does mysql completely parse the binlog in the Mixed log format?
• Binlog commands and restoration techniques in MySQL
• How to automatically restore the binlog file of the MySQL database)
• What problems may occur when the version of mysqlbinlog is inconsistent with that of mysql?