5.3 MySQL 高可用
5.3.1 MySQL 高可用解决方案
MySQL官方和社区里推出了很多高可用的解决方案，大体如下，仅供参考（数据引用自Percona）
– MMM: Multi-Master Replication Manager for MySQL，Mysql主主复制管理器是一套灵活的脚本程序，基于perl实现，用来对mysql replication进行监控和故障迁移，并能管理mysql Master-Master复制的配置(同一时间只有一个节点是可写的)
官网： http://www.mysql-mmm.org
https://code.google.com/archive/p/mysql-master-master/downloads
– MHA：Master High Availability，对主节点进行监控，可实现自动故障转移至其它从节点；通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库，出于机器成本的考虑，淘宝进行了改造，目前淘宝TMHA已经支持一主一从
官方网站：https://code.google.com/archive/p/mysql-master-ha/
https://github.com/yoshinorim/mha4mysql-manager/releases
https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58
– Galera Cluster：wsrep(MySQL extended with the Write Set Replication)通过wsrep协议在全局实现复制；任何一节点都可读写，不需要主从复制，实现多主读写
– GR（Group Replication）：MySQL官方提供的组复制技术(MySQL 5.7.17引入的技术)，基于原生复制技术Paxos算法，实现了多主更新，复制组由多个server成员构成，组中的每个server可独立地执行事务，但所有读写事务只在冲突检测成功后才会提交
5.3.2 MHA Master High Availability
5.3.2.1 MHA 工作原理和架构
MHA集群架构
MHA工作原理
- MHA利用 SELECT 1 As Value 指令判断master服务器的健康性,一旦master 宕机,MHA 从宕机崩溃的master保存二进制日志事件（binlog events）
- 识别含有最新更新的slave
- 应用差异的中继日志（relay log）到其他的slave
- 应用从master保存的二进制日志事件（binlog events）
- 提升一个slave为新的master
- 使其他的slave连接新的master进行复制
注意：
为了尽可能的减少主库硬件损坏宕机造成的数据丢失，因此在配置MHA的同时建议配置成MySQL的半同步复制
MHA软件
MHA软件由两部分组成，Manager工具包和Node工具包
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信息
masterha_stop --conf=app1.cnf 停止MHA
masterha_secondary_check 两个或多个网络线路检查MySQL主服务器的可用
Node工具包：这些工具通常由MHA Manager的脚本触发，无需人为操作）主要包括以下几个工具：
save_binary_logs #保存和复制master的二进制日志
apply_diff_relay_logs #识别差异的中继日志事件并将其差异的事件应用于其他的slave
filter_mysqlbinlog #去除不必要的ROLLBACK事件（MHA已不再使用此工具）
purge_relay_logs #清除中继日志（不会阻塞SQL线程）
MHA自定义扩展：
secondary_check_script #通过多条网络路由检测master的可用性
master_ip_ailover_script #更新Application使用的masterip
shutdown_script #强制关闭master节点
report_script #发送报告
init_conf_load_script #加载初始配置参数
master_ip_online_change_script #更新master节点ip地址
MHA配置文件：
global配置，为各application提供默认配置，默认文件路径 /etc/masterha_default.cnf
application配置：为每个主从复制集群
5.3.2.2 实现 MHA 实战案例
环境：四台主机
10.0.0.10 CentOS7 MHA管理端
10.0.0.11 CentOS8 MySQL8.0 Master
10.0.0.21 CentOS8 MySQL8.0 Slave1
10.0.0.31 CentOS8 MySQL8.0 Slave2
5.3.2.2.1 在管理节点上安装两个包mha4mysql-manager和mha4mysql-node
说明:
#manager端只支持centos7不支持centos8
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
#node端
mha4mysql-node-0.58-0.el7.centos.noarch.rpm支持MySQL5.7和MySQL8.0 ,但和CentOS8版本上的Mariadb -10.3.17不兼容
mha4mysql-node-0.56-0.el6.noarch.rpm 不支持CentOS 8，只支持CentOS7 以下版本
两个安装包
mha4mysql-manager
mha4mysql-node
#将下载好的rpm包上传到节点上然后安装
##manager节点
[root@mha-manage ~]# ls
mha4mysql-node-0.58-0.el7.centos.noarch.rpm
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
[root@mha-manage ~]# yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
[root@mha-manage ~]# yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
##node节点
[root@master ~]#ls
mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@master ~]#yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
5.3.2.2.2 在所有节点实现相互之间ssh key验证
[root@mha-manage ~]# ssh-keygen
[root@mha-manage ~]# ssh-copy-id 127.0.0.1
[root@mha-manage ~]# yum install -y rsync
[root@mha-manage ~]# rsync -av .ssh 10.0.0.11:/root/
[root@mha-manage ~]# rsync -av .ssh 10.0.0.21:/root/
[root@mha-manage ~]# rsync -av .ssh 10.0.0.31:/root/
[root@mha-manage ~]# ssh 10.0.0.11
Activate the web console with: systemctl enable --now cockpit.socket
Last login: Wed Dec 15 08:39:06 2021 from 10.0.0.254
[root@master ~]#exit
logout
Connection to 10.0.0.11 closed.
[root@mha-manage ~]# ssh 10.0.0.21
Activate the web console with: systemctl enable --now cockpit.socket
Last login: Wed Dec 15 08:38:59 2021 from 10.0.0.254
[root@slave1 ~]#exit
logout
Connection to 10.0.0.21 closed.
[root@mha-manage ~]#
5.3.2.2.3在管理节点建立配置文件
注意: 此文件的行尾不要加空格等符号
[root@mha-manage ~]# mkdir /etc/mha/
[root@mha-manage ~]# vim /etc/mha/test.cnf
[server default]
user=mhauser #用于远程连接MySQL所有节点的用户,需要有管理员的权限
password=sunxiang
manager_workdir=/data/mastermha/app1/ #目录会自动生成,无需手动创建
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root #用于实现远程ssh基于KEY的连接,访问二进制日志
repl_user=repluser #主从复制的用户信息
repl_password=sunxiang
ping_interval=1 #健康性检查的时间间隔
master_ip_failover_script=/usr/local/bin/master_ip_failover #切换VIP的perl脚本
report_script=/usr/local/bin/sendmail.sh #当执行报警脚本
check_repl_delay=0 #默认值为1,表示如果slave中从库落后主库relay log超过100M，主库不会选择这个从库为新的master，因为这个从库进行恢复需要很长的时间.通过设置参数check_repl_delay=0，mha触发主从切换时会忽略复制的延时，对于设置candidate_master=1的从库非常有用，这样确保这个从>库一定能成为最新的master
master_binlog_dir=/data/mysql/ #指定二进制日志存放的目录,mha4mysql-manager-0.58必须指定,之前版本不需要指定
[server1]
hostname=10.0.0.11
candidate_master=1
[server2]
hostname=10.0.0.21
candidate_master=1 #设置为优先候选master，即使不是集群中事件最新的slave,也会优先当master
[server3]
hostname=10.0.0.31
#最终写入文件的内容
[server default]
user=mhauser
password=sunxiang
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=sunxiang
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0
master_binlog_dir=/data/mysql/
[server1]
hostname=10.0.0.11
candidate_master=1
[server2]
hostname=10.0.0.21
candidate_master=1
[server3]
hostname=10.0.0.31
说明: 主库宕机谁来接管新的master
1. 所有从节点日志都是一致的，默认会以配置文件的顺序去选择一个新主
2. 从节点日志不一致，自动选择最接近于主库的从库充当新主
3. 如果对于某节点设定了权重（candidate_master=1），权重节点会优先选择。但是此节点日志量落后主库超过100M日志的话，也不会被选择。可以配合check_repl_delay=0，关闭日志量的检查，强制选择候选节点
5.3.2.2.4 相关脚本
##邮件脚本
#先配置邮箱
[root@mha-manage ~]# vim /etc/mail.rc
set from=448803503@qq.com
set smtp=smtp.qq.com
set smtp-auth-user=448803503@qq.com
set smtp-auth-password=***********（授权码）
#配置脚本（发送邮件）
[root@mha-manage ~]# cat >/usr/local/bin/sendmail.sh #注意路径要与上面的conf文件中一致
#!/bin/bash
echo "MySQL is down" | mail -s "MHA Sun" 448803503@qq.com ^C
[root@mha-manage ~]# cat /usr/local/bin/sendmail.sh
#!/bin/bash
echo "MySQL is down" | mail -s "MHA Sun" 448803503@qq.com
#执行脚本测试一下
[root@mha-manage ~]# bash /usr/local/bin/sendmail.sh
#添加可执行权限（因为之后在脚本中进行调用）
[root@mha-manage ~]# chmod +x /usr/local/bin/sendmail.sh
##perl脚本
[root@mha-manage ~]# vim /usr/local/bin/master_ip_failover #注意路径要与上面的conf文件中一致
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
#执行时必须删除下面三行注释
my $vip = '10.0.0.200/24';#设置Virtual IP
my $gateway = '10.0.0.1';#网关Gateway IP
my $interface = 'ens33';#指定VIP所在网卡
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$key down";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
`ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
exit 0;
}
else {
&usage();
exit 1;
}
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
[root@mha-manage ~]# chmod +x /usr/local/bin/master_ip_failover
[root@mha-manage ~]# ll /usr/local/bin/master_ip_failover
-rwxr-xr-x. 1 root root 2439 Dec 15 10:34 /usr/local/bin/master_ip_failover
5.3.2.2.5 实现Master
[root@master ~]#mkdir /data/mysql/ -pv
mkdir: created directory '/data'
mkdir: created directory '/data/mysql/'
[root@master ~]#chown -R mysql.mysql /data/mysql/
[root@master ~]#vim /etc/my.cnf
[mysqld]
server_id=11
log-bin=/data/mysql/mysql-bin
skip_name_resolve=1 #禁止反向解析
general_log #观察结果,非必须项,生产无需启用
#启动服务并设为开机启动项
[root@master ~]#systemctl enable --now mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
#配置VIP
[root@master ~]#ifconfig ens33:1 10.0.0.200/24
##配置主从
#查看二进制日志节点
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 870 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#创建主从复制用户
mysql> create user repluser@'10.0.0.%' identified by 'sunxiang';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
#创建mha用户
mysql> create user mhauser@'10.0.0.%' identified by 'sunxiang';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to mhauser@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
5.3.2.2.6 实现slave
[root@slave1 ~]#mkdir /data/mysql -pv
mkdir: created directory '/data'
mkdir: created directory '/data/mysql'
[root@slave1 ~]#chown -R mysql.mysql /data/mysql/
[root@slave1 ~]#vim /etc/my.cnf
[mysqld]
server_id=21
log-bin=/data/mysql/mysql-bin
read_only
relay_log_purge=0
skip_name_resolve=1
general_log
#启动服务并加入开机启动
[root@slave1 ~]#systemctl enable --now mysqld
#主从配置
CHANGE MASTER TO
MASTER_HOST='10.0.0.11',
MASTER_USER='repluser',
MASTER_PASSWORD='sunxiang',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=870,
MASTER_CONNECT_RETRY=10;
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.11',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='sunxiang',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=870,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 10 warnings (0.01 sec)
#开启主从复制
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
#检查一下
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.11
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1915
Relay_Log_File: slave1-relay-bin.000002
Relay_Log_Pos: 1369
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5.3.2.2.7 验证主从复制
[root@master ~]#mysql < /root/hellodb_innodb.sql
#在主从服务器分别查看数据库，都可以看到hellodb数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
5.3.2.2.8 检查MHA的环境
#检查环境
[root@mha-manage ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
[root@mha-manage ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
#查看状态
[root@mha-manage ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
5.3.2.2.9 启动MHA
#开启MHA,默认是前台运行,生产环境一般为后台执行
[root@mha-manage ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf &> /dev/null
#我这里要查看现象所以直接前台运行了
[root@mha-manage ~]# masterha_manager --conf=/etc/mha/app1.cnf
#运行之后正常就卡在这个状态了
[root@mha-manage ~]# masterha_manager --conf=/etc/mha/app1.cnf
Wed Dec 15 12:27:01 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Dec 15 12:27:01 2021 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Wed Dec 15 12:27:01 2021 - [info] Reading server configuration from /etc/mha/app1.cnf..
#重新打开一个终端查看状态
[root@mha-manage ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:27641) is running(0:PING_OK), master:10.0.0.11 #此时为运行状态
#查看到健康性检查
[root@master ~]#tail -f /var/lib/mysql/master.log
2021-12-15T04:31:05.879318Z 16 Query SELECT 1 As Value
2021-12-15T04:31:06.882170Z 16 Query SELECT 1 As Value
2021-12-15T04:31:07.885158Z 16 Query SELECT 1 As Value
2021-12-15T04:31:08.887098Z 16 Query SELECT 1 As Value
2021-12-15T04:31:09.889964Z 16 Query SELECT 1 As Value
2021-12-15T04:31:10.892877Z 16 Query SELECT 1 As Value
2021-12-15T04:31:11.895079Z 16 Query SELECT 1 As Value
2021-12-15T04:31:12.897047Z 16 Query SELECT 1 As Value
2021-12-15T04:31:13.899618Z 16 Query SELECT 1 As Value
2021-12-15T04:31:14.902633Z 16 Query SELECT 1 As Value
2021-12-15T04:31:15.905776Z 16 Query SELECT 1 As Value
2021-12-15T04:31:16.910607Z 16 Query SELECT 1 As Value
2021-12-15T04:31:17.910494Z 16 Query SELECT 1 As Value
[root@slave1 ~]#tail -f /var/lib/mysql/slave1.log
2021-12-15T04:27:06.860914Z 18 Connect mhauser@10.0.0.21 on using SSL/TLS
2021-12-15T04:27:06.861208Z 18 Query select @@version_comment limit 1
2021-12-15T04:27:06.861439Z 18 Query set sql_log_bin=0
2021-12-15T04:27:06.861598Z 18 Query create table if not exists mysql.apply_diff_relay_logs_test(id int)
2021-12-15T04:27:06.869079Z 18 Query insert into mysql.apply_diff_relay_logs_test values(1)
2021-12-15T04:27:06.870705Z 18 Query update mysql.apply_diff_relay_logs_test set id=id+1 where id=1
2021-12-15T04:27:06.871544Z 18 Query delete from mysql.apply_diff_relay_logs_test
2021-12-15T04:27:06.872827Z 18 Query drop table mysql.apply_diff_relay_logs_test
2021-12-15T04:27:06.877818Z 18 Quit
2021-12-15T04:27:07.380325Z 16 Quit
##健康性检查只检查master服务器
5.3.2.2.10 排错日志
tail /data/mastermha/app1/manager.log
[root@mha-manage ~]# tail /data/mastermha/app1/manager.log
IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 10.0.0.200/24;/sbin/arping -I ens33 -c 3 -s 10.0.0.200/24 10.0.0.1 >/dev/null 2>&1===
Checking the Status of the script.. OK
Wed Dec 15 12:27:07 2021 - [info] OK.
Wed Dec 15 12:27:07 2021 - [warning] shutdown_script is not defined.
Wed Dec 15 12:27:07 2021 - [info] Set master ping interval 1 seconds.
Wed Dec 15 12:27:07 2021 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Wed Dec 15 12:27:07 2021 - [info] Starting ping health check on 10.0.0.11(10.0.0.11:3306)..
Wed Dec 15 12:27:07 2021 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
5.3.2.2.11 模拟故障
#直接在主服务器上停服务
[root@master ~]#systemctl stop mysqld.service
#当 master down机后,mha管理程序自动退出
[root@mha-manage ~]# masterha_manager --conf=/etc/mha/app1.cnf
Wed Dec 15 13:50:19 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Dec 15 13:50:19 2021 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Wed Dec 15 13:50:19 2021 - [info] Reading server configuration from /etc/mha/app1.cnf..
Creating /data/mastermha/app1 if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/mysql/, up to mysql-bin.000002
Wed Dec 15 13:55:37 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Dec 15 13:55:37 2021 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Wed Dec 15 13:55:37 2021 - [info] Reading server configuration from /etc/mha/app1.cnf..
#我在停服务之前就查看了日志
[root@mha-manage ~]# tail -f /data/mastermha/app1/manager.log
Wed Dec 15 13:55:32 2021 - [warning] Got error on MySQL select ping: 1053 (Server shutdown in progress)
Wed Dec 15 13:55:32 2021 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql/ --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.58 --binlog_prefix=mysql-bin
Wed Dec 15 13:55:33 2021 - [info] HealthCheck: SSH to 10.0.0.11 is reachable.
Wed Dec 15 13:55:33 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.0.11' (111))
Wed Dec 15 13:55:33 2021 - [warning] Connection failed 2 time(s)..
Wed Dec 15 13:55:34 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.0.11' (111))
Wed Dec 15 13:55:34 2021 - [warning] Connection failed 3 time(s)..
Wed Dec 15 13:55:35 2021 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '10.0.0.11' (111))
Wed Dec 15 13:55:35 2021 - [warning] Connection failed 4 time(s)..
Wed Dec 15 13:55:35 2021 - [warning] Master is not reachable from health checker!
Wed Dec 15 13:55:35 2021 - [warning] Master 10.0.0.11(10.0.0.11:3306) is not reachable!
Wed Dec 15 13:55:35 2021 - [warning] SSH is reachable.
Wed Dec 15 13:55:35 2021 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/app1.cnf again, and trying to connect to all servers to check server status..
Wed Dec 15 13:55:35 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Dec 15 13:55:35 2021 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Wed Dec 15 13:55:35 2021 - [info] Reading server configuration from /etc/mha/app1.cnf..
Wed Dec 15 13:55:37 2021 - [info] GTID failover mode = 0
Wed Dec 15 13:55:37 2021 - [info] Dead Servers:
Wed Dec 15 13:55:37 2021 - [info] 10.0.0.11(10.0.0.11:3306)
Wed Dec 15 13:55:37 2021 - [info] Alive Servers:
Wed Dec 15 13:55:37 2021 - [info] 10.0.0.21(10.0.0.21:3306)
Wed Dec 15 13:55:37 2021 - [info] 10.0.0.31(10.0.0.31:3306)
Wed Dec 15 13:55:37 2021 - [info] Alive Slaves:
Wed Dec 15 13:55:37 2021 - [info] 10.0.0.21(10.0.0.21:3306) Version=8.0.26 (oldest major version between slaves) log-bin:enabled
Wed Dec 15 13:55:37 2021 - [info] Replicating from 10.0.0.11(10.0.0.11:3306)
Wed Dec 15 13:55:37 2021 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Dec 15 13:55:37 2021 - [info] 10.0.0.31(10.0.0.31:3306) Version=8.0.26 (oldest major version between slaves) log-bin:enabled
Wed Dec 15 13:55:37 2021 - [info] Replicating from 10.0.0.11(10.0.0.11:3306)
Wed Dec 15 13:55:37 2021 - [info] Checking slave configurations..
Wed Dec 15 13:55:37 2021 - [info] Checking replication filtering settings..
Wed Dec 15 13:55:37 2021 - [info] Replication filtering check ok.
Wed Dec 15 13:55:37 2021 - [info] Master is down!
Wed Dec 15 13:55:37 2021 - [info] Terminating monitoring script.
Wed Dec 15 13:55:37 2021 - [info] Got exit code 20 (Master dead).
Wed Dec 15 13:55:37 2021 - [info] MHA::MasterFailover version 0.58.
Wed Dec 15 13:55:37 2021 - [info] Starting master failover.
Wed Dec 15 13:55:37 2021 - [info]
Wed Dec 15 13:55:37 2021 - [info] * Phase 1: Configuration Check Phase..
Wed Dec 15 13:55:37 2021 - [info]
Wed Dec 15 13:55:38 2021 - [info] GTID failover mode = 0
Wed Dec 15 13:55:38 2021 - [info] Dead Servers:
Wed Dec 15 13:55:38 2021 - [info] 10.0.0.11(10.0.0.11:3306)
Wed Dec 15 13:55:38 2021 - [info] Checking master reachability via MySQL(double check)...
Wed Dec 15 13:55:38 2021 - [info] ok.
Wed Dec 15 13:55:38 2021 - [info] Alive Servers:
Wed Dec 15 13:55:38 2021 - [info] 10.0.0.21(10.0.0.21:3306)
Wed Dec 15 13:55:38 2021 - [info] 10.0.0.31(10.0.0.31:3306)
Wed Dec 15 13:55:38 2021 - [info] Alive Slaves:
Wed Dec 15 13:55:38 2021 - [info] 10.0.0.21(10.0.0.21:3306) Version=8.0.26 (oldest major version between slaves) log-bin:enabled
Wed Dec 15 13:55:38 2021 - [info] Replicating from 10.0.0.11(10.0.0.11:3306)
Wed Dec 15 13:55:38 2021 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Dec 15 13:55:38 2021 - [info] 10.0.0.31(10.0.0.31:3306) Version=8.0.26 (oldest major version between slaves) log-bin:enabled
Wed Dec 15 13:55:38 2021 - [info] Replicating from 10.0.0.11(10.0.0.11:3306)
Wed Dec 15 13:55:38 2021 - [info] Starting Non-GTID based failover.
Wed Dec 15 13:55:38 2021 - [info]
Wed Dec 15 13:55:38 2021 - [info] ** Phase 1: Configuration Check Phase completed.
Wed Dec 15 13:55:38 2021 - [info]
Wed Dec 15 13:55:38 2021 - [info] * Phase 2: Dead Master Shutdown Phase..
Wed Dec 15 13:55:38 2021 - [info]
Wed Dec 15 13:55:38 2021 - [info] Forcing shutdown so that applications never connect to the current master..
Wed Dec 15 13:55:38 2021 - [info] Executing master IP deactivation script:
Wed Dec 15 13:55:38 2021 - [info] /usr/local/bin/master_ip_failover --orig_master_host=10.0.0.11 --orig_master_ip=10.0.0.11 --orig_master_port=3306 --command=stopssh --ssh_user=root
IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 10.0.0.200/24;/sbin/arping -I ens33 -c 3 -s 10.0.0.200/24 10.0.0.1 >/dev/null 2>&1===
Disabling the VIP on old master: 10.0.0.11
Wed Dec 15 13:55:38 2021 - [info] done.
Wed Dec 15 13:55:38 2021 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Wed Dec 15 13:55:38 2021 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Wed Dec 15 13:55:38 2021 - [info]
Wed Dec 15 13:55:38 2021 - [info] * Phase 3: Master Recovery Phase..
Wed Dec 15 13:55:38 2021 - [info]
Wed Dec 15 13:55:38 2021 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Wed Dec 15 13:55:38 2021 - [info]
Wed Dec 15 13:55:38 2021 - [info] The latest binary log file/position on all slaves is mysql-bin.000002:11985
Wed Dec 15 13:55:38 2021 - [info] Latest slaves (Slaves that received relay log files to the latest):
Wed Dec 15 13:55:38 2021 - [info] 10.0.0.21(10.0.0.21:3306) Version=8.0.26 (oldest major version between slaves) log-bin:enabled
Wed Dec 15 13:55:38 2021 - [info] Replicating from 10.0.0.11(10.0.0.11:3306)
Wed Dec 15 13:55:38 2021 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Dec 15 13:55:38 2021 - [info] 10.0.0.31(10.0.0.31:3306) Version=8.0.26 (oldest major version between slaves) log-bin:enabled
Wed Dec 15 13:55:38 2021 - [info] Replicating from 10.0.0.11(10.0.0.11:3306)
Wed Dec 15 13:55:38 2021 - [info] The oldest binary log file/position on all slaves is mysql-bin.000002:11985
Wed Dec 15 13:55:38 2021 - [info] Oldest slaves:
Wed Dec 15 13:55:38 2021 - [info] 10.0.0.21(10.0.0.21:3306) Version=8.0.26 (oldest major version between slaves) log-bin:enabled
Wed Dec 15 13:55:38 2021 - [info] Replicating from 10.0.0.11(10.0.0.11:3306)
Wed Dec 15 13:55:38 2021 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Dec 15 13:55:38 2021 - [info] 10.0.0.31(10.0.0.31:3306) Version=8.0.26 (oldest major version between slaves) log-bin:enabled
Wed Dec 15 13:55:38 2021 - [info] Replicating from 10.0.0.11(10.0.0.11:3306)
Wed Dec 15 13:55:38 2021 - [info]
Wed Dec 15 13:55:38 2021 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase..
Wed Dec 15 13:55:38 2021 - [info]
Wed Dec 15 13:55:38 2021 - [info] Fetching dead master's binary logs..
Wed Dec 15 13:55:38 2021 - [info] Executing command on the dead master 10.0.0.11(10.0.0.11:3306): save_binary_logs --command=save --start_file=mysql-bin.000002 --start_pos=11985 --binlog_dir=/data/mysql/ --output_file=/data/mastermha/app1//saved_master_binlog_from_10.0.0.11_3306_20211215135537.binlog --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.58
Creating /data/mastermha/app1 if not exists.. ok.
Concat binary/relay logs from mysql-bin.000002 pos 11985 to mysql-bin.000002 EOF into /data/mastermha/app1//saved_master_binlog_from_10.0.0.11_3306_20211215135537.binlog ..
Binlog Checksum enabled
Dumping binlog format description event, from position 0 to 156.. ok.
No need to dump effective binlog data from /data/mysql//mysql-bin.000002 (pos starts 11985, filesize 11985). Skipping.
Binlog Checksum enabled
/data/mastermha/app1//saved_master_binlog_from_10.0.0.11_3306_20211215135537.binlog has no effective data events.
Event not exists.
Wed Dec 15 13:55:39 2021 - [info] Additional events were not found from the orig master. No need to save.
Wed Dec 15 13:55:39 2021 - [info]
Wed Dec 15 13:55:39 2021 - [info] * Phase 3.3: Determining New Master Phase..
Wed Dec 15 13:55:39 2021 - [info]
Wed Dec 15 13:55:39 2021 - [info] Finding the latest slave that has all relay logs for recovering other slaves..
Wed Dec 15 13:55:39 2021 - [info] All slaves received relay logs to the same position. No need to resync each other.
Wed Dec 15 13:55:39 2021 - [info] Searching new master from slaves..
Wed Dec 15 13:55:39 2021 - [info] Candidate masters from the configuration file:
Wed Dec 15 13:55:39 2021 - [info] 10.0.0.21(10.0.0.21:3306) Version=8.0.26 (oldest major version between slaves) log-bin:enabled
Wed Dec 15 13:55:39 2021 - [info] Replicating from 10.0.0.11(10.0.0.11:3306)
Wed Dec 15 13:55:39 2021 - [info] Primary candidate for the new Master (candidate_master is set)
Wed Dec 15 13:55:39 2021 - [info] Non-candidate masters:
Wed Dec 15 13:55:39 2021 - [info] Searching from candidate_master slaves which have received the latest relay log events..
Wed Dec 15 13:55:39 2021 - [info] New master is 10.0.0.21(10.0.0.21:3306)
Wed Dec 15 13:55:39 2021 - [info] Starting master failover..
Wed Dec 15 13:55:39 2021 - [info]
From:
10.0.0.11(10.0.0.11:3306) (current master)
+--10.0.0.21(10.0.0.21:3306)
+--10.0.0.31(10.0.0.31:3306)
To:
10.0.0.21(10.0.0.21:3306) (new master)
+--10.0.0.31(10.0.0.31:3306)
Wed Dec 15 13:55:39 2021 - [info]
Wed Dec 15 13:55:39 2021 - [info] * Phase 3.4: New Master Diff Log Generation Phase..
Wed Dec 15 13:55:39 2021 - [info]
Wed Dec 15 13:55:39 2021 - [info] This server has all relay logs. No need to generate diff files from the latest slave.
Wed Dec 15 13:55:39 2021 - [info]
Wed Dec 15 13:55:39 2021 - [info] * Phase 3.5: Master Log Apply Phase..
Wed Dec 15 13:55:39 2021 - [info]
Wed Dec 15 13:55:39 2021 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed.
Wed Dec 15 13:55:39 2021 - [info] Starting recovery on 10.0.0.21(10.0.0.21:3306)..
Wed Dec 15 13:55:39 2021 - [info] This server has all relay logs. Waiting all logs to be applied..
Wed Dec 15 13:55:39 2021 - [info] done.
Wed Dec 15 13:55:39 2021 - [info] All relay logs were successfully applied.
Wed Dec 15 13:55:39 2021 - [info] Getting new master's binlog name and position..
Wed Dec 15 13:55:39 2021 - [info] mysql-bin.000002:12235
Wed Dec 15 13:55:39 2021 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.21', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=12235, MASTER_USER='repluser', MASTER_PASSWORD='xxx';
Wed Dec 15 13:55:39 2021 - [info] Executing master IP activate script:
Wed Dec 15 13:55:39 2021 - [info] /usr/local/bin/master_ip_failover --command=start --ssh_user=root --orig_master_host=10.0.0.11 --orig_master_ip=10.0.0.11 --orig_master_port=3306 --new_master_host=10.0.0.21 --new_master_ip=10.0.0.21 --new_master_port=3306 --new_master_user='mhauser' --new_master_password=xxx
Unknown option: new_master_user
Unknown option: new_master_password
IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 10.0.0.200/24;/sbin/arping -I ens33 -c 3 -s 10.0.0.200/24 10.0.0.1 >/dev/null 2>&1===
Enabling the VIP - 10.0.0.200/24 on the new master - 10.0.0.21
Wed Dec 15 13:55:39 2021 - [info] OK.
Wed Dec 15 13:55:39 2021 - [info] Setting read_only=0 on 10.0.0.21(10.0.0.21:3306)..
Wed Dec 15 13:55:39 2021 - [info] ok.
Wed Dec 15 13:55:39 2021 - [info] ** Finished master recovery successfully.
Wed Dec 15 13:55:39 2021 - [info] * Phase 3: Master Recovery Phase completed.
Wed Dec 15 13:55:39 2021 - [info]
Wed Dec 15 13:55:39 2021 - [info] * Phase 4: Slaves Recovery Phase..
Wed Dec 15 13:55:39 2021 - [info]
Wed Dec 15 13:55:39 2021 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..
Wed Dec 15 13:55:39 2021 - [info]
Wed Dec 15 13:55:39 2021 - [info] -- Slave diff file generation on host 10.0.0.31(10.0.0.31:3306) started, pid: 29254. Check tmp log /data/mastermha/app1//10.0.0.31_3306_20211215135537.log if it takes time..
Wed Dec 15 13:55:40 2021 - [info]
Wed Dec 15 13:55:40 2021 - [info] Log messages from 10.0.0.31 ...
Wed Dec 15 13:55:40 2021 - [info]
Wed Dec 15 13:55:39 2021 - [info] This server has all relay logs. No need to generate diff files from the latest slave.
Wed Dec 15 13:55:40 2021 - [info] End of log messages from 10.0.0.31.
Wed Dec 15 13:55:40 2021 - [info] -- 10.0.0.31(10.0.0.31:3306) has the latest relay log events.
Wed Dec 15 13:55:40 2021 - [info] Generating relay diff files from the latest slave succeeded.
Wed Dec 15 13:55:40 2021 - [info]
Wed Dec 15 13:55:40 2021 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..
Wed Dec 15 13:55:40 2021 - [info]
Wed Dec 15 13:55:40 2021 - [info] -- Slave recovery on host 10.0.0.31(10.0.0.31:3306) started, pid: 29256. Check tmp log /data/mastermha/app1//10.0.0.31_3306_20211215135537.log if it takes time..
Wed Dec 15 13:55:41 2021 - [info]
Wed Dec 15 13:55:41 2021 - [info] Log messages from 10.0.0.31 ...
Wed Dec 15 13:55:41 2021 - [info]
Wed Dec 15 13:55:40 2021 - [info] Starting recovery on 10.0.0.31(10.0.0.31:3306)..
Wed Dec 15 13:55:40 2021 - [info] This server has all relay logs. Waiting all logs to be applied..
Wed Dec 15 13:55:40 2021 - [info] done.
Wed Dec 15 13:55:40 2021 - [info] All relay logs were successfully applied.
Wed Dec 15 13:55:40 2021 - [info] Resetting slave 10.0.0.31(10.0.0.31:3306) and starting replication from the new master 10.0.0.21(10.0.0.21:3306)..
Wed Dec 15 13:55:40 2021 - [info] Executed CHANGE MASTER.
Wed Dec 15 13:55:40 2021 - [info] Slave started.
Wed Dec 15 13:55:41 2021 - [info] End of log messages from 10.0.0.31.
Wed Dec 15 13:55:41 2021 - [info] -- Slave recovery on host 10.0.0.31(10.0.0.31:3306) succeeded.
Wed Dec 15 13:55:41 2021 - [info] All new slave servers recovered successfully.
Wed Dec 15 13:55:41 2021 - [info]
Wed Dec 15 13:55:41 2021 - [info] * Phase 5: New master cleanup phase..
Wed Dec 15 13:55:41 2021 - [info]
Wed Dec 15 13:55:41 2021 - [info] Resetting slave info on the new master..
Wed Dec 15 13:55:41 2021 - [info] 10.0.0.21: Resetting slave info succeeded.
Wed Dec 15 13:55:41 2021 - [info] Master failover to 10.0.0.21(10.0.0.21:3306) completed successfully.
Wed Dec 15 13:55:41 2021 - [info]
----- Failover Report -----
app1: MySQL Master failover 10.0.0.11(10.0.0.11:3306) to 10.0.0.21(10.0.0.21:3306) succeeded
Master 10.0.0.11(10.0.0.11:3306) is down!
Check MHA Manager logs at mha-manage:/data/mastermha/app1/manager.log for details.
Started automated(non-interactive) failover.
Invalidated master IP address on 10.0.0.11(10.0.0.11:3306)
The latest slave 10.0.0.21(10.0.0.21:3306) has all relay logs for recovery.
Selected 10.0.0.21(10.0.0.21:3306) as a new master.
10.0.0.21(10.0.0.21:3306): OK: Applying all logs succeeded.
10.0.0.21(10.0.0.21:3306): OK: Activated master IP address.
10.0.0.31(10.0.0.31:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
10.0.0.31(10.0.0.31:3306): OK: Applying all logs succeeded. Slave started, replicating from 10.0.0.21(10.0.0.21:3306)
10.0.0.21(10.0.0.21:3306): Resetting slave info succeeded.
Master failover to 10.0.0.21(10.0.0.21:3306) completed successfully. #切换成功了
Wed Dec 15 13:55:41 2021 - [info] Sending mail..
#查看状态
[root@mha-manage ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
#此时在Slave1上查看Slave状态 发现没有显示，说明此时他已近成为主设备了
mysql> show slave status\G;
Empty set, 1 warning (0.01 sec)
ERROR:
No query specified
#配置文件中设置了从服务只读，此时自动关闭了
mysql> select @@read_only;
+-------------+
| @@read_only |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
#在Slave2上查看Slave状态 正常的只不过master地址变为了10.0.0.21
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.0.0.21
Master_User: repluser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 12235
Relay_Log_File: slave2-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#在Slave1上创建数据库（写操作）
mysql> create database test2;
Query OK, 1 row affected (0.01 sec)
#在Slave2上查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
| test2 |
+--------------------+
7 rows in set (0.01 sec)
5.3.3Galera Cluster
5.3.3.1 Galera Cluster 介绍
Galera Cluster：集成了Galera插件的MySQL集群，是一种新型的，数据不共享的，高度冗余的高可用方案，目前Galera Cluster有两个版本，分别是Percona Xtradb Cluster及MariaDB Cluster，Galera本身是具有多主特性的，即采用multi-master的集群架构，是一个既稳健，又在数据一致性、完整性及高性能方面有出色表现的高可用解决方案
Galera Cluster特点
- 多主架构：真正的多点读写的集群，在任何时候读写数据，都是最新的
- 同步复制：集群不同节点之间数据同步，没有延迟，在数据库挂掉之后，数据不会丢失
- 并发复制：从节点APPLY数据时，支持并行执行，更好的性能
- 故障切换：在出现数据库故障时，因支持多点写入，切换容易
- 热插拔：在服务期间，如果数据库挂了，只要监控程序发现的够快，不可服务时间就会非常少。在
- 节点故障期间，节点本身对集群的影响非常小
- 自动节点克隆：在新增节点，或者停机维护时，增量数据或者基础数据不需要人工手动备份提供，Galera Cluster会自动拉取在线节点数据，最终集群会变为一致
- 对应用透明：集群的维护，对应用程序是透明的
Galera Cluster 缺点
- 由于DDL 需全局验证通过，则集群性能由集群中最差性能节点决定（一般集群节点配置都是一样的）
- 新节点加入或延后较大的节点重新加入需全量拷贝数据(SST，State Snapshot Transfer),作为donor( 贡献者，如： 同步数据时的提供者)的节点在同步过程中无法提供读写
- 只支持innodb存储引擎的表
Galera Cluster工作过程
Galera Cluster官方文档：
http://galeracluster.com/documentation-webpages/galera-documentation.pdf
http://galeracluster.com/documentation-webpages/index.html
https://www.percona.com/doc/percona-xtradb-cluster/LATEST/index.html
https://mariadb.com/kb/en/library/getting-started-with-mariadb-galera-cluster/
Galera Cluster 包括两个组件
- Galera replication library (galera-3)
- WSREP：MySQL extended with the Write Set Replication
WSREP复制实现：
- PXC：Percona XtraDB Cluster，是Percona对Galera的实现
参考仓库：
https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
- MariaDB Galera Cluster：
参考仓库：
https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.X/yum/centos7-amd64/
注意：两者都需要至少三个节点，不能安装mysql server 或 mariadb-server
5.3.3.1PXC原理
PXC最常使用如下4个端口号：
- 3306：数据库对外服务的端口号
- 4444：请求SST的端口号
- 4567：组成员之间进行沟通的端口号
- 4568：用于传输IST的端口号
PXC中涉及到的重要概念和核心参数：
（1）集群中节点的数量：整个集群中节点数量应该控制在最少3个、最多8个的范围内。最少3个节点是为了防止出现脑裂现象，因为只有在2个节点下才会出现此现象。脑裂现象的标志就是输入任何命令，返回的结果都是unknown command。节点在集群中，会因新节点的加入或故障、同步失效等原因发生状态的切换。
（2）节点状态的变化阶段：
- open：节点启动成功，尝试连接到集群时的状态
- primary：节点已处于集群中，在新节点加入并选取donor进行数据同步时的状态
- joiner：节点处于等待接收同步文件时的状态
- joined：节点完成数据同步工作，尝试保持和集群进度一致时的状态
- synced：节点正常提供服务时的状态，表示已经同步完成并和集群进度保持一致
- donor：节点处于为新加入的节点提供全量数据时的状态
备注：donor节点就是数据的贡献者，如果一个新节点加入集群，此时又需要大量数据的SST数据传输，就有可能因此而拖垮整个集群的性能，所以在生产环境中，如果数据量较小，还可以使用SST全量数据传输，但如果数据量很大就不建议使用这种方式，可以考虑先建立主从关系，然后再加入集群。
（3）节点的数据传输方式：
SST：State Snapshot Transfer，全量数据传输
IST：Incremental State Transfer，增量数据传输
SST数据传输有xtrabackup、mysqldump和rsync三种方式，而增量数据传输就只有一种方式xtrabackup，但生产环境中一般数据量较小时，可以使用SST全量数据传输，但也只使用xtrabackup方法。
（4）GCache模块：在PXC中一个特别重要的模块，它的核心功能就是为每个节点缓存当前最新的写集。如果有新节点加入进来，就可以把新数据的增量传递给新节点，而不需要再使用SST传输方式，这样可以让节点更快地加入集群中，涉及如下参数：
- gcache.size：缓存写集增量信息的大小，它的默认大小是128MB，通过wsrep_provider_options参数设置，建议调整为2GB~4GB范围，足够的空间便于缓存更多的增量信息。
- gcache.mem_size：GCache中内存缓存的大小，适度调大可以提高整个集群的性能
- gcache.page_size：如果内存不够用（GCache不足），就直接将写集写入磁盘文件中
5.3.3.3 实战案例：Percona XtraDB Cluster(PXC 5.7）
5.3.3.3.1 环境准备
四台主机：
pxc1:10.0.0.10
pxc2:10.0.0.20
pxc3:10.0.0.30
pxc4:10.0.0.40
OS 版本目前不支持CentOS 8
[root@pxc1 ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
关闭防火墙和SELinux，保证时间同步
注意：如果已经安装MySQL，必须卸载
5.3.3.3.2安装 Percona XtraDB Cluster 5.7
#此处使用清华大学yum源，官方源太慢了
[root@pxc1 ~]#vim /etc/yum.repos.d/pxc.repo
[percona]
name=percona_repo
baseurl =https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled = 1
gpgcheck = 0
[root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.20:/etc/yum.repos.d
[root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.30:/etc/yum.repos.d
#在三个节点都安装好PXC 5.7
[root@pxc1 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc2 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc3 ~]#yum install Percona-XtraDB-Cluster-57 -y
5.3.3.3.3 在各个节点上分别配置mysql及集群配置文件
/etc/my.cnf为主配置文件，当前版本中，其余的配置文件都放在/etc/percona-xtradb-cluster.conf.d目录里，包括mysqld.cnf，mysqld_safe.cnf，wsrep.cnf 三个文件
#主配置文件不需要修改
[root@pxc1 ~]#cat /etc/my.cnf
# The Percona XtraDB Cluster 5.7 configuration file.
...省略...
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/
[root@pxc1 ~]#ls /etc/my.cnf.d/
[root@pxc1 ~]#ls /etc/percona-xtradb-cluster.conf.d/
mysqld.cnf mysqld_safe.cnf wsrep.cnf
#下面配置文件不需要修改
[root@pxc1 ~]#cat /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
...省略...
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=1 #建议各个节点不同
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin #建议启用，非必须项
log_slave_updates
expire_logs_days=7
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#下面配置文件不需要修改
[root@pxc1 ~]#cat /etc/percona-xtradb-cluster.conf.d/mysqld_safe.cnf
...省略...
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/lib/mysql/mysql.sock
nice = 0
#PXC的配置文件必须修改
[root@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc1 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.10,10.0.0.20,10.0.0.30 #三个节点的IP
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.10 #各个节点，指定自已的IP
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-1 #各个节点，指定自已节点名称
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass" #取消本行注释,同一集群内多个节点的验证用户和密码信息必须一致
[root@pxc2 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.10,10.0.0.20,10.0.0.30
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.20 #各个节点，指定自已的IP
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-2 #各个节点，指定自已节点名称
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass" #取消本行注释
[root@pxc3 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.10,10.0.0.20,10.0.0.30
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.30 #各个节点，指定自已的IP
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-3 #各个节点，指定自已的IP
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass" #取消本行注释
注意：尽管Galera Cluster不再需要通过binlog的形式进行同步，但还是建议在配置文件中开启二进制日志功能，原因是后期如果有新节点需要加入，老节点通过SST全量传输的方式向新节点传输数据，很可能会拖垮集群性能，所以让新节点先通过binlog方式完成同步后再加入集群会是一种更好的选择
配置文件各项配置意义
| 配置 | 说明 |
|---|---|
| wsrep_provider | 指定Galera库的路径 |
| wsrep_cluster_name | Galera集群的名称 |
| wsrep_cluster_address | Galera集群中各节点地址。地址使用组通信协议gcomm://(group communication) |
| wsrep_node_name | 本节点在Galera集群中的名称 |
| wsrep_node_address | 本节点在Galera集群中的通信地址 |
| wsrep_sst_method | state_snapshot_transfer(SST)使用的传输方法，可用方法有mysqldump、rsync和xtrabackup，前两者在传输时都需要对Donor加全局只读锁(FLUSH TABLES WITH READ LOCK)，xtrabackup则不需要(它使用percona自己提供的backup lock)。强烈建议采用xtrabackup |
| wsrep_sst_auth | 在SST传输时需要用到的认证凭据，格式为：”用户：密码” |
| pxc_strict_mode | 是否限制PXC启用正在试用阶段的功能，ENFORCING是默认值，表示不启用 |
| binlog_format | 二进制日志的格式。Galera只支持row格式的二进制日志 |
| default_storage_engine | 指定默认存储引擎。Galera的复制功能只支持InnoDB |
| innodb_autoinc_lock_mode | 只能设置为2，设置为0或1时会无法正确处理死锁问题 |
5.3.3.3.4 启动PXC集群中第一个节点
[root@pxc3 ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
#启动第一个节点
[root@pxc3 ~]# systemctl start mysql@bootstrap.service
[root@pxc3 ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 *:4567 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 80 [::]:3306 [::]:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
#查看root密码
[root@pxc3 ~]# grep "temporary password" /var/log/mysqld.log
2021-12-15T15:37:37.893590Z 1 [Note] A temporary password is generated for root@localhost: fGqujaki)3U#
#登录数据库修改密码
[root@pxc3 ~]# mysql -uroot -p'fGqujaki)3U#'
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 11
Server version: 5.7.35-38-57-log
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, 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> alter user 'root'@'localhost' identified by 'sunxiang';
Query OK, 0 rows affected (0.00 sec)
#创建授权用户
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
Query OK, 0 rows affected (0.02 sec)
#查看相关变量
mysql> SHOW VARIABLES LIKE 'wsrep%'\G
*************************** 1. row ***************************
Variable_name: wsrep_OSU_method
Value: TOI
*************************** 2. row ***************************
Variable_name: wsrep_RSU_commit_timeout
Value: 5000
*************************** 3. row ***************************
Variable_name: wsrep_auto_increment_control
Value: ON
*************************** 4. row ***************************
Variable_name: wsrep_causal_reads
Value: OFF
*************************** 5. row ***************************
Variable_name: wsrep_certification_rules
Value: strict
*************************** 6. row ***************************
Variable_name: wsrep_certify_nonPK
Value: ON
*************************** 7. row ***************************
Variable_name: wsrep_cluster_address
Value: gcomm://10.0.0.10,10.0.0.20,10.0.0.30
*************************** 8. row ***************************
Variable_name: wsrep_cluster_name
Value: pxc-cluster
*************************** 9. row ***************************
Variable_name: wsrep_convert_LOCK_to_trx
Value: OFF
*************************** 10. row ***************************
Variable_name: wsrep_data_home_dir
Value: /var/lib/mysql/
*************************** 11. row ***************************
Variable_name: wsrep_dbug_option
Value:
*************************** 12. row ***************************
Variable_name: wsrep_debug
Value: OFF
*************************** 13. row ***************************
Variable_name: wsrep_desync
Value: OFF
*************************** 14. row ***************************
Variable_name: wsrep_dirty_reads
Value: OFF
*************************** 15. row ***************************
Variable_name: wsrep_drupal_282555_workaround
Value: OFF
*************************** 16. row ***************************
Variable_name: wsrep_forced_binlog_format
Value: NONE
*************************** 17. row ***************************
Variable_name: wsrep_load_data_splitting
Value: ON
*************************** 18. row ***************************
Variable_name: wsrep_log_conflicts
Value: ON
*************************** 19. row ***************************
Variable_name: wsrep_max_ws_rows
Value: 0
*************************** 20. row ***************************
Variable_name: wsrep_max_ws_size
Value: 2147483647
*************************** 21. row ***************************
Variable_name: wsrep_mode
Value:
*************************** 22. row ***************************
Variable_name: wsrep_node_address
Value: 10.0.0.30
*************************** 23. row ***************************
Variable_name: wsrep_node_incoming_address
Value: AUTO
*************************** 24. row ***************************
Variable_name: wsrep_node_name
Value: pxc-cluster-node-3
*************************** 25. row ***************************
Variable_name: wsrep_notify_cmd
Value:
*************************** 26. row ***************************
Variable_name: wsrep_on
Value: ON
*************************** 27. row ***************************
Variable_name: wsrep_preordered
Value: OFF
*************************** 28. row ***************************
Variable_name: wsrep_provider
Value: /usr/lib64/galera3/libgalera_smm.so
*************************** 29. row ***************************
Variable_name: wsrep_provider_options
Value: base_dir = /var/lib/mysql/; base_host = 10.0.0.30; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = yes; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period = PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; evs.user_send_window = 4; evs.version = 0; evs.view_forget_timeout = P1D; gcache.dir = /var/lib/mysql/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 100; gcs.fc_master_slave = no; gc
*************************** 30. row ***************************
Variable_name: wsrep_recover
Value: OFF
*************************** 31. row ***************************
Variable_name: wsrep_reject_queries
Value: NONE
*************************** 32. row ***************************
Variable_name: wsrep_replicate_myisam
Value: OFF
*************************** 33. row ***************************
Variable_name: wsrep_restart_slave
Value: OFF
*************************** 34. row ***************************
Variable_name: wsrep_retry_autocommit
Value: 1
*************************** 35. row ***************************
Variable_name: wsrep_slave_FK_checks
Value: ON
*************************** 36. row ***************************
Variable_name: wsrep_slave_UK_checks
Value: OFF
*************************** 37. row ***************************
Variable_name: wsrep_slave_threads
Value: 8
*************************** 38. row ***************************
Variable_name: wsrep_sst_allowed_methods
Value: mysqldump,rsync,skip,xtrabackup,xtrabackup-v2
*************************** 39. row ***************************
Variable_name: wsrep_sst_auth
Value: ********
*************************** 40. row ***************************
Variable_name: wsrep_sst_donor
Value:
*************************** 41. row ***************************
Variable_name: wsrep_sst_donor_rejects_queries
Value: OFF
*************************** 42. row ***************************
Variable_name: wsrep_sst_method
Value: xtrabackup-v2
*************************** 43. row ***************************
Variable_name: wsrep_sst_receive_address
Value: AUTO
*************************** 44. row ***************************
Variable_name: wsrep_start_position
Value: 00000000-0000-0000-0000-000000000000:-1
*************************** 45. row ***************************
Variable_name: wsrep_sync_wait
Value: 0
45 rows in set (0.00 sec)
#查看相关状态变量
mysql> SHOW STATUS LIKE 'wsrep%'\G
*************************** 1. row ***************************
Variable_name: wsrep_local_state_uuid
Value: f31dd7bc-5dbc-11ec-9379-1bc8d3cb9da1
*************************** 2. row ***************************
Variable_name: wsrep_protocol_version
Value: 9
*************************** 3. row ***************************
Variable_name: wsrep_last_applied
Value: 3
*************************** 4. row ***************************
Variable_name: wsrep_last_committed
Value: 3
*************************** 5. row ***************************
Variable_name: wsrep_replicated
Value: 3
*************************** 6. row ***************************
Variable_name: wsrep_replicated_bytes
Value: 768
*************************** 7. row ***************************
Variable_name: wsrep_repl_keys
Value: 3
*************************** 8. row ***************************
Variable_name: wsrep_repl_keys_bytes
Value: 96
*************************** 9. row ***************************
Variable_name: wsrep_repl_data_bytes
Value: 465
*************************** 10. row ***************************
Variable_name: wsrep_repl_other_bytes
Value: 0
*************************** 11. row ***************************
Variable_name: wsrep_received
Value: 2
*************************** 12. row ***************************
Variable_name: wsrep_received_bytes
Value: 151
*************************** 13. row ***************************
Variable_name: wsrep_local_commits
Value: 0
*************************** 14. row ***************************
Variable_name: wsrep_local_cert_failures
Value: 0
*************************** 15. row ***************************
Variable_name: wsrep_local_replays
Value: 0
*************************** 16. row ***************************
Variable_name: wsrep_local_send_queue
Value: 0
*************************** 17. row ***************************
Variable_name: wsrep_local_send_queue_max
Value: 1
*************************** 18. row ***************************
Variable_name: wsrep_local_send_queue_min
Value: 0
*************************** 19. row ***************************
Variable_name: wsrep_local_send_queue_avg
Value: 0.000000
*************************** 20. row ***************************
Variable_name: wsrep_local_recv_queue
Value: 0
*************************** 21. row ***************************
Variable_name: wsrep_local_recv_queue_max
Value: 2
*************************** 22. row ***************************
Variable_name: wsrep_local_recv_queue_min
Value: 0
*************************** 23. row ***************************
Variable_name: wsrep_local_recv_queue_avg
Value: 0.500000
*************************** 24. row ***************************
Variable_name: wsrep_local_cached_downto
Value: 1
*************************** 25. row ***************************
Variable_name: wsrep_flow_control_paused_ns
Value: 0
*************************** 26. row ***************************
Variable_name: wsrep_flow_control_paused
Value: 0.000000
*************************** 27. row ***************************
Variable_name: wsrep_flow_control_sent
Value: 0
*************************** 28. row ***************************
Variable_name: wsrep_flow_control_recv
Value: 0
*************************** 29. row ***************************
Variable_name: wsrep_flow_control_interval
Value: [ 100, 100 ]
*************************** 30. row ***************************
Variable_name: wsrep_flow_control_interval_low
Value: 100
*************************** 31. row ***************************
Variable_name: wsrep_flow_control_interval_high
Value: 100
*************************** 32. row ***************************
Variable_name: wsrep_flow_control_status
Value: OFF
*************************** 33. row ***************************
Variable_name: wsrep_flow_control_active
Value: false
*************************** 34. row ***************************
Variable_name: wsrep_flow_control_requested
Value: false
*************************** 35. row ***************************
Variable_name: wsrep_cert_deps_distance
Value: 1.000000
*************************** 36. row ***************************
Variable_name: wsrep_apply_oooe
Value: 0.000000
*************************** 37. row ***************************
Variable_name: wsrep_apply_oool
Value: 0.000000
*************************** 38. row ***************************
Variable_name: wsrep_apply_window
Value: 1.000000
*************************** 39. row ***************************
Variable_name: wsrep_apply_waits
Value: 0
*************************** 40. row ***************************
Variable_name: wsrep_commit_oooe
Value: 0.000000
*************************** 41. row ***************************
Variable_name: wsrep_commit_oool
Value: 0.000000
*************************** 42. row ***************************
Variable_name: wsrep_commit_window
Value: 1.000000
*************************** 43. row ***************************
Variable_name: wsrep_local_state
Value: 4
*************************** 44. row ***************************
Variable_name: wsrep_local_state_comment
Value: Synced
*************************** 45. row ***************************
Variable_name: wsrep_cert_index_size
Value: 1
*************************** 46. row ***************************
Variable_name: wsrep_cert_bucket_count
Value: 22
*************************** 47. row ***************************
Variable_name: wsrep_gcache_pool_size
Value: 2208
*************************** 48. row ***************************
Variable_name: wsrep_causal_reads
Value: 0
*************************** 49. row ***************************
Variable_name: wsrep_cert_interval
Value: 0.000000
*************************** 50. row ***************************
Variable_name: wsrep_open_transactions
Value: 0
*************************** 51. row ***************************
Variable_name: wsrep_open_connections
Value: 0
*************************** 52. row ***************************
Variable_name: wsrep_ist_receive_status
Value:
*************************** 53. row ***************************
Variable_name: wsrep_ist_receive_seqno_start
Value: 0
*************************** 54. row ***************************
Variable_name: wsrep_ist_receive_seqno_current
Value: 0
*************************** 55. row ***************************
Variable_name: wsrep_ist_receive_seqno_end
Value: 0
*************************** 56. row ***************************
Variable_name: wsrep_incoming_addresses
Value: 10.0.0.30:3306
*************************** 57. row ***************************
Variable_name: wsrep_cluster_weight
Value: 1
*************************** 58. row ***************************
Variable_name: wsrep_desync_count
Value: 0
*************************** 59. row ***************************
Variable_name: wsrep_evs_delayed
Value:
*************************** 60. row ***************************
Variable_name: wsrep_evs_evict_list
Value:
*************************** 61. row ***************************
Variable_name: wsrep_evs_repl_latency
Value: 0/0/0/0/0
*************************** 62. row ***************************
Variable_name: wsrep_evs_state
Value: OPERATIONAL
*************************** 63. row ***************************
Variable_name: wsrep_gcomm_uuid
Value: f31d34bc-5dbc-11ec-98e0-f776afdbd6ed
*************************** 64. row ***************************
Variable_name: wsrep_gmcast_segment
Value: 0
*************************** 65. row ***************************
Variable_name: wsrep_cluster_conf_id
Value: 1
*************************** 66. row ***************************
Variable_name: wsrep_cluster_size
Value: 1
*************************** 67. row ***************************
Variable_name: wsrep_cluster_state_uuid
Value: f31dd7bc-5dbc-11ec-9379-1bc8d3cb9da1
*************************** 68. row ***************************
Variable_name: wsrep_cluster_status
Value: Primary
*************************** 69. row ***************************
Variable_name: wsrep_connected
Value: ON
*************************** 70. row ***************************
Variable_name: wsrep_local_bf_aborts
Value: 0
*************************** 71. row ***************************
Variable_name: wsrep_local_index
Value: 0
*************************** 72. row ***************************
Variable_name: wsrep_provider_name
Value: Galera
*************************** 73. row ***************************
Variable_name: wsrep_provider_vendor
Value: Codership Oy <info@codership.com>
*************************** 74. row ***************************
Variable_name: wsrep_provider_version
Value: 3.53(re33d74b)
*************************** 75. row ***************************
Variable_name: wsrep_ready
Value: ON
75 rows in set (0.01 sec)
说明：
wsrep_cluster_size表示，该Galera集群中只有一个节点
wsrep_local_state_comment 状态为Synced(4)，表示数据已同步完成(因为是第一个引导节点，无数据需要同步)。 如果状态是Joiner, 意味着 SST 没有完成. 只有所有节点状态是Synced，才可以加新节点
wsrep_cluster_status为Primary，且已经完全连接并准备好
5.3.3.3.5启动PXC集群中其它所有节点
[root@pxc2 ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
#启动服务
[root@pxc2 ~]# systemctl start mysql
[root@pxc2 ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 *:4567 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 80 [::]:3306 [::]:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
[root@pxc1 ~]# systemctl start mysql
5.3.3.3.6 查看集群状态，验证集群是否成功
[root@pxc3 ~]# mysql -uroot -p'sunxiang' -e"show status like 'wsrep%'" | grep size
mysql: [Warning] Using a password on the command line interface can be insecure.
wsrep_cert_index_size 1
wsrep_gcache_pool_size 2464
wsrep_cluster_size 3 #此集群中有3个成员
mysql> SHOW STATUS LIKE 'wsrep%'\G
*************************** 65. row ***************************
Variable_name: wsrep_cluster_conf_id
Value: 3 #此集群中有3个成员
#在任意节点，查看集群状态
[root@pxc2 ~]# mysql -uroot -psunxiang
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 11
Server version: 5.7.35-38-57-log Percona XtraDB Cluster (GPL), Release rel38, Revision 5c9f299, WSREP version 31.53, wsrep_31.53
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, 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 VARIABLES LIKE 'wsrep_node_name';
+-----------------+--------------------+
| Variable_name | Value |
+-----------------+--------------------+
| wsrep_node_name | pxc-cluster-node-2 |
+-----------------+--------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'wsrep_node_address';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| wsrep_node_address | 10.0.0.20 |
+--------------------+-----------+
1 row in set (0.01 sec)
mysql> SHOW VARIABLES LIKE 'wsrep_on';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_on | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.00 sec)
#在任意节点创建数据库
mysql> create database test1;
Query OK, 1 row affected (0.01 sec)
#在任意节点查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
5 rows in set (0.00 sec)
#利用Xshell软件，同时在三个节点创建数据库
mysql> use test1 #只有一个成功创建别的都无法创建，避免了冲突
Database changed
mysql> create table haha(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> use test1
Database changed
mysql> create table haha(id int);
ERROR 1050 (42S01): Table 'haha' already exists
mysql> use test1
Database changed
mysql> create table haha(id int);
ERROR 1050 (42S01): Table 'haha' already exists
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| haha |
+-----------------+
1 row in set (0.00 sec)
5.3.3.3.7 在PXC集群中加入节点
一个节点加入到Galera集群有两种情况：新节点加入集群、暂时离组的成员再次加入集群
1）新节点加入Galera集群
新节点加入集群时，需要从当前集群中选择一个Donor节点来同步数据，也就是所谓的state_snapshot_tranfer(SST)过程。SST同步数据的方式由选项wsrep_sst_method决定，一般选择的是xtrabackup。
必须注意，新节点加入Galera时，会删除新节点上所有已有数据，再通过xtrabackup(假设使用的是该方式)从Donor处完整备份所有数据进行恢复。所以，如果数据量很大，新节点加入过程会很慢。而且，在一个新节点成为Synced状态之前，不要同时加入其它新节点，否则很容易将集群压垮。如果是这种情况，可以考虑使用wsrep_sst_method=rsync来做增量同步，既然是增量同步，最好保证新节点上已经有一部分数据基础，否则和全量同步没什么区别，且这样会对Donor节点加上全局readonly锁。
2）旧节点加入Galera集群
如果旧节点加入Galera集群，说明这个节点在之前已经在Galera集群中呆过，有一部分数据基础，缺少的只是它离开集群时的数据。这时加入集群时，会采用IST(incremental snapshot transfer)传输机制，即使用增量传输。
但注意，这部分增量传输的数据源是Donor上缓存在GCache文件中的，这个文件有大小限制，如果缺失的数据范围超过已缓存的内容，则自动转为SST传输。如果旧节点上的数据和Donor上的数据不匹配(例如这个节点离组后人为修改了一点数据)，则自动转为SST传输。
#在PXC集群中再加一台新的主机PXC4：10.0.0.40
[root@pxc4 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc4 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc4 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.10,10.0.0.20,10.0.0.30,10.0.0.40
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.40
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-4
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass"
#将其它节点的配置文件加以修改
[root@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://10.0.0.10,10.0.0.20,10.0.0.30,10.0.0.40
[root@pxc2 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc3 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc4 ~]#systemctl start mysql
[root@pxc4 ~]#mysql -uroot -psunxiang
Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30,
Revision
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 4 |
+--------------------+-------+
1 row in set (0.00 sec)
5.3.3.4 实战案例：MariaDB Galera Cluster
范例：在centos8 实现MariaDB Galera Cluster
#在三个节点上都实现
[root@centos8 ~]#dnf install mariadb-server-galera -y
[root@centos8 ~]#vim /etc/my.cnf.d/galera.cnf
#wsrep_cluster_address="dummy://" 在此行下面加一行
wsrep_cluster_address="gcomm://10.0.0.8,10.0.0.18,10.0.0.28"
#启动第一节点
[root@centos8 ~]#galera_new_cluster
[root@centos8 ~]#systemctl enable mariadb
#再启动其它节点
[root@centos8 ~]#systemctl enable --now mariadb
[root@centos8 ~]#ss -ntul
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 *:4567 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 80 [::]:3306 [::]:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
[root@centos8 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> show status like "wsrep_ready";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_ready | ON |
+---------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'wsrep_%'\G
MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_%';
范例：CentOS 7 实现 MariaDB Galera Cluster 5.5
#参考仓库：https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.X/yum/centos7-amd64/
yum install MariaDB-Galera-server
vim /etc/my.cnf.d/server.cnf
[galera]
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.0.0.7,10.0.0.17,10.0.0.27"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
#下面配置可选项
wsrep_cluster_name = 'mycluster' 默认my_wsrep_cluster
wsrep_node_name = 'node1'
wsrep_node_address = '10.0.0.7'
#首次启动时，需要初始化集群，在其中一个节点上执行命令
/etc/init.d/mysql start --wsrep-new-cluster
#而后正常启动其它节点
service mysql start
#查看集群中相关系统变量和状态变量
SHOW VARIABLES LIKE 'wsrep_%';
SHOW STATUS LIKE 'wsrep_%';
SHOW STATUS LIKE 'wsrep_cluster_size';
5.3.4TiDB 概述
TiDB 是 PingCAP 公司受 Google Spanner / F1 论文启发而设计的开源分布式 HTAP (Hybrid Transactional and Analytical Processing) 数据库，结合了传统的 RDBMS 和NoSQL 的最佳特性。TiDB兼容 MySQL，支持无限的水平扩展，具备强一致性和高可用性。TiDB和mysql几乎完全兼容
TiDB 是一个分布式 NewSQL 数据库。它支持水平弹性扩展、ACID 事务、标准 SQL、MySQL 语法和MySQL 协议，具有数据强一致的高可用特性，是一个不仅适合 OLTP 场景还适合 OLAP 场景的混合数据库。TiDB年可用性达到99.95%
TiDB 的目标是为 OLTP(Online Transactional Processing) 和 OLAP (Online Analytical Processing) 场景提供一站式的解决方案。
5.3.4.1 TiDB 核心特点
- 高度兼容 MySQL 大多数情况下，无需修改代码即可从 MySQL 轻松迁移至 TiDB，分库分表后的MySQL 集群亦可通过 TiDB 工具进行实时迁移
- 水平弹性扩展 通过简单地增加新节点即可实现 TiDB 的水平扩展，按需扩展吞吐或存储，轻松应对高并发、海量数据场景
- 分布式事务 TiDB 100% 支持标准的 ACID 事务
- 真正金融级高可用 相比于传统主从 (M-S) 复制方案，基于 Raft 的多数派选举协议可以提供金融级的 100% 数据强一致性保证，且在不丢失大多数副本的前提下，可实现故障的自动恢复 (auto-failover)，无需人工介入
- 一站式 HTAP 解决方案 TiDB 作为典型的 OLTP 行存数据库，同时兼具强大的 OLAP 性能，配合TiSpark，可提供一站式 HTAP解决方案，一份存储同时处理OLTP & OLAP(OLAP、OLTP的介绍和比较 )无需传统繁琐的 ETL 过程
- 云原生 SQL 数据库 TiDB 是为云而设计的数据库，同 Kubernetes 深度耦合，支持公有云、私有云和混合云，使部署、配置和维护变得十分简单。TiDB 的设计目标是 100% 的 OLTP 场景和 80%的 OLAP 场景，更复杂的 OLAP 分析可以通过 TiSpark 项目来完成。 TiDB 对业务没有任何侵入性，能优雅的替换传统的数据库中间件、数据库分库分表等 Sharding 方案。同时它也让开发运维人员不用关注数据库 Scale 的细节问题，专注于业务开发，极大的提升研发的生产力
5.3.4.2 TiDB整体架构
TiDB Server
TiDB Server 负责接收SQL请求，处理SQL相关的逻辑，并通过PD找到存储计算所需数据的TiKV地址，与TiKV交互获取数据，最终返回结果。TiDB Server 是无状态的，其本身并不存储数据，只负责计算，可以无限水平扩展，可以通过负载均衡组件（LVS、HAProxy或F5）对外提供统一的接入地址。
PD Server
Placement Driver（简称PD）是整个集群的管理模块，其主要工作有三个：一是存储集群的元信息（某个Key存储在那个TiKV节点）；二是对TiKV集群进行调度和负载均衡（如数据的迁移、Raft group leader的迁移等）；三是分配全局唯一且递增的事务ID
PD 是一个集群，需要部署奇数个节点，一般线上推荐至少部署3个节点。PD在选举的过程中无法对外提供服务，这个时间大约是3秒
TiKV Server
TiKV Server 负责存储数据，从外部看TiKV是一个分布式的提供事务的Key-Value存储引擎。存储数据的基本单位是Region，每个Region负责存储一个Key Range（从StartKey到EndKey的左闭右开区间）的数据，每个TiKV节点会负责多个Region。TiKV使用Raft协议做复制，保持数据的一致性和容灾。副本以Region为单位进行管理，不同节点上的多个Region构成一个Raft Group，互为副本。数据在多个TiKV之间的负载均衡由PD调度，这里也就是以Region为单位进行调度