MysqlMYSQL之binlog规复数据 - Powered by Discuz! Archiver

守听发表于 2024-8-18 00:31:31

MYSQL之binlog规复数据

话说谁还没删错/改错过数据是吧，一旦删错了，我们真的要跑路吗？今天我们来玩一玩binlog，看看是怎么规复数据的。

起首我们先了解下binlog基本概念：
binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如：更改数据库表和更改内容的SQL语句都会记录到binlog里，但是不会记录SELECT和SHOW这类操作。

既然他会记录sql，那我们是不是把需要规复数据的sql拿出来就好了？话不多说，进入正题。
由于我用的是容器，所以要先辈入mysql容器
sudo docker exec -it 842405d4726a /bin/bash
进入容器并进入mysql下令行客户端
sudo docker exec -it 842405d4726a mysql -u root -p
查询是否开启binlog，如果没开启，那下面就不消玩了...
mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin    | ON |
+---------------+-------+
1 row in set (0.00 sec) 查询binlog地点目录（/var/lib/mysql是目录，binlog是文件名前缀）
mysql> SHOW VARIABLES LIKE 'log_bin_basename';
+------------------+-----------------------+
| Variable_name | Value             |
+------------------+-----------------------+
| log_bin_basename | /var/lib/mysql/binlog |
+------------------+-----------------------+
1 row in set (0.02 sec)
当前 MySQL 实例上所有的二进制日志文件
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name    | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000011 |    6119 | No    |
| binlog.000012 |    157 | No    |
+---------------+-----------+-----------+
2 rows in set (0.01 sec) Flush logs（看场景使用，这里我只是做个记录，知道有这么一个命令）：
刷新日志，此刻开始产生一个新编号的binlog文件，后面的操作都会存到这个新的binlog文件中查看当前 MySQL 主服务器的二进制日志信息
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000012 |    157 |          |                |                |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec) 表明：
File 列表现当前使用的二进制日志文件名为 binlog.000012
Position 列表现当前二进制日志文件的位置为 157。(下面叫位置点/pos点，规复数据的时候要用到)
由上得知，我们的binlog文件名叫binlog.000012，位置在/var/lib/mysql目录下。
我们去这个目录下去瞅一瞅去
bash-4.4# cd /var/lib/mysql
bash-4.4# ls
'#ib_16384_0.dblwr' auto.cnf    ca-key.pem    ib_buffer_pool mysql.ibd    private_key.pem server-key.pem undo_001
'#ib_16384_1.dblwr' binlog.000011 ca.pem       ibdata1 mysql.sock    public_key.pem shiro undo_002
'#innodb_redo'    binlog.000012 client-cert.pem ibtmp1 performance_schema ruoyi
'#innodb_temp'    binlog.index client-key.pem mysql pl_code_builder    server-cert.pem sys
bash-4.4# 找到了binlog.000012就好办了，接下来就是查看内里存的是什么东西了。
这里大家留意一下，由于MySQL的binlog文件是以二进制格式存储的，这意味着它们不是纯文本文件，不能使用 vi、cat 等文本编辑器和查看器直接查看其内容。为了查看二进制日志文件的内容，我们需要使用 MySQL 提供的专用工具 mysqlbinlog。
我们用mysqlbinlog来查看一下

