背景
数据误删除,需要恢复,使用阿里云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
如果日志文件还没有写盘,在阿里云控制台看不到,想要提前结束当前的日志写文件可以执行如下操作:
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 | for file in mysql-bin.*; do |
搜索
如下,有一次误删除日志,需要再多个文件中搜索,使用grep命令查询
1 | ## 多次过滤,先找DELETE操作,再过滤出对应表的操作,最后找到指定行的(这里通过@1=7785472即表id),使用awk输出所在文件名 |
效果如下:
1 | ./mysql-bin.002563.log: ### WHERE |
日志:
1 | ### DELETE FROM `example_db`.`example_table` |
恢复
通过日志中的数据和表结构组装成insert语句,执行即可恢复删除的数据。