DAY10 - MySQL性能优化

基础章节-01-MySQL数据库服务中级课程

1.00 课程知识章节说明

目前在互联网的实际应用中，各个企业都会比较关注自身网站的数据信息，既要保证数据信息的安全性，同时也要保证数据存储读取效率并且在特殊的场景下，还要对存储的数据信息进行检索和分析；因此数据库服务业务已经在各行各业应用非常的广泛对于互联网领域的技术人员，对于数据库服务知识的掌握，也将是在求职时必备的技能，有些时候还会绝对入职的定级和薪资水平。

1.13 数据库服务克隆应用

1.13.1 数据库克隆概念介绍

在数据库MySQL 8.0（8.0.17+）版本中，引入了数据库的克隆功能，主要是借助clone-plugin实现的，是对数据页底层克隆；克隆的数据是InnoDB存储引擎中的物理快照信息，包括schemas, tables, tablespaces, and data dictionar y metadata；在数据库中出现克隆功能，主要是为了满足目前云原生的技术应用场景，同时也是为了海量数据备份而诞生的；在数据库中实现克隆功能应用有两种方式：本地克隆（Local Cloning）：启动克隆操作的MySQL数据库服务器实例中的数据，将会克隆到同服务器或同节点上的一个目录里； 1669564772486 远程克隆（Remote Cloning）：默认情况下，远程克隆操作会删除接受者（recipient）数据目录中的数据，并将其替换为捐赠者（donor）的克隆数据；也可以将数据克隆到接受者的其他目录中，以避免删除现有数据；（属于可选操作）；主要用于实现数据远程的快速热迁移操作，在迁移过程中，除了DDL操作情况，其他操作都不会出现阻塞情况；还可以利用远程克隆技术，实现快速构建数据库的主从架构环境，实现主从数据信息快速复制同步； 1669564937916

1.13.2 数据库克隆原理说明

在进行数据库克隆操作时，会经历几个重要的过程或步骤：

01 Page copy：

在进行数据页复制操作时，会涉及到两个操作动作：开启redo archiving功能，从当前点开始存储新增的redo_log，这样从当前位置点开始所有的增量修改都不会丢失；同时上一步在page track的page被发送到目标端，确保当前位置点之前所有做的变更一定发送到目标端；关于redo archiving实际上这是官方早就存在的功能，主要用于官方的企业级备份工具，clone利用了该特性来维持记录增量产生的redo 在开始克隆前会做一次checkpoint；对于redo archiving功能应用，会开启一个后台线程log_archiver_thread()来做日志归档; 当有新的写入时(notify_about_advanced_write_lsn)，也会通知线程去进行归档，当arch_log_sys处于活跃状态时，线程会控制日志写入以避免未归档的日志被覆盖（log_write_wait_on_archiver）,注意如果log_write等待时间过长的话， archive任务会被中断掉；

02 Redo copy：

停止redo archiving功能，所有归档的日志被发送到目标端，这些日志包含了从page copy阶段开始到现在的所有日志；另外可能还需要记下当前的复制点，例如：最后一个事务提交时的binlog位置点或者gtid信息，在系统页中可以找到； 03 Done：目标端重启实例，通过crash recover y将redo log应用上去；克隆原理过程分析参考链接：https://zhuanlan.zhihu.com/p/437760913

说明：整个克隆过程都会以事件信息记录，可以很清晰的看到克隆的流程，如果克隆过程中断，也会以追加方式进行继续克隆；

在进行克隆功能应用时，也是存在一些限制性操作的：（结合官方列出的限制）对于MySQL 8.0.27之前版本，在进行克隆操作期间，是不允许在捐赠者和接受者上进行DDL操作，包括：truncate table操作；

对于MySQL 8.0.27之后版本，在捐赠者上默认允许并发DDL操作，对于捐赠者上并发DDL的支持由clone_block_DDL变量控制；对于不同版本的MySQL数据库实例之间，是不能进行克隆操作的。对于捐赠者和接受者必须是确切相同数据库服务版本；例如：你不能克隆数据在between MySQL 5.7 and MySQL 8.0, or between MySQL 8.0.19 and MySQL 8.0.20；这个克隆功能只支持在数据库8.0.17版本或之后的版本参考官方链接说明：https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-limitations.html

1.13.3 数据库克隆功能实践

实现本地克隆操作过程：克隆需求：实现快速创建和源数据库服务一模一样的多实例服务程序；克隆操作步骤01：加载克隆插件信息

克隆操作步骤02：创建克隆专用用户

克隆操作步骤03：进行本地克隆操作

克隆操作步骤04：观测本地克隆状态（另开窗口使用管理员用户查看）

克隆操作步骤05：实现克隆日志观测

1
2
# 进行克隆插件加载配置
mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';

或者

1
[mysqld]

plugin-load-add=mysql_clone.so clone=FORCE_PLUS_PERMANENT

 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> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'clone';
+------------------+----------------------+
| PLUGIN_NAME      | PLUGIN_STATUS        |
+------------------+----------------------+
| clone            | ACTIVE               |
+------------------+----------------------+
1 row in set (0.00 sec)
mysql> create user clone_user@'%' identified by 'password';
mysql> grant backup_admin on *.* to 'clone_user';
-- backup_admin权限是mysql 8.0才有的备份导出的权限；
[root@xiaoQ-01 ~]# mkdir -p /data/test
[root@xiaoQ-01 ~]# chown -R mysql.mysql /data/
[root@xiaoQ-01 ~]# mysql -uclone_user -ppassword
mysql> clone local data director y = '/data/test/clonedir';
-- 完成本地数据库目录的克隆操作，如果出现异常需要删除克隆目录，在进行重新克隆操作
mysql>  select stage,state,end_time from performance_schema.clone_progress;
+---------------+----------------+-----------------------------------+
| stage         | state          | end_time                          |
+---------------+----------------+-----------------------------------+
| DROP DATA     | Completed      | 2022-11-28 01:11:17.715901        |
| FILE COPY     | Completed      | 2022-11-28 01:11:17.752819        |
| PAGE COPY     | Completed      | 2022-11-28 01:11:17.756830        |
| REDO COPY     | Completed      | 2022-11-28 01:11:17.757802        |
| FILE SYNC     | Completed      | 2022-11-28 01:11:17.912679        |
| RESTART       | Not Started    | NULL                              |
| RECOVERY      | Not Started    | NULL                              |
+---------------+----------------+-----------------------------------+
7 rows in set (0.00 sec)
-- 当克隆数据量比较大的时候，可以使用此SQL语句进行克隆状态查看
mysql>  set global log_error_verbosity=3;
[root@xiaoQ-01 ~]# tail -f db01.err

clone local data director y = ‘/data/test/clonedir’

克隆操作步骤06：启动运行克隆实例

实现远程克隆操作过程：在进行实现远程克隆操作步骤之前，可以利用虚拟软件再次克隆出一台新的数据库8.0版本的服务器主机；克隆操作步骤01：克隆操作环境准备

克隆操作步骤02：加载克隆插件信息

克隆操作步骤03：创建克隆专用用户

说明：可以在克隆捐赠者主机上和接收者主机上均创建两个用户信息，防止克隆同步数据后，接收者主机上不再含有接收用户信息；

克隆操作步骤04：进行远程克隆操作

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
[root@xiaoQ-01 ~]# mysqld_safe --datadir=/data/test/clonedir --port=3333 --socket=/tmp/mysql3333.sock --user=mysql --mysqlx=off &
[root@xiaoQ-01 ~]# netstat -lntup|grep 3333
tcp6       0      0 :::3333                 :::*                    LISTEN      52674/mysqld
# 核实查看克隆后数据库信息
[root@xiaoQ-01 ~]# mysql -uroot -p123456 -S /tmp/mysql3333.sock
mysql>  show databases;
# 在克隆接收者主机上清理数据库服务环境：
[root@xiaoQ-02 ~]# pkill mysqld
[root@xiaoQ-02 ~]# rm -rf /data/3306/data/*
[root@xiaoQ-02 ~]# rm -rf /data/3306/binlog /*
[root@xiaoQ-02 ~]# rm -rf /data/3306/logs/*
# 在克隆接收者主机上进行实例初始化操作：
[root@xiaoQ-02 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
[root@xiaoQ-02 ~]# vim /etc/my.cnf
[mysqld]
ser ver_id=16
-- 修改克隆接收者主机上的ser ver_id的配置信息
# 在克隆接收者主机上进行实例的运行操作：
[root@xiaoQ-02 ~]# /etc/ init.d/mysqld start
# 进行克隆插件加载配置
mysql> INSTALL PLUGIN clone SONAME 'mysql_clone.so';

或者

1
[mysqld]

plugin-load-add=mysql_clone.so clone=FORCE_PLUS_PERMANENT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
-- 克隆插件信息需要在克隆主机的捐赠者和接受者上都进行安装
# 查看克隆插件加载情况
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'clone';
+------------------+----------------------+
| PLUGIN_NAME      | PLUGIN_STATUS        |
+------------------+----------------------+
| clone            | ACTIVE               |
+------------------+----------------------+
1 row in set (0.00 sec)
-- 克隆插件信息需要在克隆主机的捐赠者和接受者上都进行安装后确认
# 在克隆捐赠者主机上进行授权（数据库01主机上设置）
mysql> create user test_jz@'%' identified by 'password';
mysql> grant backup_admin on *.* to test_jz@'%' ;
-- backup_admin权限是mysql 8.0才有的备份导出的权限；
# 在克隆接收者主机上进行授权（数据库02主机上设置）
mysql> create user test_js@'%' identified by 'password';
mysql> grant clone_admin on *.* to test_js@'%' ;
-- clone_admin权限是mysql 8.0才有的克隆同步数据的权限；

所在位置线程名称作用说明主库涉及线程

1
binlog dump thread

用于将主库binlog日志信息进行传输投递的线程 · 可以实现与从库的信息交互 · 可以监控二进制日志的变化 · 可以进行二进制日志的投递从库涉及线程 slave IO thread · 可以用于连接主数据库服务 · 可以实现与主库线程的交互 · 可以接收和存储二进制日志（接收的二进制日志会存储在中继日志中）

slave SQL thread · 可以解析执行中继日志信息

克隆操作步骤05：观测本地克隆状态

1.14 数据库服务主从架构

1.14.1 数据库服务主从复制概述

MySQL数据库服务从3.23版本就开始提供复制的功能，复制是指将主数据库的DDL和DML操作语句通过二进制日志传到复制服务器上；然后在从库上(复制服务器)对这些日志重新执行(也叫重做)，从而使得从库和主库的数据保持同步； MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他服务器的主库，实现链状的复制； MySQL复制的优点主要包含以下3个方面：如果主库出现问题，可以快速切换到从库提供服务；可以在从库上执行查询操作，降低主库的访问压力；可以在从库上执行备份操作，以避免备份期间影响主库的服务；由于MySQL实现的是异步的复制，所以主从库之间存在一定的差距，在从库上进行的查询操作需要考虑到这些数据的差异，一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从库查询，实时性要求高的数据仍然需要从主数据库获得；

1.14.2 数据库服务主从复制原理

复制原理涉及线程在进行主从数据复制时，是依靠相应线程信息来完成数据复制同步操作的，具体涉及到的线程信息如下：

主库线程信息查看：binlog dump 可以通过show processlist命令在主库上查看binlog dump线程，从binlog dump线程的状态可以看到，mysql的复制是主库主动推送日志到从库去的，是属于推日志的方式来做同步； 1669833334639

说明：如果是一主多从的架构，将会看见多个binlog dump线程信息，实现对多个从库的日志信息投递；

从库线程信息查看：slave io/slave sql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 在克隆接收者主机上进行设置
mysql> set global clone_valid_donor_list='192.168.30.101:3306';
-- 设置克隆同步数据的信任列表
# 在克隆接收者主机上进行克隆
[root@xiaoq-02 ~]# mysql -utest_js -ppassword -h192.168.30.102 -P3306
mysql> clone instance from test_jz@'192.168.30.101':3306 identified by 'password';
-- 在接收者主机上实现远程克隆操作
mysql>  select stage,state,end_time from performance_schema.clone_progress;
+---------------+----------------+-----------------------------------+
| stage         | state          | end_time                          |
+---------------+----------------+-----------------------------------+
| DROP DATA     | Completed      | 2022-11-29 00:15:34.002378        |
| FILE COPY     | Completed      | 2022-11-29 00:15:35.218397        |
| PAGE COPY     | Completed      | 2022-11-29 00:15:35.225659        |
| REDO COPY     | Completed      | 2022-11-29 00:15:35.229777        |
| FILE SYNC     | Completed      | 2022-11-29 00:15:35.773431        |
| RESTART       | Completed      | 2022-11-29 00:15:39.189607        |
| RECOVERY      | Completed      | 2022-11-29 00:15:39.978301        |
+---------------+----------------+-----------------------------------+
7 rows in set (0.00 sec)
-- 当克隆数据量比较大的时候，可以使用此SQL语句进行克隆状态查看，在克隆接收者主机上进行查看
mysql> show databases;
-- 此时克隆接收者主机上查看到的数据信息，与克隆捐赠者主机上查看到的数据信息一致，即远程克隆操作完成

所在位置文件信息解释说明主库涉及文件

1
binlog

可以利用二进制日志信息变化，实现主从数据库的异步方式同步从库涉及文件

1
relaylog

可以利用中继日志进行临时存储或接收主库投递的二进制日志信息，日志信息会定期自动清理

master.info 可以存储主库相关信息（主库地址端口用户密码二进制位置点-已经获取的）（和IO线程相关）

1
relay-log.info

可以存储SQL线程回放过的日志信息（与SQL线程相关）在从库上通过show processlist可以看到I/O线程和SQL线程; I/O线程等待主库上的binlog dump线程发送事件并更新到中继日志relay log；用此线程和主库建立连接，并与主库的dump thread线程进行交互，以及接收和存储主库推送过来的binlog日志信息到relay log； SQL线程读取中继日志relay log并应用变更到数据库；用此线程实现回放relay log中的日志信息，实现对从库的SQL语句操作；从MySQL的复制流程可以得知MySQL的复制是异步的，从库上的数据和主库存在一定的延时； 1669834469716

复制原理涉及文件在进行主从数据复制时，是依靠相应文件信息来完成数据复制过程中的数据保存的，具体涉及到的文件信息如下：从库上的后两个文件（matser/relay-log）已经不以文件方式保存在数据库服务的数据目录中，而是以表格形式直接存储在数据库内部：

复制原理过程详述（Classic Replication） MySQL的复制原理大致如下：

在从库上执行change master to命令，将主库连接信息和binlog位置信息写入master.info文件或 slave_master_info表中；
在从库上执行start slave命令，用于启动从库的IO和SQL线程功能；
从库IO线程主要用于读取主库连接信息，实现和主库建立连接，从而使主库派生出binlog dump线程（自动监控binlog）；
从库IO线程根据change master to命令所定义的数据位置点，获取最新的binlog日志信息
mysql主库在事务提交时会把数据变更为事件Events记录在二进制日志文件binlog中； mysql主库上的sync_binlog参数控制binlog日志刷新到磁盘；
binlog dump线程会截取binlog日志并投递其日志给从库IO线程，此时主库并不关心投递日志信息的结果；
此时从库IO线程接收binlog投递信息（缓存），随之会立即更新master.info文件或 slave_master_info数据表信息；
从库缓存的binlog日志数据信息会被写入到relaylog中继日志中；主库推送二进制日志文件binlog中的事件到从库的中继日志relay log，之后从库根据中继日志relay log重做数据变更操作，
从库SQL线程将会读取relaylog.info文件或者slave_relay_log_info数据表中信息，获取上次数据回放同步位置点；随之继续向后回放同步数据，一旦回放同步数据完成后，再次更新relay.info或slave_relay_log_info数据表信息；
在从库中回放过的relaylog日志信息，会被relay_log_purge线程定期删除处理这些日志；
通过逻辑复制以此来达到主库和从库的数据一致；

MySQL通过3个线程来完成主从库间的数据复制：其中binlog dump线程跑在主库上，I/O线程和SQL线程跑在从库上；当在从库上启动复制（START SLAVE）时，首先创建I/O线程连接主库，主库随后创建binlog dump线程读取数据库事件；并发送给I/O线程，I/O线程获取到事件数据后更新到从库的中继日志relay log中去，之后从库上的SQL线程读取中继日志relay log，根据中继日志中的更新的数据库事件并应用； 1669910905795 简述：在两台以上节点进行复制，通过binlog日志实现同步关系，并且采用异步方式进行数据同步；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
mysql> show variables like '%info%';
+---------------------------------+----------------+
| Variable_name                   | Value          |
+---------------------------------+----------------+
| master_info_repositor y         | TABLE          |
| relay_log_info_repositor y      | TABLE          |
+---------------------------------+----------------+
8 rows in set (0.00 sec)
mysql> use mysql;
mysql> show tables;
+----------------------------------+
| Tables_in_mysql                  |
+----------------------------------+
| slave_master_info                |
| slave_relay_log_info             |
+----------------------------------+
37 rows in set (0.01 sec)
-- 可以获取slave_master_info表中的信息，就是change master to配置指定的相关信息；
-- 可以获取 slave_relay_log_info表中的信息，就是SQL线程已经回放过的日志信息

1.14.3 数据库服务主从复制实践

01 复制环境搭建过程：

操作步骤01：需要准备两台以上数据库实例

操作步骤02：主数据库二进制日志功能开启

操作步骤03：核实主从复制主机的信息情况

 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
# 数据库节点01服务已经构建完毕
[root@xiaoQ-01 ~]# mysqld_safe --defaults-file=/data/3307/data/my.cnf &
[root@xiaoQ-01 ~]# netstat -lntp|grep 3307
tcp6       0      0 :::3307                 :::*                    LISTEN      114722/mysqld
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3307.sock
# 数据库节点02服务已经构建完毕
[root@xiaoQ-01 ~]# mysqld_safe --defaults-file=/data/3308/data/my.cnf &
[root@xiaoQ-01 ~]# netstat -lntp|grep 3308
tcp6       0      0 :::3308                 :::*                    LISTEN      122865/mysqld
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3308.sock
# 主数据库需要开启binlog日志功能
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------+
| Variable_name                   | Value                                |
+---------------------------------+--------------------------------------+
| log_bin                         | ON                                   |
| log_bin_basename                | /data/3307/data/binlog               |
| log_bin_index                   | /data/3307/data/binlog.index         |
| sql_log_bin                     | ON                                   |
+---------------------------------+--------------------------------------+
6 rows in set (0.01 sec)
-- 核实确认binlog日志功能在主库上已经开启
# 确认多个复制节点的服务标识不同（ser ver id/ser ver_uuid）
mysql> set global ser ver_id=7;
Quer y OK, 0 rows affected (0.00 sec)
mysql> select @@ser ver_id;
+-------------+
| @@ser ver_i |
+-------------+
| 7           |
+-------------+
1 row in set (0.00 sec)
mysql> select @@ser ver_uuid;
+--------------------------------------+
| @@ser ver_uuid                       |
+--------------------------------------+
| 949efd68-6420-11ed-b1ad-000c2996c4f5 |
+--------------------------------------+
1 row in set (0.00 sec)
-- 主库上ser ver_id ser ver_uuid信息（3307节点做检查）
mysql> set global ser ver_id=8;
Quer y OK, 0 rows affected (0.00 sec)
mysql> select @@ser ver_id;
+-------------+
| @@ser ver_i |
+-------------+
| 8           |
+-------------+
1 row in set (0.00 sec)
mysql> select @@ser ver_uuid;
+--------------------------------------+
| @@ser ver_uuid                       |
+--------------------------------------+
| ed6c1c6f-6f33-11ed-b630-000c2961cd06 |

操作步骤05：创建主从数据同步的用户信息

操作步骤06：进行从库部分数据信息的同步

操作步骤07：配置主从节点数据复制的信息

操作步骤08：激活主从节点数据复制的线程

1
2
3
4
5
6
+--------------------------------------+
1 row in set (0.00 sec)
-- 从库上ser ver_id ser ver_uuid信息（3308节点做检查）
-- 根据服务标识信息的不同，可以用于在进行主从复制时，辨别节点之间的关系
# 确认多个复制节点的时间信息同步
[root@xiaoQ-01 ~]# date

2022年 11月 30日星期三 23:44:37 CST

1
[root@xiaoQ-02 ~]# date

2022年 11月 30日星期三 23:44:52 CST

1
2
3
-- 核心确认主从节点时间信息同步
# 确认多个复制节点的版本信息一致
[root@xiaoQ-01 ~]# mysql -V

mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Ser ver - GPL)

