DAY07 - MySQL主从复制

1

log_error

1

log_bin

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

mysql> show variables like '%log%';
+------------------------------------------------+---------------------------------------------+
| Variable_name                                  | Value                                       |
+------------------------------------------------+---------------------------------------------+
| general_log                                    | OFF                                         |
| general_log_file                               | /data/3306/data/xiaoQ-01.log                |
| log_error                                      | ./xiaoQ-01.edu.err                          |
| log_bin                                        | ON                                          |
| log_bin_basename                               | /data/3306/data/binlog                      |
| log_bin_index                                  | /data/3306/data/binlog.index                |
| slow_quer y_log                                | OFF                                         |
| slow_quer y_log_file                           | /data/3306/data/xiaoQ-01-slow.log           |
+------------------------------------------------+---------------------------------------------+

1

-- 默认日志功能处于关闭，建议在需要做调试工作时（功能测试、语句审计）可以打开；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离；
# 修改日志默认状态（激活日志）：
mysql > set global general_log=1;
log_error=./xiaoQ-01.edu.err
-- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离；
# 修改日志存储路径（永久配置）：
[root@xiaoq ~]# vim /etc/my.cnf
log_error=/tmp/mysql3306.err
-- 配置文件编写完毕后，需要重启数据库服务生效
# 模拟故障日志应用
[root@oldboyxiaoq ~]# ll /data/3306/data/ ibdata1
-rw-r----- 1 mysql mysql 12582912 Nov 16 17:46 /data/3306/data/ ibdata1
[root@oldboyxiaoq ~]# chmod 000 /data/3306/data/ ibdata1
[root@oldboyxiaoq ~]# /etc/ init.d/mysqld restart
Shutting down MySQL............................... SUCCESS!
Starting MySQL......................................... ERROR! The ser ver quit without updating PID file (/data/3306/data/oldboyxiaoq.com.pid).
[root@oldboyxiaoq ~]# tail -20 /data/3306/data/oldboyxiaoq.com.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] [Ser ver] Failed to initialize DD Storage Engine
2022-11-21T01:20:47.745739Z 0 [ERROR] [MY-010020] [Ser ver] Data Dictionar y initialization failed.
2022-11-21T01:20:47.746526Z 0 [ERROR] [MY-010119] [Ser ver] Aborting
-- 根据错误日志的错误提示信息，进行错误信息进行分析，从而排查故障可能出现的原因；
# 未开启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)

1
2
3
4
5
6
7
8
9

# 已开启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)

1
2

ser ver_id=6
-- 进行主从操作时，需要进行此信息配置；

1
2

update t1 set a=10 where id<10000; 记录一条语句即可
insert into 随机数函数;

1
2

update t1 set a=10 where id<10000; 记录多条语句修改信息，生成日志
insert into 随机数函数;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

log_bin=ON
-- 默认日志功能处于关闭状态
log_bin_basename=/data/3306/data/binlog
-- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离；
# 配置信息简写方式：开启数据库binlog日志记录功能
[root@xiaoq ~]# vim /etc/my.cnf
-- 激活binlog日志记录功能，需要对数据库服务配置文件进行编辑修改
[mysqld]
ser ver_id=6
log_bin=/data/3306/binlog /mysql-bin
-- 进行binlog日志目录路径信息修改时，需要创建指定的目录并设置权限信息，最后需要重新启动数据库服务生效；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

log_bin=binlog
-- 只是设置日志名称信息，日志会自动保存到数据库服务指定的数据目录中；
# 配置文件修改后需要重启数据库服务，加载配置文件改动的信息：
[root@xiaoQ ~]# /etc/ init.d/mysqld restart
[root@xiaoQ xiaoQ]# 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日志文件的踪影
# 参数一：sync_binlog 表示刷新日志到磁盘策略
mysql> select @@sync_binlog;
+---------------------+
| @@sync_binlog       |
+---------------------+
| 1                   |
+---------------------+
1 row in set (0.00 sec)
-- 在进行主从同步过程的双一标准的其中一个1的信息配置，主要是控制缓冲区里的binlog日志信息如何刷写到磁盘中；
-- 此参数信息是有三种方式进行配置的：
-- 参数信息配置0：表示由操作系统缓存自己决定，什么时候刷新日志到磁盘中；
-- 参数信息配置1：表示每次事务提交，立即刷新日志到磁盘中；(此方式配置更安全)
-- 参数信息配置N：表示每组事务提交，按照组的事务次数定义，确定刷新日志到磁盘中的频次；（可以有效减少IO性能损耗）
-- 参数官方资料链接：https://dev.mysql.com/doc/refman/8.0/en/replication-options-binar y-log.html
# 参数二：binlog_format 定义binlog日志的格式信息
mysql> select @@binlog_format;
+------------------------+
| @@binlog_format        |
+------------------------+
| ROW                    |
+------------------------+
1 row in set (0.00 sec)
-- 在进行主从同步数据恢复时，此参数配置可能会影响数据恢复的一致性问题；
-- 此参数信息是有三种方式进行配置的，确定了主从复制的级别，只针对DML语句的日志才有效；
-- 参数信息配置 statement（SBR）：语句格式记录binlog；
create database xiaoQ;  -- DDL DCL语句只能使用statement 表示的就是原原本本的语句信息，即做什么就记录什么；
-- 参数信息配置 row（RBR）：行格式记录binlog（默认模式）
update t1 set a=10 where id<10;    -- 会记录行的变化信息，属于底层的记录信息，可能会有多个变化日志信息记录
-- 参数信息配置 mixed（MBR）：混合格式记录binlog
-- 由数据库服务自行决定，是记录语句信息，还是记录行的变化信息；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

