mysql组复制、mysql路由器、mysql的MHA高可用

---

前言

---

一、mysql组复制

1.实验机配置

server1配置

首先停止数据库
[root@server1 mysql]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!清除数据
[root@server1 ~]# cd /data/mysql 
[root@server1 mysql]# rm -fr *[root@server1 mysql]# vim /etc/my.cnf     ##根据实际情况修改主机名和网段
...
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"server_id=1                          ##
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=ROWplugin_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= "server1:33061"       ##主机名需对应
group_replication_group_seeds= "server1:33061,server2:33061,server3:33061"  
group_replication_bootstrap_group=off
group_replication_ip_whitelist="192.168.56.0/24,127.0.0.1/8"      ##ip需对应
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1初始化
[root@server1 mysql]# mysqld --initialize --user=mysql
启动服务
[root@server1 mysql]# /etc/init.d/mysqld start[root@server1 mysql]# mysql -p
#首先修改临时密码
mysql> alter user root@localhost identified by 'westos';
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 rpl_user@'%' IDENTIFIED BY 'westos';
Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
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='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> SET GLOBAL group_replication_bootstrap_group=ON;	#只在server1上执行
Query OK, 0 rows affected (0.00 sec)mysql>  START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.19 sec)mysql>  SET GLOBAL group_replication_bootstrap_group=OFF;	 #只在server1上执行
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 21262bc0-b4e3-11ed-a6a0-000c298f4b2a | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

server2配置

首先停止数据库
[root@server2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!清除数据
[root@server2 ~]# cd /data/mysql 
[root@server2 mysql]# rm -fr *[root@server2 mysql]# vim /etc/my.cnf   ##根据实际情况修改主机名和网段
...
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"server_id=2                         ##需对应
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=ROWplugin_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= "server2:33061"    ##需对应
group_replication_group_seeds= "server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="192.168.56.0/24,127.0.0.1/8"   ##需对应
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1初始化
[root@server2 mysql]# mysqld --initialize --user=mysql启动服务
[root@server2 mysql]# /etc/init.d/mysqld start[root@server2 mysql]# mysql -p
#首先修改临时密码
mysql> alter user root@localhost identified by 'westos';
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 rpl_user@'%' IDENTIFIED BY 'westos';
Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
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='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql>  START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.19 sec)mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 21262bc0-b4e3-11ed-a6a0-000c298f4b2a | server1     |        3306 | ONLINE       |
| group_replication_applier | e8be5058-b4e3-11ed-8788-000c29f15153 | server2     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

server3（与server一致）

首先停止数据库
[root@server3 ~]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS!清除数据
[root@server3 ~]# cd /data/mysql 
[root@server3 mysql]# rm -fr *[root@server3 mysql]# vim /etc/my.cnf
...
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"server_id=3
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=ROWplugin_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= "server3:33061"
group_replication_group_seeds= "server1:33061,server2:33061,server3:33061"
group_replication_bootstrap_group=off
group_replication_ip_whitelist="192.168.56.0/24,127.0.0.1/8"
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=1
#根据实际情况修改主机名和网段初始化
[root@server3 mysql]# mysqld --initialize --user=mysql启动服务
[root@server3 mysql]# /etc/init.d/mysqld start[root@server3 mysql]# mysql -p
#首先修改临时密码
mysql> alter user root@localhost identified by 'westos';
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 rpl_user@'%' IDENTIFIED BY 'westos';
Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
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='rpl_user', MASTER_PASSWORD='westos' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql>  START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.19 sec)mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 21262bc0-b4e3-11ed-a6a0-000c298f4b2a | server1     |        3306 | ONLINE       |
| group_replication_applier | 4c655909-b4e4-11ed-8dcc-000c29259218 | server3     |        3306 | ONLINE       |
| group_replication_applier | e8be5058-b4e3-11ed-8788-000c29f15153 | server2     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

2.测试

所有节点都可以读写数据

server1：
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO t1 VALUES (1, 'Luis');
Query OK, 1 row affected (0.03 sec)mysql>  SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)server2：
mysql> select * from test.t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
+----+------+
1 row in set (0.00 sec)mysql> INSERT INTO test.t1 VALUES (2, 'hello');
Query OK, 1 row affected (0.00 sec)server3：
mysql>  select * from test.t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 | Luis |
|  2 | hello  |
+----+------+
2 rows in set (0.00 sec)mysql> INSERT INTO test.t1 VALUES (3, 'westos');
Query OK, 1 row affected (0.01 sec)

二、mysql路由器

