您好,欢迎来到站长目录(28sn.com)!


使用binlog2sql工具来恢复数据库

来源:网络整理 浏览:206次 时间:2021-10-27

(一)binlog2sql介绍

binlog2sql是国内MySQL大佬danfengcao开发,许多MySQL爱好者参与改进的一款MySQL binlog解析软件。根据不同选项,可以得到原始SQL、回滚SQL、去除主键的SQL等。

github地址为:https://github.com/danfengcao/binlog2sql

该工具主要用于:

  • 数据快速回滚(闪回);
  • 从binlog生成标准SQL;

适用MySQL版本:MySQL5.6 、MySQL5.7


(二)安装binlog2sql




# 安装gitshell> yum install -y git# 安装pip工具shell> yum install -y epel-releaseshell> yum install -y python-pip# 安装binlog2sqlshell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sqlshell> pip install -r requirements.txt



(三)使用binlog2sql

要使用binlog2sql,MySQL服务器需要设置以下参数:


[mysqld]server_id = 1log_bin = /var/log/mysql/mysql-bin.logmax_binlog_size = 100Mbinlog_format = rowbinlog_row_image = full



binlog2sql在使用时需要连接到数据库上,连接用户的权限为:

select,spuer/replication client,replication slave-- 建议授权:grant select,replication slave,replication client on *.* to user;


binlog2sql的语法为:


[root@masterdb binlog2sql]# pwd/root/binlog2sql/binlog2sql[root@masterdb binlog2sql]# python binlog2sql.py --helpusage: binlog2sql.py [-h HOST] [-u USER] [-p [PASSWORD [PASSWORD ...]]]                     [-P PORT] [--start-file START_FILE]                     [--start-position START_POS] [--stop-file END_FILE]                     [--stop-position END_POS] [--start-datetime START_TIME]                     [--stop-datetime STOP_TIME] [--stop-never] [--help]                     [-d [DATABASES [DATABASES ...]]]                     [-t [TABLES [TABLES ...]]] [--only-dml]                     [--sql-type [SQL_TYPE [SQL_TYPE ...]]] [-K] [-B]                     [--back-interval BACK_INTERVAL]

语法解析:

MySQL链接配置参数

  • -h host; –p password; –u user ; -P port

解析模式参数:

  • --stop-never : 持续解析binlog,同步至执行命令时最新的binlog位置,默认false。
  • -K , --no-primary-key : 对insert语句去除主键,默认false。
  • -B , --flashback : 生成回滚SQL,可解析大文件,不受内存限制,默认false。与stop-never或no-primary-key不能同时添加。
  • --back-interval :在-B模式下,每打印1000条回滚SQL,SLEEP多少秒,默认为1。

范围控制参数:

  • --start-file :起始解析文件,只需文件名,无需全路径。必需参数。
  • --stop-file / --end-file : 终止解析文件,默认为start-file同一个文件,若解析模式为stop-never,则该参数失效。
  • --start-position / --start-pos : 起始解析位置,默认start-file文件起始位置。
  • --stop-position / --stop-pos : 终止解析位置,默认stop-file终点位置。
  • --start-datetime :起始解析时间,格式为’%Y-%m-%d %H:%M:%S’。
  • --stop-datetime :终止解析时间,格式为’%Y-%m-%d %H:%M:%S’

对象过滤参数:

  • -d , --databases : 只解析目标DB的sql,多个数据库用空格隔开,如:-d db1 db2。
  • -t  , --tables : 只解析目标表的sql,多张表用空格隔开,如:-t table1 table2。
  • --only-dml :只解析DML,忽略DDL。
  • --sql-type : 只解析指定类型,支持INSERT,UPDATE,DELETE。多个类型用空格隔开,如--sql-type INSERT UPDATE,默认全解析。


(四)binlog2sql测试

测试目的:本次实验模拟误删除数据,通过使用binlog2sql将数据找回来。

STEP1:构造测试数据


--构造测试表create table test01 (    id        int          primary key,    name      varchar(30)  not null,    birthday  date         not null );-插入3条数据insert into test01 values(1,'小明','1993-01-02'); insert into test01 values(2,'小华','1994-08-15'); insert into test01 values(3,'小丽','1995-07-12');mysql> select * from test01; +----+--------+------------+ | id | name   | birthday   | +----+--------+------------+ |  1 | 小明   | 1993-01-02 | |  2 | 小华   | 1994-08-15 | |  3 | 小丽   | 1995-07-12 | +----+--------+------------+3 rows in set (0.00 sec)



STEP2:模拟误删除数据

mysql> delete from test01;Query OK, 3 rows affected (0.00 sec)


STEP3:确认最后的日志


mysql> show master status;+-------------------+----------+--------------+------------------+-------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-------------------+----------+--------------+------------------+-------------------+| master-bin.000001 |     1600 |              |                  |                   |+-------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)



STEP4:解析出标准SQL,用于定位回滚的开始和结束位置


