• 【MySQL】binlog2sql


    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
    
  • 相关阅读:
    python测试开发django-43.xadmin添加小组件报错解决
    python测试开发django-42.xadmin自定义菜单项
    python测试开发django-41.crispy-forms设计标签式导航菜单(TabHolder)
    python测试开发django-40.模型(model)中choices使用
    python测试开发django-39.xadmin详情页面布局form_layout
    python测试开发django-45.xadmin添加小组件报错解决
    django后台xadmin如下配置(小结)
    禅道环境一键安装搭建指南
    Centos6.9部署ORTS5.0.22
    Linux 进程管理之四大名捕
  • 原文地址:https://www.cnblogs.com/zhangshengdong/p/11724922.html
Copyright © 2020-2023  润新知