Mysql 集群技术

最新推荐文章于 2024-08-27 21:25:01 发布

周湘zx

最新推荐文章于 2024-08-27 21:25:01 发布

阅读量613

点赞数 12

分类专栏：云原生高级文章标签： mysql 服务器 linux 数据库运维

本文链接：https://blog.csdn.net/weixin_68256171/article/details/141433401

版权

云原生高级专栏收录该内容

24 篇文章 1 订阅

订阅专栏

（1）single-primary mode(单写或单主模式)

（2）multi-primary mode(多写或多主模式)

3、实现mysql组复制

4、测试

五、mysql-router（mysql路由）

（1）切换方式： master未出现故障手动切换

（2）master故障手动切换

（3）自动切换

4、为MHA添加VIP功能

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

1、MySQL的源码编译安装

1、安装依赖性
[root@mysql mysql-5.7.44]# 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 -y

2、下载并解压源码包
[root@mysql ~]# ls
anaconda-ks.cfg  Desktop  Documents  Downloads  ks.cfg  Music  mysql-boost-5.7.44.tar.gz  Pictures  Public  Templates  Videos
[root@mysql ~]# tar zxf mysql-boost-5.7.44.tar.gz 
[root@mysql ~]# du -sh mysql-5.7.44/
475M	mysql-5.7.44/

3、源码编译安装mysql
[root@mysql ~]# yum install cmake -y
[root@mysql mysql-5.7.44]# 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 ~]# yum install libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm -y
[root@mysql mysql-5.7.44]# rm -fr CMakeCache.txt
[root@mysql mysql-5.7.44]# 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 mysql-5.7.44]# make -j4
[root@mysql mysql-5.7.44]# make install

2、MySQL部署

1、生成启动脚本
[root@mysql ~]# cd /usr/local/mysql/support-files/
[root@mysql support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@mysql support-files]# cp mysql.server /etc/init.d/mysqld

2、修改环境变量
[root@mysql ~]# vim ~/.bash_profile 
[root@mysql ~]# source ~/.bash_profile
[root@mysql ~]# cat ~/.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

