MySQL集群技术详解

一、MySQL在服务器中的部署方法

1.1 编译安装MySQL

1.2 部署MySQL

二、MySQL主从复制

2.1 配置master

2.2 配置slave

2.3 添加slave2

测试：

2.4 延迟复制

2.5 慢查询日志

2.6 MySQL的并行复制

2.7 MySQL主从复制原理剖析

2.8 架构缺陷

三、MySQL的半同步模式

3.1 gtid模式

3.2 开启半同步模式

四、实现mysql组复制MGR（高可用）

4.1 实现MySQL组复制

五、mysql-router（mysql路由）

六、MySQL高可用（MHA）

6.1 搭建一主两从架构

6.2 安装MHA所需要的软件

6.2.1 MHA的故障切换

6.2.1.1 MHA故障手动切换

6.2.1.2 模拟故障时的手动切换

6.2.1.3 恢复故障的mysql节点：

6.2.2 自动切换故障master

6.2.2.1 恢复节点

6.2.3 为MHA添加vip功能

6.2.3.1 自动切换模拟故障

6.2.3.2 手动切换master

一、MySQL在服务器中的部署方法

在企业中90%的服务器操作系统均为Linux
在企业中对于Mysql的安装通常用源码编译的方式来进行

1.1 编译安装MySQL

[root@mysql-node1 ~]# tar zxf mysql-boost-5.7.44.tar.gz 安装依赖性：
要拖入需要软件包：
[root@mysql-node1 ~]# yum install cmake gcc-c++ openssl-devel \
> ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el9_4.x86_64.rpm rpcgen.x86_64[root@mysql-node1 ~]# cd mysql-5.7.44/
[root@mysql-node1 ~]# cmake \
> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
> -DMYSQL_DATADIR=/data/mysql \
> -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DWITH_EXTRA_CHARSETS=all \
> -DDEFAULT_CHARSET=utf8mb4 \
> -DDEFAULT_COLLATION=utf8mb4_unicode_ci \
> -DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/[root@mysql-node2 mysql-5.7.44]# make -j4 && make install

1.2 部署MySQL

####################生成启动文件：########################
[root@mysql-node2 mysql]# cd support-files/
[root@mysql-node2 support-files]# cp mysql.server /etc/init.d/mysqld####################生成配置文件：########################
[root@mysql-node2 support-files]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0#####################修改环境变量：#################
[root@mysql-node2 support-files]# vim ~/.bash_profile 
# .bash_profile# Get the aliases and functions
if [ -f ~/.bashrc ]; then. ~/.bashrc
fi# User specific environment and startup programsPATH=$PATH:$HOME/bin:/usr/local/mysql/bin/export PATH
[root@mysql-node2 support-files]# source ~/.bash_profile #############初始化数据库并建立MySQL基本数据：######################
[root@mysql-node2 ~]# mysqld --initialize --user=mysql
[root@mysql-node2 ~]# /etc/init.d/mysqld start
[root@mysql-node2 ~]# chkconfig mysqld on
[root@mysql-node2 ~]# cd /usr/local/mysql/
[root@mysql-node2 mysql]# useradd -s /sbin/nologin -M mysql
[root@mysql-node2 mysql]# mkdir /data/mysql -p
[root@mysql-node2 mysql]# chown mysql.mysql -R /data/mysql/#####################数据库安全初始化：############################
[root@mysql-node2 ~]# mysql_secure_installation进去之后输入初始化的初始密码，新建密码，然后跟着走，两个no，其它全是yes#######################测试#####################
[root@mysql-node2 ~]# mysql -uroot -p123mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

二、MySQL主从复制

2.1 配置master

[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=10[root@mysql-node1 ~]# /etc/init.d/mysqld restart 
[root@mysql-node1 ~]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log Source distributionCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)[root@mysql-node1 ~]# cd /data/mysql/
[root@mysql-node1 mysql]# mysqlbinlog mysql-bin.000001 -vv ---- 查看二进制日志mysql> CREATE DATABASE xiaoding;
Query OK, 1 row affected (0.00 sec)mysql> CREATE TABLE xiaoding.userlist (-> username varchar(10) not null,-> password varchar(50) not null-> );
Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO xiaoding.userlist values ('ding1',203);
Query OK, 1 row affected (0.01 sec)