1
[root@xiaoQ-02 ~]# mysql -V

mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Ser ver - GPL)

 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
-- 部分实际应用场景中可以支持不一致，但是复制源端可以是低版本，复制目标端可以是高版本，反之不行；
# 在主库上创建复制同步数据用户
mysql> create user repl@'192.168.30.%' identified with mysql_native_password by '123456';
Quer y OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to repl@'192.168.30.%';
Quer y OK, 0 rows affected (0.00 sec)
-- 从库会利用主库上的同步数据用户，进行数据同步传输操作
# 可以将主库上的部分数据在从库上先进行同步
[root@xiaoQ-01 ~]# mysqldump -uroot -A -S /tmp/mysql3307.sock --master-data=2 --single-transaction >/tmp/full.sql
-- 在3307主库上进行数据的全备（模拟企业环境的历史数据全备）
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3308.sock
mysql> source /tmp/full.sql;
-- 在3308从库上进行数据的恢复（模拟企业环境的历史数据恢复）
-- 将原有主机的数据先备份，然后从库中进行恢复一部分数据，随后再进行数据信息同步追加
-- 可以利用同步方式有很多：mysqldump xtrabackup clone_plugin
# 设置从库连接主库信息，定义从库连接主库同步位置点自动复制
mysql> help change master to
-- 获取连接主库，以及定义同步位置点的数据库配置模板信息
[root@xiaoQ-01 ~]# vim /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=681;
-- 通过备份文件获取同步位置点信息
mysql> CHANGE MASTER TO

MASTER_HOST='192.168.30.101',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='binlog.000003',
MASTER_LOG_POS=681,

MASTER_CONNECT_RETRY=10;
-- 以上配置主从同步信息在从库进行执行；
# 利用相应线程实现主从数据库的数据同步复制
mysql> start slave;
-- 在从库上激活数据复制同步功能
# 若此时数据同步失败可以重新开启同步功能
mysql> stop slave;
mysql> reset slave all;
-- 在从库上重置数据复制同步功能，重新配置change master to信息，然后重新激活同步复制功能；
# 进行核实主从同步功能是否实现

02 复制数据过程监控：

实现主从复制数据监控方法：方法01：利用数据库自带命令实现监控：在从库上可以使用SQL语句，进行主从复制数据情况的监控：监控语句输出信息解读：

1
2
3
4
5
6
7
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3307.sock
mysql> create database xiaoq;
-- 在主库模拟创建数据信息
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3308.sock
mysql> show databases;
-- 在从库模拟查看数据信息（确认是否同步数据）
mysql> show slave status\G

*************************** 1. row ***************************

1
2
3
4
5
Slave_IO_State: Waiting for source to send event

Master_Host: 192.168.30.101
Master_User : repl
Master_Port: 3307

Connect_Retr y: 10

1
Master_Log_File: binlog.000003

Read_Master_Log_Pos: 869

1
2
3
4
5
6
7
8
Relay_Log_File: xiaoQ-01-relay-bin.000002
Relay_Log_Pos: 509
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 从库上查看数据同步状态情况，看到上面的两个Yes信息，就表示主从数据同步功能设置成功了
mysql> show slave status\G
-- 在主库上也可以实现数据复制监控，但是一般情况下更关注的是从库；

内容分类输出内容解释说明主库的复制信息

1
Master_Host: 192.168.30.101

表示连接主库地址信息

利于IO线程工作方面

1
Master_User : repl

表示连接主库用户信息

1
Master_Port: 3307

表示连接主库端口信息

Connect_Retr y: 10 表示连接主库重试间隔

1
Master_Log_File: binlog.000004

表示主从同步日志信息

Read_Master_Log_Pos: 156 表示主从同步位置信息从库的回放信息

1
Relay_Log_File: xiaoQ-01-relay-bin.000005

表示中继日志回放文件信息

利于SQL线程工作方面

1
Relay_Log_Pos: 365

表示中继日志回放位置信息

1
Relay_Master_Log_File: binlog.000004

表示对应主库日志文件信息可以用于帮助判断主从延时的日志量

Exec_Master_Log_Pos: 156 表示对应主库日志位置信息可以用于帮助判断主从延时的日志量从库的线程信息

1
Slave_IO_Running: Yes

表示从库同步数据时-IO线程状态

利于判断同步线程情况

1
Slave_SQL_Running: Yes

表示从库同步数据时-SQL线程状态

Last_IO_Errno: 0 表示从库IO线程异常错误代码

Last_IO_Error : 表示从库IO线程异常错误原因

Last_SQL_Errno: 0 表示从库SQL线程异常错误代码

Last_SQL_Error : 表示从库SQL线程异常错误原因过滤复制的相关信息 Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

主从复制的延时情况

1
Seconds_Behind_Master : 0

表示主从之间延时秒数信息延时从库的状态情况 SQL_Delay: 0 表示延时同步时间间隔情况

SQL_Remaining_Delay: NULL 表示最近事件延时剩余时间主从GTID复制状态情况 Retrieved_Gtid_Set:

Executed_Gtid_Set:

方法02：利用第三方专业工具实现监控：可以使用的专业监控工具：pt-table-checksum、pt-table-sync、pt-heartbeat

方法03：利用第三方开源平台实现监控：可以使用的开源平台介绍：orch （主从拓扑监控管理）

03 复制故障情况分析：

当出现主从数据库数据不同步时，就可以理解为出现了复数据制故障，一般在从库上分析故障原因，主要是从库上的两个线程出现问题；从库线程异常分析-IO 确认线程是否出现问题：

确认线程相关工作职能：（从底层角度分析IO异常原因） ① IO线程主要用于连接主数据库服务；以上工作职能失败就会导致线程状态为：connecting 可能导致异常原因：

连接地址、端口、用户、密码信息不对可能会导致连接异常；
防火墙安全策略阻止连接建立、网络通讯配置异常影响连接建立；

1
2
3
# 确认线程运行状态
Slave_IO_Running: Yes
-- 常见异常状态：connecting、no，具体问题情况分析需要查看以下信息；

Last_IO_Errno: 0 Last_IO_Error :

1
-- 根据以上错误编码和错误信息说明，可以判断产生IO线程异常的原因；

到达数据库服务连接数上限，造成主从连接产生异常；线程异常情况排查：使用主从复制专用用户进行手工连接测试，核实主从复制用户是否可以远程连接登录数据库服务

线程异常情况模拟：

1
2
3
4
5
6
# 正常远程连接测试
[root@xiaoQ-01 ~]# mysql -urepl -p123456 -h192.168.30.101 -P3307
mysql>
-- 以上测试情况成功；
# 异常远程连接测试
[root@xiaoQ-01 ~]# mysql -urepl1 -p123456 -h192.168.30.101 -P3307

mysql: [Warning] Using a password on the command line interface can be insecure.

1
2
ERROR 1045 (28000): Access denied for user 'repl1'@'xiaoQ-01' (using password: YES)
[root@xiaoQ-01 ~]# mysql -urepl -p123123 -h192.168.30.101 -P3307

mysql: [Warning] Using a password on the command line interface can be insecure.

1
2
3
ERROR 1045 (28000): Access denied for user 'repl'@'xiaoQ-01' (using password: YES)
-- 以上测试情况失败，输出信息表示建立远程连接的用户名称或密码信息异常；
[root@xiaoQ-01 ~]# mysql -urepl -p123456 -h192.168.30.102 -P3307

mysql: [Warning] Using a password on the command line interface can be insecure.

1
2
3
ERROR 2003 (HY000): Can't connect to MySQL ser ver on '192.168.30.102:3307' (113)
-- 异常测试情况失败，输出信息表示建立远程连接的地址信息异常；
[root@xiaoQ-01 ~]# mysql -urepl -p123456 -h192.168.30.101 -P3300

mysql: [Warning] Using a password on the command line interface can be insecure.

 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
ERROR 2003 (HY000): Can't connect to MySQL ser ver on '192.168.30.101:3300' (111)
-- 异常测试情况失败，输出信息表示建立远程连接的端口信息异常；
# 在从库上临时取消主从关系
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3308.sock
mysql> stop slave;
mysql> reset slave all;
-- 此次表示主从关系已经解除；
# 在从库上模拟连接错误情况
# 模拟用户名称或者密码信息错误
mysql> CHANGE MASTER TO

MASTER_HOST='192.168.30.101',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='binlog.000003',
MASTER_LOG_POS=681,