bash-4.4# mysqlbinlog /var/lib/mysql/binlog.000012
bash: mysqlbinlog: command not found 提示我们没有找到mysqlbinlog下令
由于 MySQL 容器默认只安装了 MySQL 服务器，而没有预装 MySQL 客户端工具。在这种环境下，我们可以自己安装 MySQL 客户端工具及 mysqlbinlog。
（这里我走了不少弯路，推荐大家yum快速安装）
这里我选择了用yum方式安装，我们先安装yum
bash-4.4# microdnf install yum
安装完yum，安装 MySQL 客户端工具
bash-4.4# yum install -y mysql
如果报错了先移除辩论的包，然后再实行【yum install -y mysql】
bash-4.4# yum remove mysql-community-server-minimal
验证安装：
bash-4.4# mysqlbinlog --version
mysqlbinlogVer 8.0.36 for Linux on aarch64 (Source distribution) 然后我们就可以查看binlog了
bash-4.4# mysqlbinlog /var/lib/mysql/binlog.000012
bash-4.4# mysqlbinlog /var/lib/mysql/binlog.000012|grep "Update" bash-4.4# mysqlbinlog /var/lib/mysql/binlog.000012
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240719 15:36:06 server id 1end_log_pos 126 CRC32 0xfedff2b0 Start: binlog v 4, server v 8.0.32 created 240719 15:36:06 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
5oeaZg8BAAAAegAAAH4AAAABAAQAOC4wLjMyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADmh5pmEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQA
CigAAbDy3/4=
'/*!*/;
# at 126
#240719 15:36:06 server id 1end_log_pos 157 CRC32 0x82f4e7e8 Previous-GTIDs
#
# at 157
#2407206:08:11 server id 1end_log_pos 236 CRC32 0x35450d0b Anonymous_GTIDlast_committed=0sequence_number=1rbr_only=nooriginal_committed_timestamp=1721455691656110immediate_commit_timestamp=1721455691656110transaction_length=314
# original_commit_timestamp=1721455691656110 (2024-07-20 06:08:11.656110 UTC)
# immediate_commit_timestamp=1721455691656110 (2024-07-20 06:08:11.656110 UTC)
/*!80001 SET @@session.original_commit_timestamp=1721455691656110*//*!*/;
/*!80014 SET @@session.original_server_version=80032*//*!*/;
/*!80014 SET @@session.immediate_server_version=80032*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 236
#2407206:08:11 server id 1end_log_pos 471 CRC32 0xeadf8f66 Querythread_id=9exec_time=0error_code=0Xid = 44
SET TIMESTAMP=1721455691/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
/* ApplicationName=DBeaver 23.3.3 - Main */ CREATE SCHEMA `yeYingXuan`
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci
/*!*/;
# at 471
#2407206:09:01 server id 1end_log_pos 550 CRC32 0x4104bf16 Anonymous_GTIDlast_committed=1sequence_number=2rbr_only=nooriginal_committed_timestamp=1721455741084309immediate_commit_timestamp=1721455741084309transaction_length=382
# original_commit_timestamp=1721455741084309 (2024-07-20 06:09:01.084309 UTC)
# immediate_commit_timestamp=1721455741084309 (2024-07-20 06:09:01.084309 UTC)
/*!80001 SET @@session.original_commit_timestamp=1721455741084309*//*!*/;
/*!80014 SET @@session.original_server_version=80032*//*!*/;
/*!80014 SET @@session.immediate_server_version=80032*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 550
#2407206:09:01 server id 1end_log_pos 853 CRC32 0xa4e21747 Querythread_id=9exec_time=0error_code=0Xid = 55
SET TIMESTAMP=1721455741/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
/* ApplicationName=DBeaver 23.3.3 - Main */ CREATE TABLE yeYingXuan.`user` (
id INT auto_increment NOT NULL,
CONSTRAINT user_pk PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci
/*!*/;
# at 853
#2407206:09:22 server id 1end_log_pos 932 CRC32 0xd6cc2d85 Anonymous_GTIDlast_committed=2sequence_number=3rbr_only=nooriginal_committed_timestamp=1721455762869115immediate_commit_timestamp=1721455762869115transaction_length=269
# original_commit_timestamp=1721455762869115 (2024-07-20 06:09:22.869115 UTC)
# immediate_commit_timestamp=1721455762869115 (2024-07-20 06:09:22.869115 UTC)
/*!80001 SET @@session.original_commit_timestamp=1721455762869115*//*!*/;
/*!80014 SET @@session.original_server_version=80032*//*!*/;
/*!80014 SET @@session.immediate_server_version=80032*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 932
#2407206:09:22 server id 1end_log_pos 1122 CRC32 0x9665ccf6 Querythread_id=9exec_time=0error_code=0Xid = 66
SET TIMESTAMP=1721455762/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
/* ApplicationName=DBeaver 23.3.3 - Main */ ALTER TABLE yeYingXuan.`user` ADD name varchar(100) NULL
/*!*/;
# at 1122
#2407206:09:22 server id 1end_log_pos 1201 CRC32 0xd8c2e651 Anonymous_GTIDlast_committed=3sequence_number=4rbr_only=nooriginal_committed_timestamp=1721455762877721immediate_commit_timestamp=1721455762877721transaction_length=259
# original_commit_timestamp=1721455762877721 (2024-07-20 06:09:22.877721 UTC)
# immediate_commit_timestamp=1721455762877721 (2024-07-20 06:09:22.877721 UTC)
/*!80001 SET @@session.original_commit_timestamp=1721455762877721*//*!*/;
/*!80014 SET @@session.original_server_version=80032*//*!*/;
/*!80014 SET @@session.immediate_server_version=80032*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1201
#2407206:09:22 server id 1end_log_pos 1381 CRC32 0x156c6bda Querythread_id=9exec_time=0error_code=0Xid = 69
SET TIMESTAMP=1721455762/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
/* ApplicationName=DBeaver 23.3.3 - Main */ ALTER TABLE yeYingXuan.`user` ADD age INT NULL
/*!*/;
# at 1381
#2407206:10:22 server id 1end_log_pos 1460 CRC32 0x08d41c89 Anonymous_GTIDlast_committed=4sequence_number=5rbr_only=yesoriginal_committed_timestamp=1721455822097710immediate_commit_timestamp=1721455822097710transaction_length=298
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1721455822097710 (2024-07-20 06:10:22.097710 UTC)
# immediate_commit_timestamp=1721455822097710 (2024-07-20 06:10:22.097710 UTC)
/*!80001 SET @@session.original_commit_timestamp=1721455822097710*//*!*/;
/*!80014 SET @@session.original_server_version=80032*//*!*/;
/*!80014 SET @@session.immediate_server_version=80032*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1460
#2407206:10:22 server id 1end_log_pos 1531 CRC32 0xec33d8e3 Querythread_id=9exec_time=0error_code=0
SET TIMESTAMP=1721455822/*!*/;
BEGIN
/*!*/;
# at 1531
#2407206:10:22 server id 1end_log_pos 1596 CRC32 0xff0fa400 Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 1596
#2407206:10:22 server id 1end_log_pos 1648 CRC32 0xcf459f50 Write_rows: table id 102 flags: STMT_END_F

BINLOG '
zlSbZhMBAAAAQQAAADwGAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AACkD/8=
zlSbZh4BAAAANAAAAHAGAAAAAGYAAAAAAAEAAgAD/wABAAAABgDlvKDkuIkSAAAAUJ9Fzw==
'/*!*/;
# at 1648
#2407206:10:22 server id 1end_log_pos 1679 CRC32 0x3443872d Xid = 82
COMMIT/*!*/;
# at 1679
#2407206:10:22 server id 1end_log_pos 1758 CRC32 0x7896626b Anonymous_GTIDlast_committed=5sequence_number=6rbr_only=yesoriginal_committed_timestamp=1721455822100724immediate_commit_timestamp=1721455822100724transaction_length=298
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1721455822100724 (2024-07-20 06:10:22.100724 UTC)
# immediate_commit_timestamp=1721455822100724 (2024-07-20 06:10:22.100724 UTC)
/*!80001 SET @@session.original_commit_timestamp=1721455822100724*//*!*/;
/*!80014 SET @@session.original_server_version=80032*//*!*/;
/*!80014 SET @@session.immediate_server_version=80032*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1758
#2407206:10:22 server id 1end_log_pos 1829 CRC32 0xb8962d85 Querythread_id=9exec_time=0error_code=0
SET TIMESTAMP=1721455822/*!*/;
BEGIN
/*!*/;
# at 1829
#2407206:10:22 server id 1end_log_pos 1894 CRC32 0xda95d477 Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 1894
#2407206:10:22 server id 1end_log_pos 1946 CRC32 0x32b8c70c Write_rows: table id 102 flags: STMT_END_F

BINLOG '
zlSbZhMBAAAAQQAAAGYHAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AHfUldo=
zlSbZh4BAAAANAAAAJoHAAAAAGYAAAAAAAEAAgAD/wACAAAABgDmnY7lm5sTAAAADMe4Mg==
'/*!*/;
# at 1946
#2407206:10:22 server id 1end_log_pos 1977 CRC32 0x7cae582d Xid = 84
COMMIT/*!*/;
# at 1977
#2407206:10:22 server id 1end_log_pos 2056 CRC32 0xff6c3b51 Anonymous_GTIDlast_committed=6sequence_number=7rbr_only=yesoriginal_committed_timestamp=1721455822102188immediate_commit_timestamp=1721455822102188transaction_length=298
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1721455822102188 (2024-07-20 06:10:22.102188 UTC)
# immediate_commit_timestamp=1721455822102188 (2024-07-20 06:10:22.102188 UTC)
/*!80001 SET @@session.original_commit_timestamp=1721455822102188*//*!*/;
/*!80014 SET @@session.original_server_version=80032*//*!*/;
/*!80014 SET @@session.immediate_server_version=80032*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2056
#2407206:10:22 server id 1end_log_pos 2127 CRC32 0x0872f115 Querythread_id=9exec_time=0error_code=0
SET TIMESTAMP=1721455822/*!*/;
BEGIN
/*!*/;
# at 2127
#2407206:10:22 server id 1end_log_pos 2192 CRC32 0x6c869972 Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 2192
#2407206:10:22 server id 1end_log_pos 2244 CRC32 0x76a38f1f Write_rows: table id 102 flags: STMT_END_F

BINLOG '
zlSbZhMBAAAAQQAAAJAIAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AHKZhmw=
zlSbZh4BAAAANAAAAMQIAAAAAGYAAAAAAAEAAgAD/wADAAAABgDnjovkupQUAAAAH4+jdg==
'/*!*/;
# at 2244
#2407206:10:22 server id 1end_log_pos 2275 CRC32 0xecc79b63 Xid = 86
COMMIT/*!*/;
# at 2275
#2407206:10:22 server id 1end_log_pos 2354 CRC32 0x12a444bc Anonymous_GTIDlast_committed=7sequence_number=8rbr_only=yesoriginal_committed_timestamp=1721455822103661immediate_commit_timestamp=1721455822103661transaction_length=298
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1721455822103661 (2024-07-20 06:10:22.103661 UTC)
# immediate_commit_timestamp=1721455822103661 (2024-07-20 06:10:22.103661 UTC)
/*!80001 SET @@session.original_commit_timestamp=1721455822103661*//*!*/;
/*!80014 SET @@session.original_server_version=80032*//*!*/;
/*!80014 SET @@session.immediate_server_version=80032*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2354
#2407206:10:22 server id 1end_log_pos 2425 CRC32 0x5d981940 Querythread_id=9exec_time=0error_code=0
SET TIMESTAMP=1721455822/*!*/;
BEGIN
/*!*/;
# at 2425
#2407206:10:22 server id 1end_log_pos 2490 CRC32 0x939ba98a Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 2490
#2407206:10:22 server id 1end_log_pos 2542 CRC32 0x34da618d Write_rows: table id 102 flags: STMT_END_F

BINLOG '
zlSbZhMBAAAAQQAAALoJAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AIqpm5M=
zlSbZh4BAAAANAAAAO4JAAAAAGYAAAAAAAEAAgAD/wAEAAAABgDotbXlha0VAAAAjWHaNA==
'/*!*/;
# at 2542
#2407206:10:22 server id 1end_log_pos 2573 CRC32 0x5d240951 Xid = 88
COMMIT/*!*/;
# at 2573
#2407206:10:59 server id 1end_log_pos 2652 CRC32 0xeaa53dae Anonymous_GTIDlast_committed=8sequence_number=9rbr_only=yesoriginal_committed_timestamp=1721455859183393immediate_commit_timestamp=1721455859183393transaction_length=325
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1721455859183393 (2024-07-20 06:10:59.183393 UTC)
# immediate_commit_timestamp=1721455859183393 (2024-07-20 06:10:59.183393 UTC)
/*!80001 SET @@session.original_commit_timestamp=1721455859183393*//*!*/;
/*!80014 SET @@session.original_server_version=80032*//*!*/;
/*!80014 SET @@session.immediate_server_version=80032*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2652
#2407206:10:59 server id 1end_log_pos 2732 CRC32 0x763aed44 Querythread_id=9exec_time=0error_code=0
SET TIMESTAMP=1721455859/*!*/;
BEGIN
/*!*/;
# at 2732
#2407206:10:59 server id 1end_log_pos 2797 CRC32 0x983ab39f Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 2797
#2407206:10:59 server id 1end_log_pos 2867 CRC32 0x858f3008 Update_rows: table id 102 flags: STMT_END_F

BINLOG '
81SbZhMBAAAAQQAAAO0KAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AJ+zOpg=
81SbZh8BAAAARgAAADMLAAAAAGYAAAAAAAEAAgAD//8ABAAAAAYA6LW15YWtFQAAAAAEAAAABgDo
tbXlha0cAAAACDCPhQ==
'/*!*/;
# at 2867
#2407206:10:59 server id 1end_log_pos 2898 CRC32 0x4eb8b581 Xid = 102
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*/;
bash-4.4# 是不是看完一脸懵逼，只能大概看出在什么时间做了什么操作，而具体操作的数据却看不出来，加上verbose我们再试试。
bash-4.4# mysqlbinlog --verbose /var/lib/mysql/binlog.000012

bash-4.4# mysqlbinlog --verbose /var/lib/mysql/binlog.000012

# The proper term is pseudo_replica_mode, but we use this compatibility alias# to make the statement usable on server versions 8.0.24 and older./*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#240719 15:36:06 server id 1end_log_pos 126 CRC32 0xfedff2b0 Start: binlog v 4, server v 8.0.32 created 240719 15:36:06 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;BINLOG '5oeaZg8BAAAAegAAAH4AAAABAAQAOC4wLjMyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADmh5pmEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQACigAAbDy3/4='/*!*/;# at 126#240719 15:36:06 server id 1end_log_pos 157 CRC32 0x82f4e7e8 Previous-GTIDs# # at 157#2407206:08:11 server id 1end_log_pos 236 CRC32 0x35450d0b Anonymous_GTIDlast_committed=0sequence_number=1rbr_only=nooriginal_committed_timestamp=1721455691656110immediate_commit_timestamp=1721455691656110transaction_length=314# original_commit_timestamp=1721455691656110 (2024-07-20 06:08:11.656110 UTC)# immediate_commit_timestamp=1721455691656110 (2024-07-20 06:08:11.656110 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455691656110*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 236#2407206:08:11 server id 1end_log_pos 471 CRC32 0xeadf8f66 Querythread_id=9exec_time=0error_code=0Xid = 44SET TIMESTAMP=1721455691/*!*/;SET @@session.pseudo_thread_id=9/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1168113696/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8mb4 *//*!*/;SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;/*!80016 SET @@session.default_table_encryption=0*//*!*/;/* ApplicationName=DBeaver 23.3.3 - Main */ CREATE SCHEMA `yeYingXuan`DEFAULT CHARACTER SET utf8mb4DEFAULT COLLATE utf8mb4_0900_ai_ci/*!*/;# at 471#2407206:09:01 server id 1end_log_pos 550 CRC32 0x4104bf16 Anonymous_GTIDlast_committed=1sequence_number=2rbr_only=nooriginal_committed_timestamp=1721455741084309immediate_commit_timestamp=1721455741084309transaction_length=382# original_commit_timestamp=1721455741084309 (2024-07-20 06:09:01.084309 UTC)# immediate_commit_timestamp=1721455741084309 (2024-07-20 06:09:01.084309 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455741084309*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 550#2407206:09:01 server id 1end_log_pos 853 CRC32 0xa4e21747 Querythread_id=9exec_time=0error_code=0Xid = 55SET TIMESTAMP=1721455741/*!*/;/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;/* ApplicationName=DBeaver 23.3.3 - Main */ CREATE TABLE yeYingXuan.`user` (id INT auto_increment NOT NULL,CONSTRAINT user_pk PRIMARY KEY (id))ENGINE=InnoDBDEFAULT CHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci/*!*/;# at 853#2407206:09:22 server id 1end_log_pos 932 CRC32 0xd6cc2d85 Anonymous_GTIDlast_committed=2sequence_number=3rbr_only=nooriginal_committed_timestamp=1721455762869115immediate_commit_timestamp=1721455762869115transaction_length=269# original_commit_timestamp=1721455762869115 (2024-07-20 06:09:22.869115 UTC)# immediate_commit_timestamp=1721455762869115 (2024-07-20 06:09:22.869115 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455762869115*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 932#2407206:09:22 server id 1end_log_pos 1122 CRC32 0x9665ccf6 Querythread_id=9exec_time=0error_code=0Xid = 66SET TIMESTAMP=1721455762/*!*/;/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;/* ApplicationName=DBeaver 23.3.3 - Main */ ALTER TABLE yeYingXuan.`user` ADD name varchar(100) NULL/*!*/;# at 1122#2407206:09:22 server id 1end_log_pos 1201 CRC32 0xd8c2e651 Anonymous_GTIDlast_committed=3sequence_number=4rbr_only=nooriginal_committed_timestamp=1721455762877721immediate_commit_timestamp=1721455762877721transaction_length=259# original_commit_timestamp=1721455762877721 (2024-07-20 06:09:22.877721 UTC)# immediate_commit_timestamp=1721455762877721 (2024-07-20 06:09:22.877721 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455762877721*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 1201#2407206:09:22 server id 1end_log_pos 1381 CRC32 0x156c6bda Querythread_id=9exec_time=0error_code=0Xid = 69SET TIMESTAMP=1721455762/*!*/;/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;/* ApplicationName=DBeaver 23.3.3 - Main */ ALTER TABLE yeYingXuan.`user` ADD age INT NULL/*!*/;# at 1381#2407206:10:22 server id 1end_log_pos 1460 CRC32 0x08d41c89 Anonymous_GTIDlast_committed=4sequence_number=5rbr_only=yesoriginal_committed_timestamp=1721455822097710immediate_commit_timestamp=1721455822097710transaction_length=298/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;# original_commit_timestamp=1721455822097710 (2024-07-20 06:10:22.097710 UTC)# immediate_commit_timestamp=1721455822097710 (2024-07-20 06:10:22.097710 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455822097710*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 1460#2407206:10:22 server id 1end_log_pos 1531 CRC32 0xec33d8e3 Querythread_id=9exec_time=0error_code=0SET TIMESTAMP=1721455822/*!*/;BEGIN/*!*/;# at 1531#2407206:10:22 server id 1end_log_pos 1596 CRC32 0xff0fa400 Table_map: `yeYingXuan`.`user` mapped to number 102# has_generated_invisible_primary_key=0# at 1596#2407206:10:22 server id 1end_log_pos 1648 CRC32 0xcf459f50 Write_rows: table id 102 flags: STMT_END_FBINLOG 'zlSbZhMBAAAAQQAAADwGAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQACA/z/AACkD/8=zlSbZh4BAAAANAAAAHAGAAAAAGYAAAAAAAEAAgAD/wABAAAABgDlvKDkuIkSAAAAUJ9Fzw=='/*!*/;### INSERT INTO `yeYingXuan`.`user`### SET### @1=1### @2='张三'### @3=18# at 1648#2407206:10:22 server id 1end_log_pos 1679 CRC32 0x3443872d Xid = 82COMMIT/*!*/;# at 1679#2407206:10:22 server id 1end_log_pos 1758 CRC32 0x7896626b Anonymous_GTIDlast_committed=5sequence_number=6rbr_only=yesoriginal_committed_timestamp=1721455822100724immediate_commit_timestamp=1721455822100724transaction_length=298/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;# original_commit_timestamp=1721455822100724 (2024-07-20 06:10:22.100724 UTC)# immediate_commit_timestamp=1721455822100724 (2024-07-20 06:10:22.100724 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455822100724*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 1758#2407206:10:22 server id 1end_log_pos 1829 CRC32 0xb8962d85 Querythread_id=9exec_time=0error_code=0SET TIMESTAMP=1721455822/*!*/;BEGIN/*!*/;# at 1829#2407206:10:22 server id 1end_log_pos 1894 CRC32 0xda95d477 Table_map: `yeYingXuan`.`user` mapped to number 102# has_generated_invisible_primary_key=0# at 1894#2407206:10:22 server id 1end_log_pos 1946 CRC32 0x32b8c70c Write_rows: table id 102 flags: STMT_END_FBINLOG 'zlSbZhMBAAAAQQAAAGYHAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQACA/z/AHfUldo=zlSbZh4BAAAANAAAAJoHAAAAAGYAAAAAAAEAAgAD/wACAAAABgDmnY7lm5sTAAAADMe4Mg=='/*!*/;### INSERT INTO `yeYingXuan`.`user`### SET### @1=2### @2='李四'### @3=19# at 1946#2407206:10:22 server id 1end_log_pos 1977 CRC32 0x7cae582d Xid = 84COMMIT/*!*/;# at 1977#2407206:10:22 server id 1end_log_pos 2056 CRC32 0xff6c3b51 Anonymous_GTIDlast_committed=6sequence_number=7rbr_only=yesoriginal_committed_timestamp=1721455822102188immediate_commit_timestamp=1721455822102188transaction_length=298/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;# original_commit_timestamp=1721455822102188 (2024-07-20 06:10:22.102188 UTC)# immediate_commit_timestamp=1721455822102188 (2024-07-20 06:10:22.102188 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455822102188*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 2056#2407206:10:22 server id 1end_log_pos 2127 CRC32 0x0872f115 Querythread_id=9exec_time=0error_code=0SET TIMESTAMP=1721455822/*!*/;BEGIN/*!*/;# at 2127#2407206:10:22 server id 1end_log_pos 2192 CRC32 0x6c869972 Table_map: `yeYingXuan`.`user` mapped to number 102# has_generated_invisible_primary_key=0# at 2192#2407206:10:22 server id 1end_log_pos 2244 CRC32 0x76a38f1f Write_rows: table id 102 flags: STMT_END_FBINLOG 'zlSbZhMBAAAAQQAAAJAIAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQACA/z/AHKZhmw=zlSbZh4BAAAANAAAAMQIAAAAAGYAAAAAAAEAAgAD/wADAAAABgDnjovkupQUAAAAH4+jdg=='/*!*/;### INSERT INTO `yeYingXuan`.`user`### SET### @1=3### @2='王五'### @3=20# at 2244#2407206:10:22 server id 1end_log_pos 2275 CRC32 0xecc79b63 Xid = 86COMMIT/*!*/;# at 2275#2407206:10:22 server id 1end_log_pos 2354 CRC32 0x12a444bc Anonymous_GTIDlast_committed=7sequence_number=8rbr_only=yesoriginal_committed_timestamp=1721455822103661immediate_commit_timestamp=1721455822103661transaction_length=298/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;# original_commit_timestamp=1721455822103661 (2024-07-20 06:10:22.103661 UTC)# immediate_commit_timestamp=1721455822103661 (2024-07-20 06:10:22.103661 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455822103661*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 2354#2407206:10:22 server id 1end_log_pos 2425 CRC32 0x5d981940 Querythread_id=9exec_time=0error_code=0SET TIMESTAMP=1721455822/*!*/;BEGIN/*!*/;# at 2425#2407206:10:22 server id 1end_log_pos 2490 CRC32 0x939ba98a Table_map: `yeYingXuan`.`user` mapped to number 102# has_generated_invisible_primary_key=0# at 2490#2407206:10:22 server id 1end_log_pos 2542 CRC32 0x34da618d Write_rows: table id 102 flags: STMT_END_FBINLOG 'zlSbZhMBAAAAQQAAALoJAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQACA/z/AIqpm5M=zlSbZh4BAAAANAAAAO4JAAAAAGYAAAAAAAEAAgAD/wAEAAAABgDotbXlha0VAAAAjWHaNA=='/*!*/;### INSERT INTO `yeYingXuan`.`user`### SET### @1=4### @2='赵六'### @3=21# at 2542#2407206:10:22 server id 1end_log_pos 2573 CRC32 0x5d240951 Xid = 88COMMIT/*!*/;# at 2573#2407206:10:59 server id 1end_log_pos 2652 CRC32 0xeaa53dae Anonymous_GTIDlast_committed=8sequence_number=9rbr_only=yesoriginal_committed_timestamp=1721455859183393immediate_commit_timestamp=1721455859183393transaction_length=325/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;# original_commit_timestamp=1721455859183393 (2024-07-20 06:10:59.183393 UTC)# immediate_commit_timestamp=1721455859183393 (2024-07-20 06:10:59.183393 UTC)/*!80001 SET @@session.original_commit_timestamp=1721455859183393*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 2652#2407206:10:59 server id 1end_log_pos 2732 CRC32 0x763aed44 Querythread_id=9exec_time=0error_code=0SET TIMESTAMP=1721455859/*!*/;BEGIN/*!*/;# at 2732#2407206:10:59 server id 1end_log_pos 2797 CRC32 0x983ab39f Table_map: `yeYingXuan`.`user` mapped to number 102# has_generated_invisible_primary_key=0# at 2797#2407206:10:59 server id 1end_log_pos 2867 CRC32 0x858f3008 Update_rows: table id 102 flags: STMT_END_FBINLOG '81SbZhMBAAAAQQAAAO0KAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQACA/z/AJ+zOpg=81SbZh8BAAAARgAAADMLAAAAAGYAAAAAAAEAAgAD//8ABAAAAAYA6LW15YWtFQAAAAAEAAAABgDotbXlha0cAAAACDCPhQ=='/*!*/;### UPDATE `yeYingXuan`.`user`### WHERE### @1=4### @2='赵六'### @3=21### SET### @1=4### @2='赵六'### @3=28# at 2867#2407206:10:59 server id 1end_log_pos 2898 CRC32 0x4eb8b581 Xid = 102COMMIT/*!*/;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*/;bash-4.4# 表明：
BEGIN 为开始，以 COMMIT 或者 ROLLBACK 竣事，中间包罗实际的 SQL 操作。
大家这样看的是不是就比力清晰，可以明确的看出在谁人时间对哪条数据做了处理，而且可以看出处理前的数据与处理后的数据。

  数据备份与规复
备份数据库：
规复数据前把我们如今的库做个备份，这里我记录了【备份所有库】、【备份指定库】、【备份指定表】的方法，大家按实际场景使用。
备份所有库：
bash-4.4# mysqldump -u root -p --all-databases > /bak/all_databases_backup.sql
备份指定库：
bash-4.4# mysqldump -u root -p yeYingXuan > /bak/databases_backup.sql
备份指定表：
bash-4.4# mysqldump -uroot -p yeYingXuan user > /bak/usre.sql
规复备份所有库数据
mysql -uroot -p < /bak/all_databases_backup.sql
规复备份指定库数据
mysql -uroot -p yeYingXuan < /bak/databases_backup.sql
表明：yeYingXuan = 要规复的库规复备份指定表数据
mysql -uroot -p yeYingXuan</bak/usre.sql
表明：
-u 指定登录MySQL的用户名为root。
-p：表现输入密码，有密码后面跟密码，-p后没有空格，例如：-p1234
yeYingXuan：要备份的库。
user > /bak/usre.sql ：将user表备份到文件bak目录到usre.sql文件中

binlog数据规复：
实行的sql都捞出来了，那么接下来就该最紧张的一步，怎么规复数据呢？
这里我模拟了几种场景，希望有能帮到大家。
方法一（把数据拿出来写sql进行规复）：
对某一条实行了错误的更新/删除操作：这种规复是最简单的，我们直接在binlog找到这条数据的操作记录，找到原数据的值，然后实行新增/更新操作即可。
例如：
bash-4.4# mysqlbinlog --verbose /var/lib/mysql/binlog.000012

| grep -A 50 '2024-07-20 06:17' |more# original_commit_timestamp=1721456269222116 (2024-07-20 06:17:49.222116 UTC)# immediate_commit_timestamp=1721456269222116 (2024-07-20 06:17:49.222116 UTC)/*!80001 SET @@session.original_commit_timestamp=1721456269222116*//*!*/;/*!80014 SET @@session.original_server_version=80032*//*!*/;/*!80014 SET @@session.immediate_server_version=80032*//*!*/;SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 2977#2407206:17:49 server id 1end_log_pos 3048 CRC32 0xf40a16d8 Querythread_id=12exec_time=0error_code=0SET TIMESTAMP=1721456269/*!*/;BEGIN/*!*/;# at 3048#2407206:17:49 server id 1end_log_pos 3113 CRC32 0x170c9683 Table_map: `yeYingXuan`.`user` mapped to number 102# has_generated_invisible_primary_key=0# at 3113#2407206:17:49 server id 1end_log_pos 3165 CRC32 0xb8e8f644 Delete_rows: table id 102 flags: STMT_END_FBINLOG 'jVabZhMBAAAAQQAAACkMAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQACA/z/AIOWDBc=jVabZiABAAAANAAAAF0MAAAAAGYAAAAAAAEAAgAD/wADAAAABgDnjovkupQUAAAARPbouA=='/*!*/;### DELETE FROM `yeYingXuan`.`user`### WHERE### @1=3### @2='王五'### @3=20# at 3165#2407206:17:49 server id 1end_log_pos 3196 CRC32 0x064e38f9 Xid = 139COMMIT/*!*/;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*/;bash-4.4# 可以看到删除的数据id=3，name=王五，age=20，那么我们直接新增sql规复即可。
INSERT INTO `user` (id,`name`,age) VALUES (3,'王五',20)

方法二和方法三要用的时间点和位置点/pos点，这里先给大家介绍下在哪看。
https://i-blog.csdnimg.cn/direct/df139a078607492f9cc7beb9d8d4002b.png
方法二（根据时间点区间规复）：
我们模拟新增一条name=小龙女的数据，然后删除再规复。
https://i-blog.csdnimg.cn/direct/e6016c72549549118fb385a06792425b.png
然后删除该条数据，查看binlog日志。
bash-4.4# mysqlbinlog --verbose /var/lib/mysql/binlog.000012

# at 5989
#2407208:06:13 server id 1end_log_pos 6052 CRC32 0xd6df0c13 Table_map: `yeYingXuan`.`user` mapped to number 178
# has_generated_invisible_primary_key=0
# at 6052
#2407208:06:13 server id 1end_log_pos 6107 CRC32 0x76a8609f Write_rows: table id 178 flags: STMT_END_F

BINLOG '
9W+bZhMBAAAAPwAAAKQXAAAAALIAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
AeATDN/W
9W+bZh4BAAAANwAAANsXAAAAALIAAAAAAAEAAgAD/wAFAAAACQDlsI/pvpnlpbMeAAAAn2Codg==
'/*!*/;
### INSERT INTO `yeYingXuan`.`user`
### SET
### @1=5
### @2='小龙女'
### @3=30
# at 6107
#2407208:06:13 server id 1end_log_pos 6138 CRC32 0xb06e6629 Xid = 2486
COMMIT/*!*/;
# at 6138
#2407208:08:04 server id 1end_log_pos 6217 CRC32 0xabe69750 Anonymous_GTIDlast_committed=20sequence_number=21rbr_only=yesoriginal_committed_timestamp=1721462884639790immediate_commit_timestamp=1721462884639790transaction_length=309
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1721462884639790 (2024-07-20 08:08:04.639790 UTC)
# immediate_commit_timestamp=1721462884639790 (2024-07-20 08:08:04.639790 UTC)
/*!80001 SET @@session.original_commit_timestamp=1721462884639790*//*!*/;
/*!80014 SET @@session.original_server_version=80032*//*!*/;
/*!80014 SET @@session.immediate_server_version=80032*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 6217
#2407208:08:04 server id 1end_log_pos 6298 CRC32 0x2f166d0e Querythread_id=55exec_time=0error_code=0
SET TIMESTAMP=1721462884/*!*/;
BEGIN
/*!*/;
# at 6298
#2407208:08:04 server id 1end_log_pos 6361 CRC32 0xa5403440 Table_map: `yeYingXuan`.`user` mapped to number 178
# has_generated_invisible_primary_key=0
# at 6361
#2407208:08:04 server id 1end_log_pos 6416 CRC32 0x4e634334 Delete_rows: table id 178 flags: STMT_END_F

BINLOG '
ZHCbZhMBAAAAPwAAANkYAAAAALIAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
AeBANECl
ZHCbZiABAAAANwAAABAZAAAAALIAAAAAAAEAAgAD/wAFAAAACQDlsI/pvpnlpbMeAAAANENjTg==
'/*!*/;
### DELETE FROM `yeYingXuan`.`user`
### WHERE
### @1=5
### @2='小龙女'
### @3=30
# at 6416
#2407208:08:04 server id 1end_log_pos 6447 CRC32 0x72e5d1de Xid = 2492
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和一条删除的sql，新增的时间点是 240720 8:06:13，竣事的时间点是240720 8:06:13，只要我们规复的时间段包罗了这个时间段即可。接下来我们用时间点进行数据规复，
bash
-4.4
# mysqlbinlog --start-datetime="2024-07-20 07:56:24" --stop-datetime="2024-07-20 08:07:13" /var/lib/mysql/binlog.000012 | mysql -uroot -p yeYingXuan
Enter password:
bash-4.4# 表明：
--start-datetime=开始时间
--stop-datetime=竣事时间
/var/lib/mysql/binlog.000014 = 要规复到binlog文件。
-uroot =用户名为root。
-p=密码。
yeYingXuan=要规复到数据库。
查看数据已经乐成规复。
https://i-blog.csdnimg.cn/direct/74f54b2351234255ba79f5ce0eacff26.png

方法三：（按位置点规复）
我们模拟新作一条name=杨过的数据，然后删除后规复。
https://i-blog.csdnimg.cn/direct/96230bf5ed384910bf42cc214a115312.png
然后删除该条数据，查看binlog日志。
bash-4.4# mysqlbinlog --verbose /var/lib/mysql/binlog.000012

# at 7287
#2407208:29:34 server id 1end_log_pos 7366 CRC32 0xd9a43076 Anonymous_GTIDlast_committed=24sequence_number=25rbr_only=yesoriginal_committed_timestamp=1721464174583630immediate_commit_timestamp=1721464174583630transaction_length=296
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1721464174583630 (2024-07-20 08:29:34.583630 UTC)
# immediate_commit_timestamp=1721464174583630 (2024-07-20 08:29:34.583630 UTC)
/*!80001 SET @@session.original_commit_timestamp=1721464174583630*//*!*/;
/*!80014 SET @@session.original_server_version=80032*//*!*/;
/*!80014 SET @@session.immediate_server_version=80032*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 7366
#2407208:29:34 server id 1end_log_pos 7437 CRC32 0x68009279 Querythread_id=53exec_time=0error_code=0
SET TIMESTAMP=1721464174/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
BEGIN
/*!*/;
# at 7437
#2407208:29:34 server id 1end_log_pos 7500 CRC32 0xbfdc8e15 Table_map: `yeYingXuan`.`user` mapped to number 180
# has_generated_invisible_primary_key=0
# at 7500
#2407208:29:34 server id 1end_log_pos 7552 CRC32 0x4e2f0591 Write_rows: table id 180 flags: STMT_END_F

BINLOG '
bnWbZhMBAAAAPwAAAEwdAAAAALQAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
AeAVjty/
bnWbZh4BAAAANAAAAIAdAAAAALQAAAAAAAEAAgAD/wAGAAAABgDmnajov4cZAAAAkQUvTg==
'/*!*/;
### INSERT INTO `yeYingXuan`.`user`
### SET
### @1=6
### @2='杨过'
### @3=25
# at 7552
#2407208:29:34 server id 1end_log_pos 7583 CRC32 0x3d3aaba3 Xid = 2670
COMMIT/*!*/;
# at 7583
#2407208:30:43 server id 1end_log_pos 7662 CRC32 0x3f31e9c6 Anonymous_GTIDlast_committed=25sequence_number=26rbr_only=yesoriginal_committed_timestamp=1721464243592594immediate_commit_timestamp=1721464243592594transaction_length=296
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1721464243592594 (2024-07-20 08:30:43.592594 UTC)
# immediate_commit_timestamp=1721464243592594 (2024-07-20 08:30:43.592594 UTC)
/*!80001 SET @@session.original_commit_timestamp=1721464243592594*//*!*/;
/*!80014 SET @@session.original_server_version=80032*//*!*/;
/*!80014 SET @@session.immediate_server_version=80032*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 7662
#2407208:30:43 server id 1end_log_pos 7733 CRC32 0x55267e82 Querythread_id=53exec_time=0error_code=0
SET TIMESTAMP=1721464243/*!*/;
BEGIN
/*!*/;
# at 7733
#2407208:30:43 server id 1end_log_pos 7796 CRC32 0x28dab411 Table_map: `yeYingXuan`.`user` mapped to number 180
# has_generated_invisible_primary_key=0
# at 7796
#2407208:30:43 server id 1end_log_pos 7848 CRC32 0xaf6ae3b5 Delete_rows: table id 180 flags: STMT_END_F

BINLOG '
s3WbZhMBAAAAPwAAAHQeAAAAALQAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
AeARtNoo
s3WbZiABAAAANAAAAKgeAAAAALQAAAAAAAEAAgAD/wAGAAAABgDmnajov4cZAAAAteNqrw==
'/*!*/;
### DELETE FROM `yeYingXuan`.`user`
### WHERE
### @1=6
### @2='杨过'
### @3=25
# at 7848
#2407208:30:43 server id 1end_log_pos 7879 CRC32 0xdcb2c099 Xid = 2675
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*/; 可以看到新增数据的位置点是7437到7583，接下来我们用位置点规复数据
bash-4.4# mysqlbinlog --start-position=7437 --stop-position=7583 /var/lib/mysql/binlog.000012 > /bak/binlog_statements.sql
bash-4.4# mysql -u root -p < binlog_statements.sql
Enter password:
bash-4.4# 表明：

1、将要规复的数据添加到/bak目录到binlog_statements.sql文件中
bash-4.4# mysqlbinlog --start-position=2898 --stop-position=3196 /var/lib/mysql/binlog.000012 > /bak/binlog_statements.sql
--start-position=开始pos点
--stop-position=竣事pos点
/var/lib/mysql/binlog.000014 = 要规复到binlog文件。
/bak/binlog_statements.sql=生成到bak目录下的binlog_statements.sql文件中。
2、将生成的 SQL 文件（binlog_statements.sql）导入到 MySQL 数据库中实行，以应用这段时间范围内的变动。

bash-4.4# mysql -u root -p < binlog_statements.sql
Enter password:
bash-4.4# 看数据已经乐成规复。
https://i-blog.csdnimg.cn/direct/77650f6bfff54602b5b2dd88c67449eb.png
大家可以动手试试，真遇到告急环境，以备不时之需。此外，下面留言功能已开启，如果大家有任何疑问、发起或想分享的经验，都欢迎在这里留言。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

页: [1]

IT评测·应用市场-qidao123.com技术社区's Archiver

MYSQL之binlog规复数据