MYSQL之binlog恢复数据

于 2024-07-31 10:02:10 发布
阅读量480 收藏 14
点赞数 10
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/shenyulong1214/article/details/140814143
版权
话说谁还没删错/改错过数据是吧，一旦删错了，我们真的要跑路吗？今天我们来玩一玩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
mysqlbinlog  Ver 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 1  end_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 1  end_log_pos 157 CRC32 0x82f4e7e8   Previous-GTIDs
# [empty]
# at 157
#240720  6:08:11 server id 1  end_log_pos 236 CRC32 0x35450d0b   Anonymous_GTID  last_committed=0  sequence_number=1  rbr_only=no  original_committed_timestamp=1721455691656110  immediate_commit_timestamp=1721455691656110  transaction_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
#240720  6:08:11 server id 1  end_log_pos 471 CRC32 0xeadf8f66   Query  thread_id=9  exec_time=0  error_code=0  Xid = 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
#240720  6:09:01 server id 1  end_log_pos 550 CRC32 0x4104bf16   Anonymous_GTID  last_committed=1  sequence_number=2  rbr_only=no  original_committed_timestamp=1721455741084309  immediate_commit_timestamp=1721455741084309  transaction_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
#240720  6:09:01 server id 1  end_log_pos 853 CRC32 0xa4e21747   Query  thread_id=9  exec_time=0  error_code=0  Xid = 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
#240720  6:09:22 server id 1  end_log_pos 932 CRC32 0xd6cc2d85   Anonymous_GTID  last_committed=2  sequence_number=3  rbr_only=no  original_committed_timestamp=1721455762869115  immediate_commit_timestamp=1721455762869115  transaction_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
#240720  6:09:22 server id 1  end_log_pos 1122 CRC32 0x9665ccf6   Query  thread_id=9  exec_time=0  error_code=0  Xid = 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
#240720  6:09:22 server id 1  end_log_pos 1201 CRC32 0xd8c2e651   Anonymous_GTID  last_committed=3  sequence_number=4  rbr_only=no  original_committed_timestamp=1721455762877721  immediate_commit_timestamp=1721455762877721  transaction_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
#240720  6:09:22 server id 1  end_log_pos 1381 CRC32 0x156c6bda   Query  thread_id=9  exec_time=0  error_code=0  Xid = 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
#240720  6:10:22 server id 1  end_log_pos 1460 CRC32 0x08d41c89   Anonymous_GTID  last_committed=4  sequence_number=5  rbr_only=yes  original_committed_timestamp=1721455822097710  immediate_commit_timestamp=1721455822097710  transaction_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
#240720  6:10:22 server id 1  end_log_pos 1531 CRC32 0xec33d8e3   Query  thread_id=9  exec_time=0  error_code=0
SET TIMESTAMP=1721455822/*!*/;
BEGIN
/*!*/;
# at 1531
#240720  6:10:22 server id 1  end_log_pos 1596 CRC32 0xff0fa400   Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 1596
#240720  6:10:22 server id 1  end_log_pos 1648 CRC32 0xcf459f50   Write_rows: table id 102 flags: STMT_END_F

BINLOG '
zlSbZhMBAAAAQQAAADwGAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AACkD/8=
zlSbZh4BAAAANAAAAHAGAAAAAGYAAAAAAAEAAgAD/wABAAAABgDlvKDkuIkSAAAAUJ9Fzw==
'/*!*/;
# at 1648
#240720  6:10:22 server id 1  end_log_pos 1679 CRC32 0x3443872d   Xid = 82
COMMIT/*!*/;
# at 1679
#240720  6:10:22 server id 1  end_log_pos 1758 CRC32 0x7896626b   Anonymous_GTID  last_committed=5  sequence_number=6  rbr_only=yes  original_committed_timestamp=1721455822100724  immediate_commit_timestamp=1721455822100724  transaction_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
#240720  6:10:22 server id 1  end_log_pos 1829 CRC32 0xb8962d85   Query  thread_id=9  exec_time=0  error_code=0
SET TIMESTAMP=1721455822/*!*/;
BEGIN
/*!*/;
# at 1829
#240720  6:10:22 server id 1  end_log_pos 1894 CRC32 0xda95d477   Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 1894
#240720  6:10:22 server id 1  end_log_pos 1946 CRC32 0x32b8c70c   Write_rows: table id 102 flags: STMT_END_F