mysql> show binar y logs;
+------------------+-------------+--------------+
| Log_name         | File_size   | Encr ypted   |
+------------------+-------------+--------------+
| binlog.000001    | 156         | No           |
+------------------+-------------+--------------+
-- 获取数据库服务运行过程中，使用的binlog日志的情况
mysql> flush logs;
Quer y OK, 0 rows affected (0.12 sec)
-- 可以执行flush刷新命令，从而生成新的binlog日志文件，类似于实现了日志切割功能；
mysql> show binar y logs;
+------------------+-------------+--------------+
| Log_name         | File_size   | Encr ypted   |
+------------------+-------------+--------------+
| binlog.000001    | 200         | No           |
| binlog.000002    | 156         | No           |
+------------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> create database test_binlog;
Quer y OK, 1 row affected (0.03 sec)
-- 模拟数据服务有修改操作
mysql> select * from world.city limit 1;
Quer y OK, 1 row affected (0.03 sec)
-- 模拟数据服务有修改操作
mysql> show binar y logs;
+------------------+-------------+--------------+
| Log_name         | File_size   | Encr ypted   |
+------------------+-------------+--------------+
| 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日志情况，以及产生的日志量字节大小；
mysql> show binlog events in 'binlog.000002';
-- binlog日志信息是以事件方式进行记录的，所以日志查看过程是查看事件信息
-- 一般binlog日志的前两行，表示日志格式头信息（日志简单的描述信息）
-- 一般binlog日志中的quer y信息，就是对数据库的操作语句，其中包含了创建数据库的语句；

1

Log_name

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 模拟生成binlog日志事件信息
mysql> source ~/world.sql;
mysql> drop database world;
mysql> source ~/world.sql;
# 获取删除数据库的事件信息：
# 筛选数据库日志方式一：
[root@xiaoq data]# mysql -e "show binlog events in 'binlog.000002'"|grep "drop database"
binlog.000002   722789  Quer y   1   722896  drop database world /* xid=5363 */
-- 获取指定事件信息产生的起点位置和终点位置信息；
# 筛选数据库日志方式二：
mysql> pager less
-- 在数据库中定义pager功能，数据库连接会话退出即失效；
mysql> show binlog events in 'binlog.000002';
-- 此时查看日志事件信息具有了翻页功能

1

mysql> pager grep "drop database"

1
2

-- 表示开启数据库pager的过滤功能
mysql> show binlog events in 'binlog.000002';

1
2
3
4
5
6

-- 再次查看binlog事件信息时，只过滤显示删除数据库的操作事件日志
# 进行数据库创建操作
mysql> create database xiaoQ;
mysql> show databases;
# 查看获取binlog日志记录信息
[root@xiaoQ ~]# mysqlbinlog /var/lib/mysql/binlog.000001

1
2
3

-- 由于在数据库在客户端配置文件中添加了default-character-set=utf8mb4字符编码信息，因此造成无法查看binlog
[root@xiaoQ ~]# cat /etc/my.cnf.d/client.cnf
[client]

1
2

-- 可以临时调整先将客户端的字符编码配置信息注释，
[root@xiaoQ ~]# mysqlbinlog /var/lib/mysql/binlog.000001

1

# at 494

1
2

SET TIMESTAMP=1656009302/*!*/;
create database xiaoQ

1
2
3

-- 在binlog日志文件中，已经记录了之前的创建xiaoQ的更改操作记录信息
# 切换新的binlog日志文件做模拟数据恢复
mysql> flush logs;

1

binlog日志内容中主要关注的信息：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

Quer y 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@xiaoq ~]# 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 ser ver versions 8.0.24 and older.

1

# at 4

1
2

# Warning: this binlog is either in use or was not closed properly.
BINLOG '

1

# at 125

1
2
3

# [empty]
-- binlog日志文件156之前的内容是可以忽略的，表示是日志文件的头格式内容信息
# at 156

1
2

# original_commit_timestamp=1669007779100380 (2022-11-21 13:16:19.100380 HKT)
# immediate_commit_timestamp=1669007779100380 (2022-11-21 13:16:19.100380 HKT)

1
2
3
4

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
-- binlog日志文件已事件形式进行记录，主要关注两个at内容之间的信息，即表示的是一个事件信息；
# at 233
-- binlog日志中一个事件的开始，就表示上一个事件的结束，在binlog中记录的事件日志信息是连续的；

1
2
3
4
5

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/*!*/;

1
2
3

SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_ser ver=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;

1

create database bindb

1

# at 344

1
2
3