3、生成配置文件
[root@mysql ~]# vim /etc/my.cnf
[root@mysql ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0

4、数据库初始化建立mysql基本数据
[root@mysql mysql]# useradd -s /sbin/nologin -M mysql
[root@mysql mysql]# mkdir /data/mysql -p
[root@mysql mysql]# chown mysql.mysql -R /data/mysql
[root@mysql ~]# mysqld --initialize --user=mysql
……
2024-08-22T10:40:32.552432Z 1 [Note] A temporary password is generated for root@localhost: nQsu;#%#u6uT        # nQsu;#%#u6uT数据库密码


[root@mysql ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql.zx.org.err'.
 SUCCESS! 
[root@mysql ~]# yum install chkconfig -y
[root@mysql ~]# chkconfig --list
[root@mysql ~]# chkconfig mysqld on

5、数据库安全初始化
[root@mysql ~]# mysql_secure_installation

6、测试
[root@mysql ~]# mkdir -p /var/lib/mysql/
[root@mysql ~]# ln -s /data/mysql/mysql.sock /var/lib/mysql/mysql.sock

二、MySQL的主从复制

1、配置master

[root@mysql-node1 ~]# vim /etc/my.cnf
[root@mysql-node1 ~]# cat /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
Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@mysql-node1 mysql]# mysql -uroot -p123 -e "SELECT @@server_id"

#进入数据库配置用户权限
[root@mysql-node1 ~]# mysql -p123
MySQL [(none)]> CREATE USER 'repl'@'%' IDENTIFIED BY '123';    #生成专门用来做复制的用
户，此用户是用于slave端做认证用
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO repl@'%';    #对这个用户进行授权
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SHOW MASTER STATUS;                            #查看master的状态
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      595 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

2、配置slave

[root@mysql-node2 ~]# vim /etc/my.cnf
[root@mysql-node2 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20

[root@mysql-node2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

[root@mysql-node2 ~]# mysql -p123
MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.25.254.110',MASTER_USER='repl',MASTER_PASSWORD='123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=595;

MySQL [(none)]> start slave;
MySQL [(none)]> SHOW SLAVE STATUS\G;

测试：

master：
MySQL [(none)]> create database lee;
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> create table lee.userlist (
    -> username varchar(20) not null,
    -> password varchar(50) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

MySQL [(none)]> INSERT INTO lee.userlist VALUE ('lee','123');
Query OK, 1 row affected (0.07 sec)

MySQL [(none)]> SELECT * FROM lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
+----------+----------+
1 row in set (0.00 sec)

slave：在slave中查看数据，有同步过来！
[root@mysql-node2 ~]# mysql -pl23
mysql> SELECT * FROM lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
+----------+----------+
1 row in set (0.00 sec)

当有数据时添加node3

[root@mysql-node3 ~]#  vim /etc/my.cnf
[root@mysql-node3 ~]#  cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30
[root@mysql-node3 ~]#  /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@mysql-node3 ~]#  mkdir -p /var/lib/mysql/
[root@mysql-node3 ~]# ln -s /data/mysql/mysql.sock /var/lib/mysql/mysql.sock
[root@mysql-node3 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql-node3 ~]# mkdir /data/mysql -p
[root@mysql-node3 ~]# chown mysql.mysql -R /data/mysql
[root@mysql-node3 ~]# mysqld --initialize --user=mysql
[root@mysql-node3 ~]# chkconfig mysqld on
[root@mysql-node3 ~]# mysql_secure_installation

[root@mysql-node1 mysql]# mysqldump -uroot -p123 lee > lee.sql    #从master节点备份数据
[root@mysql-node1 mysql]# scp lee.sql root@172.25.254.130:/root/

[root@mysql-node3 ~]# mysql -uroot -p123 -e "create database lee;"    #利用master节点中备份出来的lee.sql在slave2中拉平数据
[root@mysql-node3 ~]# mysql -uroot -p lee <lee.sql
Enter password: 
[root@mysql-node3 ~]# mysql -uroot -p123 -e "select * from lee.userlist;"
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
+----------+----------+

MySQL [(none)]> change master to master_host='172.25.254.110',master_user='repl',master_password='123',master_log_file='mysql-bin.000001',master_log_pos=1240;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

MySQL [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> show slave status\G;

测试：

[root@mysql-node1 ~]# mysql -uroot -p123 -e "INSERT INTO lee.userlist
VALUES('user3','123');"
[root@mysql-node2 ~]# mysql -uroot -p123 -e 'select * from lee.userlist;'
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
| user3    | 123      |
+----------+----------+
[root@mysql-node3 ~]# mysql -uroot -p123 -e 'select * from lee.userlist;'
+----------+----------+
| username | password |
+----------+----------+
| lee      | 123      |
| user3    | 123      |
+----------+----------+

4、延迟复制

延迟复制时用来控制sql线程的，和i/o线程无关

这个延迟复制不是i/o线程过段时间来复制，i/o是正常工作的

是日志已经保存在slave端了，那个sql要等多久进行回放

[root@mysql-node2 ~]# mysql -p123
MySQL [(none)]> STOP SLAVE SQL_THREAD;

MySQL [(none)]> CHANGE MASTER TO MASTER_DELAY=60;

MySQL [(none)]> START SLAVE SQL_THREAD;

MySQL [(none)]> SHOW SLAVE STATUS\G;

测试：在master中写入数据后过了延迟时间才能被查询到

5、慢查询日志

慢查询，顾名思义，执行很慢的查询

当执行SQL超过long_query_time参数设定的时间阈值（默认10s）时，就被认为是慢查询，这个 SQL语句就是需要优化的
慢查询被记录在慢查询日志里
慢查询日志默认是不开启的
如果需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

MySQL [(none)]> SET GLOBAL slow_query_log=ON;        ##慢查询日志开启
MySQL [(none)]> show variables like "slow%";
MySQL [(none)]> SHOW VARIABLES like "long%";
MySQL [(none)]> select sleep (10);
[root@mysql-node1 mysql]# cat /data/mysql/mysql-node1-slow.log    #慢查询日志

6、MySQL的并行复制

查看slave中的线程信息

默认情况下slave中使用的是sql单线程回放

在master中时多用户读写，如果使用sql单线程回放那么会造成组从延迟严重

开启MySQL的多线程回放可以解决上述问题

[root@mysql-node2 ~]# vim /etc/my.cnf
[root@mysql-node2 ~]# cat /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
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
[root@mysql-node2 ~]# /etc/init.d/mysqld restart

此时sql线程转化为协调线程，16个worker负责处理sql协调线程发送过来的处理请求

7、原理剖析

三个线程

实际上主从同步的原理就是基于 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？当读取的而操作远远高与写操作时。采用一主多从架构，数据库外层接入负载均衡层并搭配高可用机制

8、架构缺陷

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

三、半同步模式

1、原理

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

2、gtid模式

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

当激活GITD之后

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

#在master端和slave端开启gtid模式
[root@mysql-node1 mysql]# vim /etc/my.cnf
[root@mysql-node1 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL............ SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@mysql-node1 mysql]# cat /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-node2 ~]# cat /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
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON

[root@mysql-node3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=30
gtid_mode=ON
enforce-gtid-consistency=ON

[root@mysql-node2 ~]# mysql -uroot -p123
MySQL [(none)]> stop slave;
[root@mysql-node3 ~]# mysql -uroot -p123
MySQL [(none)]> stop slave;                       #停止slave端
MySQL [(none)]> change master to master_host='172.25.254.110', master_user='repl', master_password='123', master_auto_position=1;    #开启slave端的gtid
MySQL [(none)]> start slave;
MySQL [(none)]> SHOW SLAVE STATUS\G;

3、启用半同步模式

在master端配置启用半同步模式

[root@mysql-node1 mysql]# vim /etc/my.cnf
[root@mysql-node1 mysql]# cat /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]# /etc/init.d/mysqld restart
[root@mysql-node1 ~]# mysql -p123
MySQL [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
MySQL [(none)]> 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 [(none)]> SET GLOBAL rpl_semi_sync_master_enabled = 1;    #打开半同步功能
MySQL [(none)]> 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.02 sec)

MySQL [(none)]> 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.01 sec)

在slave端开启半同步功能

[root@mysql-node2 ~]# vim /etc/my.cnf    #开启半同步功能： rpl_semi_sync_master_enabled=1

[root@mysql-node2 ~]# mysql -p123
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.7.44 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]>  SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> STOP SLAVE IO_THREAD;        #重启io线程，半同步才能生效
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> START SLAVE IO_THREAD;       #重启io线程，半同步才能生效
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.01 sec)

MySQL [(none)]> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

MySQL [(none)]> select * from information_schema.PLUGINS where PLUGIN_NAME LIKE '%semi%'\G;

测试：在master端写入数据

[root@mysql-node1 ~]# mysql -p123
MySQL [(none)]> insert into lee.userlist values ('user4','123');
MySQL [(none)]> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 4     |
| 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      | 1226  |
| Rpl_semi_sync_master_tx_wait_time          | 4907  |
| Rpl_semi_sync_master_tx_waits              | 4     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 4     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

模拟故障：

[root@mysql-node2 ~]# mysql -p123
MySQL [(none)]> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)

[root@mysql-node3 ~]# mysql -p123
MySQL [(none)]> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)

#在master端插入数据
MySQL [(none)]> insert into lee.userlist values ('user5','555');
Query OK, 1 row affected (10.00 sec)        #10秒超时

MySQL [(none)]> SHOW STATUS LIKE 'Rpl_semi%';
+--------------------------------------------+-------+
| 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             | 4     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |
| Rpl_semi_sync_master_status                | OFF   |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 1226  |
| Rpl_semi_sync_master_tx_wait_time          | 4907  |
| Rpl_semi_sync_master_tx_waits              | 4     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 4     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

四、mysql高可用之组复制 (MGR)

组复制是 MySQL 5.7.17 版本出现的新特性，它提供了高可用、高扩展、高可靠的 MySQL 集群服务
MySQL 组复制分单主模式和多主模式，传统的mysql复制技术仅解决了数据同步的问题， MGR 对属于同一组的服务器自动进行协调。
对于要提交的事务，组成员必须就全局事务序列中给定事务的顺序达成一致
提交或回滚事务由每个服务器单独完成，但所有服务器都必须做出相同的决定
如果存在网络分区，导致成员无法达成事先定义的分割策略，则在解决此问题之前系统不会继续进行，这是一种内置的自动裂脑保护机制
MGR由组通信系统( Group Communication System ，GCS ) 协议支持该系统提供故障检测机制、组成员服务以及安全且有序的消息传递

1、组复制流程

首先将多个节点共同组成一个复制组，在执行读写（RW）事务的时候，需要通过一致性协议层（Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节点）的同意，大多数指的是同意的节点数量需要大于（N/2+1），这样才可以进行提交，而不是原发起方一个说了算。而针对只读（RO）事务则不需要经过组内同意，直接提交即可

2、组复制单主和多主模式

（1）single-primary mode(单写或单主模式)

单写模式 group 内只有一台节点可写可读，其他节点只可以读。当主服务器失败时，会自动选择新的主服务器

（2）multi-primary mode(多写或多主模式)

组内的所有机器都是 primary 节点，同时可以进行读写操作，并且数据是最终一致的。

3、实现mysql组复制

[root@mysql-node1 ~]# vim /etc/my.cnf
[root@mysql-node1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=1
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log-bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.254.110:33061"
group_replication_group_seeds="172.25.254.110:33061,172.25.254.120:33061,172.25.254.130: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

[root@mysql-node1 ~]# rm -rf /data/mysql/*
[root@mysql-node1 ~]# mysqld --user=mysql --initialize
[root@mysql-node1 ~]# killall -9 mysql
[root@mysql-node1 ~]# ps aux | grep mysql
root      35147  0.0  0.0 112808   968 pts/3    S+   04:30   0:00 grep --color=auto mysql
[root@mysql-node1 ~]#  /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql-node1.zx.org.err'.
. SUCCESS! 

[root@mysql-node1 ~]# mysql -uroot -plee
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> CREATE USER rpl_user@'%' IDENTIFIED BY 'lee';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='lee' FOR CHANNEL 'group_replication_recovery'; 
Query OK, 0 rows affected, 2 warnings (0.03 sec)

MySQL [(none)]> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.12 sec)

MySQL [(none)]> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.02 sec)

MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST        | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | 919678f5-6387-11ef-bf1b-000c29caba2c | mysql-node1.zx.org |        3306 | ONLINE       |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
1 row in set (0.00 sec)

[root@mysql-node1 ~]# vim /etc/hosts
[root@mysql-node1 ~]# cat /etc/hosts        # 三台主机都要做
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
172.25.254.110	mysql-node1.zx.org
172.25.254.120  mysql-node2.zx.org
172.25.254.130  mysql-node3.zx.org

若出现该问题：
[root@mysql-node1 ~]# mysql -uroot -p'yaY,gL>=X7eR'
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

解决措施：参考：https://blog.csdn.net/weixin_45777669/article/details/115672825
[root@mysql-node1 ~]# vim /etc/my.cnf    #添加skip-grant-tables=1跳过权限认证
[root@mysql-node1 ~]# /etc/init.d/mysqld restart
[root@mysql-node1 ~]# mysql -uroot
MySQL [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [mysql]> UPDATE user SET `password_expired`='N' where user='root'; 
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

MySQL [mysql]> select * from mysql.user where user='root' \G;

MySQL [mysql]> UPDATE user SET `authentication_string` = PASSWORD('root');
Query OK, 3 rows affected, 1 warning (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 1

MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> alter user root@localhost identified by '123';
Query OK, 0 rows affected (0.01 sec)

MySQL [mysql]> exit;
Bye

密码更改后注释skip-grant-tables、skip-networking 使用修改后的密码重启mysql
[root@mysql-node1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL.. SUCCESS!

[root@mysql-node1 ~]# scp /etc/my.cnf root@172.25.254.120:/etc/my.cnf
[root@mysql-node1 ~]# scp /etc/my.cnf root@172.25.254.130:/etc/my.cnf

[root@mysql-node2 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=2
#skip-networking
#skip-grant-tables=1
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log-bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.254.120:33061"
group_replication_group_seeds="172.25.254.110:33061,172.25.254.120:33061,172.25.254.130: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

[root@mysql-node2 ~]# /etc/init.d/mysqld restart
[root@mysql-node2 ~]# mysql -plee
MySQL [(none)]> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> CREATE USER rpl_user@'%' IDENTIFIED BY 'lee';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='lee' FOR CHANNEL
    -> 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.04 sec)

MySQL [(none)]> START GROUP_REPLICATION;
MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST        | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | 4a975168-6393-11ef-850e-000c29caba2c | mysql-node1.zx.org |        3306 | ONLINE       |
| group_replication_applier | b6704994-6395-11ef-94bf-000c2973bf31 | mysql-node2.zx.org |        3306 | ONLINE       |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
2 rows in set (0.01 sec)

[root@mysql-node3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=3
#skip-networking
#skip-grant-tables=1
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log-bin=binlog
binlog_format=ROW
plugin_load_add='group_replication.so'
transaction_write_set_extraction=XXHASH64
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off
group_replication_local_address="172.25.254.130:33061"
group_replication_group_seeds="172.25.254.110:33061,172.25.254.120:33061,172.25.254.130: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

[root@mysql-node3 ~]# mysql -plee
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> CREATE USER rpl_user@'%' IDENTIFIED BY 'lee';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='lee' FOR CHANNEL
    -> 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

MySQL [(none)]> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (3.16 sec)

MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST        | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
| group_replication_applier | 4a975168-6393-11ef-850e-000c29caba2c | mysql-node1.zx.org |        3306 | ONLINE       |
| group_replication_applier | b6704994-6395-11ef-94bf-000c2973bf31 | mysql-node2.zx.org |        3306 | ONLINE       |
| group_replication_applier | f9def3bf-638c-11ef-8412-000c29a37fab | mysql-node3.zx.org |        3306 | ONLINE       |
+---------------------------+--------------------------------------+--------------------+-------------+--------------+
3 rows in set (0.00 sec)

4、测试

在每个节点都可以完成读写

[root@mysql-node1 ~]# mysql -plee
MySQL [(none)]>  CREATE DATABASE lee;
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> CREATE TABLE lee.userlist(
    -> username VARCHAR(10) PRIMARY KEY NOT NULL,
    -> password VARCHAR(50) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

MySQL [(none)]> INSERT INTO lee.userlist VALUES ('user1','111');
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> SELECT * FROM lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.00 sec)

[root@mysql-node2 ~]# mysql -plee
MySQL [(none)]> INSERT INTO lee.userlist values ('user2','222');
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> select *from lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
+----------+----------+
2 rows in set (0.00 sec)

[root@mysql-node3 ~]# mysql -plee
MySQL [(none)]> INSERT INTO lee.userlist values ('user3','333');
Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> select * from lee.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
| user3    | 333      |
+----------+----------+
3 rows in set (0.00 sec)

五、mysql-router（mysql路由）

MySQL Router 是一个对应用程序透明的InnoDB Cluster连接路由服务，提供负载均衡、应用连接故障转移和客户端路由。利用路由器的连接路由特性，用户可以编写应用程序来连接到路由器，并令路由器使用相应的路由策略来处理连接，使其连接到正确的MySQL数据库服务器

[root@mysql-router ~]# rpm -ivh mysql-router-community-8.4.0-1.el7.x86_64.rpm

[root@mysql-router ~]# vim /etc/mysqlrouter/mysqlrouter.conf
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.254.110:3306,172.25.254.120:3306,172.25.254.130:3306
routing_strategy = round-robin

[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.254.130:3306,172.25.254.120:3306,172.25.254.110:3306
routing_strategy = first-available

[root@mysql-router ~]# systemctl start mysqlrouter.service


[root@mysql-node2 ~]# mysql -plee
MySQL [(none)]> create user root@'%' identified by '1ee';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> grant all on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)

[root@mysql-router ~]# mysql -uroot -plee -h 172.25.254.110 -P 7001

六、mysql高可用之MHA

1、MHA概述

——Master的单点故障问题

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

故障切换备选主库的算法

一般判断从库的是从（position/GTID）判断优劣，数据有差异，最接近于master的slave，成为备选主。
数据一致的情况下，按照配置文件顺序，选择备选主库。
设定有权重（candidate_master=1），按照权重强制指定备选主。
- （1）默认情况下如果一个slave落后master 100M的relay logs的话，即使有权重，也会失效。
- （2）如果check_repl_delay=0的话，即使落后很多日志，也强制选择其为备选主

2、MHA部署实施

（1）免密认证

[root@mysql-mha MHA-7]# ssh-keygen 
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
/root/.ssh/id_rsa already exists.
Overwrite (y/n)? y
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:2o4QwNUu5uzWAWzf1z7T7B4WKtDGYP3sljyqqhTKRSg root@mysql-mha.zx.org
The key's randomart image is:
+---[RSA 2048]----+
|    ..           |
| . o  .  .       |
|E +...  o .      |
| . o* .. + o     |
|   =++ .S +.o .  |
| . ooooo.o.o.o . |
|  o.o.......Boo  |
|   .o..o   ++oo. |
|   ...o.o..  =o  |
+----[SHA256]-----+
# /etc/hosts做解析
[root@mysql-mha ~]# vim /etc/hosts
[root@mysql-mha ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
172.25.254.110  mysql-node1.zx.org
172.25.254.120  mysql-node2.zx.org
172.25.254.130  mysql-node3.zx.org
172.25.254.140	mysql-mha.zx.org

[root@mysql-node1 ~]# vim /etc/ssh/sshd_config        # 打开UseDNS no
[root@mysql-node2 ~]# vim /etc/ssh/sshd_config
[root@mysql-node3 ~]# vim /etc/ssh/sshd_config
[root@mysql-node1 ~]# systemctl restart sshd
[root@mysql-node2 ~]# systemctl restart sshd
[root@mysql-node3 ~]# systemctl restart sshd
[root@mysql-mha MHA-7]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.110
[root@mysql-mha MHA-7]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.120
[root@mysql-mha MHA-7]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.120


[root@mysql-mha ~]# cd .ssh/
[root@mysql-mha .ssh]# scp id_rsa root@172.25.254.110:/root/.ssh/id_rsa  
[root@mysql-mha .ssh]# scp id_rsa root@172.25.254.120:/root/.ssh/id_rsa    
[root@mysql-mha .ssh]# scp id_rsa root@172.25.254.130:/root/.ssh/id_rsa 

[root@mysql-mha masterha]# masterha_check_ssh --conf=/etc/masterha/app1.cnf    # 检查免密登录

https://blog.csdn.net/qq_65960840/article/details/135016471
[root@mysql-node1 ~]# ssh-keygen -t rsa
[root@mysql-node1 ~]# cp .ssh/id_rsa.pub node1_id_rsa.pub
[root@mysql-node1 ~]# scp node1_id_rsa.pub root@172.25.254.140:.ssh/

[root@mysql-node2 ~]# ssh-keygen -t rsa
[root@mysql-node2 ~]# cp .ssh/id_rsa.pub node2_id_rsa.pub
[root@mysql-node2 ~]# scp node2_id_rsa.pub root@172.25.254.140:.ssh/

[root@mysql-node3 ~]# ssh-keygen -t rsa
[root@mysql-node3 ~]# cp .ssh/id_rsa.pub node3_id_rsa.pub
[root@mysql-node3 ~]# scp node3_id_rsa.pub root@172.25.254.140:.ssh/

[root@mysql-mha ~]# ssh-keygen -t rsa
[root@mysql-mha ~]# cd .ssh
[root@mysql-mha .ssh]# cat node1_id_rsa.pub node2_id_rsa.pub node3_id_rsa.pub >> au
[root@mysql-mha .ssh]# scp authorized_keys root@172.25.254.110:.ssh/                                                                   
[root@mysql-mha .ssh]# scp authorized_keys root@172.25.254.120:.ssh/                                                                  
[root@mysql-mha .ssh]# scp authorized_keys root@172.25.254.130:.ssh/

[root@mysql-node1 ~]# chmod 700 .ssh
[root@mysql-node1 ~]# chmod 600 .ssh/authorized_keys
[root@mysql-node2 ~]# chmod 700 .ssh
[root@mysql-node2 ~]# chmod 600 .ssh/authorized_keys
[root@mysql-node3 ~]# chmod 700 .ssh
[root@mysql-node3 ~]# chmod 600 .ssh/authorized_keys
[root@mysql-mha ~]# chmod 700 .ssh
[root@mysql-mha ~]# chmod 600 .ssh/authorized_keys

[root@mysql-mha ~]# ssh-copy-id root@172.25.254.110    # 将公钥复制到目标主机
[root@mysql-mha ~]# ssh-copy-id root@172.25.254.120
[root@mysql-mha ~]# ssh-copy-id root@172.25.254.130

（2）初始化+ 搭建主两从架构

[root@mysql-node1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=1
gtid_mode=ON
enforce_gtid_consistency=ON

[root@mysql-node2 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=2
gtid_mode=ON
enforce_gtid_consistency=ON

[root@mysql-node3 ~]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=3
gtid_mode=ON
enforce_gtid_consistency=ON

[root@mysql-node1 ~]# /etc/init.d/mysqld stop
[root@mysql-node2 ~]# /etc/init.d/mysqld stop
[root@mysql-node3 ~]# /etc/init.d/mysqld stop
[root@mysql-node1 ~]# rm -rf /data/mysql/*
[root@mysql-node2 ~]# rm -rf /data/mysql/*
[root@mysql-node2 ~]# rm -rf /data/mysql/*
[root@mysql-node1 ~]# mysqld --user=mysql --initialize
[root@mysql-node2 ~]# mysqld --user=mysql --initialize
[root@mysql-node3 ~]# mysqld --user=mysql --initialize
[root@mysql-node1 ~]# /etc/init.d/mysqld start
[root@mysql-node2 ~]# /etc/init.d/mysqld start
[root@mysql-node3 ~]# /etc/init.d/mysqld start

[root@mysql-node1 ~]# mysql -plee
MySQL [(none)]>  CREATE USER 'repl'@'%' IDENTIFIED BY 'lee';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

[root@mysql-node2 ~]# mysql -p
MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.25.254.110', MASTER_USER='repl',
    -> MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

MySQL [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> sTART SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.01 sec)

[root@mysql-node3 ~]# mysql -p
MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.25.254.110', MASTER_USER='repl',
    -> MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

MySQL [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)

MySQL [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.01 sec)

（3）安装MHA所需要的软件

[root@mysql-mha ~]# unzip MHA-7.zip
[root@mysql-mha ~]# cd MHA-7/
[root@mysql-mha MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  perl-Email-Date-Format-1.002-15.el7.noarch.rpm  perl-MIME-Lite-3.030-1.el7.noarch.rpm
mha4mysql-manager-0.58.tar.gz                   perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm       perl-MIME-Types-1.38-2.el7.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm     perl-Mail-Sender-0.8.23-1.el7.noarch.rpm        perl-Net-Telnet-3.03-19.el7.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm          perl-Mail-Sendmail-0.79-21.el7.noarch.rpm       perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@mysql-mha MHA-7]# yum install *.rpm -y
[root@mysql-mha MHA-7]# vim /etc/ssh/sshd_config    # UseDNS no
[root@mysql-mha MHA-7]# systemctl restart sshd

[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.110:/root
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.120:/root
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.130:/root
[root@mysql-node1 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@mysql-node2 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@mysql-node2 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

[root@mysql-mha MHA-7]# masterha_        # Manager工具包
masterha_check_repl       masterha_check_status     masterha_manager          masterha_master_switch    masterha_stop
masterha_check_ssh        masterha_conf_host        masterha_master_monitor   masterha_secondary_check  
[root@mysql-mha ~]# masterha_manager --help    # 生成配置目录和配置文件
Usage:
    masterha_manager --global_conf=/etc/masterha_default.cnf
    --conf=/usr/local/masterha/conf/app1.cnf

    See online reference
    (http://code.google.com/p/mysql-master-ha/wiki/masterha_manager) for
    details.
[root@mysql-mha MHA-7]# mkdir /etc/masterha    # 生成配置文件
[root@mysql-mha MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  perl-Email-Date-Format-1.002-15.el7.noarch.rpm  perl-MIME-Lite-3.030-1.el7.noarch.rpm
mha4mysql-manager-0.58.tar.gz                   perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm       perl-MIME-Types-1.38-2.el7.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm     perl-Mail-Sender-0.8.23-1.el7.noarch.rpm        perl-Net-Telnet-3.03-19.el7.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm          perl-Mail-Sendmail-0.79-21.el7.noarch.rpm       perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm

[root@mysql-mha MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz
[root@mysql-mha MHA-7]# cd mha4mysql-manager-0.58/
[root@mysql-mha mha4mysql-manager-0.58]# ls
AUTHORS  bin  COPYING  debian  lib  Makefile.PL  MANIFEST  MANIFEST.SKIP  README  rpm  samples  t  tests
[root@mysql-mha mha4mysql-manager-0.58]# cd samples/conf/
[root@mysql-mha conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app1.cnf

[root@mysql-node1 ~]# mysql -uroot -plee      # 三个主机都需要
MySQL [(none)]> create user root@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> grant all on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)
[root@mysql-node3 ~]# mysql -uroot -p123 -h172.25.254.110    确保能够互相登录

在软件中包含的工具包介绍

1.Manager工具包主要包括以下几个工具：

masterha_check_ssh #检查MHA的SSH配置状况
masterha_check_repl #检查MySQL复制状况
masterha_manger #启动MHA
masterha_check_status #检测当前MHA运行状态
masterha_master_monitor #检测master是否宕机
masterha_master_switch #控制故障转移（自动或者手动）
masterha_conf_host #添加或删除配置的server信息

2.Node工具包（通常由masterHA主机直接调用，无需人为执行）

save_binary_logs #保存和复制master的二进制日志
apply_diff_relay_logs #识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog #去除不必要的ROLLBACK事件（MHA已不再使用这个工具）
purge_relay_logs #清除中继日志（不会阻塞SQL线程）

[root@mysql-node2 ~]# ip addr add 172.25.254.111/24 dev eth0
[root@mysql-mha ~]# vim /etc/masterha/app1.cnf    # 编辑配置文件
[root@mysql-mha ~]# cat /etc/masterha/app1.cnf
[server default]
user=root
password=123
ssh_user=root
repl_user=repl
repl_password=lee
master_binlog_dir=/data/mysql
remote_workdir=/tmp/masterha
secondary_check_script= masterha_secondary_check -s 172.25.254.110 -s 172.25.254.111
ping_interval=3
# master_ip_failover_script= /script/masterha/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
# master_ip_online_change_script= /script/masterha/master_ip_online_change
[server default]
manager_workdir=/etc/masterha
manager_log=/etc/masterha/manager.log

[server1]
hostname=172.25.254.110
candidate_master=1
check_repl_delay=0 

[server2]
hostname=172.25.254.120
candidate_master=1

[server3]
hostname=172.25.254.130
no_master=1

[root@mysql-mha masterha]# masterha_check_ssh --conf=/etc/masterha/app1.cnf     # 检测网络及ssh免密
[root@mysql-mha masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf    # 检查数据主从复制情况

3、MHA的故障切换

MHA的故障切换过程共包括以下的步骤：

1.配置文件检查阶段，这个阶段会检查整个集群配置文件配置
2.宕机的master处理，这个阶段包括虚拟ip摘除操作，主机关机操作
3.复制dead master和最新slave相差的relay log，并保存到MHA Manger具体的目录下
4.识别含有最新更新的slave
5.应用从master保存的二进制日志事件（binlog events）
6.提升一个slave为新的master进行复制 7.使其他的slave连接新的master进行复制

（1）切换方式： master未出现故障手动切换

#在master数据节点还在正常工作情况下
[root@mysql-mha ~]# masterha_master_switch \
> --conf=/etc/masterha/app1.cnf \
> --master_state=alive \
> --new_master_host=172.25.254.120 \
> --new_master_port=3306 \
> --orig_master_is_new_slave \
> --running_updates_limit=10000

检测：

（2）master故障手动切换

[root@mysql-node2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS! 

#在MHA中做故障切换
[root@mysql-mha masterha]# masterha_master_switch --master_state=dead  --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.254.120 --dead_master_port=3306 --new_master_host=172.25.254.110 --new_master_port=3306 --ignore_last_failover
# --ignore_last_failover 表示忽略在/etc/masterha/目录中在切换过程中生成的锁文件

恢复故障mysql节点
[root@mysql-node2 ~]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS! 
[root@mysql-node2 ~]# mysql -plee
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.25.254.110', MASTER_USER='repl',MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

#测试一主两从是否正常
[root@mysql-mha masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf

（3）自动切换

#删掉切换锁文件
[root@mysql-mha masterha]# rm -fr app1.failover.complete
#监控程序通过指定配置文件监控master状态，当master出问题后自动切换并退出避免重复做故障切换
[root@mysql-mha masterha]# masterha_manager --conf=/etc/masterha/app1.cnf
[root@mysql-mha masterha]# cat /etc/masterha/manager.log

#恢复故障节点
[root@mysql-node2 ~]# /etc/init.d/mysqld start
Starting MySQL SUCCESS!
MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.25.254.110', MASTER_USER='repl',MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;

#清除锁文件
[root@mysql-mha masterha]# rm -rf app1.failover.complete manager.log

4、为MHA添加VIP功能

[root@mysql-mha ~]# cp master_ip_failover master_ip_online_change /usr/local/bin/
[root@mysql-mha ~]# chmod +x /usr/local/bin/master_ip_*
#修改脚本在脚本中只需要修改下vip即可
[root@mysql-mha ~]# vim /usr/local/bin/master_ip_failover
my $vip = '172.25.254.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";

[root@mysql-mha ~]# vim /usr/local/bin/master_ip_online_change
my $vip = '172.25.254.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
my $exit_code = 0;

# 启动监控程序
[root@mysql-mha ~]# masterha_manager --conf=/etc/masterha/app1.cnf &

#在master节点添加VIP
[root@mysql-node1 ~]# ip add add 172.25.254.100/24 dev eth0

# 模拟故障
[root@mysql-node1 ~]# /etc/init.d/mysqld stop    # 关闭主节点服务
Shutting down MySQL............ SUCCESS!

[root@mysql-mha ~]# cd /etc/masterha/
[root@mysql-mha masterha]# cat manager.log

[root@mysql-node2 ~]# /etc/init.d/mysqld start
[root@mysql-node2 ~]# mysql -plee
MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.25.254.110', MASTER_USER='repl',MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1;
[root@mysql-node2 ~]# rm -rf app1.failover.complete manager.log

# 手动切换后查看vip变化
[root@mysql-mha masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.110 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

周湘zx

关注

12
点赞
踩
18

收藏

觉得还不错? 一键收藏
0
评论
Mysql 集群技术

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

扫一扫

专栏目录