BINLOG '
zlSbZhMBAAAAQQAAAGYHAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AHfUldo=
zlSbZh4BAAAANAAAAJoHAAAAAGYAAAAAAAEAAgAD/wACAAAABgDmnY7lm5sTAAAADMe4Mg==
'/*!*/;
# at 1946
#240720  6:10:22 server id 1  end_log_pos 1977 CRC32 0x7cae582d   Xid = 84
COMMIT/*!*/;
# at 1977
#240720  6:10:22 server id 1  end_log_pos 2056 CRC32 0xff6c3b51   Anonymous_GTID  last_committed=6  sequence_number=7  rbr_only=yes  original_committed_timestamp=1721455822102188  immediate_commit_timestamp=1721455822102188  transaction_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
#240720  6:10:22 server id 1  end_log_pos 2127 CRC32 0x0872f115   Query  thread_id=9  exec_time=0  error_code=0
SET TIMESTAMP=1721455822/*!*/;
BEGIN
/*!*/;
# at 2127
#240720  6:10:22 server id 1  end_log_pos 2192 CRC32 0x6c869972   Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 2192
#240720  6:10:22 server id 1  end_log_pos 2244 CRC32 0x76a38f1f   Write_rows: table id 102 flags: STMT_END_F

BINLOG '
zlSbZhMBAAAAQQAAAJAIAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AHKZhmw=
zlSbZh4BAAAANAAAAMQIAAAAAGYAAAAAAAEAAgAD/wADAAAABgDnjovkupQUAAAAH4+jdg==
'/*!*/;
# at 2244
#240720  6:10:22 server id 1  end_log_pos 2275 CRC32 0xecc79b63   Xid = 86
COMMIT/*!*/;
# at 2275
#240720  6:10:22 server id 1  end_log_pos 2354 CRC32 0x12a444bc   Anonymous_GTID  last_committed=7  sequence_number=8  rbr_only=yes  original_committed_timestamp=1721455822103661  immediate_commit_timestamp=1721455822103661  transaction_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
#240720  6:10:22 server id 1  end_log_pos 2425 CRC32 0x5d981940   Query  thread_id=9  exec_time=0  error_code=0
SET TIMESTAMP=1721455822/*!*/;
BEGIN
/*!*/;
# at 2425
#240720  6:10:22 server id 1  end_log_pos 2490 CRC32 0x939ba98a   Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 2490
#240720  6:10:22 server id 1  end_log_pos 2542 CRC32 0x34da618d   Write_rows: table id 102 flags: STMT_END_F