MASTER_CONNECT_RETRY=10;
-- 模拟从库连接主库时，密码信息出现错误；
mysql> start slave;
-- 再次启动主从同步功能
# 模拟连接地址信息输入错误
CHANGE MASTER TO

MASTER_HOST='192.168.30.103',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='binlog.000003',
MASTER_LOG_POS=681,

MASTER_CONNECT_RETRY=10;
-- 模拟从库连接主库时，地址信息出现错误；
mysql> start slave;
-- 再次启动主从同步功能
# 模拟连接会话超过上限情况
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3307.sock
-- 在主库上进行连接上限配置
mysql> select @@max_connections;
+---------------------------+
| @@max_connections         |
+---------------------------+
| 151                       |
+---------------------------+
1 row in set (0.00 sec)
-- 连接会话数默认为支持151人同时并发连接，超过上限数值后随即拒绝建立连接，但其中还给管理员预留了一个；
mysql> set global max_connections=3;
-- 模拟调整连接数信息
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3308.sock

线程异常情况处理：

② IO线程主要用于进行日志信息请求，以及接收日志信息，并将日志信息进行保存（落地）；以上工作职能失败就会导致线程状态为：no 可能导致异常原因：

IO线程在请求日志信息失败，有可能日志信息被无意清理了；
IO线程在请求日志信息失败，有可能主从配置的标识信息重复冲突了；线程异常情况模拟：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
mysql> stop slave;
-- 停止从库建立连接
[root@xiaoQ-01 ~]# mysql -uroot  -S /tmp/mysql3307.sock
-- 模拟将连接跑满（重复建立4个连接）
[root@xiaoQ-01 ~]#  mysql -uroot  -S /tmp/mysql3307.sock
ERROR 1040 (HY000): Too many connections
-- 再建立新的连接报错
mysql> start slave;
-- 从库重新建立连接
# 查看主从同步状态情况
# 用户名称或者密码错误异常情况查看：
mysql> show slave status\G;

*************************** 1. row ***************************

1
2
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes

Last_IO_Errno: 1045 Last_IO_Error : error connecting to master ‘repl@192.168.30.101:3307’ - retr y-time: 10 retries: 9 message: Access denied for user ‘repl’@‘xiaoQ-01’ (using password: YES)

1
2
3
-- IO线程运行状态为connecting，并且显示IO线程错误码，以及IO线程错误原因说明；
# 地址信息输入异常情况查看：
mysql> show slave status\G;

*************************** 1. row ***************************

1
2
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes

Last_IO_Errno: 2003 Last_IO_Error : error connecting to master ‘repl@192.168.30.103:3307’ - retr y-time: 10 retries: 1 message: Can’t connect to MySQL ser ver on ‘192.168.30.103:3307’ (113)

1
2
3
-- IO线程运行状态为connecting，并且显示IO线程错误码，以及IO线程错误原因说明；
# 连接上限超出限制情况查看：
mysql> show slave status\G

*************************** 1. row ***************************

1
2
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes

Last_IO_Errno: 1040 Last_IO_Error : error connecting to master ‘repl@192.168.30.101:3307’ - retr y-time: 10 retries: 7 message: Too many connections

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
-- IO线程运行状态为connecting，并且显示IO线程错误码，以及IO线程错误原因说明；
# 主从搭建过程出现异常：
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3308.sock
mysql> stop slave;
mysql> reset slave all;
-- 对主从关系进行重置；
CHANGE MASTER TO

MASTER_HOST='192.168.30.101',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='binlog.000003',
MASTER_LOG_POS=681,

MASTER_CONNECT_RETRY=10;
-- 模拟从库重新连接主库；
mysql> drop database xiaoq;
mysql> start slave;
-- 再次启动主从同步功能
mysql> show slave status\G

*************************** 1. row ***************************

1
2
3
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 此时主从异常情况修复；

在进行异常情况模拟前，核实确认好主从同步状态是否正常；

线程异常情况处理：

1
2
3
# 异常情况模拟-01：主库日志信息被清理了
# 在从库上核实同步状态情况
mysql> show slave status\G

*************************** 1. row ***************************

1
2
3
4
5
Slave_IO_State: Waiting for source to send event

Master_Host: 192.168.30.101
Master_User : repl
Master_Port: 3307

Connect_Retr y: 10

1
Master_Log_File: binlog.000004

Read_Master_Log_Pos: 341

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
Relay_Log_File: xiaoQ-01-relay-bin.000005
Relay_Log_Pos: 506
# 在主库上清理日志文件信息
mysql> reset master ;
Quer y OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+-----------+-------------------+-----------------------+-------------------------+
| File             | Position  | Binlog_Do_DB      | Binlog_Ignore_DB      | Executed_Gtid_Set       |
+------------------+-----------+-------------------+-----------------------+-------------------------+
| binlog.000001    | 156       |                   |                       |                         |
+------------------+-----------+-------------------+-----------------------+-------------------------+
1 row in set (0.00 sec)
-- 模拟主库已经将日志信息清理
# 查看主从同步状态情况
mysql> show slave status\G

*************************** 1. row ***************************

1
2
Slave_IO_Running: No
Slave_SQL_Running: Yes

Last_IO_Errno: 13114 Last_IO_Error : Got fatal error 1236 from master when reading data from binar y log: ‘could not find next log; the first event ‘binlog.000004’ at 156, the last event read from ‘./binlog.000004’ at 341, the last byte read from ‘./binlog.000004’ at 341.’

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- IO线程运行状态为no，并且显示IO线程错误码，以及IO线程错误原因说明；
# 异常情况模拟-02：主从ser ver_id ser ver_uuid信息配置相同了
# 查看主从服务ser ver_id信息
mysql> select @@ser ver_id;
+-----------------+
| @@ser ver_id    |
+-----------------+
| 7               |
+-----------------+
1 row in set (0.00 sec)
mysql> select @@ser ver_id;
+-----------------+
| @@ser ver_id    |
+-----------------+
| 8               |
+-----------------+
1 row in set (0.00 sec)
-- 核实确认主库ser ver_id为7，从库ser ver_id为8；
# 模拟修改从服务ser ver_id信息
mysql> stop slave;
mysql> set global ser ver_id=7;
mysql> start slave;
# 查看主从同步状态情况
mysql> show slave status\G

*************************** 1. row ***************************

1
2
Slave_IO_Running: No
Slave_SQL_Running: Yes

Last_IO_Errno: 13117 Last_IO_Error : Fatal error : The slave I/O thread stops because master and slave have equal MySQL ser ver ids; these ids must be different for replication to work (or the –replicate-same-ser ver-id option must be used on slave but this does not always make sense; please check the manual before using it).

1
2
-- IO线程运行状态为no，并且显示IO线程错误码，以及IO线程错误原因说明；
# 主从搭建过程出现异常修复：

从库线程异常分析-SQL 确认线程是否出现问题：

确认线程相关工作职能：（从底层角度分析SQL异常原因） ① SQL线程主要用于回放执行relay log日志信息，即执行相关数据同步SQL语句信息；以上工作职能失败就会导致线程状态为：no（研究SQL线程故障，实质就是在研究SQL语句为什么无法执行）可能导致异常原因：（从库数据或设置异常导致）

创建的对象已经存在，涉及到的对象可能有库、表、用户、索引…;
插入（insert）的操作对象有异常、修改（update alter）的操作对象有异常、删除（delete drop）的操作对象有异常；
由于数据库设置的约束信息，与执行的SQL语句产生冲突问题；
在数据库不同版本之间进行数据同步时，可能出现配置冲突问题（比如：5.6可以识别时间为0字段，5.7不能识别时间为0字段）可能造成异常情况：
在进行主从配置时，指定的位置点出现错误（change master to）；
在进行主从配置前，从库被写入相应的数据信息了，与主库同步数据产生冲突（误连接从库进行操作了）；
在从库工作繁忙状态时，从库宕机了，业务恢复后可能出现异步同步数据错乱（主库操作创建表操作没同步，同步了插入表操作）；
在进行主从切换时（假设进行的是手工切换），没有正确操作锁定源主库和binlog日志信息；（画图说明）导致切换前主库数据没有完全同步，切换后从库数据（原主库）比主库数据（原从库）信息更全；
在应用数据库双主结构时，没有正确使用（经常导致相互同步数据，主键或唯一键冲突）（画图说明）若企业创建必须使用双主架构，实现双写机制，可以使用全局序列机制，实现主键或唯一键的统一分配；线程异常情况模拟：在进行异常情况模拟前，核实确认好主从同步状态是否正常；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3308.sock
mysql> stop slave;
mysql> reset slave all;
-- 对主从关系进行重置；
CHANGE MASTER TO

MASTER_HOST='192.168.30.101',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='binlog.000001',
MASTER_LOG_POS=156,

MASTER_CONNECT_RETRY=10;
-- 模拟从库重新连接主库；
mysql> start slave;
-- 再次启动主从同步功能
mysql> show slave status\G

*************************** 1. row ***************************

1
2
3
4
5
6
7
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 此时主从异常情况修复；
# 主从搭建过程出现异常修复：
mysql> set global ser ver_id=8;
mysql> stop slave;
mysql> start slave;

*************************** 1. row ***************************

1
2
3
4
5
6
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 此时主从异常情况修复；
# 确认线程运行状态
Slave_SQL_Running: Yes
-- 常见异常状态：no，具体问题情况分析需要查看以下信息；

Last_SQL_Errno: 0 Last_SQL_Error :

1
2
3
4
-- 根据以上错误编码和错误信息说明，可以判断产生SQL线程异常的原因；
# 异常情况模拟：模拟误连接从库做写入操作，与主库产生冲突
# 在从库上核实同步状态情况
mysql> show slave status\G

*************************** 1. row ***************************

1
Slave_IO_State: Waiting for source to send event

线程异常处理原则：

主从同步出现数据同步异常，一切数据信息以主库为准；
尽量不要使用双主架构，避免数据信息双写，造成的数据同步异常；
主从同步数据信息时候，可以设置从库只读，避免从库误写入冲突；

1
2
3
Master_Host: 192.168.30.101
Master_User : repl
Master_Port: 3307

Connect_Retr y: 10

1
Master_Log_File: binlog.000001

Read_Master_Log_Pos: 338

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
Relay_Log_File: xiaoQ-01-relay-bin.000003
Relay_Log_Pos: 321
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 在从库上创建数据信息（模拟误连接从库执行操作情况）
mysql> create database test1;
Quer y OK, 1 row affected (0.00 sec)
# 在主库上创建数据信息（实现主库创建数据与从库一致）
mysql> create database test1;
Quer y OK, 1 row affected (0.00 sec)
# 查看主从同步状态情况
mysql> show slave status\G

*************************** 1. row ***************************

1
2
Slave_IO_Running: Yes
Slave_SQL_Running: No

Last_SQL_Errno: 1007 Last_SQL_Error : Error ‘Can’t create database ’test1’; database exists’ on quer y. Default database: ’test1’. Quer y: ‘create database test1’

1
2
3
4
-- SQL线程运行状态为no，并且显示SQL线程错误码，以及SQL线程错误原因说明；
# 从库出现SQL线程异常处理
# 处理方案01：将冲突操作进行回退
mysql> show slave status\G

*************************** 1. row ***************************

1
2
Slave_IO_Running: Yes
Slave_SQL_Running: No

Last_SQL_Errno: 1007 Last_SQL_Error : Error ‘Can’t create database ’test1’; database exists’ on quer y. Default database: ’test1’. Quer y: ‘create database test1’

1
2
3
4
5
6
-- 此时SQL线程出现异常，需要根据异常错误代码和错误原因描述处理问题；
mysql> drop database test1;
-- 删除从库产生冲突的数据库信息，从而实现冲突情况的回退机制；
mysql> start slave;
-- 冲突异常问题回退后，可以重新启动主从关系
mysql> show slave status\G

*************************** 1. row ***************************

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 主从关系已经恢复正常；
# 处理方案02：跳过主从同步异常错误，以从库数据为准（不建议使用，除非配合pt-checksum/pt-sync工具使用）
mysql> create database test2;
Quer y OK, 1 row affected (0.00 sec)
-- 从库创建数据test2，重新SQL线程异常情况
mysql> create database test2;
Quer y OK, 1 row affected (0.00 sec)
-- 主库创建数据test2，重新SQL线程异常情况
mysql> show slave status\G

*************************** 1. row ***************************

1
2
Slave_IO_Running: Yes
Slave_SQL_Running: No

Last_SQL_Errno: 1007 Last_SQL_Error : Error ‘Can’t create database ’test2’; database exists’ on quer y. Default database: ’test2’. Quer y: ‘create database test2’

1
2
3
-- 此时SQL线程出现异常，需要根据异常错误代码和错误原因描述处理问题；
mysql> stop slave;
Quer y OK, 0 rows affected, 1 warning (0.01 sec)

04 复制延时问题分析：

主从复制的延时问题主要描述的是：（在出现主从数据同步延时问题时，从库的线程还是能够正常工作运行的）在主库操作执行语句信息后，从库经过一段时间后才进行操作执行的同步；在主库操作执行语句信息后，从库经过一段时间后也没有进行相关的操作；

主从复制的延时问题造成的影响是：对于读写分离架构是依赖于主从同步数据环境的，主库作为写节点，从库作为读节点，延时严重会影响从库的读操作体验；对于高可用架构也是依赖于主从同步数据环境的，主库作为主节点，从库作为备节点，延时严重会影响主备的切换一致性；

主从复制的延时问题出现的原因是：外部因素导致的延时问题：

网络通讯不稳定，有带宽阻塞等情况，造成主从数据传输同步延时；
主从硬件差异大，从库磁盘性能较低，内存和CPU资源都不够充足；
主从配置区别大，从库配置没有优化，导致并发处理能力低于主库；（参考了解）主库因素导致的延时问题：
主要涉及Dump thread工作效率缓慢，可能是由于主库并发压力比较大；
主要涉及Dump thread工作效率缓慢，可能是由于从库数量比较多导致；
主要涉及Dump thread工作效率缓慢，主要由于线程本身串型工作方式；（利用组提交缓解此类问题-5.6开始 group commit ）主库本身可以并发多个事务运行，默认情况下主从同步Dump thread只有一个，只能采用串型方式传输事务日志信息；从库因素导致的延时问题：
从库产生延迟受SQL线程影响较大，由于线程本身串型工作方式导致；利用不同数据库并行执行事务操作，但是一个库有多张表情况，产生大量并发事务操作，依旧是串型的（5.6开始多SQL线程回放）利用logical_clock机制进行并发回放，由于组提交事务是没有冲突的，从库并行执行也不会产生冲突（5.7开始多SQL线程回放）根据日志内容信息，获取logical_clock机制的组提交标记信息：（事务级别并发）其他因素导致的延时问题：
由于数据库大事务产生的数据同步延时问题；（更新100W数据/尽量切割事务）
由于数据库锁冲突机制的数据同步延时问题；（资源被锁无法同步/隔离级别配置RR-锁冲突严重，可调整RC降低延时索引主从一致）
由于数据库过度追求安全配置也会导致同步延时问题（从库关闭双一参数）；

