MySQL集群技术详解

俗庸203

于 2024-08-26 17:57:40 发布

阅读量577

点赞数 19

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/D2961953033/article/details/141567968

版权

五、mysql-router（mysql路由）

6.2.1.3 恢复故障的mysql节点：

一、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 programs

PATH=$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 -p123

mysql> 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 distribution

Copyright (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 event
                  Master_Host: 172.25.254.10
                  Master_User: ding
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-node2-relay-bin.000010
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 746
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_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: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: b2e05366-6030-11ef-b06b-000c2909b7ec
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

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 programs

PATH=$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 event
                  Master_Host: 172.25.254.10
                  Master_User: ding
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1511
               Relay_Log_File: mysql-node3-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

master上面再写个数据：
[root@mysql-node1 ~]# mysql -uroot -p123

mysql> 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 -p123

mysql> 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: 10
                  Master_UUID: 4e591de3-6167-11ef-a877-000c2909b7ec
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 60    ----- 设置的延迟时间
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 


在master上面删除一个数据库：
[root@mysql-node1 ~]# mysql -uroot -p123

mysql> 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 -p123

mysql> 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 restart

mysql> 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 event
                  Master_Host: 172.25.254.10
                  Master_User: ding
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154       ---- 自动识别，全局ID
               Relay_Log_File: mysql-node2-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes



[root@mysql-node3 ~]# /etc/init.d/mysqld restart

mysql> 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 event
                  Master_Host: 172.25.254.10
                  Master_User: ding
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-node3-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_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 -p123

mysql> 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 -p123

mysql> 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 -p123

mysql> 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 7001

mysql> 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。
整个故障转移过程对应用程序完全透明。