基于MySQL 8.0部署MHA集群
https://blog.csdn.net/weixin_41909810/article/details/82940088
https://blog.csdn.net/wangbin9536/article/details/104157646
https://blog.csdn.net/Howei__/article/details/106144946
https://blog.csdn.net/qq_42024433/article/details/106163834
https://blog.csdn.net/ymeng9527/article/details/97661028
MySQL 8.0集群模式配置（主从、双主、MHA高可用模式配置）
https://blog.csdn.net/richie696/article/details/114261284
MySQL 8.0高可用性HA主从配置(双主双从HA模式)
https://blog.csdn.net/www520507/article/details/52789051
https://blog.csdn.net/weixin_40004659/article/details/113639446
MHA初探（主从复制，GID，MHA高可用）
https://blog.csdn.net/VegetandBird_s/article/details/102712633
MHA集群概述、部署MHA集群 测试配置
https://blog.csdn.net/xixi1067087210/article/details/81181213
https://blog.csdn.net/qq_36441027/article/details/81191758
MHA+keepalive高可用环境搭建
https://blog.csdn.net/yabingshi_tech/article/details/55509269
https://blog.csdn.net/guoshaoliang789/article/details/86086181
MaxScale代理软件(读写分离)
https://blog.csdn.net/LLcmpgheng/article/details/100574729
MySQL 5.7 MHA+MaxScale 2.0构建高可用环境
https://blog.csdn.net/worisaa/article/details/53365722
https://blog.csdn.net/wjl7813/article/details/79184455
https://blog.csdn.net/weixin_36135773/article/details/78799883
CentOS 7部署MySQL三种方法
https://blog.csdn.net/VegetandBird_s/article/details/102555932
CentOS 7二进制安装MySQL 8.0以及MySQL 8.0的新特性
https://blog.csdn.net/qq_37369726/article/details/104449605
一、环境介绍
操作系统：CentOS Linux release 7.7.1908 (Core)
MySQL服务器：8.0.11 MySQL Community Server - GPL
主从同步方式：基于GTID半同步复制
master_ip:192.168.56.101 mysql-101
slave1_ip:192.168.56.103 mysql-103
slave2_ip:192.168.56.105 mysql-105
所有Linux服务器关闭防火墙
1、关闭防火墙服务
① 关闭防火墙并查看防火墙状态
systemctl stop firewalld.service && systemctl status firewalld.service
② 禁止防火墙服务重启（永久关闭）
systemctl disable firewalld.service && systemctl status firewalld.service
2、关闭防火墙SELinux
① 修改selinux配置文件
sed -i "s#SELINUX=enforcing#SELINUX=disabled#g" /etc/sysconfig/selinux
② 修改完成后生效
reboot 或 init 6
③ 查看修改后状态
sestatus
二、修改MySQL配置文件
Master配置文件：
vim /etc/my.cnf
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir=/usr/local/mysql/mysql-8.0.11
datadir=/data/mysql/mysql8.0_data
port = 3308
socket = /tmp/mysql.sock
character-set-server=utf8
log-error = /data/mysql/mysqld.log
pid-file = /data/mysql/mysqld.pid
#GTID:
server_id=135
gtid_mode=on
enforce_gtid_consistency=on
#binlog
log_bin=master-binlog
log-slave-updates=1
binlog_format=row
#relay log
skip_slave_start=1
max_connect_errors=1000
default_authentication_plugin=‘mysql_native_password‘
Slave1配置文件：
vim /etc/my.cnf
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir=/data/mysql/mysql-8.0.11
datadir=/data/mysql/mysql8.0_data
port=3308
socket=/tmp/mysql-8-0-11.sock
character-set-server=utf8
log-error=/data/mysql/mysql8.0_data/mysqld.log
pid-file=/data/mysql/mysql8.0_data/mysqld.pid
max_connect_errors=1000
#GTID:
gtid_mode=on
enforce_gtid_consistency=on
server_id=144
#binlog
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row
#relay log
skip_slave_start=1
default_authentication_plugin=‘mysql_native_password‘
Slave2配置文件：
vim /etc/my.cnf
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir=/data/mysql/mysql-8.0.11
datadir=/data/mysql/mysql8.0_data
port=3308
socket=/tmp/mysql-8-0-11.sock
character-set-server=utf8
log-error=/data/mysql/mysql8.0_data/mysqld.log
pid-file=/data/mysql/mysql8.0_data/mysqld.pid
#GTID:
gtid_mode=on
enforce_gtid_consistency=on
server_id=143
#binlog
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row
#relay log
skip_slave_start=1
三、检查主从状态
1、主库检查从库状态
mysql> show slave hosts;
2、从库检查状态
Slave1_ip:192.168.56.103
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.101
Master_User: repl
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: master-binlog.000010
Read_Master_Log_Pos: 195
Relay_Log_File: wangbin3-relay-bin.000016
Relay_Log_Pos: 417
Relay_Master_Log_File: master-binlog.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 195
Relay_Log_Space: 668
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 135
Master_UUID: 791b77ef-4587-11ea-9571-0800278ffd3d
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 791b77ef-4587-11ea-9571-0800278ffd3d:1-11
Executed_Gtid_Set: 791b77ef-4587-11ea-9571-0800278ffd3d:1-11,
ac073db4-45c7-11ea-b18a-080027e75c4d:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)
Slave2_ip:192.168.56.105
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.101
Master_User: repl
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: master-binlog.000010
Read_Master_Log_Pos: 195
Relay_Log_File: wangbin-relay-bin.000013
Relay_Log_Pos: 417
Relay_Master_Log_File: master-binlog.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 195
Relay_Log_Space: 889
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 135
Master_UUID: 791b77ef-4587-11ea-9571-0800278ffd3d
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 791b77ef-4587-11ea-9571-0800278ffd3d:1-11
Executed_Gtid_Set: 0b1232b5-4658-11ea-9c94-080027e12091:1-2,
791b77ef-4587-11ea-9571-0800278ffd3d:1-11
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)
四、环境准备
三台服务器都必须进行下面环境准备
1、配置IP与主机名映射
编辑三台服务器
cat >> /etc/hosts <<EOF
192.168.56.101 mysql-101
192.168.56.103 mysql-103
192.168.56.105 mysql-105
EOF
2、配置服务间免认证登录
生成免认证密码文件
[root@mysql-101 .ssh]# ssh-keygen -P "" -f ~/.ssh/id_rsa
拷贝免登录文件到当前服务器和另外两台服务器
[root@mysql-101 .ssh]# ssh-copy-id -i /root/.ssh/id_rsa root@192.168.56.101
[root@mysql-101 .ssh]# ssh-copy-id -i /root/.ssh/id_rsa root@192.168.56.103
[root@mysql-101 .ssh]# ssh-copy-id -i /root/.ssh/id_rsa root@192.168.56.105
验证免认证登录是否配置成功
ssh root@192.168.56.101
ssh root@192.168.56.103
ssh root@192.168.56.105
注：上面的三步操作另外两台Linux服务器也必须得执行
3、安装yum扩展包
所有Linux服务器
wget http://mirrors.sohu.com/fedora-epel/epel-release-latest-7.noarch.rpm
yum -y install epel-release-latest-7.noarch.rpm
4、修改/etc/yum.repos.d/epel.repo文件参数
所有Linux服务器
vim /etc/yum.repos.d/epel.repo文件中的
gpgcheck=0
5、安装所需要的依赖包
所有服务器
yum list perl-DBD-MySQL ncftp perl-DBI
yum install -y perl-DBD-MySQL ncftp perl-DBI
6、安装监控服务器依赖包
Linux服务器：slave2
yum -y install perl-Config-Tiny.noarch perl-Time-HiRes.x86_64 perl-Parallel-ForkManager perl-Log-Dispatch.noarch
7、安装MHA软件包
https://pan.baidu.com/s/1hOVlMh7WpNaihqK1eEbS0w
所有服务器都必须安装节点软件包
rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm
只在从库Slave 2安装监控软件包
生产环境中监控服务要尽量安装在单独的Linux服务器上面
rpm -ivh mha4mysql-manager-0.57-0.el7.noarch.rpm
五、配置MHA
1、主库创建用户
create user mha@‘192.168.56.%‘ identified by ‘mysql‘;
grant all privileges on *.* to mha@‘192.168.56.%‘;
2、创建目录
Master服务器和Slave1服务器创建目录
mkdir -p /etc/mha
管理节点（Slave2）创建目录
mkdir -p /etc/mha
mkdir -p /home/mha
3、配置管理节点（slave2）的配置文件
vim /etc/mha/mysql-mha.conf
[server default]
user=dba_mha
password=123456
manager_workdir=/home/mha
manager_log=/home/mha/manager.log
remote_workdir=/home/mha
ssh_user=root
repl_user=repl
repl_password=repl
ping_interval=1
master_binlog_dir=/data/mysql
ssh_port=22
master_ip_failover_script = /usr/bin/master_ip_failover
secondary_check_script = /usr/bin/masterha_secondary_check -s 192.168.56.101 -s 192.168.56.103 -s 192.168.56.101
[server1]
hostname=192.168.56.101
candidate_master=1
[server2]
hostname=192.168.56.103
candidate_master=1
[server3]
hostname=192.168.56.105
no_master=1
4、配置管理节点（Slave2）VIP漂移配置文件
vim /usr/bin/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => ‘all‘;
use Getopt::Long;
my (
$command, $orig_master_host, $orig_master_ip,$ssh_user,
$orig_master_port, $new_master_host, $new_master_ip,$new_master_port,
$orig_master_ssh_port,$new_master_ssh_port,$new_master_user,$new_master_password
);
my $vip = ‘192.168.56.110/24‘;
my $key = ‘1‘;
my $ssh_start_vip = "/sbin/ifconfig enp0s3:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig enp0s3:$key down";
my $ssh_Bcast_arp= "/sbin/arping -I enp0s3 -c 3 -A 192.168.56.110";
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,
‘orig_master_ssh_port=i‘ => \$orig_master_ssh_port,
‘new_master_host=s‘ => \$new_master_host,
‘new_master_ip=s‘ => \$new_master_ip,
‘new_master_port=i‘ => \$new_master_port,
‘new_master_ssh_port‘ => \$new_master_ssh_port,
‘new_master_user‘ => \$new_master_user,
‘new_master_password‘ => \$new_master_password
);
exit &main();
sub main {
$ssh_user = defined $ssh_user ? $ssh_user : ‘root‘;
print "\n\nIN SCRIPT TEST====$ssh_user|$ssh_stop_vip==$ssh_user|$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
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" ) {
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
&start_arp();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub start_arp() {
`ssh $ssh_user\@$new_master_host \" $ssh_Bcast_arp \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --ssh_user=user --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";
执行权限设置：
chmod +x /usr/bin/master_ip_failover
5、检查ssh配置
[root@mysql-105 mha]# masterha_check_ssh --conf=/etc/mha/mysql-mha.conf
Tue Feb 4 22:10:05 2020 - [info] All SSH connection tests passed successfully.
出现 All SSH connection tests passed successfully 表示配置成功
6、检查主从复制配置
[root@mysql-105 mha]# masterha_check_repl --conf=/etc/mha/mysql-mha.conf
MySQL Replication Health is OK.
7、Master手动执行VIP命令
/sbin/ifconfig enp0s3:1 192.168.56.110/24
8、管理节点（Slave2）启动MHA
masterha_manager --conf=/etc/mha/mysql-mha.conf &
MySQL MHA之master_ip_failover.sh脚本
https://blog.csdn.net/yiyuf/article/details/40343967
Haproxy+keeplived+MyCat+MHA集群的详细配置
https://blog.csdn.net/zhydream77/article/details/81630610
https://blog.csdn.net/weixin_43181843/article/details/83795745
MHA+Keepalived
https://blog.csdn.net/yiyuf/article/details/40340895
基于GTID增强半同步复制MySQL高可用架构之MHA之主从切换（手动切换、在线切换、自动切换）
https://blog.csdn.net/weixin_44299264/article/details/98200228
MHA实现MySQL 8.0主从故障切换
https://blog.csdn.net/qq_37369726/article/details/104462513
MySQL 8.0 MHA架构sysbench压测
https://blog.csdn.net/cuihengju8933/article/details/100461010
MySQL 8.0主从复制+主备切换
https://blog.csdn.net/liurui321/article/details/78978457
MongoDB介绍、安装、用户管理
https://blog.csdn.net/qq_37369726/article/details/104131018
MongoDB 4.4.4配置分片集群
https://blog.csdn.net/qq_37369726/article/details/104152103
https://blog.csdn.net/richie696/article/details/114660811