主从复制的延时问题监控的方式是：

1
2
3
4
5
6
7
mysql> set global sql_slave_skip_counter=1;
Quer y OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Quer y OK, 0 rows affected, 1 warning (0.01 sec)
-- 先停止主从同步关系，实现跳过错误提示步骤，最后重新建立主从连接；（一般都是配合工具进行修复）
-- 在主键冲突导致的问题，跳过错误时要非常小心，建议将跳过的错误的ID数值需要先抓取出来，否则可以会丢失操作事件
mysql> show slave status\G

*************************** 1. row ***************************

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 主从关系已经恢复正常；
# 处理方案03：可以设定跳过指定的错误编码
mysql> select @@slave_skip_errors;
+---------------------------+
| @@slave_skip_errors       |
+---------------------------+
| OFF                       |
+---------------------------+
1 row in set, 1 warning (0.00 sec)
-- 这种应用风险比较大，不建议生产环境使用
[root@xiaoQ-01 ~]# cd /data/3307/data/
[root@xiaoQ-01 data]# mysqlbinlog binlog.000001

#221204 10:27:02 ser ver id 7 end_log_pos 415 CRC32 0xd4ca0729 Anonymous_GTID last_committed=1 #221204 12:50:03 ser ver id 7 end_log_pos 603 CRC32 0x06629cba Anonymous_GTID last_committed=2 …省略部分信息…

1
2
-- 可以看到日志文件中，有大量last_commited信息，用于标记相同组提交的同步事件信息，并发执行是以事务为单位；
-- 可以看到日志文件中，会利用sequence_number信息，表示一个事务内执行操作顺序；

1.14.4 数据库服务主从复制扩展

01 主从复制扩展应用：延时从库

概念介绍说明：表示人为主动方式将一个从库进行配置，使从库可以按照指定的时间延时后，再进行和主库完成相应数据信息同步；功能作用说明：通常对于数据库服务中的数据信息产生损坏，可能有两方面因素造成：物理损坏：主机故障、磁盘异常、数据文件损坏…，可以利用传统主从复制方式，规避此类问题，利用从库替代主库工作任务；逻辑损坏：误删除操作（drop truncate delete），可以利用备份数据+binlog日志方式，可以实现数据信息的修复，但是代价比较高；利用延时从库同步功能，主要是对逻辑原因造成的数据损坏进行弥补修复，从而避免全备数据恢复业务产生的代价较高问题；当出现逻辑损坏操作时，可以利用延时从库的延时同步特性，将异常操作不做同步，将从库未做破坏的数据信息恢复到主库中；功能应用实践： ① 创建新的从库环境

1
mysql> show slave status\G

*************************** 1. row ***************************

1
2
3
Seconds_Behind_Master : 0
-- 表示主从之间延时秒数时间信息
Relay_Master_Log_File: binlog.000004

Exec_Master_Log_Pos: 156

 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
-- 在从库上利用 show slave status\G 获取binlog日志同步执行位置点
-- 在主库上利用 show master status 获取binlog日志同步生成位置点，与从库进行对比，即可判定是否出现主从延迟问题；
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3309.sock
mysql> set global ser ver_id=9;
Quer y OK, 0 rows affected (0.00 sec)
mysql> select @@ser ver_id;
+-------------+
| @@ser ver_i |
+-------------+
| 9           |
+-------------+
1 row in set (0.00 sec)
-- 调整从库ser ver_id信息，避免和主库产生冲突
# 可以将主库上的部分数据在从库上先进行同步
[root@xiaoQ-01 ~]# mysqldump -uroot -A -S /tmp/mysql3307.sock --master-data=2 --single-transaction >/tmp/full.sql
-- 在3307主库上进行数据的全备（模拟企业环境的历史数据全备）
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3309.sock
mysql> source /tmp/full.sql;
-- 在3309从库上进行数据的恢复（模拟企业环境的历史数据恢复）
-- 将原有主机的数据先备份，然后从库中进行恢复一部分数据，随后再进行数据信息同步追加
-- 可以利用同步方式有很多：mysqldump xtrabackup clone_plugin
# 设置从库连接主库信息，定义从库连接主库同步位置点自动复制
mysql> help change master to
-- 获取连接主库，以及定义同步位置点的数据库配置模板信息
[root@xiaoQ-01 ~]# vim /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=156;
-- 通过备份文件获取同步位置点信息
mysql> CHANGE MASTER TO

MASTER_HOST='192.168.30.101',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='binlog.000004',
MASTER_LOG_POS=156,

MASTER_CONNECT_RETRY=10;
-- 以上配置主从同步信息在从库进行执行；
# 利用相应线程实现主从数据库的数据同步复制
mysql> start slave;
-- 在从库上激活数据复制同步功能
# 进行核实主从同步功能是否实现
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3307.sock
mysql> create database xiaoh;
-- 在主库模拟创建数据信息
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql3307.sock
mysql> show databases;

事件序号操作语句解释说明 01 插入语句 insert 假设在09:59时，持续有插入操作行为，需要进行同步 02 删除语句 drop 假设在10:00时，产生了删除操作行为，需要避免同步

② 配置延时从库功能

③ 延时从库应用过程延时从库的应用思路分析：延时的根本效果是主库执行操作完成后，会经过指定的时间后，从库在执行主库曾经执行的操作；基于主从同步原理分析，延时同步效果是在SQL线程上进行控制实现的，并非在IO线程上进行控制实现的； SQL线程的延时控制机制，主要是需要识别同步操作任务的时间戳信息，根据时间戳和延时时间信息结合，判断相关任务是否同步执行；简述：基于主从同步原理，IO线程同步主库操作事件是持续同步的，只是SQL线程在进行事件信息回放时，进行了延时控制；企业应用延时从库事件模拟：企业异常情况处理过程说明： 1）网站页面需要挂维护页面进行说明； 2）从库服务关闭SQL线程，停止事件任务回放； 3）将从库出现故障前的数据信息，即由于延时配置没有执行的操作回放，到出现故障点的时刻停止回放；

1
2
-- 在从库模拟查看数据信息（确认是否同步数据）
mysql> show slave status\G

*************************** 1. row ***************************

1
2
3
4
5
Slave_IO_State: Waiting for source to send event

Master_Host: 192.168.30.101
Master_User : repl
Master_Port: 3307

Connect_Retr y: 10

1
Master_Log_File: binlog.000004

Read_Master_Log_Pos: 347

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
Relay_Log_File: xiaoQ-01-relay-bin.000002
Relay_Log_Pos: 512
Relay_Master_Log_File: binlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 从库上查看数据同步状态情况，看到上面的两个Yes信息，就表示主从数据同步功能设置成功了
# 在从库上配置应用延时同步功能
mysql> stop slave;
mysql> change master to master_delay=300;
mysql> start slave;
mysql> show slave status\G

*************************** 1. row *************************** SQL_Delay: 300 SQL_Remaining_Delay: NULL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 设置延时时间为300s后同步数据（生产建议延时3~6小时），以及最近事件要做同步的延时剩余时间；
# 核实当前主从同步是完整状态
mysql> show master status;
+------------------+-----------+-------------------+-----------------------+------------------------+
| File             | Position  | Binlog_Do_DB      | Binlog_Ignore_DB      | Executed_Gtid_Set      |
+------------------+-----------+-------------------+-----------------------+------------------------+
| binlog.000004    | 347       |                   |                       |                        |
+------------------+-----------+-------------------+-----------------------+------------------------+
1 row in set (0.00 sec)
-- 核实主库应用日志文件和事件位置点情况；
mysql> show slave status\G

*************************** 1. row ***************************

1
Master_Log_File: binlog.000004

Read_Master_Log_Pos: 347

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Relay_Log_File: xiaoQ-01-relay-bin.000003
Relay_Log_Pos: 277
-- 核实从库应用日志文件和事件位置点情况，确认和主库应用日志信息和事件位置点情况一致；
# 延时从库应用效果环境模拟
mysql > create database relaydb;
mysql > use relaydb;
mysql > create table t1 (id int);
mysql > insert into t1 values(1),(2),(3);
mysql > commit;
mysql > insert into t1 values(11),(12),(13);
mysql > commit;
mysql > insert into t1 values(111),(112),(113);
mysql > commit;
mysql > drop database relaydb;
-- 以上操作语句在主库上进行执行；
mysql> show master status;
+------------------+-----------+-------------------+-----------------------+------------------------+
| File             | Position  | Binlog_Do_DB      | Binlog_Ignore_DB      | Executed_Gtid_Set      |
+------------------+-----------+-------------------+-----------------------+------------------------+
| binlog.000004    | 1793      |                   |                       |                        |
+------------------+-----------+-------------------+-----------------------+------------------------+
1 row in set (0.00 sec)
-- 核实主库应用日志文件和事件位置点情况；
mysql> show slave status\G

*************************** 1. row ***************************

1
Master_Log_File: binlog.000004

Read_Master_Log_Pos: 1793

1
2
Relay_Log_File: xiaoQ-01-relay-bin.000003
Relay_Log_Pos: 277

SQL_Delay: 300 SQL_Remaining_Delay: 91

1
2
3
4
5
6
-- 核实从库应用日志文件和事件位置点情况，
# 数据信息修复方式一：手工截取日志信息进行回放数据，恢复业务；
# 操作过程01：停止从库SQL线程回放日志事件
mysql > stop slave sql_thread;
-- 停止从库SQL线程，终止持续同步操作，使从库不再回放同步数据；
mysql > show slave status\G

*************************** 1. row ***************************

1
Master_Log_File: binlog.000004

Read_Master_Log_Pos: 3239

1
2
3
4
Relay_Log_File: xiaoQ-01-relay-bin.000003
Relay_Log_Pos: 1723
Slave_IO_Running: Yes
Slave_SQL_Running: No

SQL_Delay: 300 SQL_Remaining_Delay: N

1
2
-- 核实从库SQL线程状态是否为NO，以及获取读取的relay_log日志文件信息
# 操作过程02：根据relaylog起点信息以及异常操作位置点信息，截取日志内容信息

起点信息：

1
2
3
Relay_Log_File: xiaoQ-01-relay-bin.000003
Relay_Log_Pos: 1723
mysql> show relaylog events in 'xiaoQ-01-relay-bin.000003';

.. 省略部分… | xiaoQ-01-relay-bin.000003 | 3056 | Quer y | 1 | 3239 | drop database relaydb /* xid=745 */

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 获取终点信息 3056
[root@xiaoQ-01 ~]# cd /data/3309/data/
[root@xiaoQ-01 data]# mysqlbinlog --start-position=1723 --stop-position=3056  xiaoQ-01-relay-bin.000003 >/tmp/relay.sql
-- 在从库服务器上完成日志信息的截取操作
# 操作过程03：从库中恢复截取日志数据
mysql> set sql_log_bin=0;
mysql> source /tmp/relay.sql;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| relaydb            |
+--------------------+
-- 核实数据库以及数据表信息已恢复，并且原有主从关系已经彻底奔溃，需要进行主从关系重构
mysql> stop slave;
mysql> reset slave all;
-- 从库身份解除
# 数据信息修复方式二：持续延时从库数据回放同步过程，但同步过程停止在异常操作前；
# 操作过程01：停止从库SQL线程回放日志事件
mysql > stop slave sql_thread;
-- 停止从库SQL线程，终止持续同步操作，使从库不再回放同步数据；
mysql > show slave status\G

*************************** 1. row ***************************

1
Master_Log_File: binlog.000004

Read_Master_Log_Pos: 4685

02 主从复制扩展应用：过滤复制

概念介绍说明：当在企业数据库服务应用当中，如果在主库上有多个数据库业务，希望将不同的数据库业务同步到不同的从库上，实现数据库业务分离；为了满足以上需求，就可以利用过滤复制功能，将指定的数据信息复制到指定从库上，而不是全备方式同步数据；基于过滤复制功能，还是可以实现在主从同步数据信息时，排除指定库的数据信息不做主从同步操作； 1670349486561 实现工作机制：解决方案一：在主库上进行限制在主库上进行复制同步数据时，主库上存在A、B、C三个数据库信息，若只想复制其中A数据库的数据信息；可以让数据库服务只记录A数据库的事件日志信息，对于B和C数据库信息进行不写入日志操作；但是利用这种方法实现主从信息同步的过滤，可能会导致B和C库数据一旦损坏，由于没有记录日志，无法进行恢复的情况；解决方案二：在从库上进行限制在从库上进行复制同步数据时，利用从库上的SQL线程进行控制，只回放同步过来的A库数据信息，屏蔽其他数据库的信息不做回放；利用从库进行同步数据过滤，不能减轻主库同步数据的压力，但可以减轻从库进行数据回放的压力；

功能应用实践： ① 查看主从限制过滤参数信息

1
2
3
4
Relay_Log_File: xiaoQ-01-relay-bin.000003
Relay_Log_Pos: 3169
Slave_IO_Running: Yes
Slave_SQL_Running: No

SQL_Delay: 300 SQL_Remaining_Delay: NULL

1
2
3
4
5
6
-- 核实从库SQL线程状态是否为NO，以及获取读取的relay_log日志文件信息
# 操作过程02：回放日志事件在异常操作位置点前
mysql> show relaylog events in 'xiaoQ-01-relay-bin.000003';
+----------------------------------+------+-------------------+-----------+-----------------+------------------------------------------+
| Log_name                         | Pos  | Event_type        | Ser ver_i | End_log_pos     | Info                                     |
+----------------------------------+------+-------------------+-----------+-----------------+------------------------------------------+