BINLOG '
zlSbZhMBAAAAQQAAALoJAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AIqpm5M=
zlSbZh4BAAAANAAAAO4JAAAAAGYAAAAAAAEAAgAD/wAEAAAABgDotbXlha0VAAAAjWHaNA==
'/*!*/;
# at 2542
#240720  6:10:22 server id 1  end_log_pos 2573 CRC32 0x5d240951   Xid = 88
COMMIT/*!*/;
# at 2573
#240720  6:10:59 server id 1  end_log_pos 2652 CRC32 0xeaa53dae   Anonymous_GTID  last_committed=8  sequence_number=9  rbr_only=yes  original_committed_timestamp=1721455859183393  immediate_commit_timestamp=1721455859183393  transaction_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
#240720  6:10:59 server id 1  end_log_pos 2732 CRC32 0x763aed44   Query  thread_id=9  exec_time=0  error_code=0
SET TIMESTAMP=1721455859/*!*/;
BEGIN
/*!*/;
# at 2732
#240720  6:10:59 server id 1  end_log_pos 2797 CRC32 0x983ab39f   Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 2797
#240720  6:10:59 server id 1  end_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
#240720  6:10:59 server id 1  end_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 1  end_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 1  end_log_pos 157 CRC32 0x82f4e7e8   Previous-GTIDs
# [empty]
# at 157
#240720  6:08:11 server id 1  end_log_pos 236 CRC32 0x35450d0b   Anonymous_GTID  last_committed=0  sequence_number=1  rbr_only=no  original_committed_timestamp=1721455691656110  immediate_commit_timestamp=1721455691656110  transaction_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
#240720  6:08:11 server id 1  end_log_pos 471 CRC32 0xeadf8f66   Query  thread_id=9  exec_time=0  error_code=0  Xid = 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
#240720  6:09:01 server id 1  end_log_pos 550 CRC32 0x4104bf16   Anonymous_GTID  last_committed=1  sequence_number=2  rbr_only=no  original_committed_timestamp=1721455741084309  immediate_commit_timestamp=1721455741084309  transaction_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
#240720  6:09:01 server id 1  end_log_pos 853 CRC32 0xa4e21747   Query  thread_id=9  exec_time=0  error_code=0  Xid = 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
#240720  6:09:22 server id 1  end_log_pos 932 CRC32 0xd6cc2d85   Anonymous_GTID  last_committed=2  sequence_number=3  rbr_only=no  original_committed_timestamp=1721455762869115  immediate_commit_timestamp=1721455762869115  transaction_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
#240720  6:09:22 server id 1  end_log_pos 1122 CRC32 0x9665ccf6   Query  thread_id=9  exec_time=0  error_code=0  Xid = 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
#240720  6:09:22 server id 1  end_log_pos 1201 CRC32 0xd8c2e651   Anonymous_GTID  last_committed=3  sequence_number=4  rbr_only=no  original_committed_timestamp=1721455762877721  immediate_commit_timestamp=1721455762877721  transaction_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
#240720  6:09:22 server id 1  end_log_pos 1381 CRC32 0x156c6bda   Query  thread_id=9  exec_time=0  error_code=0  Xid = 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
#240720  6:10:22 server id 1  end_log_pos 1460 CRC32 0x08d41c89   Anonymous_GTID  last_committed=4  sequence_number=5  rbr_only=yes  original_committed_timestamp=1721455822097710  immediate_commit_timestamp=1721455822097710  transaction_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
#240720  6:10:22 server id 1  end_log_pos 1531 CRC32 0xec33d8e3   Query  thread_id=9  exec_time=0  error_code=0
SET TIMESTAMP=1721455822/*!*/;
BEGIN
/*!*/;
# at 1531
#240720  6:10:22 server id 1  end_log_pos 1596 CRC32 0xff0fa400   Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 1596
#240720  6:10:22 server id 1  end_log_pos 1648 CRC32 0xcf459f50   Write_rows: table id 102 flags: STMT_END_F

BINLOG '
zlSbZhMBAAAAQQAAADwGAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AACkD/8=
zlSbZh4BAAAANAAAAHAGAAAAAGYAAAAAAAEAAgAD/wABAAAABgDlvKDkuIkSAAAAUJ9Fzw==
'/*!*/;
### INSERT INTO `yeYingXuan`.`user`
### SET
###   @1=1
###   @2='张三'
###   @3=18
# at 1648
#240720  6:10:22 server id 1  end_log_pos 1679 CRC32 0x3443872d   Xid = 82
COMMIT/*!*/;
# at 1679
#240720  6:10:22 server id 1  end_log_pos 1758 CRC32 0x7896626b   Anonymous_GTID  last_committed=5  sequence_number=6  rbr_only=yes  original_committed_timestamp=1721455822100724  immediate_commit_timestamp=1721455822100724  transaction_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
#240720  6:10:22 server id 1  end_log_pos 1829 CRC32 0xb8962d85   Query  thread_id=9  exec_time=0  error_code=0
SET TIMESTAMP=1721455822/*!*/;
BEGIN
/*!*/;
# at 1829
#240720  6:10:22 server id 1  end_log_pos 1894 CRC32 0xda95d477   Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 1894
#240720  6:10:22 server id 1  end_log_pos 1946 CRC32 0x32b8c70c   Write_rows: table id 102 flags: STMT_END_F

BINLOG '
zlSbZhMBAAAAQQAAAGYHAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AHfUldo=
zlSbZh4BAAAANAAAAJoHAAAAAGYAAAAAAAEAAgAD/wACAAAABgDmnY7lm5sTAAAADMe4Mg==
'/*!*/;
### INSERT INTO `yeYingXuan`.`user`
### SET
###   @1=2
###   @2='李四'
###   @3=19
# at 1946
#240720  6:10:22 server id 1  end_log_pos 1977 CRC32 0x7cae582d   Xid = 84
COMMIT/*!*/;
# at 1977
#240720  6:10:22 server id 1  end_log_pos 2056 CRC32 0xff6c3b51   Anonymous_GTID  last_committed=6  sequence_number=7  rbr_only=yes  original_committed_timestamp=1721455822102188  immediate_commit_timestamp=1721455822102188  transaction_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
#240720  6:10:22 server id 1  end_log_pos 2127 CRC32 0x0872f115   Query  thread_id=9  exec_time=0  error_code=0
SET TIMESTAMP=1721455822/*!*/;
BEGIN
/*!*/;
# at 2127
#240720  6:10:22 server id 1  end_log_pos 2192 CRC32 0x6c869972   Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 2192
#240720  6:10:22 server id 1  end_log_pos 2244 CRC32 0x76a38f1f   Write_rows: table id 102 flags: STMT_END_F

