binlog2sql离线安装

原创福娃筱欢恩墨学院 2023-09-19

773

生产环境大多不联网，故需要离线安装。操作系统默认已有Python2.7.5。保留Python2.7.5，安装Python3.8.2，不更改原有环境变量参数，避免有应用和数据库在同一台服务器情况下影响应用正常运行。

环境

CentOS Linux release 7.6
Mysql 8.0.20
Mysql 5.7.42

数据库已开启binlog，验证方法如下：

法一：配置文件
[root@localhost ~]# cat /etc/my.cnf | grep bin
log_bin = /data/mysqldb/binlog/mybinlog  #二进制日志文件
binlog_format = ROW
max_binlog_size = 100M #限制单个文件大小,默认大小:1,073,741,824,即1G,太大了
binlog_cache_size = 4M
max_binlog_cache_size = 50M

法二：数据目录下是否有binlog文件。目录视情况更改，有的binlog单独目录，有的和数据目录共用1个。
该示例是以binlog单独目录显示。
[root@localhost ~]# ls -l /data/mysqldb/binlog/
total 36
-rw-r-----. 1 mysql mysql  177 Aug  9 08:00 mybinlog.000001
-rw-r-----. 1 mysql mysql  177 Aug  9 08:01 mybinlog.000002
-rw-r-----. 1 mysql mysql 2136 Aug  9 08:17 mybinlog.000003

注：如果满足其中1种即说明开启binlog

binlog格式，需是row格式
show variables like 'binlog_format';

--输出结果
root@localhost :fuwa 14:18:41>show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

下载

下载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
https://files.pythonhosted.org/packages/e3/54/8c496e300d610299bf168e2068dc10a64b66b299cbe596a27aac5d5b3e7b/mysql-replication-0.21.tar.gz
相关主页
Binlog2sql开源主页 https://github.com/danfengcao/binlog2sql
PyMysql开源历史版本 https://github.com/PyMySQL/PyMySQL/releases
mysql-replication 历史版本 https://pypi.org/project/mysql-replication/#history

安装

Pip 安装

Pip包含在Python里，Python安装后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

--查看python pip版本
[root@localhost Python-3.8.2]# python --version
Python 2.7.5
[root@localhost Python-3.8.2]# python3 --version
Python 3.8.2
[root@localhost Python-3.8.2]# pip3 --version #pip包含在python里，python安装后pip就有了
pip 19.2.3 from /usr/lib/python3.8/site-packages/pip (python 3.8)

PyMySQL/mysql-replication 安装

pip3 install PyMySQL-0.9.3.tar.gz
pip3 install mysql-replication-0.21.tar.gz
pip3 list 

[root@localhost ~]# pip3 list
Package           Version
----------------- -------
mysql-replication 0.21   
pip               19.2.3 
PyMySQL           0.9.3  
setuptools        41.2.0

解压缩 Binlog2sql

binlog2sql 解压即可，不需要安装,只有binlog2sql 文件夹是需要的，真正有用的。

unzip binlog2sql-master.zip
mv binlog2sql-master/binlog2sql ./
rm -rf binlog2sql-master

案例：误删除数据恢复

案例准备：建库，建表，并插入数据
故障模拟：误删除数据
恢复目标：找回数据

案例准备

create database fuwa;
use fuwa;
create table fuwa(id int,name varchar(10),addtime datetime default now());
insert into fuwa values(1,'赵',now()),(2,'钱',now()),(3,'孙','2022-01-12 12:12:12'),(4,'李','2000-12-12 1:00:00');
select * from fuwa;

root@localhost :fuwa 08:25:05>select * from fuwa;
+------+------+---------------------+
| id   | name | addtime             |
+------+------+---------------------+
|    1 | 赵   | 2023-09-19 08:25:05 |
|    2 | 钱   | 2023-09-19 08:25:05 |
|    3 | 孙   | 2022-01-12 12:12:12 |
|    4 | 李   | 2000-12-12 01:00:00 |
+------+------+---------------------+

故障模拟

select now();
delete from fuwa.fuwa;
select * from fuwa.fuwa;

root@localhost :fuwa 08:26:06>select * from fuwa.fuwa;
Empty set (0.00 sec)   #说明数据已被误删除

故障恢复

本模拟环境以法二为例，若使用法一:root用户，只需要更改-u后面的用户为root即可。
法一：使用root 操作
法二：设置专门的最小权限binlog挖掘用户

create user binlog2sql identified by 'binlog2sql';
grant select, replication slave, replication client on *.* to binlog2sql;#*.*代表：库.表
或
grant select, replication slave, replication client on fuwa.* to binlog2sql;  #fuwa即库名

检索需要用到的binlog文件

mysql -ubinlog2sql -pbinlog2sql -e 'flush logs';
mysql -ubinlog2sql -pbinlog2sql -e 'show master status';

--输出结果
[root@localhost ~]# mysql -ubinlog2sql -pbinlog2sql -e 'show master status';
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mybinlog.000008 |     1636 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+

如果删除的时间比较久，可根据大概的时间范围，结合binlog最后更新时间，确定可能用到的binlog 文件
获取binlog 位置：

mysql -ubinlog2sql -pbinlog2sql -e 'select @@log_bin_basename'