2.2 配置slave

[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
super_read_only=on   ---- 让它只能读取，不能写入。
server-id=20[root@mysql-node2 ~]# /etc/init.d/mysqld restart
[root@mysql-node2 ~]# mysql -uroot -p123
mysql> CHANGE MASTER TO
MASTER_HOST='172.25.254.10',MASTER_USER='ding',MASTER_PASSWORD='123',MASTER_LOG_F
ILE='mysql-bin.000001',MASTER_LOG_POS=(这个值是看你上面master上的Pos值);
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave;  #########启动Slave
Query OK, 0 rows affected (0.01 sec)mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.254.10Master_User: dingMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos: 154Relay_Log_File: mysql-node2-relay-bin.000010Relay_Log_Pos: 367Relay_Master_Log_File: mysql-bin.000005Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 154Relay_Log_Space: 746Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 10Master_UUID: b2e05366-6030-11ef-b06b-000c2909b7ecMaster_Info_File: /data/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 
1 row in set (0.00 sec)ERROR: 
No query specifiedmysql>

master上面创建新的数据库表：
[root@mysql-node1 ~]# mysql -uroot -p123
mysql> CREATE DATABASE ding;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE ding.userlist (
-> username varchar(10) not null,
-> password varchar(50) not null
-> );mysql> insert into ding.userlist values ('ding1','203');
Query OK, 1 row affected (0.01 sec)mysql> insert into ding.userlist values ('ding2','621');
Query OK, 1 row affected (0.00 sec)mysql>)master上查看数据：
mysql> select * from ding.userlist;
+----------+----------+
| username | password |
+----------+----------+
| ding1    | 203      |
| ding2    | 621      |
+----------+----------+
2 rows in set (0.00 sec)mysql> 在Slave中查看数据是否同步过来
[root@mysql-node2 ~]# mysql -uroot -p123
mysql> select * from ding.userlist;
+----------+----------+
| username | password |
+----------+----------+
| ding1    | 203      |
| ding2    | 621      |
+----------+----------+
2 rows in set (0.00 sec)mysql>

2.3 添加slave2

master上面把MySQL传到slave2上面：
[root@mysql-node1 ~]# rsync -al -r /usr/local/mysql root@172.25.254.30:/usr/local/
master上备份数据：
[root@mysql-node1 ~]# mysqldump -root -p123 ding > ding.sql   ----- 备份数据库
利用scp命令传到slave2上面：
[root@mysql-node1 ~]# scp ding.sql root@172.25.254.30:/root/slave2 上面：
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30[root@mysql-node3 ~]# vim ~/.bash_profile
# .bash_profile# Get the aliases and functions
if [ -f ~/.bashrc ]; then. ~/.bashrc
fi# User specific environment and startup programsPATH=$PATH:$HOME/bin:/usr/local/mysql/bin/export PATH
[root@mysql-node3 ~]# source ~/.bash_profile[root@mysql-node3 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql-node3 ~]# mkdir /data/mysql -p
[root@mysql-node3 ~]# chown mysql.mysql /data/mysql/ -R
[root@mysql-node3 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld初始化：
[root@mysql-node3 ~]# mysqld --user=mysql --initialize
完了之后看/data/mysql/底下是否有数据，没有就是初始化没弄好
[root@mysql-node3 ~]# ls /data/mysql/

[root@mysql-node3 ~]# /etc/init.d/mysqld start
[root@mysql-node3 ~]# mysql_secure_installation ----- 上面一样的操作

slave2上进行拉平数据：
[root@mysql-node3 ~]# cd /mnt/
[root@mysql-node3 mnt]# ls
xiaoding.sql
[root@mysql-node3 mnt]# mysql -uroot -p123 -e "CREATE DATABASE ding;"
[root@mysql-node3 mnt]# mysql -uroot -p123 ding <ding.sql要先去master上面查询日志：
[root@mysql-node1 ~]# mysql -uroot -p123
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     1511 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)mysql> slave上面：
[root@mysql-node3 mnt]# mysql -uroot -p123
mysql> CHANGE MASTER TO-> MASTER_HOST='172.25.254.10',MASTER_USER='ding',MASTER_PASSWORD='ding',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1511;
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> mysql> start slave;
Query OK, 0 rows affected (0.00 sec)mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.254.10Master_User: dingMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1511Relay_Log_File: mysql-node3-relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yesmaster上面再写个数据：
[root@mysql-node1 ~]# mysql -uroot -p123mysql> INSERT INTO ding.userlist values ('ding3','456');
Query OK, 1 row affected (0.00 sec)slave2上面看是否能查到：
[root@mysql-node3 ~]#  mysql -uroot -p123 -e "select * from ding.userlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| ding1    | 203      |
| ding2    | 621      |
| ding3    | 456      |
+----------+----------+

