一、日志信息应用实战-异常恢复¶
补充：数据库业务如何做监控？
- 对数据库进行监控，是否存在指定数据库(防止被删数据库)
- 对数据表进行监控，是否存在指定数据表(防止被删数据表)
- 对数据表的数据量进行监控，监控行数或表的大小是否有变化
1.1 DDL误删数据恢复¶
在实际生成环境中，可以利用binlog日志记录的信息截取，实现数据库异常情况下的数据信息恢复功能；
数据库异常恢复情况环境准备：
# 切换新的binlog日志文件做模拟数据恢复(多执行几次)
mysql> flush logs;
-- 确认已经刷新生成了新的binlog日志文件；
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| 3306-binlog.000009 | 156 | | | |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 进行基本的数据库SQL语句操作
mysql> create database bindb;
mysql> use bindb;
mysql> create table t1 (id int);
mysql> begin;
-- 在没有进行事务提交前，操作的事务事件信息，是不会出现在binlog事件日志中的
mysql> insert into t1 values(1);
-- 对于数据库的binlog日志，只会记录事务已经提交的DML语句信息，没有提交的DML语句是不会进行记录的；
-- 在日志中变化的DML语句信息是无法识别的，因为记录DML操作的语句默认是以ROW模式记录的；
mysql> commit;
数据库二进制日志信息查看方法：
-- 查看方式一
[root@db01-51 ~]# mysqlbinlog /data/3306/logs/3306-binlog.000009
# 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
#250309 19:04:46 server id 6 end_log_pos 125 CRC32 0x546a309c Start: binlog v 4, server v 8.0.26 created 250309 19:04:46
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
znXNZw8GAAAAeQAAAH0AAAABAAQAOC4wLjI2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBnDBqVA==
'/*!*/;
# at 125
#250309 19:04:46 server id 6 end_log_pos 156 CRC32 0x0ed49264 Previous-GTIDs
# [empty]
-- binlog日志文件156之前的内容是可以忽略的，表示是日志文件的头格式内容信息
# at 156
#250309 19:06:07 server id 6 end_log_pos 233 CRC32 0xf14234c0 Anonymous_GTIDlast_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1741518367107402 immediate_commit_timestamp=1741518367107402 transaction_length=188
# original_commit_timestamp=1741518367107402 (2025-03-09 19:06:07.107402 CST)
# immediate_commit_timestamp=1741518367107402 (2025-03-09 19:06:07.107402 CST)
/*!80001 SET @@session.original_commit_timestamp=1741518367107402*//*!*/;
/*!80014 SET @@session.original_server_version=80026*//*!*/;
/*!80014 SET @@session.immediate_server_version=80026*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
-- binlog日志文件已事件形式进行记录，主要关注两个at内容之间的信息，即表示的是一个事件信息；
# at 233
-- binlog日志中一个事件的开始，就表示上一个事件的结束，在binlog中记录的事件日志信息是连续的；
#250309 19:06:07 server id 6 end_log_pos 344 CRC32 0xb6f46587 Query thread_id=12 exec_time=0 error_code=0 Xid = 79
SET TIMESTAMP=1741518367/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
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*//*!*/;
create database bindb
/*!*/;
# at 344
#250309 19:07:07 server id 6 end_log_pos 421 CRC32 0x9806d162 Anonymous_GTIDlast_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1741518427652652 immediate_commit_timestamp=1741518427652652 transaction_length=191
# original_commit_timestamp=1741518427652652 (2025-03-09 19:07:07.652652 CST)
# immediate_commit_timestamp=1741518427652652 (2025-03-09 19:07:07.652652 CST)
/*!80001 SET @@session.original_commit_timestamp=1741518427652652*//*!*/;
/*!80014 SET @@session.original_server_version=80026*//*!*/;
/*!80014 SET @@session.immediate_server_version=80026*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 421
#250309 19:07:07 server id 6 end_log_pos 535 CRC32 0x054bbe56 Query thread_id=12 exec_time=0 error_code=0 Xid = 84
use `bindb`/*!*/;
SET TIMESTAMP=1741518427/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table t1 (id int)
/*!*/;
# at 535
#250309 19:07:32 server id 6 end_log_pos 614 CRC32 0xee5b9b9d Anonymous_GTIDlast_committed=2 sequence_number=3 rbr_only=yes original_committed_timestamp=1741518452128026 immediate_commit_timestamp=1741518452128026 transaction_length=275
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1741518452128026 (2025-03-09 19:07:32.128026 CST)
# immediate_commit_timestamp=1741518452128026 (2025-03-09 19:07:32.128026 CST)
/*!80001 SET @@session.original_commit_timestamp=1741518452128026*//*!*/;
/*!80014 SET @@session.original_server_version=80026*//*!*/;
/*!80014 SET @@session.immediate_server_version=80026*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 614
#250309 19:07:25 server id 6 end_log_pos 690 CRC32 0xc9be87c4 Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1741518445/*!*/;
BEGIN
/*!*/;
# at 690
#250309 19:07:25 server id 6 end_log_pos 739 CRC32 0x9272f930 Table_map: `bindb`.`t1` mapped to number 95
# at 739
#250309 19:07:25 server id 6 end_log_pos 779 CRC32 0x4c993855 Write_rows: table id 95 flags: STMT_END_F
BINLOG '
bXbNZxMGAAAAMQAAAOMCAAAAAF8AAAAAAAEABWJpbmRiAAJ0MQABAwABAQEAMPlykg==
bXbNZx4GAAAAKAAAAAsDAAAAAF8AAAAAAAEAAgAB/wABAAAAVTiZTA==
'/*!*/;
# at 779
#250309 19:07:32 server id 6 end_log_pos 810 CRC32 0xf221fe87 Xid = 86
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*/;
-- 查看方式二
mysql> show binlog events in '3306-binlog.000009';
+--------------------+-----+----------------+-----------+-------------+----------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+-----+----------------+-----------+-------------+----------------------------------------------------+
| 3306-binlog.000009 | 4 | Format_desc | 6 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| 3306-binlog.000009 | 125 | Previous_gtids | 6 | 156 | |
| 3306-binlog.000009 | 156 | Anonymous_Gtid | 6 | 233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 3306-binlog.000009 | 233 | Query | 6 | 344 | create database bindb /* xid=79 */ |
| 3306-binlog.000009 | 344 | Anonymous_Gtid | 6 | 421 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 3306-binlog.000009 | 421 | Query | 6 | 535 | use `bindb`; create table t1 (id int) /* xid=84 */ |
| 3306-binlog.000009 | 535 | Anonymous_Gtid | 6 | 614 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 3306-binlog.000009 | 614 | Query | 6 | 690 | BEGIN |
| 3306-binlog.000009 | 690 | Table_map | 6 | 739 | table_id: 95 (bindb.t1) |
| 3306-binlog.000009 | 739 | Write_rows | 6 | 779 | table_id: 95 flags: STMT_END_F |
| 3306-binlog.000009 | 779 | Xid | 6 | 810 | COMMIT /* xid=86 */ |
+--------------------+-----+----------------+-----------+-------------+----------------------------------------------------+
11 rows in set (0.00 sec)
binlog日志内容中主要关注的信息：
- 通过日志信息查看DDL操作语句信息（记录方式 SBR）
- 通过日志信息查看DML操作语句信息（记录方式 RBR）
以上ROW模式记录的信息是加密显示，无法直接查看的，可以使用下面命令参数进行获取详细信息：
-- 以下日志记录的信息，可以用命令实现，如下：
mysql > insert into t1 set id=1;
等价于
mysql > insert into t1 values(1);
-- 这里添加的参数信息，表示将DML的ROW格式语句信息，进行格式化处理输出；
[root@master ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/3306/logs/3306-binlog.000009
# at 965
#250309 19:18:31 server id 6 end_log_pos 1014 CRC32 0x4964c02d Table_map: `bindb`.`t1` mapped to number 95
# at 1014
#250309 19:18:31 server id 6 end_log_pos 1054 CRC32 0xd1355eee Write_rows: table id 95 flags: STMT_END_F
### INSERT INTO `bindb`.`t1`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
数据库模拟异常情况破坏操作：
-- 模拟破坏性操作，删除数据表
mysql> drop database bindb;
数据库异常情况数据恢复操作：
# 需要恢复建库开始，删除之前的所有操作（即所有binlog日志信息），实现日志信息的截取
-- 查看截取日志信息事件区域范围，起始position选择create字段前面的set字段对应的position号码，结束position选择drop字段前面的set字段对应的position号码
mysql> show binlog events in '3306-binlog.000009';
-- 起始position为233，结束position为1162，依据binlog日志的position号码，即可获取到想要恢复数据信息；
[root@oldboyxiaoq ~]# mysqlbinlog --start-position=233 --stop-position=1162 /data/3306/logs/3306-binlog.000009 >/tmp/bin.sql
# 根据截取的日志信息，进行数据库服务数据恢复
-- 建议在进行数据日志恢复数据时，将数据恢复时执行的SQL语句信息，不做binlog日志记录；
mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql
# 查看确认数据信息是否恢复
mysql> use bindb;
mysql> show tables;
mysql> select * from t1;
1.2 DML误删数据恢复¶
步骤一：模拟环境准备
-- 生成新的binlog日志信息
mysql> flush logs;
-- 验证，目前使用的binlog日志为3306-binlog.000010
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| 3306-binlog.000010 | 156 | | | |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
-- 创建测试数据库表
mysql> create database bindb;
mysql> use bindb;
mysql> create table t1 (id int,name char(5));
mysql> insert into t1 values (1,'a');
mysql> insert into t1 values (2,'b');
mysql> insert into t1 values (3,'c');
mysql> update t1 set name='d' where id=1;
步骤二：模拟DML语句误操作行为
mysql> delete from t1;
步骤三：获取修复的数据信息
-- DML语句中不会调用函数信息：以语句方式记录
-- DML语句信息截取可以通过解码文件信息截取
[root@db01-51 ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/3306/logs/3306-binlog.000010|grep -E "^[a-Z]|^# at|^###"|grep -v "^SET"
DELIMITER /*!*/;
# at 4
# at 125
# at 156
# at 233
drop database bindb
# at 340
# at 417
create database bindb
# at 528
# at 605
use `bindb`/*!*/;
create table t1 (id int,name char(5))
# at 732
-- 起始的位置点
# at 811
BEGIN
# at 887
# at 944
### INSERT INTO `bindb`.`t1`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='a' /* STRING(20) meta=65044 nullable=1 is_null=0 */
# at 986
COMMIT/*!*/;
# at 1017
# at 1096
BEGIN
# at 1172
# at 1229
### INSERT INTO `bindb`.`t1`
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='b' /* STRING(20) meta=65044 nullable=1 is_null=0 */
# at 1271
COMMIT/*!*/;
# at 1302
# at 1381
BEGIN
# at 1457
# at 1514
### INSERT INTO `bindb`.`t1`
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2='c' /* STRING(20) meta=65044 nullable=1 is_null=0 */
# at 1556
COMMIT/*!*/;
# at 1587
# at 1666
BEGIN
# at 1751
# at 1808
### UPDATE `bindb`.`t1`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='a' /* STRING(20) meta=65044 nullable=1 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='d' /* STRING(20) meta=65044 nullable=1 is_null=0 */
# at 1858
COMMIT/*!*/;
-- 结束的位置点
# at 1889
# at 1968
BEGIN
# at 2044
# at 2101
### DELETE FROM `bindb`.`t1`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='d' /* STRING(20) meta=65044 nullable=1 is_null=0 */
### DELETE FROM `bindb`.`t1`
### WHERE
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='b' /* STRING(20) meta=65044 nullable=1 is_null=0 */
### DELETE FROM `bindb`.`t1`
### WHERE
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2='c' /* STRING(20) meta=65044 nullable=1 is_null=0 */
# at 2157
COMMIT/*!*/;
DELIMITER ;
-- 根据起始位置点生成恢复表数据的sql语句
[root@db01-51 ~]# mysqlbinlog --start-position=811 --stop-position=1889 /data/3306/logs/3306-binlog.000010 >/tmp/bindb02.sql
步骤四：恢复误操作的数据信息
mysql> set sql_log_bin=0;
mysql> source /tmp/bindb02.sql
-- 结果验证
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | d |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
1.3 数据库数据异常恢复（痛点情况）¶
情况一：日志文件被清理过，可能建库语句所在日志已经丢失；（在后面课程章节处理）
项目背景：一个数据库三年前就创建了，但是日志信息只记录一个月，这个库被误删除了；
解决方案：
A计划：最近一次全备+全备之后，误删除之前所有binlog，进行一同恢复；（全备数据+增量数据）
B计划：利用延时从库，进行数据恢复；
- 步骤一：修复从库数据信息
- 步骤二：切换从库为主库，前端业务访问从库
- 步骤三：重新建立主从关系 ，备份数据信息 -- 恢复到原主库 -- 原主库同步新主库数据 -- 直到数据一致
- 步骤四：重新切换主从关系
情况二：所需日志跨越多个文件，如何进行日志信息的截取；
解决方案：
A计划：只有position号的方式，可以进行分段截取，进行分段恢复数据；
B计划：根据Datatime时间信息方式，可能会出现准确性不高的情况（因为每一秒可能有多个事件产生）；
C计划：启用GTID（全局事务ID）方式，无论跨越多少个日志文件，每个事务操作的事件ID信息都是唯一且递增的（5.6+引入）；
实践操作：
C计划：基于GTID方式对binlog进行管理（利用GTID实现日志截取）
数据库异常恢复情况环境准备：
-- 配置文件信息修改完毕后，重启数据库服务使配置生效
[root@master ~]# vim /data/3306/data/my.cnf
...
...
[mysqld]
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=on
-- 重启数据库后进行验证
mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON |
+-------------+
1 row in set (0.00 sec)
mysql> select @@enforce_gtid_consistency;
+----------------------------+
| @@enforce_gtid_consistency |
+----------------------------+
| ON |
+----------------------------+
1 row in set (0.00 sec)
# 刷新新的binlog日志进行操作
-- 生成新的binlog日志信息
mysql> flush logs;
mysql> create database test5;
-- 验证，目前使用的binlog日志为3306-binlog.000022
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------------------------------+
| 3306-binlog.000022 | 384 | | | 3fb53cbb-eb7c-11ef-828e-0050562b04b9:1-10 |
+--------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
-- 创建表
mysql> flush logs;
mysql> use test5;
mysql> create table t1(id int);
-- 验证，目前使用的binlog日志为3306-binlog.000023
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------------------------------+
| 3306-binlog.000023 | 386 | | | 3fb53cbb-eb7c-11ef-828e-0050562b04b9:1-11 |
+--------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
-- 插入第一条表数据
mysql> flush logs;
mysql> begin;
mysql> insert into t1 values(1);
mysql> commit;
-- 验证，目前使用的binlog日志为3306-binlog.000024
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------------------------------+
| 3306-binlog.000024 | 471 | | | 3fb53cbb-eb7c-11ef-828e-0050562b04b9:1-12 |
+--------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
-- 插入第二条表数据
mysql> begin;
mysql> insert into t1 values(2);
mysql> commit;
-- 验证，目前使用的binlog日志为3306-binlog.000024
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------------------------------+
| 3306-binlog.000024 | 746 | | | 3fb53cbb-eb7c-11ef-828e-0050562b04b9:1-13 |
+--------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
-- 插入第三条表数据
mysql> begin;
mysql> insert into t1 values(3);
mysql> commit;
-- 验证，目前使用的binlog日志为3306-binlog.000024
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------------------------------+
| 3306-binlog.000024 | 1021 | | | 3fb53cbb-eb7c-11ef-828e-0050562b04b9:1-14 |
+--------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.01 sec)
# 进行binlog事件信息查看
-- 可以获取以上的数据操作事件信息
mysql> show binlog events in '3306-binlog.000024';
+--------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| 3306-binlog.000024 | 4 | Format_desc | 6 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| 3306-binlog.000024 | 125 | Previous_gtids | 6 | 196 | 3fb53cbb-eb7c-11ef-828e-0050562b04b9:1-11 |
| 3306-binlog.000024 | 196 | Gtid | 6 | 275 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:12' |
| 3306-binlog.000024 | 275 | Query | 6 | 351 | BEGIN |
| 3306-binlog.000024 | 351 | Table_map | 6 | 400 | table_id: 89 (test5.t1) |
| 3306-binlog.000024 | 400 | Write_rows | 6 | 440 | table_id: 89 flags: STMT_END_F |
| 3306-binlog.000024 | 440 | Xid | 6 | 471 | COMMIT /* xid=26 */ |
| 3306-binlog.000024 | 471 | Gtid | 6 | 550 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:13' |
| 3306-binlog.000024 | 550 | Query | 6 | 626 | BEGIN |
| 3306-binlog.000024 | 626 | Table_map | 6 | 675 | table_id: 89 (test5.t1) |
| 3306-binlog.000024 | 675 | Write_rows | 6 | 715 | table_id: 89 flags: STMT_END_F |
| 3306-binlog.000024 | 715 | Xid | 6 | 746 | COMMIT /* xid=30 */ |
| 3306-binlog.000024 | 746 | Gtid | 6 | 825 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:14' |
| 3306-binlog.000024 | 825 | Query | 6 | 901 | BEGIN |
| 3306-binlog.000024 | 901 | Table_map | 6 | 950 | table_id: 89 (test5.t1) |
| 3306-binlog.000024 | 950 | Write_rows | 6 | 990 | table_id: 89 flags: STMT_END_F |
| 3306-binlog.000024 | 990 | Xid | 6 | 1021 | COMMIT /* xid=34 */ |
+--------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
17 rows in set (0.00 sec)
数据库模拟异常情况破坏操作：
-- 模拟破坏性操作，删除数据库
mysql> drop database test5;
数据库异常情况数据恢复操作：
# 根据日志信息查看相关的事件情况（获取GTID编号范围）,这里获取到的GTID编号范围为3fb53cbb-eb7c-11ef-828e-0050562b04b9:10-14
mysql> show binlog events in '3306-binlog.000024';
+--------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| 3306-binlog.000024 | 4 | Format_desc | 6 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| 3306-binlog.000024 | 125 | Previous_gtids | 6 | 196 | 3fb53cbb-eb7c-11ef-828e-0050562b04b9:1-11 |
| 3306-binlog.000024 | 196 | Gtid | 6 | 275 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:12' |
| 3306-binlog.000024 | 275 | Query | 6 | 351 | BEGIN |
| 3306-binlog.000024 | 351 | Table_map | 6 | 400 | table_id: 89 (test5.t1) |
| 3306-binlog.000024 | 400 | Write_rows | 6 | 440 | table_id: 89 flags: STMT_END_F |
| 3306-binlog.000024 | 440 | Xid | 6 | 471 | COMMIT /* xid=26 */ |
| 3306-binlog.000024 | 471 | Gtid | 6 | 550 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:13' |
| 3306-binlog.000024 | 550 | Query | 6 | 626 | BEGIN |
| 3306-binlog.000024 | 626 | Table_map | 6 | 675 | table_id: 89 (test5.t1) |
| 3306-binlog.000024 | 675 | Write_rows | 6 | 715 | table_id: 89 flags: STMT_END_F |
| 3306-binlog.000024 | 715 | Xid | 6 | 746 | COMMIT /* xid=30 */ |
| 3306-binlog.000024 | 746 | Gtid | 6 | 825 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:14' |
| 3306-binlog.000024 | 825 | Query | 6 | 901 | BEGIN |
| 3306-binlog.000024 | 901 | Table_map | 6 | 950 | table_id: 89 (test5.t1) |
| 3306-binlog.000024 | 950 | Write_rows | 6 | 990 | table_id: 89 flags: STMT_END_F |
| 3306-binlog.000024 | 990 | Xid | 6 | 1021 | COMMIT /* xid=34 */ |
| 3306-binlog.000024 | 1021 | Gtid | 6 | 1098 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:15' |
| 3306-binlog.000024 | 1098 | Query | 6 | 1205 | drop database test5 /* xid=38 */ |
+--------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
19 rows in set (0.00 sec)
# 需要恢复建库开始，删除之前的所有操作（即所有binlog日志信息），实现日志信息的截取
[root@db01-51 ~]# cd /data/3306/logs/
[root@db01-51 ~]# mysqlbinlog --include-gtids='3fb53cbb-eb7c-11ef-828e-0050562b04b9:10-14' 3306-binlog.000022 3306-binlog.000023 3306-binlog.000024 >/tmp/gtid.sql
-- 依据binlog日志的GTID信息，即可获取到想要恢复数据信息；
# 根据截取的日志信息，进行数据库服务数据恢复
-- 建议在进行数据日志恢复数据时，将数据恢复时执行的SQL语句信息，不做binlog日志记录；恢复后别忘在改为1；
mysql> set sql_log_bin=0;
-- 默认此时报错恢复失败，因为GTID截取的日志恢复数据时，具有幂等性，由于binlog中已经记录了10-14的GTID事件信息
mysql> source /tmp/gtid.sql
-- 通过查看确认，核实清楚binlog中已经记录了10-14的GTID事件信息
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------------------------------+
| 3306-binlog.000024 | 1205 | | | 3fb53cbb-eb7c-11ef-828e-0050562b04b9:1-15 |
+--------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
# 利用GTID日志信息恢复报错处理方式一：将系统中日志中的GTID信息清除掉（不建议）
# 利用GTID日志信息恢复报错处理方式二：删除与幂等性冲突的记录信息
-- 表示跳过gtid的检查过程，即截取的日志中不再含有GTID的配置语句信息，自然解决了幂等性冲突问题；
-- 开启了GTID之后，依然可以使用pos方式进行日志信息截取与恢复；
[root@db01-51 ~]# mysqlbinlog --skip-gtids --include-gtids='3fb53cbb-eb7c-11ef-828e-0050562b04b9:10-14' 3306-binlog.000022 3306-binlog.000023 3306-binlog.000024 >/tmp/gtid02.sql
-- 再次进行尝试
mysql> set sql_log_bin=0;
mysql> source /tmp/gtid02.sql
# 查看确认数据信息是否恢复
-- 查看test5数据库中的t1表的数据信息是否恢复
mysql> use test5;
mysql> show tables;
mysql> select * from t1;
# 操作扩展：可以实现排除指定gtid信息不做日志记录截取
[root@oldboyxiaoq ~]# mysqlbinlog --exclude-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:4' --include-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:3-7' /data/3306/data/binlog.000004
# 操作扩展：跨多日志文件信息截取
[root@oldboyxiaoq ~]# mysqlbinlog --skip-gtids --include-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:1-10' /data/3306/data/binlog.000001 /data/3306/data/binlog.000002 /data/3306/data/binlog.000003 >/tmp/gtid.sql
- GTID概念介绍：
GTID（global transation id）称为全局事务（事件）ID，标识binlog日志记录的唯一性；
GTID信息的表示方式：
| 表现形式 | 关键列 | 解释说明 |
|---|---|---|
| server_uuid:N | server_uuid | 表示数据库初始化启动之后，自动生成的随机数信息（唯一的） |
| N | 表示第几个相关的事务或事件信息，会不断进行自增 |
server_uuid信息查看：
-- 表示数据库每次初始化之后自动生成，不建议手工进行修改；
mysql> select @@server_uuid;
+--------------------------------------+
| @@server_uuid |
+--------------------------------------+
| 3fb53cbb-eb7c-11ef-828e-0050562b04b9 |
+--------------------------------------+
1 row in set (0.00 sec)
-- 在数据库的数据目录文件中也可以查询到
[root@db01-51 ~]# cat /data/3306/data/auto.cnf
[auto]
server-uuid=3fb53cbb-eb7c-11ef-828e-0050562b04b9
- GTID功能作用：
利用GTID方式管理binlog，实质上就是对于数据库的每个事务产生事件信息打上唯一标识信息（id号）；
利用GTID方式管理binlog，主要目的是处理数据库主从问题，解决主从数据库的数据一致性问题；
简单描述：标识事务的唯一性，保证日志恢复时的一致性，并且具备”幂等性”；
- GTID功能配置：
GTID功能相关参数介绍：
# GTID功能参数信息介绍（3个重要的配置参数）
-- 设置是否开启显示gtid信息功能（在5.7之后是有个匿名的gtid，是数据库系统自己维护的）
mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| OFF |
+-------------+
1 row in set (0.00 sec)
-- 设置是否开启GTID强制一致性功能，开启gtid必须开启此功能
mysql> select @@enforce_gtid_consistency;
+----------------------------+
| @@enforce_gtid_consistency |
+----------------------------+
| OFF |
+----------------------------+
1 row in set (0.00 sec)
-- 对某些 SQL 会有限制，例如 CREATE TABLE … SELECT 必须得分成两条语句执行。
-- OFF： 表示事务允许违反 GTID 一致性。
-- ON： 表示事务不允许违反 GTID 一致性，有相关 SQL 会直接返回异常。
-- WARN：表示事务允许违反 GTID 一致性，但会将警告信息记录到 ERROR LOG。
-- 和配置主从有关（在8.0.26开始 推荐配置log_replica_updates替代log_slave_updates参数）
-- 此参数表示从服务器从主服务器接收的更新信息，是否也会记录在从服务器本地的二进制文件中
mysql> select @@log_slave_updates;
+---------------------+
| @@log_slave_updates |
+---------------------+
| 1 |
+---------------------+
1 row in set, 1 warning (0.00 sec)
GTID功能相关参数激活：
[root@master ~]# vim /etc/my.cnf
-- 配置文件信息修改完毕后，重启数据库服务使配置生效
...
...
[mysqld]
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=on
-- 重启数据库后进行验证
mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON |
+-------------+
1 row in set (0.00 sec)
mysql> select @@enforce_gtid_consistency;
+----------------------------+
| @@enforce_gtid_consistency |
+----------------------------+
| ON |
+----------------------------+
1 row in set (0.00 sec)
- GTID信息查看：
-- 在GTID功能被激活后，就会在Executed_Gtid_Set列中显示GTID集合信息；如果刚刚重启会新起一个binlog日志
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| 3306-binlog.000011 | 156 | | | |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
-- 模拟创建数据库，产生新的事件信息
mysql> create database test3;
-- 再次进行查看，GTID信息随着新的事件产生，随之发生变化
mysql> show master status;
+--------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+----------------------------------------+
| 3306-binlog.000011 | 344 | | | 3fb53cbb-eb7c-11ef-828e-0050562b04b9:1 |
+--------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
-- 模拟创建数据库，产生新的事件信息
mysql> create database test4;
Query OK, 1 row affected (0.03 sec)
-- 再次进行查看，GTID信息随着新的事件产生，随之发生变化
mysql> show master status;
+--------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+------------------------------------------+
| 3306-binlog.000011 | 532 | | | 3fb53cbb-eb7c-11ef-828e-0050562b04b9:1-2 |
+--------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
-- 在每个数据库操作事件之前，会显示GTID的唯一标识信息
mysql> show binlog events in '3306-binlog.000011';
+--------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| 3306-binlog.000011 | 4 | Format_desc | 6 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| 3306-binlog.000011 | 125 | Previous_gtids | 6 | 156 | |
| 3306-binlog.000011 | 156 | Gtid | 6 | 233 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:1' |
| 3306-binlog.000011 | 233 | Query | 6 | 344 | create database test3 /* xid=7 */ |
| 3306-binlog.000011 | 344 | Gtid | 6 | 421 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:2' |
| 3306-binlog.000011 | 421 | Query | 6 | 532 | create database test4 /* xid=9 */ |
+--------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
6 rows in set (0.00 sec)
============================================================================================
情况三：如何从日志文件中恢复单库、单表、或者部分行数据信息；
解决方案：
A计划：可以利用命令单独截取某个数据库的日志信息；mysqlbinlog -d world xxx > xxxx
B计划：可以借助第三方工具实现单表或部分数据恢复；binlog2sql（python） 过滤指定表数据或过滤指定表的部分数据；
实战操作：
A计划：单库日志信息截取，企业实战过程：
数据库异常恢复情况环境准备：
# 查看获取当前binlog日志状态信息
mysql> flush logs;
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------------------------------+
| 3306-binlog.000025 | 196 | | | 3fb53cbb-eb7c-11ef-828e-0050562b04b9:1-15 |
+--------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.01 sec)
# 进行基本的数据库SQL语句操作：
-- 通过操作不同的数据库，以及不同的数据表，实现binlog日志事件信息的交叉
-- 创建了一个test1数据库，并在数据库中创建了一个表，在表中插入了一些数据信息
mysql> create database test1;
mysql> use test1;
mysql> create table t1 (id int);
mysql> begin;
mysql> insert into t1 values(1);
mysql> insert into t1 values(2);
mysql> commit;
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
-- 创建了一个test2数据库，并在数据库中创建了一个表，在表中插入了一些数据信息
mysql> create database test2;
mysql> use test2;
mysql> create table t2 (id int);
mysql> insert into t2 values(1);
mysql> insert into t2 values(2);
mysql> commit;
-- 在test1数据库中插入两条数据
mysql> use test1;
mysql> begin;
mysql> insert into t1 values(3);
mysql> insert into t1 values(4);
-- 在test2数据库中插入两条数据
mysql> use test2;
mysql> insert into t2 values(3);
mysql> insert into t2 values(4);
mysql> commit;
-- 结果验证
mysql> select * from test1.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
mysql> select * from test2.t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
数据库模拟异常情况破坏操作：
-- 模拟破坏性操作，删除数据库
mysql> drop database test1;
数据库异常情况数据恢复操作：
# 根据日志信息查看相关的事件情况，获取GTID取值范围为16-22
mysql> show binlog events in '3306-binlog.000025';
+--------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| 3306-binlog.000025 | 4 | Format_desc | 6 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| 3306-binlog.000025 | 125 | Previous_gtids | 6 | 196 | 3fb53cbb-eb7c-11ef-828e-0050562b04b9:1-15 |
| 3306-binlog.000025 | 196 | Gtid | 6 | 273 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:16' |
| 3306-binlog.000025 | 273 | Query | 6 | 384 | create database test1 /* xid=163 */ |
| 3306-binlog.000025 | 384 | Gtid | 6 | 461 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:17' |
| 3306-binlog.000025 | 461 | Query | 6 | 575 | use `test1`; create table t1 (id int) /* xid=168 */ |
| 3306-binlog.000025 | 575 | Gtid | 6 | 654 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:18' |
| 3306-binlog.000025 | 654 | Query | 6 | 730 | BEGIN |
| 3306-binlog.000025 | 730 | Table_map | 6 | 779 | table_id: 96 (test1.t1) |
| 3306-binlog.000025 | 779 | Write_rows | 6 | 819 | table_id: 96 flags: STMT_END_F |
| 3306-binlog.000025 | 819 | Table_map | 6 | 868 | table_id: 96 (test1.t1) |
| 3306-binlog.000025 | 868 | Write_rows | 6 | 908 | table_id: 96 flags: STMT_END_F |
| 3306-binlog.000025 | 908 | Xid | 6 | 939 | COMMIT /* xid=170 */ |
| 3306-binlog.000025 | 939 | Gtid | 6 | 1016 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:19' |
| 3306-binlog.000025 | 1016 | Query | 6 | 1127 | create database test2 /* xid=174 */ |
| 3306-binlog.000025 | 1127 | Gtid | 6 | 1204 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:20' |
| 3306-binlog.000025 | 1204 | Query | 6 | 1318 | use `test2`; create table t2 (id int) /* xid=179 */ |
| 3306-binlog.000025 | 1318 | Gtid | 6 | 1397 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:21' |
| 3306-binlog.000025 | 1397 | Query | 6 | 1473 | BEGIN |
| 3306-binlog.000025 | 1473 | Table_map | 6 | 1522 | table_id: 97 (test2.t2) |
| 3306-binlog.000025 | 1522 | Write_rows | 6 | 1562 | table_id: 97 flags: STMT_END_F |
| 3306-binlog.000025 | 1562 | Table_map | 6 | 1611 | table_id: 97 (test2.t2) |
| 3306-binlog.000025 | 1611 | Write_rows | 6 | 1651 | table_id: 97 flags: STMT_END_F |
| 3306-binlog.000025 | 1651 | Xid | 6 | 1682 | COMMIT /* xid=180 */ |
| 3306-binlog.000025 | 1682 | Gtid | 6 | 1761 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:22' |
| 3306-binlog.000025 | 1761 | Query | 6 | 1837 | BEGIN |
| 3306-binlog.000025 | 1837 | Table_map | 6 | 1886 | table_id: 96 (test1.t1) |
| 3306-binlog.000025 | 1886 | Write_rows | 6 | 1926 | table_id: 96 flags: STMT_END_F |
| 3306-binlog.000025 | 1926 | Table_map | 6 | 1975 | table_id: 96 (test1.t1) |
| 3306-binlog.000025 | 1975 | Write_rows | 6 | 2015 | table_id: 96 flags: STMT_END_F |
| 3306-binlog.000025 | 2015 | Table_map | 6 | 2064 | table_id: 97 (test2.t2) |
| 3306-binlog.000025 | 2064 | Write_rows | 6 | 2104 | table_id: 97 flags: STMT_END_F |
| 3306-binlog.000025 | 2104 | Table_map | 6 | 2153 | table_id: 97 (test2.t2) |
| 3306-binlog.000025 | 2153 | Write_rows | 6 | 2193 | table_id: 97 flags: STMT_END_F |
| 3306-binlog.000025 | 2193 | Xid | 6 | 2224 | COMMIT /* xid=189 */ |
| 3306-binlog.000025 | 2224 | Gtid | 6 | 2301 | SET @@SESSION.GTID_NEXT= '3fb53cbb-eb7c-11ef-828e-0050562b04b9:23' |
| 3306-binlog.000025 | 2301 | Query | 6 | 2408 | drop database test1 /* xid=201 */ |
+--------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
37 rows in set (0.00 sec)
# 需要恢复建库开始，删除之前的所有操作（即所有binlog日志信息），实现日志信息的截取
-- 依据binlog日志的position号码，即可获取到想要恢复数据信息，并利用-d参数导出指定数据库相关数据；
[root@db01-51 ~]# cd /data/3306/logs/
[root@db01-51 logs]# mysqlbinlog --skip-gtids --include-gtids='3fb53cbb-eb7c-11ef-828e-0050562b04b9:16-22' -d test1 3306-binlog.000025 >/tmp/test01.sql
# 根据截取的日志信息，进行数据库服务数据恢复
mysql> set sql_log_bin=0;
-- 建议在进行数据日志恢复数据时，将数据恢复时执行的SQL语句信息，不做binlog日志记录；恢复后别忘在改为1；
mysql> source /tmp/test01.sql
# 查看确认数据信息是否恢复
-- 查看test1数据库中的t1表的数据信息是否恢复，观察到数据已恢复
mysql> use test1;
mysql> show tables;
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
-- 查看test2数据库中的t2表的数据信息是否破坏,观察到t2表的数据信息没有收到破坏
mysql> use test2;
mysql> show tables;
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
B计划：可以借助第三方工具实现单表或部分数据恢复；
官方链接：https://github.com/danfengcao/binlog2sql
利用binlog2sql工具可以处理上面的企业需求，此软件是利用python语言开发的，主要用来处理binlog日志信息；
从软件应用方面来说主要包含两个核心功能：
- 可以友好的展示或者管理二进制日志信息（binlog），进而可以过滤出单独表的信息，甚至表中指定行的信息；
- 可以快速的实现DML操作语句的闪回功能，即实现通过日志信息翻转方式，进行数据信息的恢复；
说明：binlog2sql工具是模拟了一个从库，进行日志信息分析，需要保证数据库服务启动状态，且不支持离线方式分析日志内容；
数据库异常恢复情况环境准备：
# 下载第三方日志分析工具
-- 此工具在mariadb中可以通过打补丁方式，进行部署安装；但是在mysql 8.0中暂时还没有集成，需要单独安装
[root@master ~]# cd /opt/
-- 上传软件包到/opt目录下面
[root@master opt]# unzip binlog2sql-master.zip
[root@master opt]# cd binlog2sql-master/
# 部署第三方工具运行环境
[root@master binlog2sql-master]# yum install -y python3
[root@master binlog2sql-master]# pip3 install -r requirements.txt
[root@master binlog2sql-master]# pip3 show pymysql
[root@master binlog2sql-master]# pip3 install --upgrade pymysql （此步骤可以忽略）
-- 以上pip3下载软件缓慢，可以优化pip3下载源
-- 下载源优化方法：https://developer.aliyun.com/mirror/pypi?spm=a2c6h.13651102.0.0.3e221b11H9Q7La
# 在指定数据库中创建多个数据表
mysql> use test1;
mysql> create table t11 (id int);
mysql> insert into t11 values (1),(2);
mysql> commit;
数据库日志信息工具分析查看：（解析日志事件SQL）
-- 表的数据信息导出后，可以直接复制命令信息恢复，或者导出sql文件进行导入恢复；
[root@master binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t11 --start-file='3306-binlog.000025'
INSERT INTO `test1`.`t11`(`id`) VALUES (1); #start 2679 end 2850 time 2025-03-10 10:16:34 gtid
INSERT INTO `test1`.`t11`(`id`) VALUES (2); #start 2679 end 2850 time 2025-03-10 10:16:34 gtid
数据库模拟异常情况破坏操作：
# 在指定数据库的相应数据表中做修改操作
mysql> use test1;
mysql> update t1 set id=10 where id=1;
mysql> commit;
# 在指定数据库的相应数据表中做删除操作
mysql> use test1;
mysql> delete from t1 where id=3;
mysql> commit;
# 删除数据结果查看
mysql> select *from test1.t1;
+------+
| id |
+------+
| 10 |
| 2 |
| 4 |
+------+
3 rows in set (0.00 sec)
数据库日志信息工具分析查看：（解析日志事件SQL）
[root@master binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --start-file='3306-binlog.000025'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 654 end 819 time 2025-03-10 09:18:21 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 654 end 908 time 2025-03-10 09:18:30 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 1761 end 1926 time 2025-03-10 09:21:32 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 1761 end 2015 time 2025-03-10 09:21:38 gtid
UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 2960 end 3140 time 2025-03-10 10:19:44 gtid
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 3250 end 3415 time 2025-03-10 10:20:03 gtid
# 只想查看删除操作信息
-- sql-type参数只能过滤DML类型语句信息，一般常见过滤的是 insert update delete
[root@master binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=delete --start-file='3306-binlog.000025'
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 3250 end 3415 time 2025-03-10 10:20:03 gtid
# 只想查看修改操作信息
[root@master binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=update --start-file='3306-binlog.000025'
UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 2960 end 3140 time 2025-03-10 10:19:44 gtid
# 只想查看插入操作信息
[root@master binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=insert --start-file='3306-binlog.000025'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 654 end 819 time 2025-03-10 09:18:21 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 654 end 908 time 2025-03-10 09:18:30 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 1761 end 1926 time 2025-03-10 09:21:32 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 1761 end 2015 time 2025-03-10 09:21:38 gtid
数据库日志信息工具回滚操作：（生成指定事件回滚语句-闪回操作）
假设在某个企业的应用场景中，有3000万行数据，占用200G的存储空间，其中误删除了10行数据信息，请问如何进行恢复数据？
# 误删除操作语句反转操作
-- 获取删除操作语句信息
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=delete --start-file='3306-binlog.000025'
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 3250 end 3415 time 2025-03-10 10:20:03 gtid
-- 在获取删除操作语句命令后加 -B 参数，正好获得了反转语句的操作信息
[root@oldboyxiaoq binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=delete --start-file='3306-binlog.000025' -B
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 3250 end 3415 time 2025-03-10 10:20:03 gtid
# 误修改操作语句反转操作
-- 获取修改操作语句信息
[root@master binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=update --start-file='3306-binlog.000025'
UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 2960 end 3140 time 2025-03-10 10:19:44 gtid
-- 在获取修改操作语句命令后加 -B 参数，正好获得了反转语句的操作信息
[root@master binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=update --start-file='3306-binlog.000025' -B
UPDATE `test1`.`t1` SET `id`=1 WHERE `id`=10 LIMIT 1; #start 2960 end 3140 time 2025-03-10 10:19:44 gtid
# 误插入操作语句反转操作
-- 获取插入操作语句信息
[root@master binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=insert --start-file='3306-binlog.000025'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 654 end 819 time 2025-03-10 09:18:21 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 654 end 908 time 2025-03-10 09:18:30 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 1761 end 1926 time 2025-03-10 09:21:32 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 1761 end 2015 time 2025-03-10 09:21:38 gtid
-- 在获取插入操作语句命令后加 -B 参数，正好获得了反转语句的操作信息
[root@master binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.51 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=insert --start-file='3306-binlog.000025' -B
DELETE FROM `test1`.`t1` WHERE `id`=4 LIMIT 1; #start 1761 end 2015 time 2025-03-10 09:21:38 gtid
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 1761 end 1926 time 2025-03-10 09:21:32 gtid
DELETE FROM `test1`.`t1` WHERE `id`=2 LIMIT 1; #start 654 end 908 time 2025-03-10 09:18:30 gtid
DELETE FROM `test1`.`t1` WHERE `id`=1 LIMIT 1; #start 654 end 819 time 2025-03-10 09:18:21 gtid
二、日志信息滚动切割¶
在应用binlog日志过程中，经常需要对日志文件进行日志切割（滚动更新），可以有效避免日志文件数据量过大问题；
在某些场景中，如果需要对binlog日志文件进行备份操作时，也可以对原有使用的binlog日志文件进行滚动更新；
常用的日志滚动更新方法：
# 方法一：
-- 滚动更新前的日志文件就会处于静止状态，不会在进行数据信息的更新
mysql> flush logs;
# 方式二：
[root@master ~ ]# mysql -uroot -p123456 flush-logs
# 方式三：
mysql> restart;
-- mysql 8.0之后支持的数据库中重启服务；之前的版本只支持shutdown关闭数据库；
[root@master ~ ]# /etc/init.d/mysqld restart
# 方式四：
-- 配置binlog日志最大数据存储量，默认大小为1G，到达最大日志存储量也会进行自动切割；
mysql> select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
| 1073741824 |
+-------------------+
1 row in set (0.00 sec)
三、日志信息清理方法¶
在系统中日志信息，随着时间的推移将会越来越多，将严重占用磁盘空间，因此需要对日志做相应清理工作；
对于日志信息常用的清理方式有两种：
方式一：进行日志信息自动清理
mysql> show variables like '%expire%';
+--------------------------------+---------+
| Variable_name | Value |
+--------------------------------+---------+
| binlog_expire_logs_seconds | 2592000 | -- 距离当前时间多少秒进行清理binlog
| disconnect_on_expired_password | ON |
| expire_logs_days | 0 | -- 距离当前时间多少天进行清理binlog
+--------------------------------+---------+
3 rows in set (0.00 sec)
-- 在最新数据库8.0中，可以以秒为单位进行日志信息清理，默认是30天进行日志清理，或者也可以以天为单位进行清理；
-- 在最先数据库8.0前，主要是以天为单位进行清理，但默认清理功能并未激活；
-- 在企业实战环境中，建议过期时间最少保留一轮全备周期以上，有条件最好是保留两轮+1；
方式二：进行日志信息手工清理
-- 获取清理日志命令帮助信息
mysql> help purge binary logs;
-- 删除到指定日志文件前结束(不包含指定日志文件)
mysql> purge binary logs to '3306-binlog.000005';
-- 可以基于日志时间点信息进行日志清理
mysql> PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';
说明：在对数据库服务日志信息进行清理时，最好使用数据库服务自带的清理工具进行清理，不建议使用rm做日志清理；
四、日志信息远程备份¶
可以实现将数据库中（特别是主库）生成的binlog日志文件，及时备份保存到专门的日志备份服务器中，并且整个备份操作都是在线的；
需要备份的数据库(10.0.0.51)准备工作：
-- 创建用户并赋权
[root@master ~]# mysql -uroot -p123456
mysql> create user root@'%' identified with mysql_native_password by '123456';
mysql> grant all on *.* to root@'%';
存放备份数据的机器(10.0.0.52)配置操作(需要提前安装好mysql环境)：
[root@master ~]# mkdir -p /binlog_backup
[root@master ~]# cd /binlog_backup/
-- 备份过程可以放后台一直运行，但是需要注意当连接的数据库服务器停止或重启了，也会导致备份中断；
[root@master binlog_backup]# mysqlbinlog -R --host=10.0.0.51 --user=root --password=123456 --raw --stop-never 3306-binlog.000005 &
-- 验证
[root@db02-52 binlog_backup]# ll
total 8
-rw-r----- 1 root root 205 Mar 9 16:20 3306-binlog.000005
-rw-r----- 1 root root 656 Mar 9 16:20 3306-binlog.000006
# 数据库服务多实例情况binlog日志备份
-- 需要考虑备份后日志文件名称一样的覆盖问题
mysqlbinlog -R --host=10.0.0.51 -P 3306 --user=root --password=123456 --raw --stop-never binlog.000002 &
mysqlbinlog -R --host=10.0.0.51 -P 3307 --user=root --password=123456 --raw --stop-never binlog.000002 &
远程备份命令参数说明：
| 参数信息 | 官方说明 | 解释说明 |
|---|---|---|
| -R --read-from-remote-server |
Read binary logs from a MySQL server. | 读取binlog日志文件从数据库服务端 |
| -h --host |
Get the binlog from server | 指定binlog日志文件存储服务器地址 |
| -u --user=name |
Connect to the remote server as username | 指定binlog日志服务器连接用户信息 |
| -p --password[=name] |
Password to connect to remote server. | 指定binlog日志服务器连接密码信息 |
| --raw | Requires -R. Output raw binlog data instead of SQL statements, output is to log files | 指定binlog日志信息记录二进制信息 |
| --stop-never | Wait for more data from the server instead of stopping at the end of the last log | 指定binlog日志信息将会一直备份记录 |
| binlog.000008 | 代表从哪个binlog日志开始进行备份 |