BINLOG '
zlSbZhMBAAAAQQAAAJAIAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AHKZhmw=
zlSbZh4BAAAANAAAAMQIAAAAAGYAAAAAAAEAAgAD/wADAAAABgDnjovkupQUAAAAH4+jdg==
'/*!*/;
### INSERT INTO `yeYingXuan`.`user`
### SET
###   @1=3
###   @2='王五'
###   @3=20
# at 2244
#240720  6:10:22 server id 1  end_log_pos 2275 CRC32 0xecc79b63   Xid = 86
COMMIT/*!*/;
# at 2275
#240720  6:10:22 server id 1  end_log_pos 2354 CRC32 0x12a444bc   Anonymous_GTID  last_committed=7  sequence_number=8  rbr_only=yes  original_committed_timestamp=1721455822103661  immediate_commit_timestamp=1721455822103661  transaction_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
#240720  6:10:22 server id 1  end_log_pos 2425 CRC32 0x5d981940   Query  thread_id=9  exec_time=0  error_code=0
SET TIMESTAMP=1721455822/*!*/;
BEGIN
/*!*/;
# at 2425
#240720  6:10:22 server id 1  end_log_pos 2490 CRC32 0x939ba98a   Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 2490
#240720  6:10:22 server id 1  end_log_pos 2542 CRC32 0x34da618d   Write_rows: table id 102 flags: STMT_END_F

BINLOG '
zlSbZhMBAAAAQQAAALoJAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AIqpm5M=
zlSbZh4BAAAANAAAAO4JAAAAAGYAAAAAAAEAAgAD/wAEAAAABgDotbXlha0VAAAAjWHaNA==
'/*!*/;
### INSERT INTO `yeYingXuan`.`user`
### SET
###   @1=4
###   @2='赵六'
###   @3=21
# at 2542
#240720  6:10:22 server id 1  end_log_pos 2573 CRC32 0x5d240951   Xid = 88
COMMIT/*!*/;
# at 2573
#240720  6:10:59 server id 1  end_log_pos 2652 CRC32 0xeaa53dae   Anonymous_GTID  last_committed=8  sequence_number=9  rbr_only=yes  original_committed_timestamp=1721455859183393  immediate_commit_timestamp=1721455859183393  transaction_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
#240720  6:10:59 server id 1  end_log_pos 2732 CRC32 0x763aed44   Query  thread_id=9  exec_time=0  error_code=0
SET TIMESTAMP=1721455859/*!*/;
BEGIN
/*!*/;
# at 2732
#240720  6:10:59 server id 1  end_log_pos 2797 CRC32 0x983ab39f   Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 2797
#240720  6:10:59 server id 1  end_log_pos 2867 CRC32 0x858f3008   Update_rows: table id 102 flags: STMT_END_F

BINLOG '
81SbZhMBAAAAQQAAAO0KAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AJ+zOpg=
81SbZh8BAAAARgAAADMLAAAAAGYAAAAAAAEAAgAD//8ABAAAAAYA6LW15YWtFQAAAAAEAAAABgDo
tbXlha0cAAAACDCPhQ==
'/*!*/;
### UPDATE `yeYingXuan`.`user`
### WHERE
###   @1=4
###   @2='赵六'
###   @3=21
### SET
###   @1=4
###   @2='赵六'
###   @3=28
# at 2867
#240720  6:10:59 server id 1  end_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#

解释：

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
#240720  6:17:49 server id 1  end_log_pos 3048 CRC32 0xf40a16d8   Query  thread_id=12  exec_time=0  error_code=0
SET TIMESTAMP=1721456269/*!*/;
BEGIN
/*!*/;
# at 3048
#240720  6:17:49 server id 1  end_log_pos 3113 CRC32 0x170c9683   Table_map: `yeYingXuan`.`user` mapped to number 102
# has_generated_invisible_primary_key=0
# at 3113
#240720  6:17:49 server id 1  end_log_pos 3165 CRC32 0xb8e8f644   Delete_rows: table id 102 flags: STMT_END_F