安装软件
[root@server4 ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm
配置服务
[root@server4 ~]# cd /etc/mysqlrouter/
[root@server4 mysqlrouter]# vim mysqlrouter.conf
…
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 192.168.56.11:3306,192.168.56.12:3306,192.168.56.13:3306
routing_strategy = round-robin
[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 192.168.56.13:3306,192.168.56.12:3306,192.168.56.11:3306
routing_strategy = first-available
启动服务
[root@server4 mysqlrouter]# systemctl enable --now mysqlrouter.service
安装mysql客户端工具
[root@server4 ~]# yum install -y mariadb
在mysql集群中创建远程测试用户
mysql> grant all on test.* to ‘wxh’@‘%’ identified by ‘westos’;
连接mysql路由器
[root@server4 ~]# mysql -h 192.168.56.14 -P 7001 -u wxh -pwestos
MySQL [(none)]> select * from test.t1;
±—±-------+
| c1 | c2 |
±—±-------+
| 1 | Luis |
| 2 | wxh |
| 3 | westos |
±—±-------+
3 rows in set (0.00 sec)
在server1上查看网络连接
[root@server1 mysql]# yum install -y lsof
[root@server1 mysql]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 15184 mysql 22u IPv6 87530 0t0 TCP *:mysql (LISTEN)
mysqld 15184 mysql 83u IPv6 93365 0t0 TCP server1:mysql->server4:55504 (ESTABLISHED)
[root@server4 ~]# mysql -h 192.168.56.14 -P 7002 -u wxh -pwestos
[root@server3 mysql]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 30811 mysql 22u IPv6 73360 0t0 TCP *:mysql (LISTEN)
mysqld 30811 mysql 81u IPv6 78061 0t0 TCP server3:mysql->server4:38594 (ESTABLISHED)

三、mysql之MHA高可用

1.MHA概念

1.什么是MHA
MHA（Master High Availability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件
MHA的出现就是解决MySQL单点故障的问题
MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作
MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用
2.MHA的组成
2.1MHA Node（数据节点）
MHA Node运行在每台MySQL服务器上
2.2MHA Manager（管理节点）
MHA Manager可以单独部署在一台独立的机器上，管理多个Master-Slave集群；也可以部署在一台Slave节点上
MHA Manager会定时探测集群中的master节点。当master出现故障时，它可以自动将最新数据的Slave提升为新的Master，然后将所有其他的Slave重新指向新的Master
整个故障迁移的过程对应用程序完全透明
3.MHA特点
自动故障切换的过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失
使用半同步复制，可以大大降低数据丢失的风险，如果只有一个Slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的Slave服务器上，因此可以保证所有节点的数据一致性
目前MHA支持一主多从架构，最少三台服务，即一主两从
MHA：为了解决的是故障切换、数据尽可能地保存，以及所有节点日志地一致性
4.MHA工作原理（流程）
MHA有3个部分
核心是主从
Manager 管理节点：管理数据库集群信息，定义、触发故障切换
Node 数据节点：主要负责保存日志、比较中继日志，选择主备
MHA会通过Node监控MySQL数据库服务地节点信息，定期检测和返回Master角色地健康状态（健康检查）
MHA通过将VIP定义在Master节点上，并且数据库的访问也从此VIP进入，当Master异常时，MHA会进行"故障切换"，就是VIP漂移+二进制日志保存
漂移到主备节点后，会通过脚本命令来控制MySQL服务器角色的变更，例如将主-备服务器变更为主服务器，将其他的从服务器指向新的主服务器，来继续运行
MHA工作原理总结如下：
1.从宕机崩溃的master保存二进制日志时间（binlog events）
2.识别含有最新的更新的slave日志
3.应用差异的中继日志（relay log）到其他的slave
4.应用从master保存的二进制日志事件
5.提升一个slave为新的master
6.使其他的slave联机新的master进行复制

1.创建一主两从集群

master配置：

清理数据
[root@server1 ~]# cd /data/mysql
[root@server1 mysql]# rm -fr *修改配置
[root@server1 mysql]# vim /etc/my.cnf
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog初始化
[root@server1 mysql]# mysqld --initialize --user=mysql启动服务
[root@server1 mysql]# /etc/init.d/mysqld start配置master
[root@server1 mysql]# mysql -p
mysql> alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.01 sec)mysql> grant replication slave on *.* to repl@'%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.01 sec)

slave配置：（server3与server2配置方法一致）

[root@server2 ~]# cd /data/mysql/
[root@server2 mysql]# rm -fr *[root@server2 mysql]# vim /etc/my.cnf
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog[root@server2 mysql]# mysqld --initialize --user=mysql[root@server2 mysql]# /etc/init.d/mysqld start[root@server2 mysql]# mysql -p
mysql>  alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.01 sec)mysql> change master to master_host='192.168.56.11', master_user='repl', master_password='westos', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 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: 192.168.56.11Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000002Read_Master_Log_Pos: 681Relay_Log_File: server2-relay-bin.000002Relay_Log_Pos: 888Relay_Master_Log_File: binlog.000002Slave_IO_Running: YesSlave_SQL_Running: Yes

