查询数据库相关配置参数

root[test]>showglobalvariableslike'binlog%format%';+---------------+-------+|Variable_name|Value|+---------------+-------+|binlog_format|ROW|+---------------+-------+1rowinset(0.00sec)root[test]>showglobalvariableslike'binlog%row%image%';+------------------+-------+|Variable_name|Value|+------------------+-------+|binlog_row_image|FULL|+------------------+-------+1rowinset(0.00sec)root[test]>showglobalvariableslike'%log%bin%';+----------------------------------+-----------------------------------------------+|Variable_name|Value|+----------------------------------+-----------------------------------------------+|log_bin|ON||log_bin_basename|/data1/mysql_log_23306/binlog/mysql-bin||log_bin_index|/data1/mysql_log_23306/binlog/mysql-bin.index||log_bin_trust_function_creators|ON||log_bin_use_v1_row_events|OFF||log_statements_unsafe_for_binlog|ON|+----------------------------------+-----------------------------------------------+6rowsinset(0.01sec)

安装binlog2sql

正克隆到'binlog2sql'...remote:Countingobjects:298,done.remote:Compressingobjects:100%(4/4),done.remote:Total298(delta0),reused1(delta0),pack-reused294接收对象中:100%(298/298),147.01KiB|49.00KiB/s,done.处理delta中:100%(151/151),done.[root@mysql-serverbinlog2sql]#source../venv4archer/bin/activate(venv4archer)[root@mysql-serverbinlog2sql]#pipinstall-rrequirements.txtRequirementalreadysatisfied:PyMySQL==0.7.11in/data1/venv4archer/lib/python3.6/site-packages(from-rrequirements.txt(line1))(0.7.11)Collectingwheel==0.29.0(from-rrequirements.txt(line2))Cacheentrydeserializationfailed,entryignoredDownloadinghttps://files.pythonhosted.org/packages/8a/e9/8468cd68b582b06ef554be0b96b59f59779627131aad48f8a5bce4b13450/wheel-0.29.0-py2.py3-none-any.whl(66kB)100%|████████████████████████████████|71kB103kB/sCollectingmysql-replication==0.13(from-rrequirements.txt(line3))Downloadinghttps://files.pythonhosted.org/packages/dd/23/384047702e694139e9fe75a8ba7ad007e8942fd119ebadabc32ce19f70f2/mysql-replication-0.13.tar.gzBuildingwheelsforcollectedpackages:mysql-replicationRunningsetup.pybdist_wheelformysql-replication...doneStoredindirectory:/root/.cache/pip/wheels/91/33/05/32b16ccadd4fc566ff38af96afdeb5d57d49c2f1eff0402164Successfullybuiltmysql-replicationInstallingcollectedpackages:wheel,mysql-replicationFoundexistinginstallation:wheel0.31.1Uninstallingwheel-0.31.1:Successfullyuninstalledwheel-0.31.1Successfullyinstalledmysql-replication-0.13wheel-0.29.0

创建测试数据，并执行误删除

root[(none)]>flushlogs;QueryOK,0rowsaffected(0.00sec)root[(none)]>usetestDatabasechangedroot[test]>createtableuser(idint(12)unsignedauto_incrementcomment'id'primarykey,namevarchar(15),add_timetimestamp);QueryOK,0rowsaffected(0.01sec)root[test]>insertintouser(name,add_time)values('neo','2018-09-01'),('trinity','2018-09-02'),('jason','2018-09-05');QueryOK,3rowsaffected(0.00sec)Records:3Duplicates:0Warnings:0root[test]>deletefromuserwhereadd_time<'2018-09-05';QueryOK,2rowsaffected(0.01sec)

查看二进制日志文件

root[test]>showbinarylogs;+------------------+-----------+|Log_name|File_size|+------------------+-----------+|mysql-bin.000002|2236||mysql-bin.000003|201||mysql-bin.000004|1218|+------------------+-----------+3rowsinset(0.00sec)

解析出标准SQL

(venv4archer)[root@mysql-serverbinlog2sql]#pythonbinlog2sql/binlog2sql.py-uroot-p'root'-h127.0.0.1-P23306-dtest-tuser--start-file='mysql-bin.000004'--start-datetime='2018-09-1417:00:00'--stop-datetime='2018-09-1418:25:00'>/tmp/20180914_raw.sql(venv4archer)[root@mysql-serverbinlog2sql]#cat/tmp/20180914_raw.sqlUSEb'test';createtableuser(idint(12)unsignedauto_incrementcomment'id'primarykey,namevarchar(15),add_timetimestamp);INSERTINTO`test`.`user`(`id`,`name`,`add_time`)VALUES(1,'neo','2018-09-0100:00:00');#start411end824time2018-09-1418:22:33INSERTINTO`test`.`user`(`id`,`name`,`add_time`)VALUES(2,'trinity','2018-09-0200:00:00');#start411end824time2018-09-1418:22:33INSERTINTO`test`.`user`(`id`,`name`,`add_time`)VALUES(3,'jason','2018-09-0500:00:00');#start411end824time2018-09-1418:22:33DELETEFROM`test`.`user`WHERE`id`=1AND`name`='neo'AND`add_time`='2018-09-0100:00:00'LIMIT1;#start855end1187time2018-09-1418:23:31DELETEFROM`test`.`user`WHERE`id`=2AND`name`='trinity'AND`add_time`='2018-09-0200:00:00'LIMIT1;#start855end1187time2018-09-1418:23:31

解析出回滚SQL

(venv4archer)[root@mysql-serverbinlog2sql]#pythonbinlog2sql/binlog2sql.py-uroot-p'root'-h127.0.0.1-P23306-dtest-tuser--start-file='mysql-bin.000004'--start-datetime='2018-09-1417:00:00'--stop-datetime='2018-09-1418:25:00'-B>/tmp/20180914_rollback.sql(venv4archer)[root@mysql-serverbinlog2sql]#cat//tmp/20180914_rollback.sqlINSERTINTO`test`.`user`(`id`,`name`,`add_time`)VALUES(2,'trinity','2018-09-0200:00:00');#start855end1187time2018-09-1418:23:31INSERTINTO`test`.`user`(`id`,`name`,`add_time`)VALUES(1,'neo','2018-09-0100:00:00');#start855end1187time2018-09-1418:23:31DELETEFROM`test`.`user`WHERE`id`=3AND`name`='jason'AND`add_time`='2018-09-0500:00:00'LIMIT1;#start411end824time2018-09-1418:22:33DELETEFROM`test`.`user`WHERE`id`=2AND`name`='trinity'AND`add_time`='2018-09-0200:00:00'LIMIT1;#start411end824time2018-09-1418:22:33DELETEFROM`test`.`user`WHERE`id`=1AND`name`='neo'AND`add_time`='2018-09-0100:00:00'LIMIT1;#start411end824time2018-09-1418:22:33