使用 mariadb-binlog --flashback 参数恢复 MySQL binlog 删除的数据

原创黄山谷 2025-08-11

509

使用 mysqlbinlog 去实现 drop table恢复删除数据只能根据备份+追踪到删除前的position 去恢复数据,如果delete 只能恢复到删除前的一个 position/date，不像 binlog2sql、mysqlbinlog_flashback ，一些三方工具支持 flashback 的功能。

对比基于binlog 的恢复方式。（自己总结的，估计大佬们有其他高端姿势，哈哈！）

工具	恢复方式
mysqlbinlog	可以恢复到删除前的一个 position/date
mariadb-binlog	提取出两个 position/date 之间删除的语句，反写插入 sql
mysqlbinlog_flashback	指定 schema table 就可以输出，反写插入 sql
binlog2sql	提取出两个 position/date 之间删除的语句，反写插入 sql

查看 mariadb 的文档，发现 mariadb 的 mariadb-binlog是支持–flashback的，既然这样我们可不可以用 mariadb-binlog去恢复 mysql 的 binlog 呢？

环境准备：
mysql 8.0.39
rhel 8.10

这里就用墨天轮的 hr schema 一张 jobs 是表做测试

mysql> select * from hr.jobs;
+------------+---------------------------------+------------+------------+
| job_id     | job_title                       | min_salary | max_salary |
+------------+---------------------------------+------------+------------+
| AC_ACCOUNT | Public Accountant               |       4200 |       9000 |
| AC_MGR     | Accounting Manager              |       8200 |      16000 |
| AD_ASST    | Administration Assistant        |       3000 |       6000 |
| AD_PRES    | President                       |      20000 |      40000 |
| AD_VP      | Administration Vice President   |      15000 |      30000 |
| FI_ACCOUNT | Accountant                      |       4200 |       9000 |
| FI_MGR     | Finance Manager                 |       8200 |      16000 |
| HR_REP     | Human Resources Representative  |       4000 |       9000 |
| IT_PROG    | Programmer                      |       4000 |      10000 |
| MK_MAN     | Marketing Manager               |       9000 |      15000 |
| MK_REP     | Marketing Representative        |       4000 |       9000 |
| PR_REP     | Public Relations Representative |       4500 |      10500 |
| PU_CLERK   | Purchasing Clerk                |       2500 |       5500 |
| PU_MAN     | Purchasing Manager              |       8000 |      15000 |
| SA_MAN     | Sales Manager                   |      10000 |      20000 |
| SA_REP     | Sales Representative            |       6000 |      12000 |
| SH_CLERK   | Shipping Clerk                  |       2500 |       5500 |
| ST_CLERK   | Stock Clerk                     |       2000 |       5000 |
| ST_MAN     | Stock Manager                   |       5500 |       8500 |
+------------+---------------------------------+------------+------------+
19 rows in set (0.00 sec)

直接 delete 表

mysql> delete from hr.jobs;
Query OK, 19 rows affected (0.00 sec)

mysql> select * from hr.jobs;
Empty set (0.00 sec)

查看正在使用的 binlog

show master status;

show binlog events in 'mybinlog.000013';   
找到 delete 的 position 起始点
--105794   --106716

找到/bin/mariadb-binlog 上传到 mysql bin 目录下

可以看到 mariadb-binlog 是支持 --flashback

mariadb-binlog --help

···
 -B, --flashback     Flashback feature can rollback you committed data to a
                      special time point.
···

那我们来尝试下恢复

mariadb-binlog /mysql/data/mybinlog.000013 -vv --start-position=105794 --stop-position=106716 --database=hr --table=jobs --flashback

