编程笔记

lifelong learning & practice makes perfect

mysql|阿里云RDS Mysql使用binlog恢复数据

背景

数据误删除,需要恢复,使用阿里云RDS mysql 8.0,已开启binlog,且是ROW模式

binlog日志模式

日志模式 说明 适用场景 优缺点
STATEMENT 记录执行的 SQL 语句。 简单的应用,数据修改操作较少,且不涉及存储过程、函数等复杂操作。 优点:日志文件相对较小,复制速度快。缺点:对于包含函数、存储过程、用户自定义变量的语句,可能无法正确地进行二进制日志复制;存在安全风险(可能泄露敏感信息)。
ROW 记录每一行数据的变化 (INSERT, UPDATE, DELETE)。 复杂的应用,数据修改操作频繁,需要保证数据一致性。 优点:能够精确地复制数据,避免 STATEMENT 模式下的安全风险和不兼容问题。缺点:日志文件相对较大,复制速度相对较慢。
MIXED 混合模式,MySQL 会自动选择 STATEMENT 或 ROW 模式。 大多数应用场景,MySQL 会根据实际情况选择最优的模式。 优点:兼顾了 STATEMENT 和 ROW 模式的优点,在大多数情况下都能取得较好的性能和一致性。缺点:MySQL 的选择机制可能并不总是最佳的,需要根据实际情况进行调整。
NO_BINLOG_EVENTS 不记录任何二进制日志事件。 不进行二进制日志复制的场景,例如一些只读的副本。 优点:不产生 binlog 文件,节省磁盘空间。缺点:无法进行二进制日志复制,无法进行数据恢复。

补充说明:

  • gtid (全局事务ID) 的使用与 binlog 模式无关,但它可以显著提高复制的效率和可靠性。 建议在任何模式下都开启 gtid 模式。
  • 选择合适的 binlog 模式需要根据具体的应用场景和数据特点进行权衡。 如果数据修改操作比较复杂,或者需要保证数据的精确复制,则应选择 ROW 模式;如果数据修改操作比较简单,且性能是主要考虑因素,则可以选择 STATEMENT 模式;MIXED 模式则适合大多数情况。
  • MySQL 8.0 及以后版本,默认使用 ROW 模式。

步骤

下载binlog

可以使用命令查看mysql中的binlog日志文件

1
SHOW BINARY LOGS;
Log_name File_size Encrypted
binlog.0000062 1,074,523,920 No
binlog.0000063 598,800,601 No
binlog.0000064 311,410 No
binlog.0000065 180 No
binlog.0000066 180 No
binlog.0000067 180 No
binlog.0000068 180 No
binlog.0000069 180 No
binlog.0000070 792 No
  • Log_name 列表示日志文件的名称。
  • File_size 列表示每个日志文件的大小(以字节为单位)。
  • Encrypted 列表示该日志文件是否被加密。

在阿里云RDS实例页面下载日志备份,操作如下:

阿里云工作台 >> 云数据库RDS版 >> 找到相关实例 >> 进入管理界面 >> 找到备份恢复 >> 基础备份列表 >> 日志备份 >> 选择相应时间段的biglog

下载binlog

如果日志文件还没有写盘,在阿里云控制台看不到,想要提前结束当前的日志写文件可以执行如下操作:

1
FLUSH LOGS;

解析

需要使用mysql工具解析,没有安装可使用下面的命令安装mysql 8.0的工具

1
sudo apt install mysql-server-core-8.0

解析:

1
mysqlbinlog -vv --base64-output=DECODE-ROWS mysql-bin.0000062 > /tmp/binlog/mysql-bin-0000062.log

解析后就可以使用grep等工具查询相应的sql来恢复数据了

批量解析

如果有多个binlog文件,可以使用下面的命令批量解析,放到bash脚本中执行

1
2
3
for file in mysql-bin.*; do
mysqlbinlog -vv --base64-output=DECODE-ROWS "$file" > "/tmp/binlog/mysql-bin.${file##*.}.log"
done

搜索