[root@xiaoq ~]# mysqlbinlog --base64-output=decode-rows -vvv /data/3306/data/binlog.000003
-- 以上添加的参数信息，表示将DML的ROW格式语句信息，进行格式化处理输出；
# at 739

1

-- 利用DML语句做的插入语句信息就显示出来了

1
2

-- 以上日志记录的信息，可以用命令实现，如下：
mysql > insert into t1 set id=1;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

mysql > insert into t1 values(1);
mysql> drop database bindb;
-- 模拟破坏性操作，删除数据库
# 需要恢复建库开始，删除之前的所有操作（即所有binlog日志信息），实现日志信息的截取
mysql> show binlog events in 'binlog.000002';
-- 查看截取日志信息事件区域范围
[root@oldboyxiaoq ~]# 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;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

# 刷新新的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@oldboyxiaoq ~]# 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@oldboyxiaoq ~]# mysqlbinlog --skip-gtids --include-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:3-7' /data/3306/data/binlog.000004 >/tmp/gtid.sql

1
2
3
4
5
6
7
8
9

-- 表示跳过gtid的检查过程，即截取的日志中不再含有GTID的配置语句信息，自然解决了幂等性冲突问题；
-- 开启了GTID之后，依然可以使用pos方式进行日志信息截取与恢复；
# 查看确认数据信息是否恢复
mysql> use gtdb;
mysql> show tables;
mysql> select * from t1;
-- 查看test1数据库中的t1表的数据信息是否恢复
# 操作扩展：可以实现排除指定gtid信息不做日志记录截取
[root@oldboyxiaoq ~]# mysqlbinlog --exclude-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:4'  --include-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:3-7'

1
2

# 操作扩展：跨多日志文件信息截取
[root@oldboyxiaoq ~]# mysqlbinlog --skip-gtids --include-gtids='7afe4f8c-5e36-11ed-b083-000c29d44f34:1-10' /data/3306/data/binlog.000001

1
2
3
4
5
6
7
8
9

mysql> select @@ser ver_uuid;
+---------------------------------------------------+
| @@ser ver_uuid                                    |
+---------------------------------------------------+
| 7afe4f8c-5e36-11ed-b083-000c29d44f34              |
+---------------------------------------------------+
1 row in set (0.00 sec)
-- 表示数据库每次初始化之后自动生成，不建议手工进行修改；
[root@oldboyxiaoq ~]# cat /data/3306/data/auto.cnf

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

-- 在数据库的数据目录文件中也可以查询到
# 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 一致性。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

-- 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参数）
-- 此参数表示从服务器从主服务器接收的更新信息，是否也会记录在从服务器本地的二进制文件中
[root@xiaoq ~]# vim /etc/my.cnf
[mysqld]

gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=on
-- 配置文件信息修改完毕后，重启数据库服务使配置生效
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;
Quer y 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;
Quer y 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     | Ser ver_i | End_log_pos | Info                                                              |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| binlog.000004 | 4   | Format_desc    | 1         | 125         | Ser ver 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 | Quer y         | 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 | Quer y         | 1         | 532         | create database test4 /* xid=8 */                                 |
+---------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
6 rows in set (0.00 sec)
-- 在每个数据库操作事件之前，会显示GTID的唯一标识信息

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

# 查看获取当前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@oldboyxiaoq ~]# 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;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

-- 查看test1数据库中的t1表的数据信息是否恢复
mysql> use test2;
mysql> show tables;
mysql> select * from t2;
-- 查看test2数据库中的t2表的数据信息是否破坏
# 下载第三方日志分析工具
[root@xiaoq ~]# cd /opt/
[root@xiaoq ~]# git clone https://github.com/danfengcao/binlog2sql.git
[root@xiaoq ~]# cd /opt/binlog2sql
-- 此工具在mariadb中可以通过打补丁方式，进行部署安装；但是在mysql 8.0中暂时还没有集成，需要单独安装
# 部署第三方工具运行环境
[root@xiaoq ~]# yum install -y python3
[root@xiaoq ~]# pip3 install -r requirments.txt
[root@xiaoq ~]# pip3 show pymysql
[root@xiaoq ~]# 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;
[root@xiaoq 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@oldboyxiaoq 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;
[root@oldboyxiaoq 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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

# 只想查看删除操作信息
[root@oldboyxiaoq 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@oldboyxiaoq 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@oldboyxiaoq 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@oldboyxiaoq 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@oldboyxiaoq 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@oldboyxiaoq 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@oldboyxiaoq 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@oldboyxiaoq 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@oldboyxiaoq 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 参数，正好获得了反转语句的操作信息
# 方法一：
mysql> flush logs;
-- 滚动更新前的日志文件就会处于静止状态，不会在进行数据信息的更新
# 方式二：
[root@xiaoq ~ ]# mysql -uroot -p123456 flush-logs
# 方式三：
mysql> restart;
-- mysql 8.0之后支持的数据库中重启服务；之前的版本只支持shutdown关闭数据库；
[root@xiaoq ~ ]# /etc/ init.d/mysqld restart
# 方式四：
mysql> select @@max_binlog_size;