--输出结果
[root@localhost ~]# mysql -ubinlog2sql -pbinlog2sql -e 'select @@log_bin_basename'
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------------+
| @@log_bin_basename            |
+-------------------------------+
| /data/mysqldb/binlog/mybinlog |
+-------------------------------+

获取误删除操作的undo语句

已知：
误操作时间范围：2023-09-19 08:25:05 之后
mybinlog.000008包含的08:25之前的操作
所以要挖掘的binlog文件为：mybinlog.000008

获取Undo语句

[root@localhost ~]# python3 binlog2sql/binlog2sql.py -ubinlog2sql -pbinlog2sql -dfuwa -P 2213 -t fuwa  --start-file='mybinlog.000009' --start-datetime='2023-09-19 13:48:00' --only-dml --sql-type=delete
DELETE FROM `fuwa`.`fuwa` WHERE `id`=1 AND `name`='赵' AND `addtime`='2023-09-19 13:48:44' LIMIT 1; #start 473 end 753 time 2023-09-19 13:48:57
DELETE FROM `fuwa`.`fuwa` WHERE `id`=2 AND `name`='钱' AND `addtime`='2023-09-19 13:48:44' LIMIT 1; #start 473 end 753 time 2023-09-19 13:48:57
DELETE FROM `fuwa`.`fuwa` WHERE `id`=3 AND `name`='孙' AND `addtime`='2022-01-12 12:12:12' LIMIT 1; #start 473 end 753 time 2023-09-19 13:48:57
DELETE FROM `fuwa`.`fuwa` WHERE `id`=4 AND `name`='李' AND `addtime`='2000-12-12 01:00:00' LIMIT 1; #start 473 end 753 time 2023-09-19 13:48:57

把undo 语句保存为sql文件

[root@localhost ~]# python3 binlog2sql/binlog2sql.py -ubinlog2sql -pbinlog2sql -dfuwa -P 2213 -t fuwa  -B --start-file='mybinlog.000009' --start-datetime='2023-09-19 13:48:00' --only-dml --sql-type=delete |cut -d '#' -f1 > fuwa.sql
[root@localhost ~]# vi fuwa.sql 
[root@localhost ~]# cat fuwa.sql 
INSERT INTO `fuwa`.`fuwa`(`id`, `name`, `addtime`) VALUES (4, '李', '2000-12-12 01:00:00'); 
INSERT INTO `fuwa`.`fuwa`(`id`, `name`, `addtime`) VALUES (3, '孙', '2022-01-12 12:12:12'); 
INSERT INTO `fuwa`.`fuwa`(`id`, `name`, `addtime`) VALUES (2, '钱', '2023-09-19 13:48:44'); 
INSERT INTO `fuwa`.`fuwa`(`id`, `name`, `addtime`) VALUES (1, '赵', '2023-09-19 13:48:44');

执行恢复操作

可以使用业务用户，也可以使用超级用户恢复数据。

mysql -uroot -pmysql < fuwa.sql
mysql -uroot -pmysql -e 'select * from fuwa.fuwa'
--输出结果
[root@localhost ~]# mysql -uroot -pmysql -e 'select * from fuwa.fuwa'
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 | 钱   | 2023-09-19 13:48:44 |
|    1 | 赵   | 2023-09-19 13:48:44 |

binlog2sql 可用参数

--查看帮助
[root@localhost ~]# python3 binlog2sql/binlog2sql.py 
usage: 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]

Parse MySQL binlog to SQL you want

optional arguments:
  --stop-never          Continuously parse binlog. default: stop at the latest event when you start.
  --help                help information
  -K, --no-primary-key  Generate insert sql without primary key if exists
  -B, --flashback       Flashback data to start_position of start_file
  --back-interval BACK_INTERVAL
                        Sleep time between chunks of 1000 rollback sql. set it to 0 if do not need sleep

connect setting:
  -h HOST, --host HOST  Host the MySQL database server located
  -u USER, --user USER  MySQL Username to log in as
  -p [PASSWORD [PASSWORD ...]], --password [PASSWORD [PASSWORD ...]]
                        MySQL Password to use
  -P PORT, --port PORT  MySQL port to use

interval filter:
  --start-file START_FILE
                        Start binlog file to be parsed
  --start-position START_POS, --start-pos START_POS
                        Start position of the --start-file
  --stop-file END_FILE, --end-file END_FILE
                        Stop binlog file to be parsed. default: '--start-file'
  --stop-position END_POS, --end-pos END_POS
                        Stop position. default: latest position of '--stop-file'
  --start-datetime START_TIME
                        Start time. format %Y-%m-%d %H:%M:%S
  --stop-datetime STOP_TIME
                        Stop Time. format %Y-%m-%d %H:%M:%S;

schema filter:
  -d [DATABASES [DATABASES ...]], --databases [DATABASES [DATABASES ...]]
                        dbs you want to process
  -t [TABLES [TABLES ...]], --tables [TABLES [TABLES ...]]
                        tables you want to process

type filter:
  --only-dml            only print dml, ignore ddl
  --sql-type [SQL_TYPE [SQL_TYPE ...]]
                        Sql type you want to process, support INSERT, UPDATE, DELETE.

最后修改时间：2023-09-20 10:14:32

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者