[root@masterdb binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p123456 -dlijiamandb -t test01 --sql-type DELETE --start-file='master-bin.000001' USE lijiamandb; create table test01 (    id        int          primary key,    name      varchar(30)  not null,    birthday  date         not null ); DELETE FROM `lijiamandb`.`test01` WHERE `birthday`='1993-01-02' AND `id`=1 AND `name`='小明' LIMIT 1; #start 1287 end 1569 time 2020-04-24 13:40:26 DELETE FROM `lijiamandb`.`test01` WHERE `birthday`='1994-08-15' AND `id`=2 AND `name`='小华' LIMIT 1; #start 1287 end 1569 time 2020-04-24 13:40:26 DELETE FROM `lijiamandb`.`test01` WHERE `birthday`='1995-07-12' AND `id`=3 AND `name`='小丽' LIMIT 1; #start 1287 end 1569 time 2020-04-24 13:40:26


值得注意的是,虽然我指定了只解析DELETE语句,但还是把DDL给解析出来了。


STEP5:解析出回滚SQL

[root@masterdb binlog2sql]# python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p123456 --flashback  -dlijiamandb -t test01 --sql-type DELETE --start-file='master-bin.000001' INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1995-07-12', 3, '小丽'); #start 1287 end 1569 time 2020-04-24 13:40:26INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1994-08-15', 2, '小华'); #start 1287 end 1569 time 2020-04-24 13:40:26INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1993-01-02', 1, '小明'); #start 1287 end 1569 time 2020-04-24 13:40:26


STEP6:还原到数据库



[root@masterdb binlog2sql]# mysql -uroot -p123456 lijiamandbmysql> INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1995-07-12', 3, '小丽'); #start 1287 end 1569 time 2020-04-24 13:40:2669 time 2020-04-24 13:40:26INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1993-01-02', 1, '小明'); #start 1287 end 1569 time 2020-04-24 13:40:26Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1994-08-15', 2, '小华'); #start 1287 end 1569 time 2020-04-24 13:40:26Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO `lijiamandb`.`test01`(`birthday`, `id`, `name`) VALUES ('1993-01-02', 1, '小明'); #start 1287 end 1569 time 2020-04-24 13:40:26Query OK, 1 row affected (0.00 sec)mysql> select * from test01;+----+--------+------------+ | id | name   | birthday   |+----+--------+------------+ |  1 | 小明   | 1993-01-02 | |  2 | 小华   | 1994-08-15 | |  3 | 小丽   | 1995-07-12 |+----+--------+------------+3 rows in set (0.00 sec)



(五)总结

使用binlog2sql最大的好处就是解析出来的SQL语句非常直观,并且在注释中还包含了时间,这对于我们去查找故障发生点非常实用。想一想之前用过的mysqlbinlog工具,解析出来的结果中含有大量无关的信息,为我们排查问题增加了难度,而binlog2sql解析出来的SQL非常干净,便于我们排查问题,恢复数据。


推荐站点

  • 我爱发烧音乐我爱发烧音乐

    我爱发烧音乐囊括了从流行音乐到古典音乐多个类型的音乐作品,专栏推荐最新的音乐,提供音乐排名榜单!可供免费线上收听音乐,歌曲流畅,音效极佳! 网站提供的钢琴以及二胡专栏,可供收听者,陶冶情操,改善心情,是难得的轻音乐典藏!

    www.520fs.com
  • 世纪音乐网世纪音乐网

    世纪音乐网是专业的在线音乐试听MP3下载网站。歌曲总计30余万首,收录了网上最新歌曲和流行音乐,DJ舞曲,非主流音乐,经典老歌,劲舞团歌曲,搞笑歌曲,儿童歌曲,英文歌曲等。是您上网听歌的最佳网站。

    www.ssjj.com
  • 杭州网杭州网

      杭州网是杭州地区唯一的新闻门户网站,由中共杭州市委宣传部、杭州日报报业集团和杭州广播电视集团共同组建的杭州网络传媒有限公司运营。

    www.hangzhou.com.cn
  • 深圳在线深圳在线

      深圳在线 www.szol.net是深圳本地最大、最早的地方生活资讯网站之一,网站名“深圳在线www.szol.net”由南方报业传媒集团编辑委员会总编辑、南方日报社总编辑、南方都市报总编辑、南方书画院名誉院长王春芙亲笔题名,深圳在线www.szol.net团队与深圳热线www.szonline.net、奥一网www.oeeee.com都源于全国最早成立于1996年的知名网络公司——深圳万用网。

    www.szol.net
  • 今题网今题网

     今题网- 中国领先的社区服务网,提供社区服务, 在线交友和商家推广服务,于2004年创建上线,公司现有员工超过百名。今题网自成立以来,凭借其独特的定位和丰富的社区交友功能, 凭借其团队超强的搜索引擎优化技术吸引超过千万的用户成为今题网的注册会员。

    www.jinti.com

鄂公网安备 42062502000001号