BINLOG '
jVabZhMBAAAAQQAAACkMAAAAAGYAAAAAAAEACnllWWluZ1h1YW4ABHVzZXIAAwMPAwKQAQYBAQAC
A/z/AIOWDBc=
jVabZiABAAAANAAAAF0MAAAAAGYAAAAAAAEAAgAD/wADAAAABgDnjovkupQUAAAARPbouA==
'/*!*/;
### DELETE FROM `yeYingXuan`.`user`
### WHERE
###   @1=3
###   @2='王五'
###   @3=20
# at 3165
#240720  6:17:49 server id 1  end_log_pos 3196 CRC32 0x064e38f9   Xid = 139
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#

可以看到删除的数据id=3，name=王五，age=20，那么我们直接新增sql恢复即可。

INSERT INTO `user` (id,`name`,age) VALUES (3,'王五',20)

方法二和方法三要用的时间点和位置点/pos点，这里先给大家介绍下在哪看。

方法二（根据时间点区间恢复）：

我们模拟新增一条name=小龙女的数据，然后删除再恢复。

然后删除该条数据，查看binlog日志。

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

# at 5989
#240720  8:06:13 server id 1  end_log_pos 6052 CRC32 0xd6df0c13   Table_map: `yeYingXuan`.`user` mapped to number 178
# has_generated_invisible_primary_key=0
# at 6052
#240720  8:06:13 server id 1  end_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
#240720  8:06:13 server id 1  end_log_pos 6138 CRC32 0xb06e6629   Xid = 2486
COMMIT/*!*/;
# at 6138
#240720  8:08:04 server id 1  end_log_pos 6217 CRC32 0xabe69750   Anonymous_GTID  last_committed=20  sequence_number=21  rbr_only=yes  original_committed_timestamp=1721462884639790  immediate_commit_timestamp=1721462884639790  transaction_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
#240720  8:08:04 server id 1  end_log_pos 6298 CRC32 0x2f166d0e   Query  thread_id=55  exec_time=0  error_code=0
SET TIMESTAMP=1721462884/*!*/;
BEGIN
/*!*/;
# at 6298
#240720  8:08:04 server id 1  end_log_pos 6361 CRC32 0xa5403440   Table_map: `yeYingXuan`.`user` mapped to number 178
# has_generated_invisible_primary_key=0
# at 6361
#240720  8:08:04 server id 1  end_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
#240720  8:08:04 server id 1  end_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=要恢复到数据库。

查看数据已经成功恢复。

方法三：（按位置点恢复）

我们模拟新作一条name=杨过的数据，然后删除后恢复。

然后删除该条数据，查看binlog日志。

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

# at 7287
#240720  8:29:34 server id 1  end_log_pos 7366 CRC32 0xd9a43076   Anonymous_GTID  last_committed=24  sequence_number=25  rbr_only=yes  original_committed_timestamp=1721464174583630  immediate_commit_timestamp=1721464174583630  transaction_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
#240720  8:29:34 server id 1  end_log_pos 7437 CRC32 0x68009279   Query  thread_id=53  exec_time=0  error_code=0
SET TIMESTAMP=1721464174/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
BEGIN
/*!*/;
# at 7437
#240720  8:29:34 server id 1  end_log_pos 7500 CRC32 0xbfdc8e15   Table_map: `yeYingXuan`.`user` mapped to number 180
# has_generated_invisible_primary_key=0
# at 7500
#240720  8:29:34 server id 1  end_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
#240720  8:29:34 server id 1  end_log_pos 7583 CRC32 0x3d3aaba3   Xid = 2670
COMMIT/*!*/;
# at 7583
#240720  8:30:43 server id 1  end_log_pos 7662 CRC32 0x3f31e9c6   Anonymous_GTID  last_committed=25  sequence_number=26  rbr_only=yes  original_committed_timestamp=1721464243592594  immediate_commit_timestamp=1721464243592594  transaction_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
#240720  8:30:43 server id 1  end_log_pos 7733 CRC32 0x55267e82   Query  thread_id=53  exec_time=0  error_code=0
SET TIMESTAMP=1721464243/*!*/;
BEGIN
/*!*/;
# at 7733
#240720  8:30:43 server id 1  end_log_pos 7796 CRC32 0x28dab411   Table_map: `yeYingXuan`.`user` mapped to number 180
# has_generated_invisible_primary_key=0
# at 7796
#240720  8:30:43 server id 1  end_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
#240720  8:30:43 server id 1  end_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#

