Mysqlbinlog quick traversal of search records

Mysqlbinlog quick traversal of search records

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.log echo "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 preceding times are too frequent If there are too many records, I don't know if I can find them. First, check z_grep_single.log. If there is a record, and then retrieve the mysqlbinlog in z_grep.log. /Usr/local/mysql/bin/mysqlbinlog -- base64-output = DECODE-ROWS-v | grep AD_ADVERTISEMENT | grep AD_LINK | grep ad_init_user> export echo "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] # view. 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.