测试：

[root@mysql-node2 ~]#  mysql -uroot -p123 -e "select * from ding.userlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| ding1    | 203      |
| ding2    | 621      |
| ding3    | 456      |
+----------+----------+
[root@mysql-node2 ~]# [root@mysql-node3 ~]#  mysql -uroot -p123 -e "select * from ding.userlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| ding1    | 203      |
| ding2    | 621      |
| ding3    | 456      |
+----------+----------+

2.4 延迟复制

设置延迟复制，就是为了防止误操作。不用所有slave都做延迟复制，一台就够了[root@mysql-node3 ~]# mysql -uroot -p123mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)mysql> CHANGE MASTER TO MASTER_DELAY=60;
Query OK, 0 rows affected (0.00 sec)mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)mysql> SHOW SLAVE STATUS\G;Replicate_Ignore_Server_Ids: Master_Server_Id: 10Master_UUID: 4e591de3-6167-11ef-a877-000c2909b7ecMaster_Info_File: /data/mysql/master.infoSQL_Delay: 60    ----- 设置的延迟时间SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: 在master上面删除一个数据库：
[root@mysql-node1 ~]# mysql -uroot -p123mysql> delete from ding.userlist where username='ding3';
Query OK, 1 row affected (0.01 sec)mysql> select * from ding.userlist;
+----------+----------+
| username | password |
+----------+----------+
| ding1    | 203      |
| ding2    | 621      |
+----------+----------+
2 rows in set (0.00 sec)slave2上面设置了延迟复制：
[root@mysql-node3 ~]#  mysql -uroot -p123 -e "select * from ding.userlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| ding1    | 203      |
| ding2    | 621      |
| ding3    | 456      |
+----------+----------+模拟误删了，就可以有时间出来使用mysqldump命令进行备份了。

2.5 慢查询日志

master上查看慢查询：mysql> SHOW variables like "slow%";
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_launch_time    | 2                                |
| slow_query_log      | OFF                              |
| slow_query_log_file | /data/mysql/mysql-node3-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.00 sec)
现在没开启：可以把所有的set参数都写到/etc/my.cnf里面去。但是写在这里面需要重启才生效，只有在启动的时候生效开启慢查询：
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30
slow_query_log=on    ----- 写上这个 开启慢查询让它立即生效：
mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.00 sec)mysql> SHOW variables like "slow%";
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_launch_time    | 2                                |
| slow_query_log      | ON                               |
| slow_query_log_file | /data/mysql/mysql-node3-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.00 sec)mysql> mysql> SHOW VARIABLES like "long%";   ---- 慢查询的时间。
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)mysql> 测试慢查询;
mysql> select sleep (10);   ---- 等十秒才会出来
+------------+
| sleep (10) |
+------------+
|          0 |
+------------+
1 row in set (10.00 sec)mysql> 查看日志：
[root@mysql-node1 ~]# cat /data/mysql/mysql-node1-slow.log 
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /data/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2024-08-23T18:12:22.387364Z
# User@Host: root[root] @ localhost []  Id:    14
# Query_time: 10.000467  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1724436742;
select sleep (10);    ----- 这里可以看到为什么导致慢查询。
[root@mysql-node1 ~]#

2.6 MySQL的并行复制

默认情况下slave中使用的是sql单线程回放
在master中时多用户读写，如果使用sql单线程回放那么会造成组从延迟严重
开启MySQL的多线程回放可以解决上述问题

