mysql通过binlog恢复删除数据

 

有时候手贱不小心将数据库的数据清空了怎么办？不要慌，如果你的mysql开启了binlog那你可以大胆放心的往下看！如果没开启，那就先开启，防止下次出现这个问题。

1.数据库需要开启log_bin功能并设置开启binlog行级模式(ROW):我测试数据库在windows上，所以找到my.ini文件添加一下内容，如果是linux，找到my.cnf，我使用的是解压版的mysql5.6所以没有my.ini,需要在安装目录乡下创建一个my.ini，添加以下内容

[mysql]
 
# 设置mysql客户端默认字符集
 
default-character-set=utf8 
 
[mysqld]
 
#设置3306端口
 
port =  
 
# 设置mysql的安装目录
 
basedir=C:\\mysql\\mysql-5.6.43-winx64
datadir=C:\\mysql\\mysql-5.6.43-winx64\\data
 
# 允许最大连接数
 
max_connections=1000
 
# 服务端使用的字符集默认为8比特编码的latin1字符集
 
character-set-server=utf8
 
# 创建新表时将使用的默认存储引擎
 
default-storage-engine=INNODB

log-bin=mysql-bin
binlog-format=Row

server-id=1

2.重启mysql,cmd命令需要用管理员开启，否则权限可能不够

关闭mysql：net stop mysql
开启mysql：net start mysql

3.查看binlog

先登录到mysql

mysql -hlocalhost -uroot -p123456

再查看binlog是否已经开启

show variables like 'log_bin';

如果如下图所示，则开启成功

继续查看当前的binlog文件

show master status;

如图所示，当前binlog文件是mysql-bin.000002,接着我们看该日志的所有events

show binlog events in 'mysql-bin.000002';

如图所示，我红色框出来的是我清空activity_manager这张表执行的sql的开始到结束，对应的position分别是120和171354，这个非常有用，一句sql从begin到commit结束，这个不用过多的解释了。

接着我们在mysql的data文件夹下执行：

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000002 -v --start-position=120 --stop-position=171354 --result-file=mysql008.sql

此语句是将binlog日志转成对应的sql，即上面我们情况表的delete语句，但是我们发现这个sql不是我们的标准sql，对于我们是没有用的，那我们怎么样转成标准slq以及转换成insert语句重新插入到我们的数据库呢？下面我将介绍一款非常好用的工具binlog2sql.

5.binlog2sql安装

下载binlog2sql，https://download.csdn.net/download/chf_940684164/10500490，你也可以通过别的的方式下载

事前准备：1.binlog开启2.python安装好3.pip安装好

下面我将binlog2sql安装在linux下，我们先创个文件夹，将下载好的binlog2sql-master.zip，解压缩,进入binlog2sql文件，我们可以看到requirements.txt

mkdir binlog2sql
cd binlog2sql
unzip binlog2sql-master.zip
mv binlog2sql-master.zip binlog2sql
cd binlog2sql
pip install -r requirements.txt

到此我们就将binlog2sql安装完毕了！

6.参数列表

--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。可选。默认False。

--sql-type 只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开，如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型，则三者都不解析。

7.将binlog日志反解析回滚

如果你想看binlog原本的删除sql，那么执行下面的语句

python binlog2sql.py -h192.168.7.141 -P3306 -uroot -p'123456' -dcivil_admin_3.0 -tactivity_manager --start-file='mysql-bin.000002' --start-position=120 --stop-position=171354 > /home/binlog2sql/binlog2sql/raw.sql

如果你想得到回滚的sql，那么执行下面的语句，就可以得到对应的insert语句 

python binlog2sql.py -h192.168.7.141 -P3306 -uroot -p'123456' -dcivil_admin_3.0 -tactivity_manager --start-file='mysql-bin.000002' --start-position=120 --stop-position=171354 -B > /home/binlog2sql/binlog2sql/back.sql

我们只需要将得到的back.sql在数据库里执行以下，就可以恢复删除数据了。