…忽略部分..

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
| xiaoQ-01-relay-bin.000003        | 4394 | Xid               | 1         | 4495            | COMMIT /* xid=757 */                     |
| xiaoQ-01-relay-bin.000003        | 4425 | Anonymous_Gtid    | 1         | 4572            | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'     |
| xiaoQ-01-relay-bin.000003        | 4502 | Quer y            | 1         | 4685            | drop database relaydb /* xid=759 */      |
+----------------------------------+------+-------------------+-----------+-----------------+------------------------------------------+
-- 获取异常操作日志文件信息和事件位置点信息，其中位置点信息以Pos列显示的为准，并且是提前一个事务位置点；
mysql > change master to master_delay=0;
-- 在从库重启进行日志回放操作前，关闭从库延迟回放的功能
mysql > start slave until relay_log_file="log_name", relay_log_pos=log_pos;
mysql > start slave until relay_log_file='xiaoQ-01-relay-bin.000003', relay_log_pos=4425;
-- 启动日志信息回放功能，直到指定位置点结束日志信息回放
mysql > start slave until sql_before_gtids="xxxx3:4";
-- 如果开启了GTID功能，也可以按照GTID位置点进行数据信息回放（参考）
mysql> show slave status\G

*************************** 1. row ***************************

1
2
Slave_IO_Running: Yes
Slave_SQL_Running: No

Until_Log_File: xiaoQ-01-relay-bin.000003 Until_Log_Pos: 4425

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
-- 从库重新回放操作恢复数据后，从库状态信息中SQL还是为NO，是正常的，因为直到指定位置点就终止回放；
# 操作过程03：核实异常数据信息是否恢复
mysql > show databases;
+--------------------+
| Database           |
+--------------------+
| relaydb            |
+--------------------+
-- 核实数据库以及数据表信息已恢复，并且原有主从关系已经彻底奔溃，需要进行主从关系重构
mysql> stop slave;
mysql> reset slave all;
-- 从库身份解除
-- 参考官方资料：https://dev.mysql.com/doc/refman/8.0/en/start-replica.html
# 查看主库复制过滤限制参数信息（了解即可）
mysql> show master status;
+------------------+-----------+-------------------+-----------------------+-------------------------+
| File             | Position  | Binlog_Do_DB      | Binlog_Ignore_DB      | Executed_Gtid_Set       |

② 数据同步复制过滤效果配置

③ 进行同步复制过滤效果测试

1
2
3
4
5
6
7
8
9
+------------------+-----------+-------------------+-----------------------+-------------------------+
| binlog.000004    | 4685      |                   |                       |                         |
+------------------+-----------+-------------------+-----------------------+-------------------------+
1 row in set (0.00 sec)
-- 主库状态信息中，Binlog_Do_DB表示同步复制白名单过滤设置，Binlog_Ignore_DB表示同步复制黑名单过滤设置
-- 过滤白名单表示会记录事件日志信息，过滤黑明白表示不会记录事件日志信息，一般选择其一进行应用即可
-- 可以应用主库的过滤同步功能，实现数据库中默认库的同步复制限制；
# 查看从库复制过滤限制参数信息
mysql> show slave status\G

*************************** 1. row *************************** Replicate_Do_DB: xiaoQ Replicate_Ignore_DB: xiaoA

1
-- 表示库级别的过滤操作，白名单设置表示回放库级别操作，黑名单设置表示忽略库级别操作

Replicate_Do_Table: xiaoQ.t1 Replicate_Ignore_Table: xiaoA.t1

1
-- 表示表级别的过滤操作，白名单设置表示回放表级别操作，黑名单设置表示忽略表级别操作

Replicate_Wild_Do_Table: xiaoQ.t* Replicate_Wild_Ignore_Table: xiaoA.t*

1
2
3
4
-- 表示模糊级别的过滤操作，主要是可以针对多表信息，配置白名单或黑名单；
-- 以上在从库上线实现数据同步过滤机制的参数信息有6个，主要可以分为3组，一般应用使用一个参数即可；
# 编写配置文件实现过滤
[root@xiaoQ-01 ~]# vim /data/3309/my.cnf

replicate_do_db=ppt replicate_do_db=word

1
2
3
4
5
6
7
8
# 在线调整参数实现过滤
mysql> help change replication filter
mysql> stop slave sql_thread;
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (word, ppt);
mysql> start slave sql_thread;
-- 一般编写配置文件和在线配置都会进行，可以不重启数据库服务生效过滤机制，日后重启数据库后过滤机制依然生效；
# 查看获取从库过滤配置
mysql> show slave status\G

Replicate_Do_DB: word,ppt Replicate_Ignore_DB:

1
2
3
# 在主库上进行数据库创建模拟
mysql> create database word;
mysql> show slave status\G

*************************** 1. row ***************************

1
Master_Log_File: binlog.000004

Read_Master_Log_Pos: 4870

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- 从库日志信息同步查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| word               |
+--------------------+
-- 查看从库数据同步情况
mysql> create database ppt;
mysql> show slave status\G

*************************** 1. row ***************************

1
Master_Log_File: binlog.000004

Read_Master_Log_Pos: 5052

1
2
3
4
5
6
7
-- 从库日志信息同步查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| ppt                |
+--------------------+

03 主从复制扩展应用：半同步复制

概念介绍说明：在MySQL5.5版本之前，数据库的复制是异步操作，主库和从库的数据之间存在一定的延迟，这样就存在数据存储不一致的隐患；假设当主库上写入一个事务并提交成功，而从库尚未得到主库推送的binlog日志时，主库宕机了；例如主库可能因磁盘损坏、内存故障等造成主库上该事务binlog丢失，此时从库就可能损失这个事务，从而造成主从不一致；为了解决这个问题，数据库服务引入了半同步复制机制。当采用异步方式同步数据，由于从库异常宕机情况出现，造成主从数据不一致情况出现，还会有以下影响情况：会造成从库可能创建语句没有执行，后续的插入语句也必然失败，形成SQL线程运行故障；由于主从数据信息不一致，在架构设计上在读取从库数据信息时，就会读取数据信息异常；

说明：利用半同步复制机制，主要是用于解决主从数据复制不一致的问题，即解决主从数据一致性问题，也可以避免SQL线程故障；

实现工作机制：在MySQL5.5之前的异步复制时，主库执行完commit提交操作后，在主库写入binlog日志后即可成功返回客户端；无需等待binlog日志传送给从库；半同步复制时，为了保证主库上的每个binlog事务能够被可靠的复制到从库上，主库在每次事务成功提交时，并不及时反馈给前端用户；而是等待其中一个从库也接收到binlog事务并成功写入中继日志后，主库才返回commit操作成功给客户端。半同步复制保证了事务成功提交后，至少有两份日志记录，一份在主库的binlog日志上，另一份在至少一个从库的中继日志relaylog上从而更进一步保证了数据的完整性。简单说明：半同步复制技术应用，主要是阻塞主库事务提交的执行过程，从而实现数据最终一致性目的；半同步复制技术与传统主从复制技术不同之处：在主库提交操作时候会受到阻塞，等待从库IO线程返回ack确认信号后，才能使主库提交操作成功；从库IO线程接收到binlog日志信息，当日志信息写入到磁盘上的relaylog文件时，会给主库返回ack信号；在主库上会利用ack_receiver线程接收返回的ack信号；当主库上的ack_receiver线程接收到ack信号信息时，会产生事件触发机制，告诉主库事务提交操作成功了；如果在接收ack信号时，等待信号时间超过了预设值的超时时间，半同步复制会切换为原始的异步复制方式；预设的等待超时时间的数值，由参数rpl_semi_sync_master_timeout设置的毫秒数决定；

功能应用实践： ① 主从数据库安装半同步功能插件：

1
2
3
-- 查看从库数据同步情况
mysql> create database xiaoA;
mysql> show slave status\G

*************************** 1. row ***************************

1
Master_Log_File: binlog.000004

Read_Master_Log_Pos: 5240

 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
-- 从库日志信息同步查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
|                    |
+--------------------+
-- 查看从库数据同步情况，并未实现xiaoA数据库的复制，即实现了数据同步过滤效果；
# 进行主从同步重构
[root@xiaoQ-01 ~]# mysqldump -uroot -A -S /tmp/mysql3307.sock --master-data=2 --single-transaction >/tmp/full.sql
[root@xiaoQ-01 ~]# grep "\-- CHANGE" /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=5240;
-- 在主库进行数据备份，并获取备份位置点信息
mysql> stop slave;
mysql> reset slave all;
mysql> CHANGE MASTER TO

MASTER_HOST='192.168.30.101',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='binlog.000004',
MASTER_LOG_POS=5240,

MASTER_CONNECT_RETRY=10;
mysql> start slave;
-- 实现从库数据库同步功能重构
# 主库安装半同步插件（3307）

说明：一般在高可用数据库架构环境中，可以在高可用的两台主机上均安装好主库插件和从库插件；

② 主从数据库启动半同步插件功能：

③ 主从数据库半同步功能永久配置：

 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
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-- 主库利用插件控制ack_receiver线程接收ack确认信息，并且会控制commit阻塞，实现半同步复制功能
mysql> show plugins;
+---------------------------------+----------+--------------------+--------------------------+----------+
| Name                            | Status   | Type               | Librar y                 | License  |
+---------------------------------+----------+--------------------+--------------------------+----------+
| rpl_semi_sync_master            | ACTIVE   | REPLICATION        | semisync_master.so       | GPL      |
+---------------------------------+----------+--------------------+--------------------------+----------+
-- 查看插件是否进行加载
# 从库安装半同步插件（3309）
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
-- 从库利用插件控制IO线程发送ack确认信息；
mysql> show plugins;
+---------------------------------+----------+--------------------+--------------------------+----------+
| Name                            | Status   | Type               | Librar y                 | License  |
+---------------------------------+----------+--------------------+--------------------------+----------+
| rpl_semi_sync_slave             | ACTIVE   | REPLICATION        | semisync_slave.so        | GPL      |
+---------------------------------+----------+--------------------+--------------------------+----------+
-- 查看插件是否进行加载
# 主库启动半同步功能
mysql> set global rpl_semi_sync_master_enabled =1;
# 从库启动半同步功能
mysql> set global rpl_semi_sync_slave_enabled =1;
# 重启从库上的IO线程
mysql> stop slave IO_THREAD;
mysql> start slave IO_THREAD;
# 核实确认半同步功能状态：
mysql> show status like 'rpl_semi_sync_master_status';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| Rpl_semi_sync_master_status          | ON    |
+--------------------------------------+-------+
1 row in set (0.01 sec)
-- 核实主库半同步功能是否激活
mysql> show status like 'rpl_semi_sync_slave_status';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| Rpl_semi_sync_slave_status           | ON    |
+--------------------------------------+-------+
1 row in set (0.00 sec)
-- 核实从库半同步功能是否激活
# 在数据库配置文件中编写以下参数

rpl_semi_sync_master_enabled=on

1
-- 主库半同步功能启停设置，on为激活设置

rpl_semi_sync_master_timeout=1000

1
-- 主库接收从库确认信息的超时时间设置（单位毫秒）

rpl_semi_sync_master_trace_level=32 rpl_semi_sync_master_wait_for_slave_count=1 rpl_semi_sync_master_wait_no_slave=on rpl_semi_sync_master_wait_point=after_sync

1
2
3
binlog_group_commit_sync_delay=1
binlog_group_commit_sync_no_delay_count=1000
-- 实现事务组提交方式，将多个事务合并成组推送到从库上，避免dump线程采用串型方式提交事务，造成主从同步延时；

rpl_semi_sync_slave_enabled=on

1
-- 从库半同步功能启停设置，on为激活设置

rpl_semi_sync_slave_trace_level=32

主机角色主机名称地址信息主库服务器 db-01 192.168.10.101 从库服务器 db-02 192.168.10.102 从库服务器 db-03 192.168.10.103

04 主从复制扩展应用：GTID复制

概念介绍说明： GTID（global transaction id）是对于一个已提交事务的唯一编号，并且是一个全局唯一编号（主从复制过程）；是数据库5.6版本开始的一个功能新特性，主要是用于解决主从复制的一致性问题；

复制原理机制： master节点在更新数据的时候，会在事务前产生GTID信息，一同记录到binlog日志中； slave节点的io线程将主库推送的binlog写入到本地relay log中；然后SQL线程从relay log中读取GTID，设置gtid_next的值为该gtid，然后对比slave端的binlog是否有记录；如果有记录的话，说明该GTID的事务已经运行，slave会忽略；如果没有记录的话，slave就会执行该GTID对应的事务，并记录到binlog中。 1670602175403

功能应用实践： ① 主从复制GTID功能实现环境：为了实现GTID机制的主从复制，需要准备好主从架构环境：对原有数据库服务环境清理：

② 主从复制GTID功能配置编写

1
2
3
4
5
6
7
8
9
# 在所有主从节点均进行清理操作：
[root@xiaoQ-01 ~]# pkill mysqld
[root@xiaoQ-01 ~]# rm -rf /data/3306/*
[root@xiaoQ-01 ~]# rm -rf /data/binlog /*
[root@xiaoQ-01 ~]# mv /etc/my.cnf /tmp
[root@xiaoQ-01 ~]# mkdir -p /data/3306/data /data/binlog
[root@xiaoQ-01 ~]# chown -R mysql.mysql /data/*
-- 所有数据库主从节点均进行以上清理操作；
# 配置参数信息

gtid-mode=on

1
-- 启用gtid复制方式，默认采用传统的复制方式

enforce-gtid-consistency=true

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 开启gtid所有节点的强制一致性
log-slave-updates=1
-- 定义slave更新是否记入二进制日志，从而增强数据一致性，是在高可用架构中重要配置环节
# 主库db01配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
ser ver_id=51
port=3306

secure-file-priv=/tmp autocommit=0

1
2
log_bin=/data/binlog /mysql-bin
binlog_format=row

gtid-mode=on enforce-gtid-consistency=true

1
2
log-slave-updates=1
[mysql]

prompt=db01 [\d]>

1
2
3
4
EOF
# 主库db02配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]

③ 主从复制GTID配置重构主从

知识扩展：实现自动获取同步位置点主从同步获取主库的gtid信息，获取同步位置点，并且不断更新位置点：

1
2
3
4
5
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
ser ver_id=52
port=3306

secure-file-priv=/tmp #autocommit=0

1
2
log_bin=/data/binlog /mysql-bin
binlog_format=row

gtid-mode=on enforce-gtid-consistency=true

1
2
log-slave-updates=1
[mysql]

prompt=db02 [\d]>

1
2
3
4
5
6
7
8
9
EOF
# 主库db03配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
ser ver_id=53
port=3306

secure-file-priv=/tmp #autocommit=0

1
2
log_bin=/data/binlog /mysql-bin
binlog_format=row

gtid-mode=on enforce-gtid-consistency=true