slave上面看，默认是单线程：mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  6 | system user |           | NULL | Connect | 7288 | Waiting for master to send event                       | NULL             |
|  7 | system user |           | NULL | Connect | 1227 | Slave has read all relay log; waiting for more updates | NULL             |
| 10 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)mysql> [root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
super_read_only=on
gtid_mode=ON  ##打开gtid
enforce-gtid-consistency=ON  ##保证gtid强一致
slave-parallel-type=LOGICAL_CLOCK   ##基于组提交
slave-parallel-workers=16			##开启的线程数量
master_info_repository=TABLE		##master信息在表中的记录，默认记录在/data/mysql/master.info中  日志都在磁盘里面，会很慢
relay_log_info_repository=TABLE		##回放日志信息在表中记录，默认记录在/data/mysql/relay-log.info中
relay_log_recovery=ON				##日志回放恢复功能开启[root@mysql-node2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@mysql-node2 ~]# 查看进程：
[root@mysql-node2 ~]# mysql -uroot -p123mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  2 | system user |           | NULL | Connect |   62 | Slave has read all relay log; waiting for more updates | NULL             |
|  3 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
|  4 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
|  5 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
|  6 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
|  7 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
|  8 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
| 10 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
| 11 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
| 12 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
| 13 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
| 14 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
| 15 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
| 16 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
| 17 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
| 18 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
| 19 | system user |           | NULL | Connect |   62 | Waiting for an event from Coordinator                  | NULL             |
| 20 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
18 rows in set (0.00 sec)mysql> 这就开启了16个SQL进程

2.7 MySQL主从复制原理剖析

三个线程：

实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于3 个线程来操作，一个主库线程，两个从库线程。二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。
从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志（Relay log）。
从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

复制三步骤

步骤1：Master将写操作记录到二进制日志（binlog）。
步骤2：Slave将Master的binary log events拷贝到它的中继日志（relay log）；
步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从接入点开始复制。

具体操作

slaves端中设置了master端的ip，用户，日志，和日志的Position，通过这些信息取得master的认证及信息
master端在设定好binlog启动后会开启binlog dump的线程
master端的binlog dump把二进制的更新发送到slave端的
slave端开启两个线程，一个是I/O线程，一个是sql线程。i/o线程用于接收master端的二进制日志，此线程会在本地打开relaylog中继日志，并且保存到本地磁盘。sql线程读取本地relog中继日志进行回放
什么时候我们需要多个 slave ？

当读取的而操作远远高与写操作时。我们采用一主多从架构

数据库外层接入负载均衡层并搭配高可用机制

2.8 架构缺陷

主从架构采用的是异步机制
master更新完成后直接发送二进制日志到slave，但是slaves是否真正保存了数据master端不会检测
master端直接保存二进制日志到磁盘
当master端到slave端的网络出现问题时或者master端直接挂掉，二进制日志可能根本没有到达slave
master出现问题slave端接管master，这个过程中数据就丢失了
这样的问题出现就无法达到数据的强一致性，零数据丢失

三、MySQL的半同步模式

用户线程写入完成后master中的dump会把日志推送到slave端
slave中的io线程接收后保存到relaylog中继日志
保存完成后slave向master端返回ack
在未接受到slave的ack时master端时不做提交的，一直处于等待当收到ack后提交到存储引擎
在5.6版本中用到的时after_commit模式，after_commit模式时先提交在等待ack返回后输出ok

3.1 gtid模式

当为启用gtid时我们要考虑的问题

在master端的写入时多用户读写，在slave端的复制时单线程日志回放，所以slave端一定会延迟与master端
这种延迟在slave端的延迟可能会不一致，当master挂掉后slave接管，一般会挑选一个和master延迟日志最接近的充当新的master
那么为接管master的主机继续充当slave角色并会指向到新的master上，作为其slave
这时候按照之前的配置我们需要知道新的master上的pos的id，但是我们无法确定新的master和slave之间差多少

激活gtid之后：

当master出现问题后，slave2和master的数据最接近，会被作为新的master
slave1指向新的master，但是他不会去检测新的master的pos id，只需要继续读取自己gtid_next即可

设定gtid：

#####################master上面：##########################
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=10
gtid_mode=ON
enforce-gtid-consistency=ON
[root@mysql-node1 ~]# /etc/init.d/mysqld restart 
Shutting down MySQL............ SUCCESS! 
Starting MySQL. SUCCESS! 
[root@mysql-node1 ~]# ####################slave2上面：#########################
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30
slow_query_log=on
gtid_mode=ON
enforce-gtid-consistency=ON
[root@mysql-node3 ~]# /etc/init.d/mysqld restart
Shutting down MySQL... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@mysql-node3 ~]# [root@mysql-node2 ~]# /etc/init.d/mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@mysql-node2 ~]# master上看gtid是否开启：
[root@mysql-node1 ~]# mysqlbinlog -vv /data/mysql/mysql-bin.000002
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