如下,有一次误删除日志,需要再多个文件中搜索,使用grep命令查询

1
2
3
4
## 多次过滤,先找DELETE操作,再过滤出对应表的操作,最后找到指定行的(这里通过@1=7785472即表id),使用awk输出所在文件名
find . -name "*.log" -print0 | while IFS= read -r -d $'\0' file; do
grep 'DELETE' -B30 -A30 "$file" | grep 'example_table' -B30 -A30 | grep '@1=7785472' -B1 -A30 | awk -v file="$file" '{print file ": " $0}'
done

效果如下:

1
2
3
4
5
6
7
./mysql-bin.002563.log: ### WHERE
./mysql-bin.002563.log: ### @1=7785472 /* INT meta=0 nullable=0 is_null=0 */
./mysql-bin.002563.log: ### @2=1 /* INT meta=0 nullable=1 is_null=0 */
./mysql-bin.002563.log: ### @3=100 /* INT meta=0 nullable=1 is_null=0 */
./mysql-bin.002563.log: ### @4=140000 /* INT meta=0 nullable=1 is_null=0 */
./mysql-bin.002563.log: ### @5=140000 /* INT meta=0 nullable=1 is_null=0 */
./mysql-bin.002563.log: ### @6=140000 /* INT meta=0 nullable=1 is_null=0 */

日志:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
### DELETE FROM `example_db`.`example_table`
### WHERE
### @1=7785472 /* INT meta=0 nullable=0 is_null=0 */
### @2=1 /* INT meta=0 nullable=1 is_null=0 */
### @3=100 /* INT meta=0 nullable=1 is_null=0 */
### @4=140000 /* INT meta=0 nullable=1 is_null=0 */
### @5=140000 /* INT meta=0 nullable=1 is_null=0 */
### @6=140000 /* INT meta=0 nullable=1 is_null=0 */
### @7=0 /* INT meta=0 nullable=1 is_null=0 */
### @8=0 /* INT meta=0 nullable=1 is_null=0 */
### @9=7820 /* INT meta=0 nullable=1 is_null=0 */
### @10='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### @11=0 /* INT meta=0 nullable=1 is_null=0 */
### @12='测试' /* VARSTRING(512) meta=512 nullable=1 is_null=0 */
### @13='' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
### @14=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
### @15=NULL /* SHORTINT meta=0 nullable=1 is_null=1 */
### @16=2048 /* LONGINT meta=0 nullable=1 is_null=0 */
### @17=719317 /* INT meta=0 nullable=1 is_null=0 */
### @18=517475 /* INT meta=0 nullable=1 is_null=0 */
### @19=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @20=140000 /* INT meta=0 nullable=1 is_null=0 */
### @21=0 /* INT meta=0 nullable=1 is_null=0 */
### @22=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### @23=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @24=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @25='16166407' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
### @26='次' /* VARSTRING(256) meta=256 nullable=1 is_null=0 */
### @27=0 /* LONGINT meta=0 nullable=1 is_null=0 */
### @28=0 /* INT meta=0 nullable=0 is_null=0 */
### @29=NULL /* INT meta=0 nullable=1 is_null=1 */
### @30=2 /* INT meta=0 nullable=1 is_null=0 */
### @31=1 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @32=4 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @33=140000 /* INT meta=0 nullable=0 is_null=0 */
### @34='[]' /* LONGBLOB/LONGTEXT meta=4 nullable=0 is_null=0 */
### @35=0 /* SHORTINT meta=0 nullable=1 is_null=0 */
### @36=1736843515937 /* LONGINT meta=0 nullable=1 is_null=0 */
### @37=1736843515937 /* LONGINT meta=0 nullable=1 is_null=0 */
### @38=0 /* SHORTINT meta=0 nullable=0 is_null=0 */
### @39=NULL /* TINYINT meta=0 nullable=1 is_null=1 */
### @40=0 /* INT meta=0 nullable=1 is_null=0 */

恢复

通过日志中的数据和表结构组装成insert语句,执行即可恢复删除的数据。

引用

欢迎关注我的其它发布渠道