# 查看当前binlog状态
show master status\G;
# mysql中查看指定binlog信息
show binlog events in 'mysql-bin.000010';
# 查询指定时间段内/位置的删除语句
mysqlbinlog --no-defaults --start-datetime='2020-03-03 07:00:00' --stop-datetime='2020-03-03 07:30:00' mysql-bin.000003 -d dbname -v | grep DELETE -A 5
mysqlbinlog --start-position=331 --stop-position=465 --database=test mysql-bin.000014 -v
# 恢复
mysqlbinlog --start-position=331 --stop-position=465 --database=test mysql-bin.000014 -v --skip-gtids | mysql -v test
# 将delete语句恢复成insert
mysqlbinlog --database=test mysql-bin.000014 --start-position=331 --stop-position=465 -v --base64-output=deco
de-rows | sed -n '/###/p' | sed -n 's/###//p' > test.sql
实例: 恢复delete语句删除的数据(本实例表只有3列,结合实际自行修改)
mysqlbinlog --database=test mysql-bin.000014 --start-position=331 --stop-position=465 -v --base64-output=decode-rows | sed -n '/###/p' | sed -n 's/###//p' > test.sql
sed 's/DELETE FROM/insert into/g' test.sql | sed 's/WHERE/select/g' | sed 's/@3.*/\0;/g' | sed 's/@[1-2].*/\0,/g' | sed 's/[^@]\(.*\)=//g' > execSQL.sql
source execSQL.sql
--base64-output=decode-rows -v # 将日志中“BINLOG” 字符串转化成sql