Use BINLOG to find the SQL statement of the specified date

Use BINLOG to find the SQL statement of the specified date and search for posts on the 95 database server. post_thread's postid = 1652971, the SQL statement www.2cto.com # grep datadir/etc/mysql/my that was executed on February 24. cnfdatadir =/ssd/mysql # cd/ssd/mysql # mysql-A-e "show master status" | awk 'nr = 2 {print $1} '| awk-F. '{print $1}' mysqld-bin the content here is the prefix of mysqlbinlog, because if it is a slave database, there will be replylog. find the Binlog, and retrieve the last log of the previous day and all the logs created on the 24 th # ls-l... -Rw ---- 1 mysql 1075302338 Feb 22 mysqld-bin.000035-rw-rw ---- 1 mysql 1092104643 Feb 22 mysqld-bin.000036-rw-rw ---- 1 mysql 1073742083 Feb 23 mysqld-bin.000037-rw-rw ---- 1 mysql 1073742129 Feb 23 mysqld-bin.000038-rw-rw ---- 1 mysql 1073742102 Feb 23 mysqld-bin.000039-rw-rw ---- 1 mysql 1073742095 Feb 24 mysqld-bin.000040-rw-r W ---- 1 mysql 1076478877 Feb 24 mysqld-bin.000041-rw-rw ---- 1 mysql 1087015180 Feb 24 mysqld-bin.000042-rw-rw ---- 1 mysql 1073742090 Feb 25 mysqld-bin.000043-rw-rw ---- 1 mysql 1073742037 Feb 25 mysqld-bin.000044-rw-rw ---- 1 mysql mysql 1073741919 Feb 25 mysqld-bin.000045-rw-rw ---- 1 mysql 1073742218 Feb 26 mysqld-bin.000046-rw-rw --- -1 mysql 689342483 Feb 26 mysqld-bin.000047... At this time we need to analyze the binlog for mysqld-bin.0000 {39,4 [0-2]} # ls mysqld-bin.0000 {39,4 [0-2]} mysqld-bin.000039 mysqld-bin.000040 mysqld-bin.000041 mysqld-bin.000042 # cat/root/findsql. sh #! /Bin/bash BINLOGDIR = 'cat/etc/mysql/my. cnf | grep datadir | awk '{print $3}' MYSQL = "/usr/bin/mysql-A-e" DATABASE = "posts" cd "$ {BINLOGDIR }" BINLOGS = 'ls mysqld-bin.0000 {39,4 [0-2]} 'for I in $ {BINLOGS} do $ {MYSQL} "show binlog events in' $ {I} '" | grep "Query"> "$ {BINLOGDIR}/$ {I }. SQL "done # chmod 755/root/findsql. sh First exports the binlog without any modification. At least we will understand such statements and then search for the statements we need. Www.2cto.com #/root/findsql. sh # ls mysqld-bin.0000 {39,4 [0-2]}. sqlmysqld-bin.000039. SQL mysqld-bin.000041.sqlmysqld-bin.000040. SQL mysqld-bin.000042. SQL in these SQL to filter the data, the screening here needs to look at your own capabilities, will awk sed grep these three is enough, awk recommended will be. Grep usually uses a lot of resources, and it should not be difficult. # Cat/root/parsesql. sh #! /Bin/bashfor I in 'ls mysqld-bin.0000 {39,4 [0-2]}. SQL 'do awk-F' use 'posts '; ''{print $2} '$ I | grep-v' chapterclick = chapterclick' | grep-v 'novelscore = novelscore '| grep 1652971> 1652971. SQL echo $ idone # chmod 755 /root/parsesql. sh #/root/parsesql. sh1652971. SQL is our final result.