话说谁还没删错/改错过数据是吧,一旦删错了,我们真的要跑路吗?今天我们来玩一玩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#
看数据已经成功恢复。
大家可以动手试试,真遇到紧急情况,以备不时之需。此外,下面留言功能已开启,如果大家有任何疑问、建议或想分享的经验,都欢迎在这里留言。
版权归原作者 码农 夜樱轩 所有, 如有侵权,请联系我们删除。