MySQL 数据库日志总结(一)
数据库服务日志概述介绍
任何一种数据库中，都会有各种各样的日志，记录这数据库工作的方方面面，以帮助数据库管理员追踪数据库曾经发生过的各种事件；
主要是针对数据库server层产生的数据信息，主要用于记录和数据库服务运行本身有关的日志、以及SQL语句操作执行相关的日志；
据库服务日志常用分类
在MySQL数据库服务中，有4种不同的日志是最常用的日志类型，这些日志记录这数据库在不同方面的踪迹；
日志信息查看方法：
mysql> show variables like '%log%';
+------------------------------------------------+---------------------------------------------+
| Variable_name | Value |
+------------------------------------------------+---------------------------------------------+
| general_log | OFF |
| general_log_file | /data/3306/data/wenC-01.log |
| log_error | ./wenC-01.err |
| log_bin | ON |
| log_bin_basename | /data/3306/data/binlog |
| log_bin_index | /data/3306/data/binlog.index |
| slow_query_log | OFF |
| slow_query_log_file | /data/3306/data/wenC-01-slow.log |
+------------------------------------------------+---------------------------------------------+
常用日志信息介绍：
| 序号 | 日志名称 | 解释说明 |
|---|---|---|
| 01 | general_log | 表示查询日志（通用日志），默认日志状态处于关闭，可以进行在线调整配置 作用：记录了客户端从会话连接开始，执行过的所有SQL语句信息； |
| 02 | log_error | 表示错误日志（运行日志），默认日志状态处于激活 作用：记录了数据库服务启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息； |
| 03 | log_bin | 表示二进制日志（binlog日志），默认日志状态处于激活（8.0之后） 作用：记录了所有的DDL语句和DML语句，但是不包括数据库查询语句；语句以事件的形式保存，描述了数据的更改过程，此日志对于灾难时的数据恢复起着极其重要的作用。 |
| 04 | slow_query_log | 表示慢查询日志，记录了所有执行时间超过参数long_query_time设置值并且扫描记录数小于min_examined_row_limit的所有SQL语句的日志。 |
数据库服务日志信息配置
1.分类日志信息配置：通用日志（general_log）
- 1_1 日志信息基本配置：
general_log=OFF
-- 默认日志功能处于关闭，建议在需要做调试工作时（功能测试、语句审计）可以打开；
general_log_file=/data/3306/data/wenC-01.log
-- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离；
# 修改日志默认状态（激活日志）：
mysql > set global general_log=1;
说明：企业真实环境，由于日志记录量比较大，所以不建议打开此日志记录功能，可以在有需要时打开，支持在线配置调整；
2.分类日志信息配置：错误日志（log_error）
- 2_1 日志信息基本配置
log_error=./wenC-01.err
-- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离；
# 修改日志存储路径（永久配置）：
[root@cheng ~]# vim /etc/my.cnf
log_error=/data/3306/log/wenC-01.err
-- 配置文件编写完毕后，需要重启数据库服务生效
# 模拟故障日志应用
[root@cheng ~]# ll /data/3306/data/ibdata1
-rw-r----- 1 mysql mysql 12582912 Nov 16 17:46 /data/3306/data/ibdata1
[root@cheng ~]# chmod 000 /data/3306/data/ibdata1
[root@cheng ~]# /etc/init.d/mysqld restart
Shutting down MySQL............................... SUCCESS!
Starting MySQL......................................... ERROR! The server quit without updating PID file (/data/3306/data/wenC-01.pid).
[root@cheng ~]# tail -20 /data/3306/log/wenC-01.err
2022-11-21T01:20:47.735040Z 1 [ERROR] [MY-012271] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2022-11-21T01:20:47.744091Z 1 [ERROR] [MY-012278] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2022-11-21T01:20:47.744808Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2022-11-21T01:20:47.745739Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2022-11-21T01:20:47.746526Z 0 [ERROR] [MY-010119] [Server] Aborting
-- 根据错误日志的错误提示信息，进行错误信息进行分析，从而排查故障可能出现的原因；
说明：企业真实环境，日志处于默认激活记录状态，可以使用错误日志信息做故障诊断，记录错误信息级别为note warning error；
3.分类日志信息配置：二进制日志（log_bin）
在进行增量恢复数据时，需要先了解什么是binlog日志，此日志文件其实就是用于记录对数据库进行操作更改的语句信息的；
并且记录更改的语句信息以事件形式进行记录，但是需要注意的是查询相关的语句是不会被记录的，比如：select、show；
然而作为所有对数据库的改操作事件信息都会被记录，比如：insert、update、create、drop。。。
查看数据库binlog日志配置参数：
进入到数据库服务系统环境中，可以使用命令进行查看binlog日志功能是否开启；
# 未开启binlog日志功能时，查看系统binlog功能配置参数状态
mysql> show variables like '%log_bin%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| log_bin | OFF |
| sql_log_bin | ON |
+-----------------------------------------+-------+
3 rows in set (0.00 sec)
--- 通过以上输出信息可以看到log_bin为off状态，表示binlog日志功能尚未开启
# 已开启binlog日志功能后，查看系统binlog功能配置参数状态
mysql> show variables like '%log_bin%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| log_bin | ON |
| sql_log_bin | ON |
+-----------------------------------------+-------+
3 rows in set (0.00 sec)
--- 通过以上输出信息可以看到log_bin为on状态，表示binlog日志功能已经开启
- 3_1 日志信息基本配置
server_id=6
-- 进行主从操作时，需要进行此信息配置；
log_bin=ON
-- 默认日志功能处于关闭状态
log_bin_basename=/data/3306/data/binlog
-- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离；
# 配置信息简写方式：开启数据库binlog日志记录功能
[root@cheng ~]# vim /etc/my.cnf
-- 激活binlog日志记录功能，需要对数据库服务配置文件进行编辑修改
[mysqld]
server_id=6
log_bin=/data/3306/binlog/mysql-bin
-- 进行binlog日志目录路径信息修改时，需要创建指定的目录并设置权限信息，最后需要重新启动数据库服务生效；
或者
log_bin=binlog
-- 只是设置日志名称信息，日志会自动保存到数据库服务指定的数据目录中；
# 配置文件修改后需要重启数据库服务，加载配置文件改动的信息：
[root@cheng ~]# /etc/init.d/mysqld restart
[root@cheng ~]# ll -h /data/3306/data/binlog*
-rw-rw----. 1 mysql mysql 245 6月 24 02:19 /data/3306/data/binlog.00000N
-rw-rw----. 1 mysql mysql 16 6月 24 02:19 /data/3306/data/binlog.index
-- 数据库服务重启后，已经可以在数据库的数据存储目录中，看到binlog日志文件的踪影
说明：企业真实环境，日志处于默认激活记录状态，可以使用日志信息进行灾难数据恢复，以及可以用于实现主从复制；
- 3_2 日志配置信息扩展
参数官方资料链接：https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html
# 参数一：sync_binlog 表示刷新日志到磁盘策略
mysql> select @@sync_binlog;
+---------------------+
| @@sync_binlog |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)
-- 在进行主从同步过程的双一标准的其中一个1的信息配置，主要是控制缓冲区里的binlog日志信息如何刷写到磁盘中；
-- 此参数信息是有三种方式进行配置的：
-- 参数信息配置0：表示由操作系统缓存自己决定，什么时候刷新日志到磁盘中；
-- 参数信息配置1：表示每次事务提交，立即刷新日志到磁盘中；(此方式配置更安全)
-- 参数信息配置N：表示每组事务提交，按照组的事务次数定义，确定刷新日志到磁盘中的频次；（可以有效减少IO性能损耗）
# 参数二：binlog_format 定义binlog日志的格式信息
mysql> select @@binlog_format;
+------------------------+
| @@binlog_format |
+------------------------+
| ROW |
+------------------------+
1 row in set (0.00 sec)
-- 在进行主从同步数据恢复时，此参数配置可能会影响数据恢复的一致性问题；
-- 此参数信息是有三种方式进行配置的，确定了主从复制的级别，只针对DML语句的日志才有效；
-- 参数信息配置 statement（SBR）：语句格式记录binlog；
create database wenC; -- DDL DCL语句只能使用statement 表示的就是原原本本的语句信息，即做什么就记录什么；
-- 参数信息配置 row（RBR）：行格式记录binlog（默认模式）
update t1 set a=10 where id<10; -- 会记录行的变化信息，属于底层的记录信息，可能会有多个变化日志信息记录
-- 参数信息配置 mixed（MBR）：混合格式记录binlog
-- 由数据库服务自行决定，是记录语句信息，还是记录行的变化信息；
SBR（statement-based replication）与RBR（Row-Based Replication）记录的优缺点分析：（面试常见问题）
| 记录方式 | 优点说明 | 缺点说明 |
|---|---|---|
| SBR | 可读性强，日志量相对较少； | 数据信息可能不准确，数据一致性不足 |
| RBR | 数据信息记录更准确，数据一致性更强 | 可读性弱，日志量相对较多，数据记录准确 |
| 举例说明 | update t1 set a=10 where id<10000; 记录一条语句即可 | insert into 随机数函数; |
| 举例说明 | update t1 set a=10 where id<10000; 记录多条语句修改信息，生成日志 | insert into 随机数函数; |
- 3_3 日志信息查看方法：
可以通过查看方式，获取binlog日志里的数据信息，一般在数据库启动时，日志记录功能就开启了；
可以利用日志中记录信息，将数据库服务的数据信息恢复到指定的时间点，同时也可以支持主从数据复制（在其它机器上回放日志）；
对于binlog日志信息的查看，主要目的是为了日后日志事件信息的截取操作；
查看方式一：确认数据库binlog日志数量
mysql> show binary logs;
+------------------+-------------+--------------+
| Log_name | File_size | Encrypted |
+------------------+-------------+--------------+
| binlog.000001 | 156 | No |
+------------------+-------------+--------------+
-- 获取数据库服务运行过程中，使用的binlog日志的情况
mysql> flush logs;
Query OK, 0 rows affected (0.12 sec)
-- 可以执行flush刷新命令，从而生成新的binlog日志文件，类似于实现了日志切割功能；
mysql> show binary logs;
+------------------+-------------+--------------+
| Log_name | File_size | Encrypted |
+------------------+-------------+--------------+
| binlog.000001 | 200 | No |
| binlog.000002 | 156 | No |
+------------------+-------------+--------------+
2 rows in set (0.00 sec)
查看方式二：确认数据库binlog日志状态
mysql> create database test_binlog;
Query OK, 1 row affected (0.03 sec)
-- 模拟数据服务有修改操作
mysql> select * from world.city limit 1;
Query OK, 1 row affected (0.03 sec)
-- 模拟数据服务有修改操作
mysql> show binary logs;
+------------------+-------------+--------------+
| Log_name | File_size | Encrypted |
+------------------+-------------+--------------+
| binlog.000001 | 200 | No |
| binlog.000002 | 362 | No |
+------------------+-------------+--------------+
2 rows in set (0.00 sec)
-- 可以看到binlog日志的存储量发生了变化，但是在做查询操作时，binlog日志的存储量并未发生变化
mysql> show master status;
+------------------+------------+------------------+-----------------------+-------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+------------+------------------+-----------------------+-------------------------+
| binlog.000002 | 362 | | | |
+------------------+------------+------------------+-----------------------+-------------------------+
1 row in set (0.00 sec)
-- 查看获取当前使用的binlog日志情况，以及产生的日志量字节大小；
查看方式三：查看数据库binlog日志信息
mysql> show binlog events in 'binlog.000002';
-- binlog日志信息是以事件方式进行记录的，所以日志查看过程是查看事件信息
-- 一般binlog日志的前两行，表示日志格式头信息（日志简单的描述信息）
-- 一般binlog日志中的query信息，就是对数据库的操作语句，其中包含了创建数据库的语句；
具体binlog事件信息：
具体binlog事件记录信息分析：
| 列号 | 列信息 | 解释说明 |
|---|---|---|
| 01 | Log_name | 表示指定查看的binlog日志文件名称信息 |
| 02 | Pos | 表示binlog日志事件开始的位置点，用于截取二进制日志信息标识 |
| 05 | End_log_pos | 表示binlog日志事件结束的位置点，用于截取二进制日志信息标识 |
| 06 | Info | 表示binlog中具体的事件内容信息 |
查看方式四：筛选数据库binlog日志事件
# 模拟生成binlog日志事件信息
mysql> source ~/world.sql;
mysql> drop database world;
mysql> source ~/world.sql;
# 获取删除数据库的事件信息：
# 筛选数据库日志方式一：
[root@cheng data]# mysql -e "show binlog events in 'binlog.000002'"|grep "drop database"
binlog.000002 722789 Query 1 722896 drop database world /* xid=5363 */
-- 获取指定事件信息产生的起点位置和终点位置信息；
# 筛选数据库日志方式二：
mysql> pager less
-- 在数据库中定义pager功能，数据库连接会话退出即失效；
mysql> show binlog events in 'binlog.000002';
-- 此时查看日志事件信息具有了翻页功能
/drop database
| binlog.000002 | 722789 | Query | 1 | 722896 | drop database world /* xid=5363 */
mysql> pager grep "drop database"
PAGER set to 'grep "drop database"'
-- 表示开启数据库pager的过滤功能
mysql> show binlog events in 'binlog.000002';
| binlog.000002 | 722789 | Query | 1 | 722896 | drop database world /* xid=5363 */
-- 再次查看binlog事件信息时，只过滤显示删除数据库的操作事件日志
说明：在实际生产环境中，若binlog日志量比较大时，需要快速过滤关键日志事件行，可以使用以上查看日志方法；
获取数据库binlog日志记录信息异常：
进行数据库服务数据信息更改操作，随后查看binlog日志信息的变化：
# 进行数据库创建操作
mysql> create database wenC;
mysql> show databases;
# 查看获取binlog日志记录信息
[root@cheng ~]# mysqlbinlog /var/lib/mysql/binlog.000001
mysqlbinlog: unknown variable 'default-character-set=utf8mb4'
-- 由于在数据库在客户端配置文件中添加了default-character-set=utf8mb4字符编码信息，因此造成无法查看binlog
[root@cheng ~]# cat /etc/my.cnf.d/client.cnf
[client]
#default-character-set=utf8mb4
[client-mariadb]
#default-character-set=utf8mb4
-- 可以临时调整先将客户端的字符编码配置信息注释，
[root@cheng ~]# mysqlbinlog /var/lib/mysql/binlog.000001
... 省略部分信息 ...
# at 494
#220624 2:35:02 server id 1 end_log_pos 579 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1656009302/*!*/;
create database wenC
/*!*/;
... 省略部分信息 ...
-- 在binlog日志文件中，已经记录了之前的创建xiaoQ的更改操作记录信息
- 3_4 日志信息应用实战
数据库数据异常恢复（简单情况）
在实际生成环境中，可以利用binlog日志记录的信息截取，实现数据库异常情况下的数据信息恢复功能；
数据库异常恢复情况环境准备：
# 切换新的binlog日志文件做模拟数据恢复
mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)
mysql> show master status;
+------------------+------------+------------------+-----------------------+-------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+------------+------------------+-----------------------+-------------------------+
| binlog.000003 | 156 | | | |
+------------------+------------+------------------+-----------------------+-------------------------+
1 row in set (0.01 sec)
-- 确认已经刷新生成了新的binlog日志文件；
# 进行基本的数据库SQL语句操作：
mysql> create database bindb;
mysql> use bindb;
mysql> create table t1 (id int);
mysql> begin;
mysql> insert into t1 values(1);
-- 在没有进行事务提交前，操作的事务事件信息，是不会出现在binlog事件日志中的
mysql> commit;
-- 对于数据库的binlog日志，只会记录事务已经提交的DML语句信息，没有提交的DML语句是不会进行记录的；
-- 在日志中变化的DML语句信息是无法识别的，因为记录DML操作的语句默认是以ROW模式记录的；
数据库二进制日志信息查看方法：
[root@cheng ~]# mysqlbinlog /data/3306/data/binlog.000003
-- 对于数据库binlog日志信息，是无法直接查看内容信息，需要利用相关命令工具进行查看
# 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
#221121 13:12:59 server id 1 end_log_pos 125 CRC32 0xbb7d1fd1 Start: binlog v 4, server v 8.0.26 created 221121 13:12:59
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
2wh7Yw8BAAAAeQAAAH0AAAABAAQAOC4wLjI2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigB0R99uw==
'/*!*/;
# at 125
#221121 13:12:59 server id 1 end_log_pos 156 CRC32 0x04874c92 Previous-GTIDs
# [empty]
-- binlog日志文件156之前的内容是可以忽略的，表示是日志文件的头格式内容信息
# at 156
#221121 13:16:19 server id 1 end_log_pos 233 CRC32 0xd73c14e1 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1669007779100380 immediate_commit_timestamp=1669007779100380 transaction_length=188
# original_commit_timestamp=1669007779100380 (2022-11-21 13:16:19.100380 HKT)
# immediate_commit_timestamp=1669007779100380 (2022-11-21 13:16:19.100380 HKT)
/*!80001 SET @@session.original_commit_timestamp=1669007779100380*//*!*/;
/*!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中记录的事件日志信息是连续的；
#221121 13:16:19 server id 1 end_log_pos 344 CRC32 0x624986f5 Query thread_id=11 exec_time=0 error_code=0 Xid = 10728
SET TIMESTAMP=1669007779/*!*/;
SET @@session.pseudo_thread_id=11/*!*/;
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
binlog日志内容中主要关注的信息：
- 通过日志信息查看DDL操作语句信息（记录方式 SBR）
- 通过日志信息查看DML操作语句信息（记录方式 RBR）
以上ROW模式记录的信息是加密显示，无法直接查看的，可以使用下面命令参数进行获取详细信息：
[root@cheng ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/3306/data/binlog.000003
-- 以上添加的参数信息，表示将DML的ROW格式语句信息，进行格式化处理输出；
# at 739
#221121 13:17:45 server id 1 end_log_pos 779 CRC32 0xb468b459 Write_rows: table id 101 flags: STMT_END_F
### INSERT INTO `bindb`.`t1`
-- 利用DML语句做的插入语句信息就显示出来了
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
-- 以上日志记录的信息，可以用命令实现，如下：
mysql > insert into t1 set id=1;
等价于
mysql > insert into t1 values(1);
数据库模拟异常情况破坏操作：
mysql> drop database bindb;
-- 模拟破坏性操作，删除数据库
数据库异常情况数据恢复操作：
# 需要恢复建库开始，删除之前的所有操作（即所有binlog日志信息），实现日志信息的截取
mysql> show binlog events in 'binlog.000002';
-- 查看截取日志信息事件区域范围
[root@cheng ~]# mysqlbinlog --start-position=233 --stop-position=1162 /data/3306/data/binlog.000003 >/tmp/bin.sql
-- 依据binlog日志的position号码，即可获取到想要恢复数据信息；
# 根据截取的日志信息，进行数据库服务数据恢复
mysql> set sql_log_bin=0;
-- 建议在进行数据日志恢复数据时，将数据恢复时执行的SQL语句信息，不做binlog日志记录；
mysql> source /tmp/bin.sql
# 查看确认数据信息是否恢复
mysql> use bindb;
mysql> show tables;
mysql> select * from t1;
数据库数据异常恢复
情况一：日志文件被清理过，可能建库语句所在日志已经丢失
项目背景：一个数据库三年前就创建了，但是日志信息只记录一个月，这个库被误删除了；
解决方案：
A计划：最近一次全备+全备之后，误删除之前所有binlog，进行一同恢复；（全备数据+增量数据）
B计划：利用延时从库，进行数据恢复；
情况二：所需日志跨越多个文件，如何进行日志信息的截取
解决方案：
A计划：只有position号的方式，可以进行分段截取，进行分段恢复数据；
B计划：根据Datatime时间信息方式，可能会出现准确性不高的情况（因为每一秒可能有多个事件产生）；
C计划：启用GTID（全局事务ID）方式，无论跨越多少个日志文件，每个事务操作的事件ID信息都是唯一且递增的（5.6+引入）；
实践操作：
C计划：基于GTID方式对binlog进行管理（利用GTID实现日志截取）
数据库异常恢复情况环境准备：
# 刷新新的binlog日志进行操作
mysql> flush logs;
-- 生成新的binlog日志信息
# 确认新的日志编号是否是连续的
mysql> create database test5;
mysql> show binlog events in "binlog.000004"
-- 可以看出新的binlog日志文件中，记录的gtid编号信息是延续了上一个binlog日志gtid集合信息，继续连续进行记录；
# 进行基本的数据库SQL语句操作：
mysql> create database gtdb;
mysql> use gtdb;
mysql> create table t1(id int);
mysql> insert into t1 values(1);
mysql> commit;
mysql> insert into t1 values(2);
mysql> commit;
mysql> insert into t1 values(3);
mysql> commit;
# 进行binlog事件信息查看
mysql> show binlog events in 'binlog.000004';
-- 可以获取以上的数据操作事件信息，
数据库模拟异常情况破坏操作：
mysql> drop database gtdb;
-- 模拟破坏性操作，删除数据库
数据库异常情况数据恢复操作：
# 根据日志信息查看相关的事件情况（获取GTID编号范围）
mysql> show binlog events in 'binlog.000004';
# 需要恢复建库开始，删除之前的所有操作（即所有binlog日志信息），实现日志信息的截取
[root@cheng ~]# mysqlbinlog --include-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:3-7' /data/3306/data/binlog.000004 >/tmp/gtid.sql
-- 依据binlog日志的GTID信息，即可获取到想要恢复数据信息；
# 根据截取的日志信息，进行数据库服务数据恢复
mysql> set sql_log_bin=0;
-- 建议在进行数据日志恢复数据时，将数据恢复时执行的SQL语句信息，不做binlog日志记录；恢复后别忘在改为1；
mysql> source /tmp/gtid.sql
-- 默认此时报错恢复失败，因为GTID截取的日志恢复数据时，具有幂等性，由于binlog中已经记录了3-7的GTID事件信息
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000004 | 1905 | | | 7afe4f8c-5e36-11ed-b083-000c29d44f34:1-8 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
-- 通过查看确认，核实清楚binlog中已经记录了3-7的GTID事件信息
# 利用GTID日志信息恢复报错处理方式一：将系统中日志中的GTID信息清除掉（不建议）
# 利用GTID日志信息恢复报错处理方式二：删除与幂等性冲突的记录信息
[root@cheng ~]# mysqlbinlog --skip-gtids --include-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:3-7' /data/3306/data/binlog.000004 >/tmp/gtid.sql
-- 表示跳过gtid的检查过程，即截取的日志中不再含有GTID的配置语句信息，自然解决了幂等性冲突问题；
-- 开启了GTID之后，依然可以使用pos方式进行日志信息截取与恢复；
# 查看确认数据信息是否恢复
mysql> use gtdb;
mysql> show tables;
mysql> select * from t1;
-- 查看test1数据库中的t1表的数据信息是否恢复
# 操作扩展：可以实现排除指定gtid信息不做日志记录截取
[root@cheng ~]# mysqlbinlog --exclude-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:4' --include-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:3-7' /data/3306/data/binlog.000004
# 操作扩展：跨多日志文件信息截取
[root@cheng ~]# 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 |
+---------------------------------------------------+
| 7afe4f8c-5e36-11ed-b083-000c29d44f34 |
+---------------------------------------------------+
1 row in set (0.00 sec)
-- 表示数据库每次初始化之后自动生成，不建议手工进行修改；
[root@cheng ~]# cat /data/3306/data/auto.cnf
[auto]
server-uuid=7afe4f8c-5e36-11ed-b083-000c29d44f34
-- 在数据库的数据目录文件中也可以查询到
- GTID功能作用：
利用GTID方式管理binlog，实质上就是对于数据库的每个事务产生事件信息打上唯一标识信息（id号）；
利用GTID方式管理binlog，主要目的是处理数据库主从问题，解决主从数据库的数据一致性问题；
简单描述：标识事务的唯一性，保证日志恢复时的一致性，并且具备”幂等性”；
- GTID功能配置：
GTID功能相关参数介绍：
# GTID功能参数信息介绍（3个重要的配置参数）
mysql> select @@gtid_mode;
+-------------------+
| @@gtid_mode |
+-------------------+
| OFF |
+-------------------+
1 row in set (0.00 sec)
-- 设置是否开启显示gtid信息功能（在5.7之后是有个匿名的gtid，是数据库系统自己维护的）
mysql> select @@enforce_gtid_consistency;
+-------------------------------------+
| @@enforce_gtid_consistency |
+-------------------------------------+
| OFF |
+-------------------------------------+
1 row in set (0.00 sec)
-- 设置是否开启GTID强制一致性功能
-- 对某些 SQL 会有限制，例如 CREATE TABLE … SELECT 必须得分成两条语句执行。
-- OFF： 表示事务允许违反 GTID 一致性。
-- ON： 表示事务不允许违反 GTID 一致性，有相关 SQL 会直接返回异常。
-- WARN：表示事务允许违反 GTID 一致性，但会将警告信息记录到 ERROR LOG。
mysql> select @@log_slave_updates;
+----------------------------+
| @@log_slave_updates |
+----------------------------+
| 1 |
+----------------------------+
1 row in set, 1 warning (0.01 sec)
-- 和配置主从有关（在8.0.26开始 推荐配置log_replica_updates替代log_slave_updates参数）
-- 此参数表示从服务器从主服务器接收的更新信息，是否也会记录在从服务器本地的二进制文件中
GTID功能相关参数激活：
[root@cheng ~]# vim /etc/my.cnf
[mysqld]
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=on
-- 配置文件信息修改完毕后，重启数据库服务使配置生效
- GTID信息查看：
mysql> show master status;
+------------------+-----------+-------------------+-----------------------+-------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+-------------------+-----------------------+-------------------------+
| binlog.000004 | 156 | | | |
+------------------+-----------+-------------------+-----------------------+-------------------------+
1 row in set (0.03 sec)
-- 在GTID功能被激活后，就会在Executed_Gtid_Set列中显示GTID集合信息；
mysql> create database test3;
Query OK, 1 row affected (0.08 sec)
-- 模拟创建数据库，产生新的事件信息
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| binlog.000004 | 344 | | | 7afe4f8c-5e36-11ed-b083-000c29d44f34:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.01 sec)
-- GTID信息随着新的事件产生，随之发生变化
mysql> create database test4;
Query OK, 1 row affected (0.03 sec)
-- 模拟创建数据库，产生新的事件信息
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000004 | 532 | | | 7afe4f8c-5e36-11ed-b083-000c29d44f34:1-2 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
-- GTID信息随着新的事件产生，随之发生变化
mysql> show binlog events in 'binlog.000004';
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| binlog.000004 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| binlog.000004 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000004 | 156 | Gtid | 1 | 233 | SET @@SESSION.GTID_NEXT= '7afe4f8c-5e36-11ed-b083-000c29d44f34:1' |
| binlog.000004 | 233 | Query | 1 | 344 | create database test3 /* xid=6 */ |
| binlog.000004 | 344 | Gtid | 1 | 421 | SET @@SESSION.GTID_NEXT= '7afe4f8c-5e36-11ed-b083-000c29d44f34:2' |
| binlog.000004 | 421 | Query | 1 | 532 | create database test4 /* xid=8 */ |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
6 rows in set (0.00 sec)
-- 在每个数据库操作事件之前，会显示GTID的唯一标识信息
情况三：如何从日志文件中恢复单库、单表、或者部分行数据信息
解决方案：
A计划：可以利用命令单独截取某个数据库的日志信息；mysqlbinlog -d world xxx > xxxx
B计划：可以借助第三方工具实现单表或部分数据恢复；binlog2sql（python） 过滤指定表数据或过滤指定表的部分数据；
实战操作：
A计划：单库日志信息截取，企业实战过程：
数据库异常恢复情况环境准备：
# 查看获取当前binlog日志状态信息
mysql > show master status;
+------------------+-----------+-------------------+-----------------------+-------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+-------------------+-----------------------+-------------------------+
| binlog.000003 | 1269 | | | |
+------------------+-----------+-------------------+-----------------------+-------------------------+
# 进行基本的数据库SQL语句操作：
mysql> create database test1;
mysql> create table t1 (id int);
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)
-- 创建了一个test1数据库，并在数据库中创建了一个表，在表中插入了一些数据信息
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;
-- 创建了一个test2数据库，并在数据库中创建了一个表，在表中插入了一些数据信息
mysql> use test1;
mysql> insert into t1 values(3);
mysql> insert into t1 values(4);
mysql> use test2;
mysql> insert into t2 values(3);
mysql> insert into t2 values(4);
mysql> commit;
mysql> select * from test1.t1;
mysql> select * from test2.t2;
-- 通过操作不同的数据库，以及不同的数据表，实现binlog日志事件信息的交叉
数据库模拟异常情况破坏操作：
mysql> drop database test1;
-- 模拟破坏性操作，删除数据库
数据库异常情况数据恢复操作：
# 根据日志信息查看相关的事件情况
mysql> show binlog events in 'binlog.000003';
# 需要恢复建库开始，删除之前的所有操作（即所有binlog日志信息），实现日志信息的截取
[root@cheng ~]# mysqlbinlog --start-position=1346 --stop-position=4116 -d test1 /data/3306/data/binlog.000003 >/tmp/bin.sql
-- 依据binlog日志的position号码，即可获取到想要恢复数据信息，并利用-d参数导出指定数据库相关数据；
# 根据截取的日志信息，进行数据库服务数据恢复
mysql> set sql_log_bin=0;
-- 建议在进行数据日志恢复数据时，将数据恢复时执行的SQL语句信息，不做binlog日志记录；恢复后别忘在改为1；
mysql> source /tmp/bin.sql
# 查看确认数据信息是否恢复
mysql> use test1;
mysql> show tables;
mysql> select * from t1;
-- 查看test1数据库中的t1表的数据信息是否恢复
mysql> use test2;
mysql> show tables;
mysql> select * from t2;
-- 查看test2数据库中的t2表的数据信息是否破坏
B计划：可以借助第三方工具实现单表或部分数据恢复；
利用binlog2sql工具可以处理上面的企业需求，此软件是利用python语言开发的，主要用来处理binlog日志信息；
从软件应用方面来说主要包含两个核心功能：
- 可以友好的展示或者管理二进制日志信息（binlog），进而可以过滤出单独表的信息，甚至表中指定行的信息；
- 可以快速的实现DML操作语句的闪回功能，即实现通过日志信息翻转方式，进行数据信息的恢复；
说明：binlog2sql工具是模拟了一个从库，进行日志信息分析，需要保证数据库服务启动状态，且不支持离线方式分析日志内容；
数据库异常恢复情况环境准备：
# 下载第三方日志分析工具
[root@cheng ~]# cd /opt/
[root@cheng ~]# git clone https://github.com/danfengcao/binlog2sql.git
[root@cheng ~]# cd /opt/binlog2sql
-- 此工具在mariadb中可以通过打补丁方式，进行部署安装；但是在mysql 8.0中暂时还没有集成，需要单独安装
# 部署第三方工具运行环境
[root@cheng ~]# yum install -y python3
[root@cheng ~]# pip3 install -r requirments.txt
[root@cheng ~]# pip3 show pymysql
[root@cheng ~]# 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）
[root@cheng binlog2sql]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --start-file='binlog.000003'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1460 end 1704 time 2022-11-21 22:16:32 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1735 end 1979 time 2022-11-21 22:16:35 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2939 end 3183 time 2022-11-21 22:20:53 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3214 end 3458 time 2022-11-21 22:22:19 gtid
[root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t11 --start-file='binlog.000003'
INSERT INTO `test1`.`t11`(`id`) VALUES (1); #start 4704 end 4954 time 2022-11-21 23:47:51 gtid
INSERT INTO `test1`.`t11`(`id`) VALUES (2); #start 4704 end 4954 time 2022-11-21 23:47:51 gtid
-- 表的数据信息导出后，可以直接复制命令信息恢复，或者导出sql文件进行导入恢复；
数据库模拟异常情况破坏操作：
# 在指定数据库的相应数据表中做修改操作
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;
数据库日志信息工具分析查看：（解析日志事件SQL）
[root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --start-file='binlog.000003'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1460 end 1704 time 2022-11-21 22:16:32 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1735 end 1979 time 2022-11-21 22:16:35 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2939 end 3183 time 2022-11-21 22:20:53 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3214 end 3458 time 2022-11-21 22:22:19 gtid
UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 4985 end 5244 time 2022-11-21 23:52:33 gtid
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 5275 end 5519 time 2022-11-21 23:54:17 gtid
# 只想查看删除操作信息
[root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=delete --start-file='binlog.000003'
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 5275 end 5519 time 2022-11-21 23:54:17 gtid
-- sql-type参数只能过滤DML类型语句信息，一般常见过滤的是 insert update delete
# 只想查看修改操作信息
[root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=update --start-file='binlog.000003'
UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 4985 end 5244 time 2022-11-21 23:52:33 gtid
# 只想查看插入操作信息
[root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=insert --start-file='binlog.000003'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1460 end 1704 time 2022-11-21 22:16:32 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1735 end 1979 time 2022-11-21 22:16:35 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2939 end 3183 time 2022-11-21 22:20:53 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3214 end 3458 time 2022-11-21 22:22:19 gtid
数据库日志信息工具回滚操作：（生成指定事件回滚语句-闪回操作）
假设在某个企业的应用场景中，有3000万行数据，占用200G的存储空间，其中误删除了10行数据信息，请问如何进行恢复数据？
# 误删除操作语句反转操作
[root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=delete --start-file='binlog.000003'
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 5275 end 5519 time 2022-11-21 23:54:17 gtid
-- 获取删除操作语句信息
[root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=delete --start-file='binlog.000003' -B
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 5275 end 5519 time 2022-11-21 23:54:17 gtid
-- 在获取删除操作语句命令后加 -B 参数，正好获得了反转语句的操作信息
# 误修改操作语句反转操作
[root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=update --start-file='binlog.000003'
UPDATE `test1`.`t1` SET `id`=10 WHERE `id`=1 LIMIT 1; #start 4985 end 5244 time 2022-11-21 23:52:33 gtid
-- 获取修改操作语句信息
[root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=update --start-file='binlog.000003' -B
UPDATE `test1`.`t1` SET `id`=1 WHERE `id`=10 LIMIT 1; #start 4985 end 5244 time 2022-11-21 23:52:33 gtid
-- 在获取修改操作语句命令后加 -B 参数，正好获得了反转语句的操作信息
# 误插入操作语句反转操作
[root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=insert --start-file='binlog.000003'
INSERT INTO `test1`.`t1`(`id`) VALUES (1); #start 1460 end 1704 time 2022-11-21 22:16:32 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (2); #start 1735 end 1979 time 2022-11-21 22:16:35 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (3); #start 2939 end 3183 time 2022-11-21 22:20:53 gtid
INSERT INTO `test1`.`t1`(`id`) VALUES (4); #start 3214 end 3458 time 2022-11-21 22:22:19 gtid
-- 获取插入操作语句信息
[root@cheng binlog2sql-master]# python3 binlog2sql.py -h 10.0.0.101 -P3306 -uroot -p123456 -d test1 -t t1 --sql-type=insert --start-file='binlog.000003' -B
DELETE FROM `test1`.`t1` WHERE `id`=4 LIMIT 1; #start 3214 end 3458 time 2022-11-21 22:22:19 gtid
DELETE FROM `test1`.`t1` WHERE `id`=3 LIMIT 1; #start 2939 end 3183 time 2022-11-21 22:20:53 gtid
DELETE FROM `test1`.`t1` WHERE `id`=2 LIMIT 1; #start 1735 end 1979 time 2022-11-21 22:16:35 gtid
DELETE FROM `test1`.`t1` WHERE `id`=1 LIMIT 1; #start 1460 end 1704 time 2022-11-21 22:16:32 gtid
-- 在获取插入操作语句命令后加 -B 参数，正好获得了反转语句的操作信息
********** 如果您认为这篇文章还不错或者有所收获，请点击右下角的【推荐】/【赞助】按钮，因为您的支持是我继续创作分享的最大动力！ **********
作者：讲文张字
出处：https://www.cnblogs.com/zhangwencheng
版权：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出 原文链接
出处：https://www.cnblogs.com/zhangwencheng
版权：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出 原文链接
浙公网安备 33010602011771号