重新设定slave：
[root@mysql-node2 ~]# /etc/init.d/mysqld restartmysql> stop slave;
Query OK, 0 rows affected (0.01 sec)mysql> change master to-> master_host='172.25.254.10',-> master_user='ding',-> master_password='ding',-> master_auto_position=1-> ;
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> start slave;
Query OK, 0 rows affected (0.01 sec)mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.254.10Master_User: dingMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 154       ---- 自动识别，全局IDRelay_Log_File: mysql-node2-relay-bin.000002Relay_Log_Pos: 367Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: YesSlave_SQL_Running: Yes[root@mysql-node3 ~]# /etc/init.d/mysqld restartmysql> stop slave;
Query OK, 0 rows affected (0.01 sec)mysql> change master to-> master_host='172.25.254.10',-> master_user='ding',-> master_password='ding',-> master_auto_position=1-> ;
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave;
Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.254.10Master_User: dingMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000002Read_Master_Log_Pos: 154Relay_Log_File: mysql-node3-relay-bin.000002Relay_Log_Pos: 367Relay_Master_Log_File: mysql-bin.000002Slave_IO_Running: YesSlave_SQL_Running: Yes

3.2 开启半同步模式

master上面：
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=10
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1    ---- 开启半同步安装插件：
[root@mysql-node1 ~]# mysql -uroot -p123mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS    ---- 查看插件情况-> FROM INFORMATION_SCHEMA.PLUGINS-> WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.01 sec)默认就是开启的，查看是否开启：
mysql> show variables like 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| 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 |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)mysql>

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)mysql> 
这个也能查看

在slave1上也设定半同步：
[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1[root@mysql-node2 ~]# mysql -uroot -p123
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)[root@mysql-node2 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@mysql-node2 mysql]# 如果没用重启服务的话：
mysql> STOP SLAVE IO_THREAD; #重启io线程，半同步才能生效
Query OK, 0 rows affected (0.00 sec)mysql> START SLAVE IO_THREAD; ##重启io线程，半同步才能生效
Query OK, 0 rows affected (0.00 sec)slave2：
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20   #配置server唯一标识号
super_read_only=on  
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1[root@mysql-node3 ~]# mysql -uroot -p123
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)[root@mysql-node3 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@mysql-node3 ~]# 如果没有重启服务的话：
mysql> STOP SLAVE IO_THREAD; #重启io线程，半同步才能生效
Query OK, 0 rows affected (0.00 sec)mysql> START SLAVE IO_THREAD; ##重启io线程，半同步才能生效
Query OK, 0 rows affected (0.00 sec)

slave1上面：
mysql> STOP SLAVE IO_THREAD;   ---- 关掉IO线程
Query OK, 0 rows affected, 1 warning (0.00 sec)slave2上面：
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)master上写数据：
mysql> INSERT INTO ding.userlist values('ding3','456');
在这就会卡住slave1上面：
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)slave2上面：
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)现在就写入不会卡住了，导致数据不一致

四、实现mysql组复制MGR（高可用）

MySQL Group Replication(简称 MGR ) 是 MySQL 官方于 2016 年 12 月推出的一个全新的高可用与高扩展的解决方案

组复制是 MySQL 5.7.17 版本出现的新特性，它提供了高可用、高扩展、高可靠的 MySQL 集群服务

MySQL 组复制分单主模式和多主模式，传统的 mysql 复制技术仅解决了数据同步的问题，

MGR 对属于同一组的服务器自动进行协调。对于要提交的事务，组成员必须就全局事务序列中给定事务的顺序达成一致

提交或回滚事务由每个服务器单独完成，但所有服务器都必须做出相同的决定

如果存在网络分区，导致成员无法达成事先定义的分割策略，则在解决此问题之前系统不会继续进行，这是一种内置的自动裂脑保护机制

MGR由组通信系统 ( Group Communication System ， GCS ) 协议支持

该系统提供故障检测机制、组成员服务以及安全且有序的消息传递

4.1 实现MySQL组复制