[mysql@node5 backup]$ mariadb-binlog /mysql/data/mybinlog.000013 -vv --start-position=105794 --stop-position=106716 --database=hr --table=jobs --flashback
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
#250811 23:13:14 server id 162253  end_log_pos 126 CRC32 0x21391f96     Start: binlog v 4, server v 8.0.39 created 250811 23:13:14 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
igiaaA/NeQIAegAAAH4AAAABAAQAOC4wLjM5AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACKCJpoEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
CigAAZYfOSE=
'/*!*/;
mariadb-binlog: Character set '#255' is not a compiled character set and is not specified in the '/usr/local/mysql/share/charsets/Index.xml' file
#250811 23:34:38 server id 162253  end_log_pos 105910 CRC32 0x6e10c478  Ignorable
# Ignorable event type 29 (MySQL Rows_query)
#250811 23:34:38 server id 162253  end_log_pos 105972 CRC32 0x3b854cf3  Table_map: `hr`.`jobs` mapped to number 101
# Number of rows: 19
#250811 23:34:38 server id 162253  end_log_pos 106716 CRC32 0x35e2df65  Xid = 258
START TRANSACTION/*!*/;
#250811 23:34:38 server id 162253  end_log_pos 106685 CRC32 0x06ee7d88  Write_rows: table id 101 flags: STMT_END_F

BINLOG '
jg2aaBPNeQIAPgAAAPSdAQAAAGUAAAAAAAEAAmhyAARqb2JzAAQPD/b2CB4AaQAIAAgADAEBwAIB
IfNMhTs=
jg2aaB7NeQIAyQIAAL2gAQAAAGUAAAAAAAEAAgAE/wAGU1RfTUFODVN0b2NrIE1hbmFnZXKAABV8
gAAhNAAIU1RfQ0xFUksLU3RvY2sgQ2xlcmuAAAfQgAATiAAIU0hfQ0xFUksOU2hpcHBpbmcgQ2xl
cmuAAAnEgAAVfAAGU0FfUkVQFFNhbGVzIFJlcHJlc2VudGF0aXZlgAAXcIAALuAABlNBX01BTg1T
YWxlcyBNYW5hZ2VygAAnEIAATiAABlBVX01BThJQdXJjaGFzaW5nIE1hbmFnZXKAAB9AgAA6mAAI
UFVfQ0xFUksQUHVyY2hhc2luZyBDbGVya4AACcSAABV8AAZQUl9SRVAfUHVibGljIFJlbGF0aW9u
cyBSZXByZXNlbnRhdGl2ZYAAEZSAACkEAAZNS19SRVAYTWFya2V0aW5nIFJlcHJlc2VudGF0aXZl
gAAPoIAAIygABk1LX01BThFNYXJrZXRpbmcgTWFuYWdlcoAAIyiAADqYAAdJVF9QUk9HClByb2dy
YW1tZXKAAA+ggAAnEAAGSFJfUkVQHkh1bWFuIFJlc291cmNlcyBSZXByZXNlbnRhdGl2ZYAAD6CA
ACMoAAZGSV9NR1IPRmluYW5jZSBNYW5hZ2VygAAgCIAAPoAACkZJX0FDQ09VTlQKQWNjb3VudGFu
dIAAEGiAACMoAAVBRF9WUB1BZG1pbmlzdHJhdGlvbiBWaWNlIFByZXNpZGVudIAAOpiAAHUwAAdB
RF9QUkVTCVByZXNpZGVudIAATiCAAJxAAAdBRF9BU1NUGEFkbWluaXN0cmF0aW9uIEFzc2lzdGFu
dIAAC7iAABdwAAZBQ19NR1ISQWNjb3VudGluZyBNYW5hZ2VygAAgCIAAPoAACkFDX0FDQ09VTlQR
UHVibGljIEFjY291bnRhbnSAABBogAAjKIh97gY=
'/*!*/;
### INSERT INTO `hr`.`jobs`
### SET
###   @1='ST_MAN' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Stock Manager' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=5500 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=8500 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='ST_CLERK' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Stock Clerk' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=2000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=5000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='SH_CLERK' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Shipping Clerk' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=2500 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=5500 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='SA_REP' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Sales Representative' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=6000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=12000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='SA_MAN' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Sales Manager' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=10000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=20000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='PU_MAN' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Purchasing Manager' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=8000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=15000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='PU_CLERK' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Purchasing Clerk' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=2500 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=5500 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='PR_REP' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Public Relations Representative' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=4500 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=10500 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='MK_REP' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Marketing Representative' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=4000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=9000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='MK_MAN' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Marketing Manager' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=9000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=15000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='IT_PROG' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Programmer' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=4000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=10000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='HR_REP' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Human Resources Representative' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=4000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=9000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='FI_MGR' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Finance Manager' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=8200 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=16000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='FI_ACCOUNT' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Accountant' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=4200 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=9000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='AD_VP' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Administration Vice President' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=15000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=30000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='AD_PRES' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='President' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=20000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=40000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='AD_ASST' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Administration Assistant' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=3000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=6000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='AC_MGR' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Accounting Manager' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=8200 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=16000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
### INSERT INTO `hr`.`jobs`
### SET
###   @1='AC_ACCOUNT' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
###   @2='Public Accountant' /* VARSTRING(105) meta=105 nullable=0 is_null=0 */
###   @3=4200 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
###   @4=9000 /* DECIMAL(8,0) meta=2048 nullable=1 is_null=0 */
#250811 23:34:38 server id 162253  end_log_pos 105867 CRC32 0xf9655451  Query   thread_id=10    exec_time=0     error_code=0    xid=0
SET TIMESTAMP=1754926478/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.system_versioning_insert_history=0/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
# Ignored (Unknown charset) SET @@session.character_set_client=Unknown,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
COMMIT
/*!*/;
COMMIT
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

