841.MySQL使用安装pip和binlog2sql
一.安装pip和binlog2sql
安装依赖包
yum install libffi-devel
yum -y install gcc automake autoconf libtool make
yum install zlib-devel
1.1 安装步骤
cd /usr/local/src
wget https://bootstrap.pypa.io/get-pip.py
python get-pip.py
pip -V （查看pip是否安装成功）
yum -y install git
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt
1.2 安装python3
– 下载python
wget https://npm.taobao.org/mirrors/python/3.8.0/Python-3.8.0.tar.xz
– 解压
tar -xf Python-3.8.0.tar.xz
– 编译
cd Python-3.8.0
./configure --prefix=/usr/local/python3 --with-openssl=/usr/local/python3 --with-http_ssl_module && make && make install
– 备份 2.7
mv /usr/bin/python /usr/bin/python_2.7
– 软链
ln -sv /usr/local/python3/bin/python3.8 /usr/bin/python
–验证
输入 python，查看是否已经升级到 3.8 版本。
– 解决升级后, yum 无法正常使用的问题
vi /usr/libexec/urlgrabber-ext-down
将顶部的 /usr/bin/python 改为 /usr/bin/python2
vi /usr/bin/yum
将顶部的 /usr/bin/python 改为 /usr/bin/python2
– 将python的bin目录加入到环境变量
cd
vi .bash_profile
/usr/local/python3/bin
1.3 安装新版本openssl
最新的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
1.4 pip安装报错1
报错信息
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解决问题
1.5 pip安装报错2
报错信息:
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]#
1.6 pip安装binlog2sql报错
报错信息:
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 PyMySQL0.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 wheel0.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 “
File “/usr/local/python3/lib/python3.8/site-packages/setuptools/init.py”, line 21, in
from setuptools.dist import Distribution
File “/usr/local/python3/lib/python3.8/site-packages/setuptools/dist.py”, line 34, in
from setuptools import windows_support
File “/usr/local/python3/lib/python3.8/site-packages/setuptools/windows_support.py”, line 2, in
import ctypes
File “/usr/local/python3/lib/python3.8/ctypes/init.py”, line 7, in
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
二.binlog2sql参数相关
2.1 binlog2sql参数
python binlog2sql/binlog2sql.py --help
选项
mysql连接配置
-h host; -P port; -u user; -p password
解析模式
–stop-never 持续解析binlog。可选。，默认False，同步至执行命令时最新的binlog位置。
-K, --no-primary-key 对INSERT语句去除主键。可选。默认False
-B, --flashback 生成回滚SQL，可解析大文件，不受内存限制。可选。默认False。与stop-never或no-primary-key不能同时添加。
–back-interval -B模式下，每打印一千行回滚SQL，加一句SLEEP多少秒，如不想加SLEEP，请设为0。可选。默认1.0。
解析范围控制
–start-file 起始解析文件，只需文件名，无需全路径。必须。
–start-position/–start-pos 起始解析位置。可选。默认为start-file的起始位置。
–stop-file/–end-file 终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never，此选项失效。
–stop-position/–end-pos 终止解析位置。可选。默认为stop-file的最末位置；若解析模式为stop-never，此选项失效。
–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 只解析目标table的sql，多张表用空格隔开，如-t tbl1 tbl2。可选。默认为空。
–only-dml 只解析dml，忽略ddl。可选。默认TRUE。
–sql-type 只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开，如–sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。
2.2 mysql server相关参数
在配置文件my.cnf的mysqld这个区下设置
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
在运行中的mysql中查看
show variables like ‘server_id’;
show variables like ‘log_bin%’;
show variables like ‘max_binlog_size’;
show variables like ‘binlog_format’;
show variables like ‘binlog_row_image’;
2.3 用来闪回数据的user需要的最小权限集合
建议授权
select, super/replication client, replication slave
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON . TO repl@‘localhost’ identified by ‘123456’;
select：需要读取server端information_schema.COLUMNS表，获取表结构的元信息，拼接成可视化的sql语句
super/replication client：两个权限都可以，需要执行’SHOW MASTER STATUS’, 获取server端的binlog列表
replication slave：通过BINLOG_DUMP协议获取binlog内容的权限
三.binlog2sql使用案例
3.1 数据准备
首先我往表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)
3.2 使用binlog2sql找到恢复sql
cd /usr/local/src/binlog2sql/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’
运行记录
[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 INTOtest.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 INTOtest.t3(id,name) VALUES (3, ‘ghi’); #start 16059226 end 16059456 time 2020-08-27 13:48:14
UPDATEtest.t3SETid=1,name=‘abc’ WHEREid=1 ANDname=‘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 © 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)
总结：
已测试环境
Python 3.4+
MySQL 5.7
从MySQL binlog解析出你要的SQL。根据不同选项，你可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。
用途
数据快速回滚(闪回)
主从切换后新master丢数据的修复
从binlog生成标准SQL，带来的衍生功能