############################三台主机都需要写本地解析：##########################[root@mysql-node1 ~]# vim /etc/hosts
[root@mysql-node2 ~]# vim /etc/hosts
[root@mysql-node3 ~]# vim /etc/hosts
172.25.254.10   mysql-node1.timingding.org
172.25.254.20   mysql-node2.timingding.org
172.25.254.30   mysql-node3.timingding.org###############################写入配置：##################################[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=10   #配置server唯一标识号
gtid_mode=ON   #启用全局事件标识
enforce-gtid-consistency=ON    #强制gtid一致
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"    #禁用指定存储引擎
master_info_repository=TABLE   #复制事件数据到表中而不记录在数据目录中
relay_log_info_repository=TABLE  
binlog_checksum=NONE       #禁止对二进制日志校验
log_slave_updates=ON  	   #打开数据库中继，#当slave中sql线程读取日志后也会写入到自己的binlog中
log_bin=binlog   		#重新指定log名称
binlog_format=ROW       #使用行日志格式
plugin_load_add='group_replication.so'    #加载组复制插件
transaction_write_set_extraction=XXHASH64    #把每个事件编码为加密散列
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"       #通知插件正式加入#或创建的组名#名称为uuid格式
group_replication_start_on_boot=off    #在server启动时不自动启动组复制
group_replication_local_address="172.25.254.10:33061"   #指定插件接受其他成员的信息端口
group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061,172.25.254.30:33061"    #本地地址允许访问成员列表
group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8"   #主机白名单
group_replication_bootstrap_group=off   #不随系统自启而启动
group_replication_single_primary_mode=OFF   #使用多主模式
group_replication_enforce_update_everywhere_checks=ON   #组同步中有任何改变检测更新
group_replication_allow_local_disjoint_gtids_join=1   #放弃自己信息以master事件为主[root@mysql-node1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS! [root@mysql-node1 ~]# rm -rf /data/mysql/*##############################重新初始化：#######################################[root@mysql-node1 ~]# mysqld --initialize --user=mysql
(X;NP&0qyA:d ---- 记住初始密码#########################修改密码并输入参数：###################################[root@mysql-node1 ~]# mysql -uroot -p'(X;NP&0qyA:d'
mysql> alter user root@localhost identified by '123';
Query OK, 0 rows affected (0.01 sec)mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)mysql> CREATE USER ding@'%' IDENTIFIED BY 'ding';
Query OK, 0 rows affected (0.00 sec)mysql>  GRANT REPLICATION SLAVE ON *.* TO ding@'%';
Query OK, 0 rows affected (0.00 sec)mysql>  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)mysql>  CHANGE MASTER TO MASTER_USER='ding', MASTER_PASSWORD='ding' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> SET GLOBAL group_replication_bootstrap_group=ON;  #这里需要开启组信息，因为现在还没有组信息，不开会启动不了组。
Query OK, 0 rows affected (0.00 sec)mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.03 sec)mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
| group_replication_applier | 2e311876-61e5-11ef-96d2-000c2909b7ec | mysql-node1.timingding.org |        3306 | ONLINE       |
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
1 row in set (0.00 sec)###############################使用scp命令把配置传到20和30上面：##########################[root@mysql-node1 ~]# scp /etc/my.cnf root@172.25.254.20:/etc/my.cnf
[root@mysql-node1 ~]# scp /etc/my.cnf root@172.25.254.30:/etc/my.cnf############################172.25.254.20上面：##################################[root@mysql-node2 ~]# vim /etc/my.cnf
进去后把表示改为20
还把 信息端口ip改为20和上面一样的操作：
先暂停MySQL服务，然后删除/data/mysql/*下的文件
重新初始化，改密码，这里就不需要把组给打开，因为已经有组信息了。mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)mysql> CREATE USER ding@'%' IDENTIFIED BY 'ding';
Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO ding@'%';
Query OK, 0 rows affected (0.00 sec)mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)mysql>  SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)mysql>  CHANGE MASTER TO MASTER_USER='ding', MASTER_PASSWORD='ding' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql>  START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (5.70 sec)mysql>  SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
| group_replication_applier | 2e311876-61e5-11ef-96d2-000c2909b7ec | mysql-node1.timingding.org |        3306 | ONLINE       |
| group_replication_applier | 56159d07-61e6-11ef-a90f-000c299766ae | mysql-node2.timingding.org |        3306 | ONLINE       |
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
2 rows in set (0.00 sec)##########################172.25.254.30上面：#################################[root@mysql-node2 ~]# vim /etc/my.cnf
进去后把表示改为30
还把 信息端口ip改为30和上面一样的操作：
先暂停MySQL服务，然后删除/data/mysql/*下的文件
重新初始化，改密码，这里就不需要把组给打开，因为已经有组信息了。mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)mysql> CREATE USER ding@'%' IDENTIFIED BY 'ding';
Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO ding@'%';
Query OK, 0 rows affected (0.00 sec)mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)mysql> CHANGE MASTER TO MASTER_USER='ding', MASTER_PASSWORD='ding' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (3.06 sec)mysql>  SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST                | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
| group_replication_applier | 2e311876-61e5-11ef-96d2-000c2909b7ec | mysql-node1.timingding.org |        3306 | ONLINE       |
| group_replication_applier | 56159d07-61e6-11ef-a90f-000c299766ae | mysql-node2.timingding.org |        3306 | ONLINE       |
| group_replication_applier | 8a71f4f2-61e6-11ef-be9c-000c2908e85e | mysql-node3.timingding.org |        3306 | ONLINE       |
+---------------------------+--------------------------------------+----------------------------+-------------+--------------+
3 rows in set (0.00 sec)

五、mysql-router（mysql路由）

MySQL Router

是一个对应用程序透明的InnoDB Cluster 连接路由服务，提供负载均衡、应用连接故障转移和客户端路由。

利用路由器的连接路由特性，用户可以编写应用程序来连接到路由器，并令路由器使用相应的路由策略来处理连接，使其连接到正确的MySQL数据库服务器

###############################安装所需软件包：#####################################
[root@mysql-node1 ~]# rpm -ivh mysql-router-community-8.4.0-1.el7.x86_64.rpm [root@mysql-node1 ~]# vim /etc/mysqlrouter/mysqlrouter.conf
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.254.10:3306,172.25.254.20:3306,172.25.254.30:3306
routing_strategy = round-robin[root@mysql-node1 ~]# systemctl start mysqlrouter.service
[root@mysql-node1 ~]# netstat -antlupe | grep 7001
tcp        0      0 0.0.0.0:7001            0.0.0.0:*               LISTEN      988        150547     41013/mysqlrouter   
[root@mysql-node1 ~]#172.25.254.20和172.25.254.30上面创建远程登录用户：
[root@mysql-node2 ~]# mysql -uroot -p123mysql> create user root@'%' identified by 'ding';
Query OK, 0 rows affected (0.00 sec)mysql> grant all ON *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)[root@mysql-node3 ~]# mysql -uroot -p123mysql> create user root@'%' identified by 'ding';
Query OK, 0 rows affected (0.00 sec)mysql> grant all ON *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)测试：
[root@mysql-node1 ~]# mysql -uroot -pding -h172.25.254.10 -P 7001mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          30 |
+-------------+
1 row in set (0.00 sec)重复来一次：
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          20 |
+-------------+
1 row in set (0.00 sec)

六、MySQL高可用（MHA）

MHA是用来解决单点故障问题的

什么是 MHA ？

MHA（Master High Availability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。
MHA 的出现就是解决MySQL 单点的问题。
MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。
MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用。

MHA 的组成

MHA由两部分组成:MHAManager (管理节点) MHA Node (数据库节点),MHA Manager 可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台 slave 节点上。
MHA Manager 会定时探测集群中的 master 节点。
当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master，然后将所有其他的
slave 重新指向新的 master

MHA 的特点

自动故障切换过程中，MHA从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失
使用半同步复制，可以大大降低数据丢失的风险，如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性
目前MHA支持一主多从架构，最少三台服务，即一主两从

MHA 工作原理

目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群必须最少有3台数据库服务器，一主二从，即一台充当Master，台充当备用Master，另一台充当从库。
MHA Node 运行在每台 MySQL 服务器上
MHAManager 会定时探测集群中的master 节点
当master 出现故障时，它可以自动将最新数据的slave 提升为新的master
然后将所有其他的slave 重新指向新的master，VIP自动漂移到新的master。
整个故障转移过程对应用程序完全透明。