binlog2sql
1.安装
shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
shell> pip install -r requirements.txt
2.用户授权
create user 'zsd'@'localhost' identified by 'zsd';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'zsd'@'localhost';
直接的sql场景
MariaDB [zsd]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-09-29 14:21:15 |
+---------------------+
1 row in set (0.020 sec)
MariaDB [zsd]> create table test2(id int,name varchar(20));
MariaDB [zsd]> insert into test2 values (1,'张三');
MariaDB [zsd]> insert into test2 values (1,'李四');
MariaDB [zsd]> insert into test2 values (1,'王五');
MariaDB [zsd]> select * from test2;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
| 1 | 李四 |
| 1 | 王五 |
+------+--------+
3 rows in set (0.025 sec)
MariaDB [zsd]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-09-29 14:22:32 |
+---------------------+
1 row in set (0.014 sec)
MariaDB [zsd]> delete from test2;
Query OK, 3 rows affected (0.063 sec)
MariaDB [zsd]> commit;
Query OK, 0 rows affected (0.008 sec)
MariaDB [zsd]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-09-29 14:22:50 |
+---------------------+
1 row in set (0.014 sec)
MariaDB [zsd]> select * from test2;
Empty set (0.012 sec)
查看被删除数据的位置和时间。
[root@oradb binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uzsd -p'zsd' -dzsd -ttest2 --start-file='mysql-bin.000003' --start-datetime='2019-09-29 14:22:32' --stop-datetime='2019-09-29 14:22:50'
# D;
DELETE FROM `zsd`.`test2` WHERE `id`=1 AND `name`='张三' LIMIT 1; #start 3717 end 3918 time 2019-09-29 14:22:43
DELETE FROM `zsd`.`test2` WHERE `id`=1 AND `name`='李四' LIMIT 1; #start 3717 end 3918 time 2019-09-29 14:22:43
DELETE FROM `zsd`.`test2` WHERE `id`=1 AND `name`='王五' LIMIT 1; #start 3717 end 3918 time 2019-09-29 14:22:43
加上-B
参数,把delete
语句变成insert语句
。用于数据的恢复。
[root@oradb binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uzsd -p'zsd' -dzsd -ttest2 --start-file='mysql-bin.000003' --start-datetime='2019-09-29 14:22:32' --stop-datetime='2019-09-29 14:22:50' -B
INSERT INTO `zsd`.`test2`(`id`, `name`) VALUES (1, '王五'); #start 3717 end 3918 time 2019-09-29 14:22:43
INSERT INTO `zsd`.`test2`(`id`, `name`) VALUES (1, '李四'); #start 3717 end 3918 time 2019-09-29 14:22:43
INSERT INTO `zsd`.`test2`(`id`, `name`) VALUES (1, '张三'); #start 3717 end 3918 time 2019-09-29 14:22:43