github连接:https://github.com/danfengcao/binlog2sql
已测试环境 Python 2.7, 3.4+ MySQL 5.6, 5.7
最后一次更新是2年前的事情了，一些开源工具上线生产的时候还是需要注意。 MySQL8.0的暂时还是不要使用了。
从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。
用途 数据快速回滚(闪回) 主从切换后新master丢数据的修复 从binlog生成标准SQL，带来的衍生功能
安装依赖包
yum install libffi-devel yum -y install gcc automake autoconf libtool make yum install zlib-devel最新的openssl包, 因为 python3.8 需要使用 1.1 以上的包, 否则访问ssl 地址会报错
-- 下载软件 wget https://github.com/openssl/openssl/archive/OpenSSL_1_1_1d.tar.gz -- 解压 tar -zxvf OpenSSL_1_1_1d.tar.gz cd OpenSSL_1_1_1d -- 编译安装 mkdir /usr/local/openssl ./config --prefix=/usr/local/openssl make make install -- 备份老版本openssl，创建新版本软连接 mv /usr/bin/openssl /usr/bin/openssl.old mv /usr/include/openssl /usr/include/openssl.old ln -s /usr/local/openssl/bin/openssl /usr/bin/openssl ln -s /usr/local/openssl/include/openssl /usr/include/openssl -- 把openssl lib路径 /usr/local/openssl/lib 追加到下面到文件中 vi /usr/local/openssl/lib -- 创建软连接 ln -s /usr/local/openssl/lib/libssl.so.1.1 /usr/lib64/libssl.so.1.1 ln -s /usr/local/openssl/lib/libssl.so.1.1 /usr/lib64/libssl.so.1.1 -- 验证 openssl version安装记录
[root@10-31-1-119 openssl-OpenSSL_1_1_1d]# more /etc/ld.so.conf include ld.so.conf.d/*.conf:/usr/local/openssl/lib [root@10-31-1-119 openssl-OpenSSL_1_1_1d]# openssl version OpenSSL 1.1.1d 10 Sep 2019报错信息 DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won’t be maintained after that date. A future version of pip will drop support for Python 2.7. 安装pip的时候时候因为默认python是2.7版本，而pip对python2只支持到2020-01-01，故要升级到python3
将python2 升级到 python3解决问题
报错信息: could not fetch URL https://pypi.org/simple/pip/: There was a problem confirming the ssl certificate: HTTPSConnectionPool(host=‘pypi.org’, port=443): Max retries exceeded with url: /simple/pip/ (Caused by SSLError(“Can’t connect to HTTPS URL because the SSL module is not available.”)) 详细报错信息
[root@10-31-1-119 src]# python get-pip.py WARNING: pip is configured with locations that require TLS/SSL, however the ssl module in Python is not available. WARNING: Retrying (Retry(total=4, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/pip/ WARNING: Retrying (Retry(total=3, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/pip/ WARNING: Retrying (Retry(total=2, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/pip/ WARNING: Retrying (Retry(total=1, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/pip/ WARNING: Retrying (Retry(total=0, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError("Can't connect to HTTPS URL because the SSL module is not available.")': /simple/pip/ Could not fetch URL https://pypi.org/simple/pip/: There was a problem confirming the ssl certificate: HTTPSConnectionPool(host='pypi.org', port=443): Max retries exceeded with url: /simple/pip/ (Caused by SSLError("Can't connect to HTTPS URL because the SSL module is not available.")) - skipping ERROR: Could not find a version that satisfies the requirement pip (from versions: none) ERROR: No matching distribution found for pip WARNING: pip is configured with locations that require TLS/SSL, however the ssl module in Python is not available. Could not fetch URL https://pypi.org/simple/pip/: There was a problem confirming the ssl certificate: HTTPSConnectionPool(host='pypi.org', port=443): Max retries exceeded with url: /simple/pip/ (Caused by SSLError("Can't connect to HTTPS URL because the SSL module is not available.")) - skipping解决方案 加上国内的镜像源问题解决
[root@10-31-1-119 src]# python get-pip.py -i http://pypi.douban.com/simple --trusted-host pypi.douban.com Looking in indexes: http://pypi.douban.com/simple Collecting pip Downloading http://pypi.doubanio.com/packages/5a/4a/39400ff9b36e719bdf8f31c99fe1fa7842a42fa77432e584f707a5080063/pip-20.2.2-py2.py3-none-any.whl (1.5 MB) |████████████████████████████████| 1.5 MB 10.7 MB/s Collecting setuptools Downloading http://pypi.doubanio.com/packages/c3/a9/5dc32465951cf4812e9e93b4ad2d314893c2fa6d5f66ce5c057af6e76d85/setuptools-49.6.0-py3-none-any.whl (803 kB) |████████████████████████████████| 803 kB 49.3 MB/s Collecting wheel Downloading http://pypi.doubanio.com/packages/a7/00/3df031b3ecd5444d572141321537080b40c1c25e1caa3d86cdd12e5e919c/wheel-0.35.1-py2.py3-none-any.whl (33 kB) Installing collected packages: pip, setuptools, wheel Attempting uninstall: pip Found existing installation: pip 19.2.3 Uninstalling pip-19.2.3: Successfully uninstalled pip-19.2.3 WARNING: The scripts pip, pip3 and pip3.8 are installed in '/usr/local/python3/bin' which is not on PATH. Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location. Attempting uninstall: setuptools Found existing installation: setuptools 41.2.0 Uninstalling setuptools-41.2.0: Successfully uninstalled setuptools-41.2.0 WARNING: The scripts easy_install and easy_install-3.8 are installed in '/usr/local/python3/bin' which is not on PATH. Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location. WARNING: The script wheel is installed in '/usr/local/python3/bin' which is not on PATH. Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location. Successfully installed pip-20.2.2 setuptools-49.6.0 wheel-0.35.1 [root@10-31-1-119 src]#报错信息: ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.
[root@10-31-1-119 ~]# cd /usr/local/src/ [root@10-31-1-119 src]# git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql 正克隆到 'binlog2sql'... remote: Enumerating objects: 323, done. remote: Total 323 (delta 0), reused 0 (delta 0), pack-reused 323 接收对象中: 100% (323/323), 151.51 KiB | 10.00 KiB/s, done. 处理 delta 中: 100% (170/170), done. [root@10-31-1-119 binlog2sql]# [root@10-31-1-119 binlog2sql]# pip install -r requirements.txt bash: pip: 未找到命令... [root@10-31-1-119 binlog2sql]# source /root/.bash_profile [root@10-31-1-119 binlog2sql]# [root@10-31-1-119 binlog2sql]# [root@10-31-1-119 binlog2sql]# [root@10-31-1-119 binlog2sql]# pip install -r requirements.txt -i http://pypi.douban.com/simple --trusted-host pypi.douban.com Looking in indexes: http://pypi.douban.com/simple Collecting PyMySQL==0.7.11 Downloading http://pypi.doubanio.com/packages/c6/42/c54c280d8418039bd2f61284f99cb6d9e0eae80383fc72ceb6eac67855fe/PyMySQL-0.7.11-py2.py3-none-any.whl (78 kB) |████████████████████████████████| 78 kB 16.5 MB/s Collecting wheel==0.29.0 Downloading http://pypi.doubanio.com/packages/8a/e9/8468cd68b582b06ef554be0b96b59f59779627131aad48f8a5bce4b13450/wheel-0.29.0-py2.py3-none-any.whl (66 kB) |████████████████████████████████| 66 kB 10.9 MB/s Collecting mysql-replication==0.13 Downloading http://pypi.doubanio.com/packages/dd/23/384047702e694139e9fe75a8ba7ad007e8942fd119ebadabc32ce19f70f2/mysql-replication-0.13.tar.gz (33 kB) ERROR: Command errored out with exit status 1: command: /usr/bin/python -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-_og_eq1j/mysql-replication/setup.py'"'"'; __file__='"'"'/tmp/pip-install-_og_eq1j/mysql-replication/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' egg_info --egg-base /tmp/pip-pip-egg-info-fezrihiv cwd: /tmp/pip-install-_og_eq1j/mysql-replication/ Complete output (11 lines): Traceback (most recent call last): File "<string>", line 1, in <module> File "/usr/local/python3/lib/python3.8/site-packages/setuptools/__init__.py", line 21, in <module> from setuptools.dist import Distribution File "/usr/local/python3/lib/python3.8/site-packages/setuptools/dist.py", line 34, in <module> from setuptools import windows_support File "/usr/local/python3/lib/python3.8/site-packages/setuptools/windows_support.py", line 2, in <module> import ctypes File "/usr/local/python3/lib/python3.8/ctypes/__init__.py", line 7, in <module> from _ctypes import Union, Structure, Array ModuleNotFoundError: No module named '_ctypes' ---------------------------------------- ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output. [root@10-31-1-119 binlog2sql]# [root@10-31-1-119 binlog2sql]#解决方案
-- 安装依赖包 yum install libffi-devel -- 重新安装python3 -- 编译 cd Python-3.8.0 ./configure --prefix=/usr/local/python3 --with-openssl=/usr/local/python3 --with-http_ssl_module && make && make install首先我往表t3中录入了3条数据，然后进行了update和delete 现在的需求是恢复到最开始的3条insert的数据
mysql> create table t3(id int,name varchar(100)); Query OK, 0 rows affected (0.01 sec) mysql> select current_timestamp(); +---------------------+ | current_timestamp() | +---------------------+ | 2020-08-27 13:47:04 | +---------------------+ 1 row in set (0.01 sec) mysql> mysql> insert into t3 values (1,'abc'); Query OK, 1 row affected (0.01 sec) mysql> mysql> insert into t3 values (2,'def'); Query OK, 1 row affected (0.00 sec) mysql> insert into t3 values (3,'ghi'); Query OK, 1 row affected (0.01 sec) mysql> mysql> update t3 set name = 'test' where id <2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> delete from t3 where id = 3; Query OK, 1 row affected (0.07 sec) mysql> select * from t3; +------+------+ | id | name | +------+------+ | 1 | test | | 2 | def | +------+------+ 2 rows in set (0.00 sec) mysql> mysql> select current_timestamp(); +---------------------+ | current_timestamp() | +---------------------+ | 2020-08-27 13:48:46 | +---------------------+ 1 row in set (0.00 sec)运行记录
[root@10-31-1-119 binlog2sql]# cd /usr/local/src/binlog2sql/binlog2sql/ [root@10-31-1-119 binlog2sql]# pwd /usr/local/src/binlog2sql/binlog2sql [root@10-31-1-119 binlog2sql]# python binlog2sql.py -h 10.31.1.119 -u root -p abc123 -d test -t t3 --start-file='10-31-1-119-bin.000007' --start-datetime='2020-08-27 13:47:04' --stop-datetime='2020-08-27 13:48:46' INSERT INTO `test`.`t3`(`id`, `name`) VALUES (1, 'abc'); #start 16058170 end 16058400 time 2020-08-27 13:47:31 INSERT INTO `test`.`t3`(`id`, `name`) VALUES (2, 'def'); #start 16058431 end 16058661 time 2020-08-27 13:47:46 INSERT INTO `test`.`t3`(`id`, `name`) VALUES (3, 'ghi'); #start 16058692 end 16058922 time 2020-08-27 13:47:52 UPDATE `test`.`t3` SET `id`=1, `name`='test' WHERE `id`=1 AND `name`='abc' LIMIT 1; #start 16058953 end 16059195 time 2020-08-27 13:48:04 DELETE FROM `test`.`t3` WHERE `id`=3 AND `name`='ghi' LIMIT 1; #start 16059226 end 16059456 time 2020-08-27 13:48:14 [root@10-31-1-119 binlog2sql]#可以看到最后一个删除的 #start 16059226 end 16059456
python binlog2sql.py \ -h10.31.1.119 -P3306 -uroot -p'abc123' \ --start-file='10-31-1-119-bin.000007' \ -dtest -tt3 \ --start-position=16059226 \ --stop-position=16059456 \ -B > rollback.sql [root@10-31-1-119 binlog2sql]# python binlog2sql.py \ > -h10.31.1.119 -P3306 -uroot -p'abc123' \ > --start-file='10-31-1-119-bin.000007' \ > -dtest -tt3 \ > --start-position=16059226 \ > --stop-position=16059456 \ > -B > rollback.sql [root@10-31-1-119 binlog2sql]# [root@10-31-1-119 binlog2sql]# more rollback.sql INSERT INTO `test`.`t3`(`id`, `name`) VALUES (3, 'ghi'); #start 16059226 end 16059456 time 2020-08-27 13:48:14测试一次性生产多个回滚sql
python binlog2sql.py \ -h10.31.1.119 -P3306 -uroot -p'abc123' \ --start-file='10-31-1-119-bin.000007' \ -dtest -tt3 \ --start-position=16058953 \ --stop-position=16059456 \ -B > rollback.sql [root@10-31-1-119 binlog2sql]# python binlog2sql.py \ > -h10.31.1.119 -P3306 -uroot -p'abc123' \ > --start-file='10-31-1-119-bin.000007' \ > -dtest -tt3 \ > --start-position=16058953 \ > --stop-position=16059456 \ > -B > rollback2.sql [root@10-31-1-119 binlog2sql]# [root@10-31-1-119 binlog2sql]# more rollback2.sql INSERT INTO `test`.`t3`(`id`, `name`) VALUES (3, 'ghi'); #start 16059226 end 16059456 time 2020-08-27 13:48:14 UPDATE `test`.`t3` SET `id`=1, `name`='abc' WHERE `id`=1 AND `name`='test' LIMIT 1; #start 16058953 end 16059195 time 2020-08-27 13:48:04数据还原
[root@10-31-1-119 binlog2sql]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4912 Server version: 5.7.31-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> source rollback2.sql Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test.t3; +------+------+ | id | name | +------+------+ | 1 | abc | | 2 | def | | 3 | ghi | +------+------+ 3 rows in set (0.00 sec)1.https://blog.csdn.net/letterss/article/details/93736959 2.https://www.jianshu.com/p/ba1a3700e6e6 3.https://blog.csdn.net/rdisme/article/details/103414435 4.https://www.cnblogs.com/eagleking/p/10908608.html 5.https://blog.csdn.net/qq_42353939/article/details/94609591 6.https://www.jianshu.com/p/ba1a3700e6e6
