事件背景
开发反馈误删除了部分业务数据，需要恢复数据。这里采用binlog2sql工具进行数据回滚。环境：CentOS Linux release 7.4.1708 (Core)mysql Ver 8.0.21 for Linux on x86_64 (MySQL Community Server – GPL)数据库开启binlog。因为数据库server端服务器没有root权限，为了方便安装， 所以在网络打通且有root权限的测试服务器上安装工具恢复数据。
下载及安装
1. 安装包下载由于环境无法连接互联网，所有采用下载安装包的方式进行安装。下载Python-3.8.2https://www.python.org/ftp/python/3.8.2/Python-3.8.2.tar.xz下载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.21https://files.pythonhosted.org/packages/e3/54/8c496e300d610299bf168e2068dc10a64b66b299cbe596a27aac5d5b3e7b/mysql-replication-0.21.tar.gz
2. 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-ffimake && make installroot@localhost.localdomain:[/root/soft]python3–versionPython 3.8.2root@localhost.localdomain:[/root/soft]pip3 –versionpip 19.2.3from /usr/lib/python3.8/site-packages/pip (python3.8)3. PyMySQL&mysql-replication安装#pip3installPyMySQL-0.9.3.tar.gz#pip3installmysql-replication-0.21.tar.gzroot@localhost.localdomain:[/root/soft]pip3 listPackage Version—————– ——-mysql-replication 0.21pip 19.2.3PyMySQL 0.9.3setuptools 41.2.04. 解压binlog2sql#unzip binlog2sql-master.zip#mv binlog2sql-master/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
数据恢复
同开发确认误操作的时间以及表名。根据binlog的时间确定采用哪个binlog进行数据恢复。执行命令：可以查看恢复前执行的SQL。python binlog2sql/binlog2sql.py -utestuser -pxxx-dtestdb -t test –start-file=’mysql-bin.000013′–start-datetime=’2022-03-01 14:30:00′–stop-datetime=’2022-03-01 15:30:00′–only-dml –sql-type= DELETE >delete.txt
执行命令：生成回滚SQL。pythonbinlog2sql/binlog2sql.py-B -utestuser -pxxx-dtestdb -t test –start-file=’mysql-bin.000013′–start-datetime=’2022-03-01 14:30:00′–stop-datetime=’2022-03-01 15:30:00′–only-dml –sql-type= DELETE >insert.txt
最终执行回滚SQL重新插入数据即成功恢复数据。mysql>sourceinsert.txt
END
