Binlog2sql+CentOS7 离线安装
1. 环境
CentOS Linux release 7.7.1908 (Core)
Mysql 8.0.20
Mysql 5.7.29
数据库已开启Binlog
2. 下载
下载Python-3.8.2：https://www.python.org/ftp/python/3.8.2/Python-3.8.2.tar.xz (3.8.3 Make失败，3.8.2安装正常)
下载Binlog2sql: https://codeload.github.com/danfengcao/binlog2sql/zip/master
下载PyMySQL-0.9.3 https://codeload.github.com/PyMySQL/PyMySQL/tar.gz/v0.9.3
下载mysql-replication-0.21
相关主页
Binlog2sql开源主页 https://github.com/danfengcao/binlog2sql
PyMysql开源历史版本 https://github.com/PyMySQL/PyMySQL/releases
mysql-replication 历史版本 https://pypi.org/project/mysql-replication/#history
3 安装
3.1 Pip 安装
yum install -y zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-devel gcc libffi-devel
tar -xvf Python-3.8.2.tar.xz && cd Python-3.8.2
./configure --prefix=/usr --with-ensurepip --with-system-ffi
make && make install
[root@mysql1 Python-3.8.2]# python --version
Python 2.7.5
[root@mysql1 Python-3.8.2]# python3 --version
Python 3.8.2
[root@mysql1 Python-3.8.2]# pip3 --version
pip 19.2.3 from /usr/lib/python3.8/site-packages/pip (python 3.8)
3.2 PyMySQL/mysql-replication 安装
pip3 install PyMySQL-0.9.3.tar.gz
pip3 install mysql-replication-0.21.tar.gz
[root@mysql1 ~]# pip3 list
Package Version
----------------- -------
mysql-replication 0.21
pip 19.2.3
PyMySQL 0.9.3
setuptools 41.2.0
3.3 解压缩 Binlog2sql
unzip binlog2sql-master.zip
mv binlog2sql-master/binlog2sql ./
rm -rf binlog2sql-master
binlog2sql 解压即可，不需要安装,且只有binlog2sql 文件夹是我们真正需要的。
4. 案例：误删除数据恢复
案例准备：建库，建表，并插入数据
故障模拟：误删除数据
恢复目标：找回数据
4.1 案例准备
create database cym;
use cym;
create table t1(id int,name varchar(10),addtime datetime default now());
insert into t1 values(1,'赵',now()),(2,'钱',now()),(3,'孙','2022-01-12 12:12:12'),(4,'李','2000-12-12 1:00:00');
select * from t1;
mysql> select * from t1;
+------+------+---------------------+
| id | name | addtime |
+------+------+---------------------+
| 1 | 赵 | 2020-05-01 14:01:04 |
| 2 | 钱 | 2020-05-01 14:01:04 |
| 3 | 孙 | 2022-01-12 12:12:12 |
| 4 | 李 | 2000-12-12 01:00:00 |
+------+------+---------------------+
4 rows in set (0.00 sec)
4.2 故障模拟
select now();
delete from cym.t1;
select * from cym.t1;
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-05-01 14:02:43 |
+---------------------+
1 row in set (0.00 sec)
mysql> delete from cym.t1;
Query OK, 4 rows affected (0.00 sec)
mysql> select * from cym.t1;
Empty set (0.00 sec)
-- 确定数据丢失
4.3 故障恢复
我们可以使用root 操作，也可以设置专门的最小权限binlog挖掘用户
create user binlog2sql identified by 'binlog2sql';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON . TO binlog2sql;
4.3.1 检索需要用到的binlog文件
mysql -ubinlog2sql -pbinlog2sql -e 'show master status'
[root@mysql1 ~]# mysql -ubinlog2sql -pbinlog2sql -e 'show master status'
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000013 | 3156 | | | fa9a20b5-831c-11ea-b919-080027a0316a:1-17 |
+------------------+----------+--------------+------------------+-------------------------------------------+
如果删除的时间比较久，我们需要根据大概的时间范围，结合binlog最后更新时间，确定可能用到的binlog 文件
获取binlog 位置：
mysql -ubinlog2sql -pbinlog2sql -e 'select @@log_bin_basename'
[root@mysql1 ~]# mysql -ubinlog2sql -pbinlog2sql -e 'select @@log_bin_basename'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------------------+
| @@log_bin_basename |
+-----------------------------+
| /mysqldata/binlog/mysql-bin |
+-----------------------------+
[root@mysql1 ~]# ll /mysqldata/binlog/mysql-bin*
-rw-r-----. 1 mysql mysql 217 Apr 28 19:24 /mysqldata/binlog/mysql-bin.000009
-rw-r-----. 1 mysql mysql 241 Apr 28 21:01 /mysqldata/binlog/mysql-bin.000010
-rw-r-----. 1 mysql mysql 194 May 1 12:25 /mysqldata/binlog/mysql-bin.000011
-rw-r-----. 1 mysql mysql 217 May 1 13:26 /mysqldata/binlog/mysql-bin.000012
-rw-r-----. 1 mysql mysql 3156 May 1 14:05 /mysqldata/binlog/mysql-bin.000013
-rw-r-----. 1 mysql mysql 455 May 1 13:26 /mysqldata/binlog/mysql-bin.index
--以上时间为binlog 最后变更时间
4.3.2 获取误删除操作的undo语句
已知：
误操作时间范围：2020-05-01 14:02:43 之后
mysql-bin.000012 包含的13：26之前的操作
所以我们要挖掘的binlog文件为：mysql-bin.000013
获取Undo语句
python binlog2sql/binlog2sql.py -ubinlog2sql -pbinlog2sql -dcym -t t1 -B --start-file='mysql-bin.000013' --start-datetime='2020-05-01 14:02:43' --only-dml
[root@mysql1 ~]# python binlog2sql/binlog2sql.py -ubinlog2sql -pbinlog2sql -dcym -t t1 --start-file='mysql-bin.000013' --start-datetime='2020-05-01 14:02:43' --only-dml
DELETE FROM `cym`.`t1` WHERE `addtime`='2020-05-01 14:01:04' AND `id`=1 AND `name`='赵' LIMIT 1; #start 2427 end 2638 time 2020-05-01 14:02:43
DELETE FROM `cym`.`t1` WHERE `addtime`='2020-05-01 14:01:04' AND `id`=2 AND `name`='钱' LIMIT 1; #start 2427 end 2638 time 2020-05-01 14:02:43
DELETE FROM `cym`.`t1` WHERE `addtime`='2022-01-12 12:12:12' AND `id`=3 AND `name`='孙' LIMIT 1; #start 2427 end 2638 time 2020-05-01 14:02:43
DELETE FROM `cym`.`t1` WHERE `addtime`='2000-12-12 01:00:00' AND `id`=4 AND `name`='李' LIMIT 1; #start 2427 end 2638 time 2020-05-01 14:02:43
我们需要把undo 语句保存为sql文件
python binlog2sql/binlog2sql.py -ubinlog2sql -pbinlog2sql -dcym -t t1 -B --start-file='mysql-bin.000013' --start-datetime='2020-05-01 14:02:43' --only-dml|cut -d '#' -f1 >t1.sql
[root@mysql1 ~]# python binlog2sql/binlog2sql.py -ubinlog2sql -pbinlog2sql -dcym -t t1 -B --start-file='mysql-bin.000013' --start-datetime='2020-05-01 14:02:43' --only-dml|cut -d '#' -f1 >t1.sql
[root@mysql1 ~]# cat t1.sql
INSERT INTO `cym`.`t1`(`addtime`, `id`, `name`) VALUES ('2000-12-12 01:00:00', 4, '李');
INSERT INTO `cym`.`t1`(`addtime`, `id`, `name`) VALUES ('2022-01-12 12:12:12', 3, '孙');
INSERT INTO `cym`.`t1`(`addtime`, `id`, `name`) VALUES ('2020-05-01 14:01:04', 2, '钱');
INSERT INTO `cym`.`t1`(`addtime`, `id`, `name`) VALUES ('2020-05-01 14:01:04', 1, '赵');
4.3.3 执行恢复操作
可以使用业务用户，也可以使用超级用户恢复数据。
mysql -uroot -proot < t1.sql
mysql -uroot -proot -e 'select * from cym.t1'
[root@mysql1 ~]# mysql -uroot -proot -e 'select * from cym.t1'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+---------------------+
| id | name | addtime |
+------+------+---------------------+
| 4 | 李 | 2000-12-12 01:00:00 |
| 3 | 孙 | 2022-01-12 12:12:12 |
| 2 | 钱 | 2020-05-01 14:01:04 |
| 1 | 赵 | 2020-05-01 14:01:04 |
+------+------+---------------------+
完成恢复
5. Binlog2sql 可用参数
python binlog2sql/binlog2sql.py
参考：https://github.com/danfengcao/binlog2sql#选项
可选参数
--stop-never
-K, --no-primary-key
-B, --flashback
--back-interval
连接参数
-h -u -p -P
位置过滤参数
--start-file
--stop-file
--start-position
--stop-position
--start-datetime
--stop-datetime
对象过滤参数
-d DATABASES2 DATABASES2
-t TABLE1 TABLE2
类型过滤参数
--only-dml
--sql-type INSERT UPDATE DELETE