1
2
log-slave-updates=1
[mysql]

prompt=db03 [\d]>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
EOF
# 进行数据库所有节点初始化操作
[root@xiaoQ-01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
[root@xiaoQ-02 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
[root@xiaoQ-03 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
# 启动数据库所有节点服务
[root@xiaoQ-01 ~]# /etc/ init.d/mysqld start
[root@xiaoQ-02 ~]# /etc/ init.d/mysqld start
[root@xiaoQ-03 ~]# /etc/ init.d/mysqld start
# 重构主从关系-主库操作

db01 [(none)]>create user repl@‘192.168.30.%’ identified with mysql_native_password by ‘123456’;

1
Quer y OK, 0 rows affected (0.01 sec)

db01 [(none)]>grant replication slave on . to repl@‘192.168.30.%’;

1
2
3
Quer y OK, 0 rows affected (0.00 sec)
-- 主库上创建主从复制用户信息
# 重构主从关系-从库操作

db02 [(none)]>change master to

1
2
3
4
5
6
7
master_host='192.168.30.101',
master_user='repl',
master_password='123456',
master_auto_position=1;
-- 表示让从库自己找寻复制同步数据的起点；
-- 在第一次启动gtid功能时，会读取从库中的binlog日志信息，根据主库uuid信息，获取从库中执行过的主库gtid信息
-- 从从库中没有执行过的主库gtid信息之后进行进行数据同步操作

db02 [(none)]> start slave;

1
-- 其他从库一并操作

db01 [(none)]>show master status;

1
2
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
| File                  | Position  | Binlog_Do_DB      | Binlog_Ignore_DB      | Executed_Gtid_Set                                     |

主机角色主机名称地址信息主库服务器 db-01 192.168.10.101 从库服务器 db-03 192.168.10.103

知识扩展：进行全备恢复数据时不要加 set-gtid-purged参数如果是已经运行很久的数据库，需要构建主从，都是需要备份恢复主库数据后，再开启实现主从功能的；在mysqldump进行备份数据时，不要加set-gtid-purged参数，否则会造成从库依旧从第一个gtid信息开始同步数据；造成主从同步数据信息冲突，影响主从构建过程，导致主从同步过程失败；

05 主从复制扩展应用：Clone复制

概念介绍说明：利用clone plugin方式可以实现数据迁移备份恢复操作，同样也可以利用克隆技术实现主从数据同步操作，即完成快速构建从库；主要应用于运行一段时间的数据库，需要进行主从架构环境的构建时，可以实现主库数据信息的快速迁移；利用克隆复制备份恢复迁移数据信息，可以使备份恢复数据的效率提升；

功能应用实践： ① 主从复制克隆功能实现环境：为了实现克隆机制的主从复制，需要准备好主从架构环境：对原有数据库服务环境清理：

1
2
3
4
5
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
| mysql-bin.000002      | 681       |                   |                       | 3cfa5898-771a-11ed-b8d7-000c2996c4f5:1-2              |
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
1 row in set (0.00 sec)
-- 主库查看状态信息，获取gtid同步信息，gtid信息将会存储的位置：binlog、relaylog、master-info（uuid）

db02 [(none)]>show master status;

1
2
3
4
5
6
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
| File                  | Position  | Binlog_Do_DB      | Binlog_Ignore_DB      | Executed_Gtid_Set                                     |
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
| mysql-bin.000002      | 695       |                   |                       | 3cfa5898-771a-11ed-b8d7-000c2996c4f5:1-2              |
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
1 row in set (0.00 sec)

db03 [(none)]>show master status;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
| File                  | Position  | Binlog_Do_DB      | Binlog_Ignore_DB      | Executed_Gtid_Set                                     |
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
| mysql-bin.000002      | 695       |                   |                       | 3cfa5898-771a-11ed-b8d7-000c2996c4f5:1-2              |
+-----------------------+-----------+-------------------+-----------------------+-------------------------------------------------------+
1 row in set (0.00 sec)
-- 从库查看状态信息，获取gtid同步信息；gtid信息将会存储的位置：binlog、relaylog、master-info（uuid）
-- show binlog events in 'mysql-bin.000002' 获取从库自己的binlog信息，得到gitd同步的位置点；
# 未加set-gtid-purged参数实现的数据备份效果
[root@xiaoQ-01 ~]# mysqldump -A --master-data=2 --single-transaction >/tmp/full.sql
Warning: A partial dump from a ser ver that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the

database. If you don’t want to restore GTIDs, pass –set-gtid-purged=OFF. To make a complete dump, pass –all-databases –triggers –routines –events.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
[root@xiaoQ-01 ~]# vim /tmp/full.sql
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '3cfa5898-771a-11ed-b8d7-000c2996c4f5:1-2';
-- 表示让从库删除1-2的集合信息，即通过备份文件已经恢复了1-2的数据，可以从1-2之后进行数据信息同步；
# 已加set-gtid-purged参数实现的数据备份效果
[root@xiaoQ-01 ~]# mysqldump -A --master-data=2 --single-transaction --set-gtid-purged=OFF >/tmp/full02.sql
[root@xiaoQ-01 ~]# vim /tmp/full.sql
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '3cfa5898-771a-11ed-b8d7-000c2996c4f5:1-2';
SET SQL_LOG_BIN=0;
-- 以上信息不会出现在备份文件中
-- 表示会让从库把备份文件中的操作语句，再次根据gtid请求执行一遍，容易产生异常冲突问题；
# 清理原有从库配置应用（db03）

db03 [(none)]>stop slave; db03 [(none)]>reset slave all;

1
2
3
4
# 准备数据库空白的节点
[root@xiaoQ-03 ~]# pkill mysqld
[root@xiaoQ-03 ~]# rm -rf /data/3306/*
[root@xiaoQ-03 ~]# rm -rf /data/binlog /*

② 主从复制克隆环境功能配置：

③ 主从复制克隆功能应用启动：

④ 主从复制克隆完毕实现主从：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
[root@xiaoQ-03 ~]# mv /etc/my.cnf /tmp
[root@xiaoQ-03 ~]# mkdir -p /data/3306/data /data/binlog
[root@xiaoQ-03 ~]# chown -R mysql.mysql /data/*
-- 在新的数据库节点进行以上清理操作；
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
ser ver_id=53
port=3306

secure-file-priv=/tmp autocommit=0

1
2
log_bin=/data/binlog /mysql-bin
binlog_format=row

gtid-mode=on enforce-gtid-consistency=true

1
2
log-slave-updates=1
[mysql]

prompt=db03 [\d]>

1
2
3
4
5
6
7
8
EOF
-- 从库db03配置文件编写
[root@xiaoQ-03 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
-- 进行数据库所有节点初始化操作
[root@xiaoQ-03 ~]# /etc/ init.d/mysqld start
-- 启动数据库相应节点服务
# 实现免交互方式安装插件和创建用户（主库操作）
[root@xiaoQ-01 ~]# mysql -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so';create user test@'%' identified by '123456';grant backup_admin on *.* to

’test’@’%’;"

1
2
# 实现免交互方式安装插件和创建用户（从库操作）
[root@xiaoQ-03 ~]# mysql -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so';create user test@'%' identified by '123456';grant clone_admin on *.* to

’test’@’%’;set global clone_valid_donor_list=‘192.168.30.101:3306’;"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 从库上启用克隆功能
[root@xiaoQ-03 ~]# mysql -utest -p123456 -h192.168.30.103 -P3306 -e "clone instance from test@'192.168.30.101':3306 identified by '123456';"
# 实现克隆状态情况监控（可以实现每秒监控）
[root@xiaoQ-03 ~]# mysql -e "select stage,state,end_time from performance_schema.clone_progress;"
+-----------+-----------+----------------------------+
| stage     | state     | end_time                   |
+-----------+-----------+----------------------------+
| DROP DATA | Completed | 2022-12-11 23:58:04.373236 |
| FILE COPY | Completed | 2022-12-11 23:58:05.772489 |
| PAGE COPY | Completed | 2022-12-11 23:58:05.781343 |
| REDO COPY | Completed | 2022-12-11 23:58:05.784746 |
| FILE SYNC | Completed | 2022-12-11 23:58:06.123775 |
| RESTART   | Completed | 2022-12-11 23:58:07.796073 |
| RECOVERY  | Completed | 2022-12-11 23:58:08.819861 |
+-----------+-----------+----------------------------+
-- 可以实现每秒关注监控输出的结果信息，最后看到RECOVERY，并且状态为Completed，表示克隆完毕
# 主从方式构建一：利用position
[root@xiaoQ-03 ~]# mysql -e "select binlog_file,binlog_position from performance_schema.clone_status;"
+------------------+-----------------+
| binlog_file      | binlog_position |
+------------------+-----------------+
| mysql-bin.000002 | 1210            |
+------------------+-----------------+

主机角色主机名称地址信息主库服务器 192.168.30.101 3306 主库服务器 192.168.30.102 3306 从库服务器 192.168.30.103 3306

说明：利用clone功能实现主从，可以利用脚本自动化完成，并且可以实现主从的自愈能力，为实现主从功能上云提供方便；

06 主从复制扩展应用：多源复制（MSR）

概念介绍说明：在实际企业应用环境中，可能出现下图情况；会有多个企业系统场景，并且每个业务场景会构建不同主从架构，实现网站架构的解耦；当业务相互独立拆分后，的确读写方面来说，都能感觉到有大量的备份，相比所有业务汇总在一起时，并发效率和性能都有大幅提升；以上的业务架构设计，相当于进行垂直拆分；但是当需要进行数据信息统一查询分析时，变的非常困难，将会出现数据库孤岛问题；早期架构上解决此问题，只能采取将所有业务数据汇总到一个到的数据库中，使数据分析工作变得很麻烦，并没有什么自动化解决方案；当前有了比较好的解决思路，是利用数据中台技术，可以更好的将不同业务数据进行整合：便于进行数据节点统一管理便于进行数据信息统一分析（关注）在mysql应用过程中，为了实现数据中台构建，可以将所有业务的主库数据信息，汇总到一个从库中，即实现多源复制功能（5.7+）；实现多源复制，会对不同业务主库有一定的性能影响，并且对于多源复制的目标从库，并不会做写操作，只用于做数据分析使用； 1670777461704

功能应用实践： ① 主从多源复制功能实现环境：为了实现多源复制的主从同步，需要准备好主从架构环境：对原有数据库服务环境清理：（基于GTID环境构建）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 主从方式构建二：利用gtid
[root@xiaoQ-03 ~]# mysql -e "change master to master_host='192.168.30.101',master_user='repl',master_password='123456',master_auto_position=1;start slave"
# 核实展示最后主从状态结果
[root@xiaoQ-03 ~]# mysql -e "show slave status\G"|grep "Running:"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 在所有主从节点均进行清理操作：
[root@xiaoQ-01 ~]# pkill mysqld
[root@xiaoQ-01 ~]# rm -rf /data/3306/*
[root@xiaoQ-01 ~]# rm -rf /data/binlog /*
[root@xiaoQ-01 ~]# \mv /etc/my.cnf /tmp
[root@xiaoQ-01 ~]# mkdir -p /data/3306/data /data/binlog
[root@xiaoQ-01 ~]# chown -R mysql.mysql /data/*
-- 所有数据库主从节点均进行以上清理操作；
# 主库db01配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
ser ver_id=51
socket=/tmp/mysql.sock
log_bin=/data/binlog /mysql-bin

gtid-mode=on enforce-gtid-consistency=true

1
log-slave-updates=1

secure-file-priv=/tmp

1
2
3
innodb_flush_method=O_DIRECT
-- 这个参数控制着innodb数据文件及redo log的打开、刷写模式
-- 参考资料：https://blog.csdn.net/h106140873/article/details/125701485

slow_quer y_log=ON slow_quer y_log_file=/data/3306/data/db01-slow.log long_quer y_time=0.1

1
2
3
4
5
6
7
log_queries_not_using_indexes
-- 生成慢日志信息功能配置参数

master_info_repositor y=TABLE
-- 将master_info信息以表方式记录
relay_log_info_repositor y=TABLE
-- 将relay_log_info信息以表方式记录

② 主从多源复制重构主从环境

1
2
3
[client]
socket=/tmp/mysql.sock
[mysql]

prompt=db01 [\d]>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- 数据库命令提示符设置，可以参考官方设置：https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html
socket=/tmp/mysql.sock
EOF
# 主库db02配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
ser ver_id=52
socket=/tmp/mysql.sock
log_bin=/data/binlog /mysql-bin

gtid-mode=on enforce-gtid-consistency=true

1
log-slave-updates=1

secure-file-priv=/tmp

1
innodb_flush_method=O_DIRECT

slow_quer y_log=ON slow_quer y_log_file=/data/3306/data/db02-slow.log long_quer y_time=0.1

1
2
3
4
5
6
7
log_queries_not_using_indexes

master_info_repositor y=TABLE
relay_log_info_repositor y=TABLE
[client]
socket=/tmp/mysql.sock
[mysql]

prompt=db02 [\d]>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
socket=/tmp/mysql.sock
EOF
# 主库db03配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
ser ver_id=53
socket=/tmp/mysql.sock
log_bin=/data/binlog /mysql-bin

gtid-mode=on enforce-gtid-consistency=true

1
log-slave-updates=1

secure-file-priv=/tmp

1
innodb_flush_method=O_DIRECT

slow_quer y_log=ON slow_quer y_log_file=/data/3306/data/db03-slow.log long_quer y_time=0.1

1
2
3
4
5
6
7
log_queries_not_using_indexes

master_info_repositor y=TABLE
relay_log_info_repositor y=TABLE
[client]
socket=/tmp/mysql.sock
[mysql]

prompt=db03 [\d]>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
socket=/tmp/mysql.sock
EOF
# 进行数据库所有节点初始化操作
[root@xiaoQ-01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
[root@xiaoQ-02 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
[root@xiaoQ-03 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
# 启动数据库所有节点服务
[root@xiaoQ-01 ~]# /etc/ init.d/mysqld start
[root@xiaoQ-02 ~]# /etc/ init.d/mysqld start
[root@xiaoQ-03 ~]# /etc/ init.d/mysqld start

③ 主从多源复制监控信息查看

④ 主从多源复制数据信息过滤如果多个主库之间存在相同的数据表信息，在进行多源复制时，需要进行过滤处理，避免汇总在从库中产生数据不一致的情况；

07 主从复制扩展应用：数据组复制（MGR）

概念介绍说明： MGR全称MySQL Group Replication(MySQL组复制)，是MySQL官方于2016年12月推出的一个全新的高可用与高扩展的解决方案。 MGR提供了高可用、高扩展、高可靠的MySQL集群服务。在MGR出现之前，用户常见的MySQL高可用方式，无论怎么变化架构，本质就是Master-Slave架构。

1
MySQL 5.7.17版本开始支持无损半同步复制（lossless semi-syncreplication），从而进一步提升数据复制的强一致性。

MySQL Group Replication（MGR）是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案，以插件形式提供。 MGR基于分布式paxos协议，实现组复制，保证数据一致性。 MGR采用多副本，在2N+1个节点集群中，集群只要N+1个节点还存活着，数据库就能稳定的对外提供服务；数据库组复制功能，可以理解为是数据库主从关系的高可用环境，一般需要三个数据库实例，构成一个具有高可用、高一致性的复制环境主要涉及的功能应用包含：具有多节点之间互相通过投票的方式进行监控功能；（基于paxos协议）具有内置故障检测和自动选主功能，只要不是集群中的大多数节点都宕机，就可以继续正常工作；

如果主节点异常，会自动选举新节点实现故障转移
如何从节点异常，会自动将从节点从复制节点踢除提供单主模式与多主模式，多主模式支持多点写入；

应用模式说明： MGR单主模式（single-primar y mode）在这种模式下，组具有设置为读写模式的单主服务器，该组中的所有其他成员都设置为只读模式（这会自动发生）；

1
# 重构主从关系-主库操作

db01 [(none)]>set sql_log_bin=0; db01 [(none)]>create user repl@‘192.168.30.%’ identified with mysql_native_password by ‘123456’;

1
Quer y OK, 0 rows affected (0.01 sec)

db01 [(none)]>grant replication slave on . to repl@‘192.168.30.%’;

1
Quer y OK, 0 rows affected (0.00 sec)

db01 [(none)]>set sql_log_bin=1;

1
2
-- 两个主库上创建主从复制用户信息，并且不要产生创建用户日志信息，因为多个主节点可能用户信息不一致，会导致同步异常
# 重构主从关系-从库操作

db03 [(none)]>change master to

1
2
3
4
master_host='192.168.30.101',
master_user='repl',
master_password='123456',
master_auto_position=1 for channel 'Master_1';

db03 [(none)]>change master to

1
2
3
4
master_host='192.168.30.102',
master_user='repl',
master_password='123456',
master_auto_position=1 for channel 'Master_2';

db03 [(none)]> start slave for channel ‘Master_1’; db03 [(none)]> start slave for channel ‘Master_2’; db03 [(none)]> show slave status for channel ‘Master_1’\G db03 [(none)]> show slave status for channel ‘Master_2’\G

1
-- 多源主从状态监控信息分别查看，重点关注Channel_Name信息和IO SQL线程状态

db03 [(none)]> use performance_schema; db03 [(none)]> select * from replication_connection_configuration\G db03 [(none)]> select * from replication_connection_status where channel_name=‘master_1’\G db03 [(none)]> select * from replication_connection_status where channel_name=‘master_2’\G

1
-- 多源主从状态监控信息汇总查看，或进行单独查看

db03 [(none)]> select * from performance_schema.replication_applier_status_by_worker\G;

1
-- 多源主从复制线程工作情况查看，了解即可

db03 [(none)]> change replication filter replicate_wild_do_table=(‘db1.%’) channel “Master_1”; db03 [(none)]> change replication filter replicate_wild_do_table=(‘db2.%’) channel “Master_2”;

主机角色主机名称地址信息主库服务器 192.168.30.101 3306 从库服务器 192.168.30.102 3306 从库服务器 192.168.30.103 3306 主服务器通常是引导该组的第一台服务器，所有其它加入的服务器会自动了解主服务器，并设置为只读； MGR单主模式选举原理单主模式下，如果主节点挂了，那么其他的成员会自动选举出新的主成员，成员之间可以通过配置权重来确定下一个主成员是谁，如果没有配置权重，则会对所有在线成员的UUID进行排序，然后选取UUID最小的成员作为主成员。 1670949237999

MGR多主模式（multi-primar y mode）在多主的模式下，没有单个主概念。无需进行节点选举，因为没有服务器扮演任何特殊角色，所有服务器均设置为读写模式。 MGR多主模式选举原理多主模式，所有的组内成员对外提供读写服务，是真正意义上的并发，MGR对于高并发有很好的的处理能力。多主模式下，组内所有成员没有主从之分，对用户来说，就像在操作一个MySQL一样。所以在多主模式下，不存在选举主节点，因为所有节点都是主节点。 1670949335470

利用MGR工作模式可以实现业务架构的读写分离需求，应用MySQL原生态的router功能即可实现，并且原生态router技术更兼容MGR；因为，当MGR中主节点出现异常下线后，会选举出现的主节点，原生态router技术可以自动识别新的主节点，做读写分离的写库；将MySQL MGR + MySQL Router + MySQL Shell = InnoDB Cluster 1670925420469 官方扩展学习资料链接：https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster.html

适用场景说明： MGR天生就是为金融场景设计的，例如：支付，证券交易，保险，银行等等。因为这些场景要求数据必须做到零丢失，数据库可用性在4个9，甚至5个9的标准（年度停机时间不超过5分钟)

工作机制原理：组复制是一种可用于实现容错系统的技术，复制组是一个通过消息传递实现相互交互的ser ver集群；复制组由多个ser ver成员组成，如下图master01、master02、master03，所有成员独立完成各自的事务；

当客户端发起一个更新事务时，该事务先在本地执行，执行完成之后就要发起对事务的提交操作；
在还没有真正提交之前，需要将产生的复制写集广播出去，复制到其它所有成员节点；主库事务提交时，会将事务修改记录相关的信息和事务产生的binlog事件打包生成一个写集，将写集发送给所有节点；
如果冲突检测成功，组内决定该事务可以提交，其它成员可以应用，否则就回滚；冲突检测成功的标准是：至少半数以上个节点投票通过才能事务提交成功；
最终，所有组内成员以相同的顺序接收同一组事务；因此，组内成员以相同的顺序应用相同的修改，保证组内数据强一致性（采用了分布式事务特性） 1670867803361

功能应用实践： ① MGR复制同步功能实现环境：为了实现MGR复制的主从同步，需要准备好主从架构环境：对原有数据库服务环境清理：（基于GTID环境构建）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# 在所有主从节点均进行清理操作：
[root@xiaoQ-01 ~]# pkill mysqld
[root@xiaoQ-01 ~]# rm -rf /data/13306/*
[root@xiaoQ-01 ~]# rm -rf /data/binlog /*
[root@xiaoQ-01 ~]# \mv /etc/my.cnf /tmp
[root@xiaoQ-01 ~]# mkdir -p /data/13306/data /data/13306/binlog
[root@xiaoQ-01 ~]# chown -R mysql.mysql /data/*
-- 所有数据库主从节点均进行以上清理操作；
# 获取随机数信息充当uuid信息
[root@xiaoQ-01 ~]# cat /proc/sys/kernel/random/uuid

eb8441e9-8aef-4a86-a4bc-5beea315f04f

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- 借助随机数文件生成uuid信息，因为组复制过程也是通过GTID的uuid号码，达到复制环境中的事务一致性
-- 这里采用内部GTID功能，也就是组复制的各个节点通过同一个GTID的标识，进行事务管理，所以需要给组复制设置唯一号码
# 主库db01配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/13306/data
socket=/tmp/mysql.sock
ser ver_id=51
port=13306

secure-file-priv=/tmp

1
2
log_bin=/data/binlog /mysql-bin
binlog_format=row

gtid-mode=on enforce-gtid-consistency=true

1
log-slave-updates=1

skip_name_resolve master_info_repositor y=TABLE

1
2
3
-- 将master_info信息以表方式记录
relay_log_info_repositor y=TABLE
-- 将relay_log_info信息以表方式记录

report_host=192.168.30.101 report_port=13306

1
2
3
socket=/tmp/mysql13306.sock
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE

mysqlx=off

1
2
3
binlog_transaction_dependency_tracking=WRITESET
-- 在数据库8.0之后具有的配置，表示写集合配置信息，可以进一步提升SQL线程回放的并发度；（需要表有主键）
-- 是可以实现跨事务并发执行

transaction_write_set_extraction=XXHASH64

1
2
-- 定义写集合的hash算法信息，也属于数据库8.0之后具有的特性配置
-- 以上两行参数信息不加上，就表示与5.7版本数据库可以进行兼容，可以理解为是优化参数

loose-group_replication_group_name=“eb8441e9-8aef-4a86-a4bc-5beea315f04f "

1
2
-- 设置组复制各个节点的统一唯一uuid标识信息，即同一组复制内部的唯一标识；
-- 一样就表示可以加入同一组复制中，不同就表示不加入到相同的组复制中

loose-group_replication_start_on_boot=OFF

1
2
3
-- 在组复制过程中也是需要启动相应线程，完成组复制任务的；
-- 此参数配置表示在服务启动时，不自动运行启动组复制功能，一般都是进行手工启动
-- 主要是防止数据库意外重启后，对组复制之间关系的影响，不能让重启后数据库自动加入到组复制中

loose-group_replication_local_address=“192.168.30.101:33061”

1
-- 表示定义本地主机数据库服务的内部通讯地址和端口

loose-group_replication_group_seeds=“192.168.30.101:33061,192.168.30.102:33062,192.168.30.103:33063”

1
2
3
-- 表示定义所有集群主机的内部通讯地址和端口
-- 以上地址和端口信息，表示组复制集群内部通讯时，应用的地址和端口信息；
-- 内部通讯需求：心跳检测、复制关系、日志同步、投票、选举...，都是通过内部地址和端口进行的;

loose-group_replication_bootstrap_group=OFF

1
2
3
4
-- 表示是否将此节点作为引导节点
-- 组复制在第一次进行配置时，需要先有引导节点，其他节点做为加入节点(joiner)，不能都是ON，否则会产生争抢问题
-- 以上参数信息中loose，表示在没有组复制插件时，进行初始化操作只会报警告信息，而不会报错误提示
[mysql]

prompt=db01 [\d]>

1
2
3
4
5
6
7
8
9
EOF
# 主库db02配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/13306/data
socket=/tmp/mysql.sock
ser ver_id=52
port=13306

secure-file-priv=/tmp

1
2
log_bin=/data/binlog /mysql-bin
binlog_format=row

gtid-mode=on enforce-gtid-consistency=true

1
log-slave-updates=1

skip_name_resolve master_info_repositor y=TABLE

1
relay_log_info_repositor y=TABLE

report_host=192.168.30.102 report_port=13306

1
2
3
socket=/tmp/mysql13306.sock
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE

mysqlx=off

1
binlog_transaction_dependency_tracking=WRITESET

异常配置信息参数说明： group_replication变量使用的loose-前缀是指ser ver启用时尚未加载复制插件也将继续启动 transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name=“eb8441e9-8aef-4a86-a4bc-5beea315f04f " loose-group_replication_start_on_boot=OFF loose-group_replication_local_address=“192.168.30.102:33062” loose-group_replication_group_seeds=“192.168.30.101:33061,192.168.30.102:33062,192.168.30.103:33063” loose-group_replication_bootstrap_group=OFF

1
[mysql]

prompt=db02 [\d]>

1
2
3
4
5
6
7
8
9
EOF
# 主库db03配置文件编写
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/13306/data
socket=/tmp/mysql.sock
ser ver_id=53
port=13306

secure-file-priv=/tmp

1
2
log_bin=/data/binlog /mysql-bin
binlog_format=row

gtid-mode=on enforce-gtid-consistency=true

1
log-slave-updates=1

skip_name_resolve master_info_repositor y=TABLE

1
relay_log_info_repositor y=TABLE

report_host=192.168.30.103 report_port=13306

1
2
3
socket=/tmp/mysql13306.sock
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE

mysqlx=off

1
binlog_transaction_dependency_tracking=WRITESET

transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name=“eb8441e9-8aef-4a86-a4bc-5beea315f04f " loose-group_replication_start_on_boot=OFF loose-group_replication_local_address=“192.168.30.103:33063” loose-group_replication_group_seeds=“192.168.30.101:33061,192.168.30.102:33062,192.168.30.103:33063” loose-group_replication_bootstrap_group=OFF

1
[mysql]

prompt=db03 [\d]>

1
2
3
4
5
6
7
8
9
EOF
# 进行数据库所有节点初始化操作
[root@xiaoQ-01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/13306/data
[root@xiaoQ-02 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/13306/data
[root@xiaoQ-03 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/13306/data
# 启动数据库所有节点服务
[root@xiaoQ-01 ~]# /etc/ init.d/mysqld start
[root@xiaoQ-02 ~]# /etc/ init.d/mysqld start
[root@xiaoQ-03 ~]# /etc/ init.d/mysqld start

num conf _ info 0 1 transaction _ write _ set _ extraction = XXHASH 6 4 指示server 为每个事务收集写集合，并使用XXHASH 6 4 哈希算法将其编码为散列 0 2 loose - group _ replication _ group _ name = " eb8441e9 - 8 aef - 4a86 - a4bc - 5 beea 315f04f " 表示将加入或创建的复制组命名为eb8441e9 - 8 aef - 4a86 - a4bc - 5 beea 315f04f 可以自定义或者通过cat / proc / sys / kernel / random / uuid 获取 0 3 loose - group _ replication _ start on boot = OFF 表示设置server 启动时不自动启动组复制 0 4 loose - group _ replication _ local _ address = " 192.168.30.101 : 33061 " 表示绑定本地的192.168.30.101 : 33061 端口接受其他组成员的连接，I P 地址必须为其他组成员可正常访问 0 5 loose - group _ replication _ group _ seeds = " 192.168.30.101 : 33061 , 192.168.30.102 : 33062 , 192.168.30.103 : 33063 " 表示告诉服务器在加入组时，应当连接到这些种子服务器进行配置。本设置可以不是全部的组成员服务地址 0 6 loose - group _ replication _ bootstrap _ group = OFF 表示配置是否自动引导组 0 7 loose - group _ replication ip whitelist = " 10.30.0.0/16 , 10.31.0.0/16 , 10.27.0.0/16 " 表示配置白名单，默认情况下只允许192.168.30.101/102/103 连接到复制组，如果是其他I P 则需要配置

② MGR复制同步功能配置过程： MGR单主模式配置过程：

1
# 设置本地root用户密码和密码插件（所有节点）

mysql -S /tmp/mysql13306.sock -e “alter user ‘root’@’localhost’ identified with mysql_native_password by ‘123’;”

1
# 安装部署MGR组复制功能插件（所有节点）

mysql -uroot -p123 -S /tmp/mysql13306.sock -e “install plugin group_replication SONAME ‘group_replication.so’;”

1
# 设置创建MGR组复制功能账号（所有节点）

mysql -uroot -p123 -S /tmp/mysql13306.sock

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
set sql_log_bin=0;
create user repl@'%' identified by '123';
create user repl@'localhost' identified by '123';
create user repl@'127.0.0.1' identified by '123';
grant replication slave,replication client on *.* to repl@'%';
grant replication slave,replication client on *.* to repl@'localhost';
grant replication slave,replication client on *.* to repl@'127.0.0.1';
flush privileges;
set sql_log_bin=1;
# 启动MGR单主模式：启动MGR引导节点（在主库上执行）
change master to master_user='repl',master_password='123' for channel 'group_replication_recover y';
set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;
-- 相当于创建一个组复制集群，并指定集群中的引导节点
select * from performance_schema.replication_group_members;
-- 查看集群节点状态信息，以及集群成员信息

db01 [(none)]>select * from performance_schema.replication_group_members;

 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
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | f90d44f9-7b94-11ed-ab2d-000c2996c4f5 | 192.168.30.101 | 13306       | ONLINE       | PRIMARY     | 8.0.26         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)
# 其他几点加入MGR（在所有从库上执行）
reset master ;
-- 表示清除从库上所有日志信息，重新做日志信息的复制或生成;
change master to master_user='repl',master_password='123' for channel 'group_replication_recover y';
start group_replication;
-- 将指定从库节点加入到组复制集群中（企业中最好先备份恢复一定的数据，在进行组复制应用）
select * from performance_schema.replication_group_members;
-- 查看集群节点状态信息，以及集群成员信息
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | f90d44f9-7b94-11ed-ab2d-000c2996c4f5 | 192.168.30.101 | 13306       | ONLINE       | PRIMARY     | 8.0.26         |
| group_replication_applier | fe73f0b4-7b94-11ed-96ea-000c2961cd06 | 192.168.30.102 | 13306       | ONLINE       | SECONDARY   | 8.0.26         |
| group_replication_applier | 0a09b03e-7b95-11ed-9af8-000c29f5669f | 192.168.30.103 | 13306       | ONLINE       | SECONDARY   | 8.0.26         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
-- 此时可以看到3个节点状态为online，并且主节点为192.168.30.101，只有主节点可以写入，其他节点只读，MGR单主模式搭建成功
show variables like '%only%';
+----------------------------------------+-------+

MGR多主模式配置过程：从单主模式切换到多主模式 MGR切换模式需要重新启动组复制，因此需要在所有节点上先关闭组复制，设置group_replication_single_primar y_mode=OFF参数再重新启动组复制功能完成上面的配置后就可以执行多点写入了，多点写入会存在冲突检查，这对数据库性能耗损是挺大的，官方建议采用网络区分功能，在程序端把相同的业务定位到同一节点，尽量减少冲突发生的几率；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
| Variable_name                          | Value |
+----------------------------------------+-------+
| read_only                              | ON    |
| super_read_only                        | ON    |
+----------------------------------------+-------+
-- 此时所有从库节点只能实现只读操作，只有主库可以进行写操作
# 遇到集群构建异常，可以进行重置操作
stop group_replication;
reset master ;
set sql_log_bin=0;
change master to master_user='repl',master_password='123' for channel 'group_replication_recover y';
start group_replication;
# 多主模式需要的参数信息

group_replication_single_primar y_mode=0

1
-- 设置参数表示关闭掉单master模式

group_replication_enforce_update_ever ywhere_checks=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
33
34
35
36
37
38
39
-- 这个参数设置表示多主模式下，各个节点进行严格一致性检查
# 多主模式功能配置（在所有节点上执行）
stop group_replication;
set global group_replication_single_primar y_mode=OFF;
set global group_replication_enforce_update_ever ywhere_checks=1;
select @@group_replication_single_primar y_mode,@@group_replication_enforce_update_ever ywhere_checks;
-- 检查参数配置信息是否生效
set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;
-- 重新启动MGR组复制功能，是多主模式生效（主节点操作）
start group_replication;
-- 重新启动MGR组复制功能，是多主模式生效（从节点操作）
select * from performance_schema.replication_group_members;
-- 查看集群节点状态信息，以及集群成员信息
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 0a09b03e-7b95-11ed-9af8-000c29f5669f | 192.168.30.103 | 13306       | ONLINE       | PRIMARY     | 8.0.26         |
| group_replication_applier | f90d44f9-7b94-11ed-ab2d-000c2996c4f5 | 192.168.30.101 | 13306       | ONLINE       | PRIMARY     | 8.0.26         |
| group_replication_applier | fe73f0b4-7b94-11ed-96ea-000c2961cd06 | 192.168.30.102 | 13306       | ONLINE       | PRIMARY     | 8.0.26         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
# 修改从库只读功能配置（在所有从库上执行）
set global read_only=0;
set global super_read_only=0;
-- 默认启动组复制功能都是单master模式，从库节点都是自动设置read_only super_read_only这两个参数，需要手工修改
# 停止组复制功能（在所有节点执行）
stop group_replication;
set global group_replication_single_primar y_mode=OFF;
set global group_replication_enforce_update_ever ywhere_checks=ON;
# 随便选择某个节点执行操作
set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;
# 其他节点执行
start group_replication;
# 查看组信息，所有节点的member_role 都为primar y;
select * from performance_schema.replication_group_members;

MGR多主模式配置过程：从多主模式切换到单主模式

③ MGR复制同步功能运维管理：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# 所有节点执行以下操作
stop group_replication;
set global group_replication_enforce_update_ever ywhere_checks=OFF;
set global group_replication_single_primar y_mode=ON;
# 在主节点执行以下操作
set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;
# 在从节点执行以下操作
start group_replication;
# 查看MGR组信息：
select * from performance_schema.replication_group_members;
# MGR日常管理监控操作：
select * from performance_schema.replication_group_members;
-- 根据命令信息输出，获取各个节点主机的状态情况；
# MGR故障模拟操作过程：
[root@xiaoQ-01 ~]# /etc/ init.d/mysqld stop

db02 [(none)]>select * from performance_schema.replication_group_members;

1
2
3
4
5
6
7
8
9
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 0a09b03e-7b95-11ed-9af8-000c29f5669f | 192.168.30.103 | 13306       | ONLINE       | PRIMARY     | 8.0.26         |
| group_replication_applier | fe73f0b4-7b94-11ed-96ea-000c2961cd06 | 192.168.30.102 | 13306       | ONLINE       | SECONDARY   | 8.0.26         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)
-- 模拟主节点宕掉，会自动选举新的主节点
[root@xiaoQ-01 ~]# /etc/ init.d/mysqld start

db01 [(none)]>start group_replication; db02 [(none)]>select * from performance_schema.replication_group_members;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 0a09b03e-7b95-11ed-9af8-000c29f5669f | 192.168.30.103 | 13306       | ONLINE       | PRIMARY     | 8.0.26         |
| group_replication_applier | f90d44f9-7b94-11ed-ab2d-000c2996c4f5 | 192.168.30.101 | 13306       | ONLINE       | SECONDARY   | 8.0.26         |
| group_replication_applier | fe73f0b4-7b94-11ed-96ea-000c2961cd06 | 192.168.30.102 | 13306       | ONLINE       | SECONDARY   | 8.0.26         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)
-- 立刻恢复宕机节点，恢复节点自动成为从节点
# 通过克隆功能添加新的节点
[root@xiaoQ-01 ~]# pkill mysqld
[root@xiaoQ-01 ~]# rm -rf /data/13306/*
[root@xiaoQ-01 ~]# rm -rf /data/binlog /*
[root@xiaoQ-01 ~]# \mv /etc/my.cnf /tmp
[root@xiaoQ-01 ~]# mkdir -p /data/13306/data /data/13306/binlog
[root@xiaoQ-01 ~]# chown -R mysql.mysql /data/*
-- 初始化新的节点
cat >/etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/13306/data
socket=/tmp/mysql.sock
ser ver_id=51
port=13306

secure-file-priv=/tmp

1
2
log_bin=/data/binlog /mysql-bin
binlog_format=row

gtid-mode=on enforce-gtid-consistency=true

1
log-slave-updates=1

skip_name_resolve master_info_repositor y=TABLE

1
relay_log_info_repositor y=TABLE

应用限制说明：在应用MGR组复制功能时，也存在一些应用的限制条件：仅支持innodb存储引擎应用组复制功能； MGR集群中只支持innodb存储引擎，能够创建非innodb引擎的表，但是无法写入数据，向非innodb表写入数据直接报错；数据表中必须有主键，或者非null的唯一键； MGR集群中只支持innodb存储引擎，并且该表必须有显示的主键，或者非null的唯一键，否则即使能够创建表，也无法向表中写数据组复制存在网络限制，MGR组通信引擎目前仅支持IPv4网络，并且对节点间的网络性能要求较高；对于低延迟、高带宽的网络是部署MGR集群的基础；组复制功能会自动忽略表锁和命名锁，在MGR中lock tables、unlock tables、get_lock、release_lock等这些表锁和命名锁将忽略 MGR多主模式中，默认不支持 SERIALIZABLE 隔离级别，建议使用RC隔离级别；组复制多主模式中，对同一个对象进行并发是有冲突的，ddl和dml操作导致这种冲突在部分成员节点中无法检测到；最终可能导致数据不一致组复制多主模式中，不支持级联约束的外键，可能造成有冲突的操作无法检查；组复制功能不支持超大事务同步；组复制多主模式下可能导致死锁，比如select … for update在不同节点执行，由于多节点锁无法共享，很容易导致死锁；组复制是不支持复制过滤的，如果有节点设置了复制过滤功能，将影响节点间决议的达成；组复制功能最多支持9个节点，当大于9个节点，将拒绝新节点的加入；

report_host=192.168.30.101 report_port=13306

1
2
3
socket=/tmp/mysql13306.sock
default_authentication_plugin=mysql_native_password
binlog_checksum=NONE

mysqlx=off

1
binlog_transaction_dependency_tracking=WRITESET

transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name=“eb8441e9-8aef-4a86-a4bc-5beea315f04f " loose-group_replication_start_on_boot=OFF loose-group_replication_local_address=“192.168.30.101:33061” loose-group_replication_group_seeds=“192.168.30.101:33061,192.168.30.102:33062,192.168.30.103:33063” loose-group_replication_bootstrap_group=OFF

1
[mysql]

prompt=db01 [\d]>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
EOF
-- 编写新的节点配置文件
[root@xiaoQ-01 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/13306/data
-- 进行数据库所有节点初始化操作
[root@xiaoQ-01 ~]# /etc/ init.d/mysqld start
-- 启动数据库所有节点服务
[root@xiaoQ-01 ~]# mysql -S /tmp/mysql13306.sock -e "alter user 'root'@'localhost' identified with mysql_native_password by '123';"
-- 设置本地root用户密码和密码插件（所有节点）
[root@xiaoQ-01 ~]# mysql -uroot -p123 -S /tmp/mysql13306.sock -e "install plugin group_replication SONAME 'group_replication.so';"
-- 安装部署MGR组复制功能插件（所有节点）
[root@xiaoQ-03 ~]# mysql -uroot -p123 -S /tmp/mysql13306.sock -e  "INSTALL PLUGIN clone SONAME 'mysql_clone.so';create user test@'%' identified by '123';grant

backup_admin on . to ’test’@’%’;”

1
2
-- 在数据库服务正常节点上，创建克隆捐赠者用户信息
[root@xiaoQ-01 ~]# mysql -uroot -p123 -S /tmp/mysql13306.sock -e "INSTALL PLUGIN clone SONAME 'mysql_clone.so';create user test1@'%' identified by

‘123’;grant clone_admin on . to ’test1’@’%’;set global clone_valid_donor_list=‘192.168.30.103:13306’;”

1
2
3
4
5
6
7
8
9
[root@xiaoQ-01 ~]# mysql -utest1 -p123 -h192.168.30.101 -P13306 -e "clone instance from test@'192.168.30.103':13306 identified by '123';"
-- 在新添加节点上，创建克隆接收者用户信息
[root@xiaoQ-01 ~]# mysql -uroot -p123 -S /tmp/mysql13306.sock -e "select stage,state,end_time from performance_schema.clone_progress;"
-- 检查克隆是否完毕
change master to master_user='repl',master_password='123' for channel 'group_replication_recover y';
start group_replication;
-- 将新节点加入到组复制集群中
select * from performance_schema.replication_group_members;
-- 查看组复制成员状态信息

基础章节-01-MySQL数据库服务中级课程#

1.00 课程知识章节说明#

1.13 数据库服务克隆应用#

1.13.1 数据库克隆概念介绍#

1.13.2 数据库克隆原理说明#

01 Page copy：#

02 Redo copy：#

1.13.3 数据库克隆功能实践#

1.14 数据库服务主从架构#

1.14.1 数据库服务主从复制概述#

1.14.2 数据库服务主从复制原理#

01 复制环境搭建过程：#

02 复制数据过程监控：#

03 复制故障情况分析：#

04 复制延时问题分析：#

01 主从复制扩展应用：延时从库#

02 主从复制扩展应用：过滤复制#

03 主从复制扩展应用：半同步复制#

04 主从复制扩展应用：GTID复制#

05 主从复制扩展应用：Clone复制#

06 主从复制扩展应用：多源复制（MSR）#

07 主从复制扩展应用：数据组复制（MGR）#

基础章节-01-MySQL数据库服务中级课程

1.00 课程知识章节说明

1.13 数据库服务克隆应用

1.13.1 数据库克隆概念介绍

1.13.2 数据库克隆原理说明

01 Page copy：

02 Redo copy：

1.13.3 数据库克隆功能实践

1.14 数据库服务主从架构

1.14.1 数据库服务主从复制概述

1.14.2 数据库服务主从复制原理

01 复制环境搭建过程：

02 复制数据过程监控：

03 复制故障情况分析：

04 复制延时问题分析：

01 主从复制扩展应用：延时从库

02 主从复制扩展应用：过滤复制

03 主从复制扩展应用：半同步复制

04 主从复制扩展应用：GTID复制

05 主从复制扩展应用：Clone复制

06 主从复制扩展应用：多源复制（MSR）

07 主从复制扩展应用：数据组复制（MGR）