测试

master：写入数据
mysql> create database westos;
Query OK, 1 row affected (0.00 sec)mysql> use westos
Database changedmysql> create table user_tb (-> username varchar(25) not null,-> password varchar(50) not null);
Query OK, 0 rows affected (0.01 sec)mysql> insert into user_tb values ('user1','111');
Query OK, 1 row affected (0.03 sec)mysql> insert into user_tb values ('user2','222');
Query OK, 1 row affected (0.00 sec)

slave:
mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
+----------+----------+
2 rows in set (0.00 sec)

2.MHA部署

server4管理端配置：

server4上安装管理端软件
[root@server4 ~]# cd MHA-7/
[root@server4 MHA-7]# yum install -y *.rpm管理端配置ssh免密
[root@server4 MHA-7]# ssh-keygen
[root@server4 MHA-7]# ssh-copy-id server4把密钥复制到各节点
[root@server4 ~]# scp -r .ssh/ server1:
[root@server4 ~]# scp -r .ssh/ server2:
[root@server4 ~]# scp -r .ssh/ server3:复制客户端软件
[root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server1:
[root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server2:
[root@server4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm server3:server1、server2、server3上安装客户端软件
[root@server1 ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpmMHA配置
[root@server4 ~]# mkdir /etc/masterha
[root@server4 ~]# vim /etc/masterha/app1.cnf
[server default]
user=root				#mysql管理员
password=westos		#mysql管理员密码
ssh_user=root			#ssh免密用户
repl_user=repl			#mysql主从复制用户
repl_password=westos
master_binlog_dir=/data/mysql	#mysql数据库目录
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 192.168.56.12 -s 192.168.56.13
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
manager_workdir=/etc/masterha
manager_log=/etc/masterha/manager.log
[server1]
hostname=192.168.56.11
candidate_master=1
check_repl_delay=0[server2]
hostname=192.168.56.12
candidate_master=1
check_repl_delay=0[server3]
hostname=192.168.56.13
no_master=1在master上设置mysql 管理员权限，slave节点会自动同步
mysql> grant all on *.* to root@'%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.00 sec)

检测各节点ssh免密连接
[root@server4 ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf

检测主从复制集群状态
[root@server4 ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf

3.故障切换

手动切换（master正常）
[root@server4 ~]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=192.168.56.12 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000手动切换（master故障）
手动停止master节点上的msql服务
[root@server2 ~]# /etc/init.d/mysqld stop[root@server4 masterha]#  masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=192.168.56.12 --dead_master_port=3306 --new_master_host=192.168.56.11 --new_master_port=3306 --ignore_last_failover故障节点需要手动修复，重新加入主从集群
[root@server2 ~]# /etc/init.d/mysqld start
[root@server2 ~]# mysql -pwestosmysql> change master to master_host='192.168.56.11', master_user='repl', master_password='westos', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> start slave;
Query OK, 0 rows affected (0.00 sec)自动切换
故障切换后会生成lock文件，需要手动删除
[root@server4 masterha]# rm -f app1.failover.complete启动manger程序，并打入后台运行，完成切换任务后进程会自动退出
[root@server4 masterha]# masterha_manager --conf=/etc/masterha/app1.cnf &在进行自动切换之前，必须保证主从集群正常，一主两从加入故障切换脚本
[root@server4 ~]# vim  /etc/masterha/app1.cnf
[server default]
user=root
password=westos
ssh_user=root
repl_user=repl
repl_password=westos
master_binlog_dir=/data/mysql
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 192.168.56.12 -s 192.168.56.13
ping_interval=3
master_ip_failover_script= /usr/local/bin/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
master_ip_online_change_script= /usr/local/bin/master_ip_online_change
manager_workdir=/etc/masterha
manager_log=/etc/masterha/manager.log[server1]
hostname=192.168.56.11
candidate_master=1
check_repl_delay=0[server2]
hostname=192.168.56.12
candidate_master=1
check_repl_delay=0[server3]
hostname=192.168.56.13
no_master=1需要有可执行权限
[root@server4 ~]# ll /usr/local/bin/
total 8
-rwxr-xr-x 1 root root 2158 Feb 26 11:20 master_ip_failover
-rwxr-xr-x 1 root root 3815 Feb 26 11:21 master_ip_online_change

最后按需要修改VIP地址即可

---