看数据已经成功恢复。

大家可以动手试试，真遇到紧急情况，以备不时之需。此外，下面留言功能已开启，如果大家有任何疑问、建议或想分享的经验，都欢迎在这里留言。

 

码农 夜樱轩
关注
  • 10
    点赞
  • 踩
  • 14
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql binlog日志恢复【亲测有效，有例子实测】
03-22
5. **查看binlog事件并恢复数据**：通过`show binlog events in 'mysql-bin.000002'`查看binlog文件中的事件，然后使用`mysqlbinlog`工具指定开始和结束位置恢复特定数据，例如： ``` mysqlbinlog --start-...
mysqlbinlog和binlog2sql的使用及mysql通过binlog日志恢复数据库数据
08-04
mysqlbinlog和binlog2sql的使用及mysql通过binlog日志恢复数据库数据
mysql如何利用binlog进行数据恢复详解
12-16
首先，当发生线上误操作时，如果数据库未开启其他备份策略，binlog就成为了恢复数据的唯一途径。在ROW模式下，binlog会记录每次数据修改的细节，确保可以精确还原每一行的变化。binlog2sql项目则可以帮助将binlog...
使用binlog日志恢复MySQL数据库删除数据的方法
01-11
当面临数据丢失或误删除的情况时，binlog日志成为恢复数据的关键。 首先，要确认binlog日志是否启用。这可以通过检查MySQL配置文件（在Windows系统中通常是my.ini，Linux系统中为my.cnf）中`log-bin`选项是否设置为...
解说mysql之binlog日志以及利用binlog日志恢复数据的方法
09-09
在本文中，我们将深入理解binlog的工作原理、重要性以及如何利用binlog来恢复数据。 首先，binlog是一种记录所有改变数据库状态的语句的日志，包括DDL（Data Definition Language）和DML（Data Manipulation ...
GO -操作mysql
wuxiaoyu0806的博客
07-24 359
fmt.Println("连接数据库mysql") //不能保证连接成功。//sql注入就是，传参的名字不合法如 “xxx”，不要自己拼接；fmt.Println("连接mysql失败")// 事务的相关方法 begin() commit。//批量执行同一条sql语句，考虑预处理提升性能。//做完错误检查之后，确保db不为nil。
Mysql的索引
2401_84592402的博客
07-24 697
索引是一个排序的列表，在列表当中存储索引的值以及索引对应数据所在的物理行索引值和数据是一个映射关系。
mysql高阶语句
qq_59169480的博客
07-24 678
高级语法的查询语句。
Mysql
qq_65186476的博客
07-25 927
Mysql
MySQL常见指令
weixin_47755728的博客
07-23 503
如数值类型包括整数类型和浮点数类型 整数类型根据占用的存储空间的不同 又包括TINYINT(1字节)，SMALLINT(2字节)，INT(4字节)，BIGINT(8字节)，可以存储不同范围的整数。浮点数类型包括FLOAT(4字节)和DOUBLE(8字节)，可以存储不同范围的浮点数。日期和时间类型包括DATE日期, TIME时间, DATETIME日期时间, TIMESTAMP时间戳。格式 : grant select on 数据库 .* to 用户名@登录主机 identified by ”密码"
MySQL：送分or送命 varchar(30) 与 int(10)
在那一刻，我们或许会发现自己站在了时间的另一岸，以更加成熟和宽广的胸怀，去拥抱那份隔代相传的深情。
07-24 1313
显示宽度并不限制字段中能够存储的值的范围或精度，而是指定了某些SQL客户端工具或应用程序在显示该字段的值时可能会使用的最小字符宽度。然而，这个特性并不是所有客户端或应用程序都会遵循的，它更多是一种建议或提示。用于存储最多30个字符的可变长度字符串。INT(10)用于存储整数值，括号中的10主要是一个显示宽度的提示，对存储的整数值没有实际影响。在设计数据库表时，应该根据实际需要选择合适的字段类型和长度，而不是过分关注显示宽度的概念。
MySQL深度分页问题深度解析与解决方案
weixin_42545951的博客
07-24 674
在处理包含数百万条记录的大型数据表时，使用MySQL的`LIMIT`进行分页查询是一种常见的做法。然而，当请求的数据位于结果集的深层时，即所谓的“深度分页”，查询性能会急剧下降。本文将深入探讨深度分页问题的原因，并提供几种有效的解决方案。
Mysql高阶语句
2401_84592402的博客
07-24 464
去重查询 : distinct。
mysql中where与on区别
m0_63227888的博客
07-24 566
使用ON子句来定义表之间的连接条件。使用WHERE子句来过滤查询结果，或者作为连接后结果的进一步筛选条件。在进行表连接时，应优先使用ON子句来指定连接条件，因为这样可以提高查询效率，避免不必要的全表扫描或笛卡尔积。WHERE子句在连接操作后使用，用于过滤连接后的结果集。
【Spring Framework】MySQL 数据库连接配置
u013675821的博客
07-26 999
XML 配置：适用于传统项目，使用 XML 文件配置数据源和 JdbcTemplate。Java 注解配置：更现代化的方法，使用 Java 配置类来定义数据源和 JdbcTemplate。Spring Boot 自动配置：最简便的方式，利用 Spring Boot 的自动配置功能，通过配置文件定义数据库连接。每种方式都有其适用的场景，选择合适的配置方式可以提高开发效率和代码可维护性。通常，现代应用程序会倾向于使用 Spring Boot 的自动配置以简化开发流程。
Mysql重大更新，三个版本遭下架！
xiaofan23z的专栏
07-24 463
个时就会触发这个重大bug，7/23日mysql发布重要更新，发布了如下三个版本。书接上文，7/11日开源数据库软件服务商percona发布重要警告，最新的。后面官方确认，确实存在重大bug，但是不是表超过10000个，而是超过。当mysql表超过10000张时重启可能会崩溃。
图数据库查询与操作指南-以Nebula图数据库为例
DZSpace，专注于计算机科学与技术领域的技术博主，致力于分享实用知识与技巧，帮助读者快速掌握技术要点，共同探索计算机世界的无限可能。欢迎访问DZSpace的博客，一起学习进步！
07-26 328
图数据库查询与操作指南-以Nebula图数据库为例
二进制部署k8s集群之master节点和etcd数据库集群（上）
zx52306的博客
07-30 686
【代码】二进制部署k8s集群之master节点和etcd数据库集群（上）
互联网医院系统源码与医保购药APP开发的完整技术指南
最新发布
meihusdk的博客
07-30 117
开发过程中需注重数据安全、系统性能、用户体验等多个方面，确保系统的高效稳定运行和良好用户体验。希望这篇技术指南能为开发者提供有益的参考，助力互联网医疗健康行业的发展。
mysql binlog恢复数据
05-17
MySQL的二进制日志（binlog）记录了对数据库的所有更改操作，包括增删改等操作。如果你误删了某些数据，可以使用binlog来进行恢复。 以下是使用binlog恢复数据的步骤： 1. 确认binlog是否开启，在MySQL配置文件中确认是否有以下配置： ``` [mysqld] log-bin=mysql-bin ``` 如果没有，则需要在配置文件中添加上述配置，并重新启动MySQL服务。 2. 查看binlog日志文件列表 可以使用以下命令查看binlog日志文件列表： ``` mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v --start-datetime="2022-01-01 00:00:00" --stop-datetime="2022-01-01 23:59:59" mysql-bin.000001 > /tmp/mysql_binlog.sql ``` 上述命令将把2022年1月1日这一天的binlog日志文件转换成文本格式，并保存到/tmp/mysql_binlog.sql文件中。 3. 找到误删的SQL语句 在/binlog.sql文件中查找误删的SQL语句，可以使用grep命令查找。 例如，查找包含“DELETE FROM users WHERE id=100”的SQL语句： ``` grep "DELETE FROM users WHERE id=100" /tmp/mysql_binlog.sql ``` 4. 恢复数据 找到误删的SQL语句后，可以使用mysql客户端连接到数据库，并执行该语句来恢复数据。 例如，执行以下命令来恢复数据： ``` mysql -u root -p mysql> use mydatabase; mysql> DELETE FROM users WHERE id=100; ``` 以上就是使用binlog恢复数据的步骤。需要注意的是，在执行恢复操作前，最好先备份一下数据库，以防止误操作导致数据丢失。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值