好像是可以的，我们来把他输出到文件在，导入到数据库中试下能不能恢复

mariadb-binlog /mysql/data/mybinlog.000013 -vv --start-position=105794 --stop-position=106716 --database=hr --table=jobs --flashback > /mysql/backup/jobs.sql

导入数据

mysql> source /mysql/backup/jobs.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1193 (HY000): Unknown system variable 'check_constraint_checks'
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from hr.jobs;
+------------+---------------------------------+------------+------------+
| job_id     | job_title                       | min_salary | max_salary |
+------------+---------------------------------+------------+------------+
| AC_ACCOUNT | Public Accountant               |       4200 |       9000 |
| AC_MGR     | Accounting Manager              |       8200 |      16000 |
| AD_ASST    | Administration Assistant        |       3000 |       6000 |
| AD_PRES    | President                       |      20000 |      40000 |
| AD_VP      | Administration Vice President   |      15000 |      30000 |
| FI_ACCOUNT | Accountant                      |       4200 |       9000 |
| FI_MGR     | Finance Manager                 |       8200 |      16000 |
| HR_REP     | Human Resources Representative  |       4000 |       9000 |
| IT_PROG    | Programmer                      |       4000 |      10000 |
| MK_MAN     | Marketing Manager               |       9000 |      15000 |
| MK_REP     | Marketing Representative        |       4000 |       9000 |
| PR_REP     | Public Relations Representative |       4500 |      10500 |
| PU_CLERK   | Purchasing Clerk                |       2500 |       5500 |
| PU_MAN     | Purchasing Manager              |       8000 |      15000 |
| SA_MAN     | Sales Manager                   |      10000 |      20000 |
| SA_REP     | Sales Representative            |       6000 |      12000 |
| SH_CLERK   | Shipping Clerk                  |       2500 |       5500 |
| ST_CLERK   | Stock Clerk                     |       2000 |       5000 |
| ST_MAN     | Stock Manager                   |       5500 |       8500 |
+------------+---------------------------------+------------+------------+
19 rows in set (0.01 sec)

好像有报错，但是数据好像是恢复了。

突发奇想，没有在生产上验证，请勿在生产上操作。

墨力计划数据库实操

最后修改时间：2025-08-12 09:49:01

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

使用 mariadb-binlog --flashback 参数 恢复 MySQL binlog 删除的数据

评论

使用 mariadb-binlog --flashback 参数恢复 MySQL binlog 删除的数据