MySQL 利用mysqlbinlog二进制文件还原指定数据库

推荐这篇 mysqlbinlog详解 - https://www.cnblogs.com/Presley-lpc/p/9619571.html

1.

我的初始环境

系统：CentOS 7.6.1810(Py3.7.8)

MySQL：5.7.31

 

2.

检查MySQL是否开启binlog日志

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

log_bin为ON即为开启，

若未开启？

修改配置文件，在 [mysqld]下添加如下内容

log-bin=mysql-bin
server-id = 1
binlog_format=mixed

#log-bin  开启 Binlog 并写明存放日志的位置；默认使用的设置是“log-bin=mysql-bin”，这样日志是存放在默认的位置上的，一般是放在data目录中。

#server-id  指定一个集群内的 MySQL 服务器 ID，如果做数据库集群那么必须全局唯一，一般来说不推荐 指定 server_id 等于 1。

#binlog_format  三种Bin-log日志模式 -- 自动模式

 

 3.

模拟数据丢失 

create database test;
use test;
create table t1(
id int primary key,
name varchar(32)
);
 
INSERT INTO t1 VALUE(1,'val1');
INSERT INTO t1 VALUE(2,'val2');

create database test2;
use test2;
create table t1(
id int primary key,
name varchar(32)
);
 
INSERT INTO t1 VALUE(1,'val1');
INSERT INTO t1 VALUE(2,'val2');

drop database test;
drop database test2;

 

4.

查看日志并恢复数据

查看当前日志文件

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     4285 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.06 sec)

结果：说明 mysql-bin.000001 是当前备份的文件名

也可以直接进入/www/server/data，找到类似mysql-bin.000001的文件，选择后缀最大的文件，因为这个是最新的

如果找不到，可以全局搜索一下，使用 find / -name mysql-bin.000001; 去查看一下路径

由于binlog是二进制的，所以需要先转换成文本文件，一般可以采用Mysql自带的mysqlbinlog转换成文本。

/www/server/mysql/bin/mysqlbinlog --no-defaults --base64-output='decode-rows' -v mysql-bin.000001 > ./binlog_2020_12_07;

部分参数说明：

1. --no-defaults 为了防止报错：mysqlbinlog: unknown variable 'default_character_set=utf8mb4'
2. --base64-output='decode-rows' 和-v一起使用， 进行base64解码
3. -d databaseName：可以使用-d来指定数据库
4. --start-datetime="2020-12-07 14:27:36" ：指定开始时间，注意格式不要写错
5. --stop-datetime="" ：指定结束时间
6. --start-position="" ： 指定起始点
7. --stop-position="" ：指定结束点

 

之后导出文件 ‘binlog_2020_12_07’，我是直接在宝塔面板下载的，用vscode打开文本文件

会看到binlog的基本块如下：

# at 1488
#201207 14:27:26 server id 1  end_log_pos 1569 CRC32 0xcb9c1cbf     Query    thread_id=178    exec_time=0    error_code=0
SET TIMESTAMP=1607322446/*!*/;
BEGIN

基本块解释：

# at 1488
指明的当前位置相对文件开始的偏移位置，这个在mysqlbinlog命令中可以作为--start-position的参数

#201207 14:27:26 server id 1  end_log_pos 1569 CRC32 0xcb9c1cbf     Query    thread_id=178    exec_time=0    error_code=0
201207 14:27:26指明时间为20年12月7号14:27:26，serverid也就是你在配置文件中的配置的，end_log_pos 1569，这个块在1569结束，也就是说结束点是1569。thread_id执行的线程id，exec_time执行时间，error_code错误码

SET TIMESTAMP=1607322446/*!*/;
BEGIN
具体执行语句

 

截取部分日志：

/*!*/;
# at 1852
#201207 14:27:30 server id 1  end_log_pos 1958 CRC32 0xf8660231     Query    thread_id=178    exec_time=0    error_code=0
SET TIMESTAMP=1607322450/*!*/;
INSERT INTO t1 VALUE(2,'val2')
/*!*/;
# at 1958
#201207 14:27:30 server id 1  end_log_pos 1989 CRC32 0x3a49fcc1     Xid = 607
COMMIT/*!*/;
# at 1989
#201207 14:27:36 server id 1  end_log_pos 2054 CRC32 0xa4d22816     Anonymous_GTID    last_committed=8    sequence_number=9    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2054
#201207 14:27:36 server id 1  end_log_pos 2146 CRC32 0x11bdfbca     Query    thread_id=178    exec_time=0    error_code=0
SET TIMESTAMP=1607322456/*!*/;
drop database test
/*!*/;
# at 2146
#201207 14:27:42 server id 1  end_log_pos 2211 CRC32 0x9a60cd1d     Anonymous_GTID    last_committed=9    sequence_number=10    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2211
#201207 14:27:42 server id 1  end_log_pos 2306 CRC32 0xd29068e1     Query    thread_id=178    exec_time=0    error_code=0
SET TIMESTAMP=1607322462/*!*/;
drop database test2
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

这里我们用搜索去找 drop 等关键字，来找到删除操作的日志记录位置

可以看到以上内容，在第16行和第24行，分别删除了数据库test和test2，之后进行恢复数据库`test`操作，有两种方法:

这里我使用的是第一种

 

1) 利用 drop database test 前的 “时间” 进行恢复，请注意时间格式

会产生一个警告，不用管，输入数据库root密码进行验证敲回车

[root@iZm5eajbhqhhehk85j4d95Z data]# /www/server/mysql/bin/mysqlbinlog -d test --stop-datetime="2020-12-07 14:27:36" mysql-bin.000001 | mysql -uroot -p;
Enter password: WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.

[root@iZm5eajbhqhhehk85j4d95Z data]# 

 

2) 利用 --stop-position="" 进行恢复

[root@iZm5eajbhqhhehk85j4d95Z data]# /www/server/mysql/bin/mysqlbinlog -d test --stop-position="2054" mysql-bin.000001 | mysql -uroot -p;
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
Enter password: 
[root@iZm5eajbhqhhehk85j4d95Z data]#

再次进入数据库，可以看到数据库`test`以及数据就已经恢复了

 

5.

重置日志文件

使用 mysqlbinlog 恢复之后，日志文件会重复记录前面的操作，此时可以选择重置日志文件

mysql> reset master

再次查看二级制文件时，发现已经被重置了

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      8079 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)