本文主要是介绍【数据库】MySQL 使用Binlog恢复数据实战,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
文章目录
- 基于Binlog恢复数据
- 1)使用mysqlbinlog工具查看binlog内容
- 2)查看指定数据库binlog日志
- 3)查看指定数据库指定表指定语句的位置
- 4)根据binlog日志位置恢复删除数据
- 5)根据binlog时间点恢复删除数据
基于Binlog恢复数据
1)使用mysqlbinlog工具查看binlog内容
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000005
mysqlbinlog: MySQL 的命令行工具,用于读取和解析二进制日志文件。
–base64-output=DECODE-ROWS: 指定如何处理 base64 编码的数据。这里设置为 DECODE-ROWS,意味着对于每一条包含 base64 编码数据的 SQL 语句(如 INSERT, UPDATE, DELETE),都将解码这些数据并将其包含在 SQL 语句中。
-v: 显示详细信息。
mysql-bin.000005: 指定要解析的 binlog 文件名
2)查看指定数据库binlog日志
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000005 --database=nacos_dev
-d,–database=name 仅显示指定数据库的转储内容
3)查看指定数据库指定表指定语句的位置
查找nacos_dev
数据库users
表的binlog日志
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000007 --database=nacos_dev |grep -C 20 -i users
---------------------------------------------------------------------------------------------------------------------
mysqlbinlog: [Warning] Skipping '!includedir /etc/mysql/conf.d/' directive as maximum include recursion level was reached in file /etc/mysql/conf.d/my.cnf at line 29.
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.
# at 14143
# at 14372
#240822 5:31:50 server id 1 end_log_pos 14403 CRC32 0x527a2cf8 Xid = 828
COMMIT/*!*/;
# at 14403
#240822 6:24:23 server id 1 end_log_pos 14482 CRC32 0x2db5daac Anonymous_GTID last_committed=28 sequence_number=29 rbr_only=yes original_committed_timestamp=1724307863861951 immediate_commit_timestamp=1724307863861951 transaction_length=352
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1724307863861951 (2024-08-22 06:24:23.861951 UTC)
# immediate_commit_timestamp=1724307863861951 (2024-08-22 06:24:23.861951 UTC)
/*!80001 SET @@session.original_commit_timestamp=1724307863861951*//*!*/;
/*!80014 SET @@session.original_server_version=80027*//*!*/;
/*!80014 SET @@session.immediate_server_version=80027*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 14482
#240822 6:24:23 server id 1 end_log_pos 14553 CRC32 0x8f9293c0 Query thread_id=2639 exec_time=0 error_code=0
SET TIMESTAMP=1724307863/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
BEGIN
/*!*/;
# at 14553
#240822 6:24:23 server id 1 end_log_pos 14618 CRC32 0xcd77d550 Table_map: `nacos_dev`.`users` mapped to number 123
# at 14618
#240822 6:24:23 server id 1 end_log_pos 14724 CRC32 0xfaf76371 Write_rows: table id 123 flags: STMT_END_F
### INSERT INTO `nacos_dev`.`users`
### SET
### @1='nacos1'
### @2='$2a$10$EuWPZHzz32dJN7jexM34MOeYirDdFAZm2kuWj7VEOJhhZkDrxfvUu'
### @3=1
# at 14724
#240822 6:24:23 server id 1 end_log_pos 14755 CRC32 0xc4d78371 Xid = 945
COMMIT/*!*/;
# at 14755
#240822 6:24:23 server id 1 end_log_pos 14834 CRC32 0x05e34489 Anonymous_GTID last_committed=29 sequence_number=30 rbr_only=yes original_committed_timestamp=1724307863953622 immediate_commit_timestamp=1724307863953622 transaction_length=352
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1724307863953622 (2024-08-22 06:24:23.953622 UTC)
# immediate_commit_timestamp=1724307863953622 (2024-08-22 06:24:23.953622 UTC)
/*!80001 SET @@session.original_commit_timestamp=1724307863953622*//*!*/;
/*!80014 SET @@session.original_server_version=80027*//*!*/;
/*!80014 SET @@session.immediate_server_version=80027*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 14834
#240822 6:24:23 server id 1 end_log_pos 14905 CRC32 0xfd287302 Query thread_id=2639 exec_time=0 error_code=0
SET TIMESTAMP=1724307863/*!*/;
BEGIN
/*!*/;
# at 14905
#240822 6:24:23 server id 1 end_log_pos 14970 CRC32 0xc1874932 Table_map: `nacos_dev`.`users` mapped to number 123
# at 14970
#240822 6:24:23 server id 1 end_log_pos 15076 CRC32 0x1a2d51f9 Write_rows: table id 123 flags: STMT_END_F
### INSERT INTO `nacos_dev`.`users`
### SET
### @1='nacos2'
### @2='$2a$10$EuWPZHzz32dJN7jexM34MOeYirDdFAZm2kuWj7VEOJhhZkDrxfvUu'
### @3=1
# at 15076
#240822 6:24:23 server id 1 end_log_pos 15107 CRC32 0xa8b9a67d Xid = 947
COMMIT/*!*/;
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*/;
这段日志说明了插入2条记录到
users
表里,记录位置为end_log_pos 15107
,即15107
4)根据binlog日志位置恢复删除数据
删除users
表中的一条数据,重新查看binlog日志,发现有删除记录,现在开始恢复。
我们只需要把数据恢复到删除前的即可,所以找到删除前的日志位置是上一步操作的15107
mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000007 --database=nacos_dev |grep -C 20 -i users
------------------------------------------------------------------------------------------------
mysqlbinlog: [Warning] Skipping '!includedir /etc/mysql/conf.d/' directive as maximum include recursion level was reached in file /etc/mysql/conf.d/my.cnf at line 29.
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.
# at 14143
# at 14372
#240822 5:31:50 server id 1 end_log_pos 14403 CRC32 0x527a2cf8 Xid = 828
COMMIT/*!*/;
# at 14403
#240822 6:24:23 server id 1 end_log_pos 14482 CRC32 0x2db5daac Anonymous_GTID last_committed=28 sequence_number=29 rbr_only=yes original_committed_timestamp=1724307863861951 immediate_commit_timestamp=1724307863861951 transaction_length=352
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1724307863861951 (2024-08-22 06:24:23.861951 UTC)
# immediate_commit_timestamp=1724307863861951 (2024-08-22 06:24:23.861951 UTC)
/*!80001 SET @@session.original_commit_timestamp=1724307863861951*//*!*/;
/*!80014 SET @@session.original_server_version=80027*//*!*/;
/*!80014 SET @@session.immediate_server_version=80027*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 14482
#240822 6:24:23 server id 1 end_log_pos 14553 CRC32 0x8f9293c0 Query thread_id=2639 exec_time=0 error_code=0
SET TIMESTAMP=1724307863/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
BEGIN
/*!*/;
# at 14553
#240822 6:24:23 server id 1 end_log_pos 14618 CRC32 0xcd77d550 Table_map: `nacos_dev`.`users` mapped to number 123
# at 14618
#240822 6:24:23 server id 1 end_log_pos 14724 CRC32 0xfaf76371 Write_rows: table id 123 flags: STMT_END_F
### INSERT INTO `nacos_dev`.`users`
### SET
### @1='nacos1'
### @2='$2a$10$EuWPZHzz32dJN7jexM34MOeYirDdFAZm2kuWj7VEOJhhZkDrxfvUu'
### @3=1
# at 14724
#240822 6:24:23 server id 1 end_log_pos 14755 CRC32 0xc4d78371 Xid = 945
COMMIT/*!*/;
# at 14755
#240822 6:24:23 server id 1 end_log_pos 14834 CRC32 0x05e34489 Anonymous_GTID last_committed=29 sequence_number=30 rbr_only=yes original_committed_timestamp=1724307863953622 immediate_commit_timestamp=1724307863953622 transaction_length=352
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1724307863953622 (2024-08-22 06:24:23.953622 UTC)
# immediate_commit_timestamp=1724307863953622 (2024-08-22 06:24:23.953622 UTC)
/*!80001 SET @@session.original_commit_timestamp=1724307863953622*//*!*/;
/*!80014 SET @@session.original_server_version=80027*//*!*/;
/*!80014 SET @@session.immediate_server_version=80027*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 14834
#240822 6:24:23 server id 1 end_log_pos 14905 CRC32 0xfd287302 Query thread_id=2639 exec_time=0 error_code=0
SET TIMESTAMP=1724307863/*!*/;
BEGIN
/*!*/;
# at 14905
#240822 6:24:23 server id 1 end_log_pos 14970 CRC32 0xc1874932 Table_map: `nacos_dev`.`users` mapped to number 123
# at 14970
#240822 6:24:23 server id 1 end_log_pos 15076 CRC32 0x1a2d51f9 Write_rows: table id 123 flags: STMT_END_F
### INSERT INTO `nacos_dev`.`users`
### SET
### @1='nacos2'
### @2='$2a$10$EuWPZHzz32dJN7jexM34MOeYirDdFAZm2kuWj7VEOJhhZkDrxfvUu'
### @3=1
# at 15076
#240822 6:24:23 server id 1 end_log_pos 15107 CRC32 0xa8b9a67d Xid = 947
COMMIT/*!*/;
# at 15107
#240822 8:12:38 server id 1 end_log_pos 15186 CRC32 0x86889068 Anonymous_GTID last_committed=30 sequence_number=31 rbr_only=yes original_committed_timestamp=1724314358298945 immediate_commit_timestamp=1724314358298945 transaction_length=352
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1724314358298945 (2024-08-22 08:12:38.298945 UTC)
# immediate_commit_timestamp=1724314358298945 (2024-08-22 08:12:38.298945 UTC)
/*!80001 SET @@session.original_commit_timestamp=1724314358298945*//*!*/;
/*!80014 SET @@session.original_server_version=80027*//*!*/;
/*!80014 SET @@session.immediate_server_version=80027*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 15186
#240822 8:12:38 server id 1 end_log_pos 15257 CRC32 0x79e992e2 Query thread_id=4522 exec_time=0 error_code=0
SET TIMESTAMP=1724314358/*!*/;
BEGIN
/*!*/;
# at 15257
#240822 8:12:38 server id 1 end_log_pos 15322 CRC32 0x9525675f Table_map: `nacos_dev`.`users` mapped to number 123
# at 15322
#240822 8:12:38 server id 1 end_log_pos 15428 CRC32 0x2ec06517 Delete_rows: table id 123 flags: STMT_END_F
### DELETE FROM `nacos_dev`.`users`
### WHERE
### @1='nacos2'
### @2='$2a$10$EuWPZHzz32dJN7jexM34MOeYirDdFAZm2kuWj7VEOJhhZkDrxfvUu'
### @3=1
# at 15428
#240822 8:12:38 server id 1 end_log_pos 15459 CRC32 0x1d3b1922 Xid = 978
COMMIT/*!*/;
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*/;
根据日志位置导出sql
mysqlbinlog -v mysql-bin.000007 --database=nacos_dev --stop-position=15107 > 15107.sql
然后登录到mysql,source 15107.sql
,现在删除的数据恢复回来了
这里有一个非常关键的问题,如果你是新开启的binlog日志功能,之前的数据是没有binlog记录的,也就是删除了没有binlog记录的数据,是无法恢复的
5)根据binlog时间点恢复删除数据
mysqlbinlog -v mysql-bin.000007 --database=nacos_dev --stop-datetime="2024-08-22 06:24:23" > 240822062423.sql
这篇关于【数据库】MySQL 使用Binlog恢复数据实战的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!