mysql mha高可用集群搭建

mha集群搭建

在 MySQL 高可用架构中，MHA（Master High Availability）通常采用一主多从的架构。

MHA 可以提供主从复制架构的自动 master failover 功能。当主服务器出现故障时，MHA 会将从服务器提升为新的主服务器，并在此期间通过与其他从服务器获取额外信息来避免一致性问题。

主从从部署

基本环境准备

yum源配置

cd /etc/yum.repos.d
mkdir bak
mv *.repo ./bak
cd /etc/yum.repos.d
wget -O CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum clean all
yum makecache

修改主机名和host文件解析

hostnamectl set-hostname master1 && bash
hostnamectl set-hostname slave1 && bash
hostnamectl set-hostname slave2 && bashcat /etc/hosts
192.168.3.74 master1
192.168.3.75 slave1
192.168.3.76 slave2

配置3台数据库主机之间ssh免密登录

ssh-keygen
ssh-cpoy-id 192.168.3.74
ssh-cpoy-id 192.168.3.75
ssh-cpoy-id 192.168.3.76

安装mysql

删除原有的mariadb

yum remove mariadb* -y

添加源

cat <<EOF > /etc/yum.repos.d/mysql57.repo
[mysql57]
name=mysql57
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/
gpgcheck=0
enabled=1
EOF

yum install mysql-community-server -y

启动mysql服务，查看初始密码

systemctl enable mysqld --nowcat /var/log/mysqld.log |grep password
2024-09-19T08:36:50.471114Z 1 [Note] A temporary password is generated for root@localhost: :Q,)akd#i8U+

使用初始密码登录，修改mysql的root账号初始密码

alter user 'root'@'localhost' identified with mysql_native_password by "aaA...111";flush privileges;

也可以使用mysqladmin修改密码

mysqladmin -uroot -p"e#)er)sg?39A"  password aaA...111

主从配置

修改my.cnf配置文件（主）

vim /etc/my.cnf

server-id = 1
log-bin = mysql-bin
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates = true

修改my.cnf配置文件（从1）

vim /etc/my.cnf

server-id = 2
log-bin = mysql-bin
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates = true

修改my.cnf配置文件（从2）

vim /etc/my.cnf

server-id = 3
log-bin = mysql-bin
binlog_format = row
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates = true

重启服务

systemctl restart mysqld

master1创建MHA用于监控的账号（mha）和主从同步账号（repl）

# 创建主从同步账号repl
create user 'repl'@'192.168.%.%' identified with mysql_native_password by 'repl1234A...';# 授权
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%.%';# 刷新权限，生效
flush privileges;# 查看用户信息
select user,host from mysql.user;# 查看授权
mysql> show grants for 'repl'@'192.168.%.%';
+--------------------------------------------------------+
| Grants for repl@192.168.%.%                            |
+--------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%.%' |
+--------------------------------------------------------+
1 row in set (0.00 sec)

# 创建用于mha高可用账号mha
create user 'mha'@'192.168.%.%' identified with mysql_native_password by 'mhA1234...';# 授权
grant all privileges on *.* to 'mha'@'192.168.%.%';# 刷新权限，生效
flush privileges;# 查看用户信息
mysql> select user,host from mysql.user;
+---------------+-------------+
| user          | host        |
+---------------+-------------+
| mha           | 192.168.%.% |
| repl          | 192.168.%.% |
| mysql.session | localhost   |
| mysql.sys     | localhost   |
| root          | localhost   |
+---------------+-------------+
5 rows in set (0.00 sec)# 查看用户repl授权
mysql> show grants for 'repl'@'192.168.%.%';
+--------------------------------------------------------+
| Grants for repl@192.168.%.%                            |
+--------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%.%' |
+--------------------------------------------------------+
1 row in set (0.00 sec)
# 查看用户mha授权
mysql> show grants for 'mha'@'192.168.%.%';
+----------------------------------------------------+
| Grants for mha@192.168.%.%                         |
+----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'mha'@'192.168.%.%' |
+----------------------------------------------------+
1 row in set (0.00 sec)

# 如何删除用户
drop user 'mha'@'192.168.%.%';

slave1、slave2从库配置主从复制

change master to master_host='192.168.3.74', master_user='repl', master_password='repl1234A...' , MASTER_AUTO_POSITION=1;

GTID位置点（自动追踪需要同步的 position）：master_auto_position=1

开启从库的主从复制功能

mysql> stop slave;          #停止主从同步功能
mysql> start slave;         #开启主从同步功能
mysql> show slave status\G  #查询状态，是否成功Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

2个从库会把刚才主库创建的用户同步到从库，从库中都有mha repl 用户，若没有，检查上述步骤配置是否有误

mysql> select user,host from mysql.user;
+---------------+-------------+
| user          | host        |
+---------------+-------------+
| mha           | 192.168.%.% |
| repl          | 192.168.%.% |
| mysql.session | localhost   |
| mysql.sys     | localhost   |
| root          | localhost   |
+---------------+-------------+
5 rows in set (0.01 sec)

从库设置只读模式

# 设置只读模式
mysql -uroot -paaA...111 -e 'set global read_only=1'# 查看是否为只读模式
mysql -uroot -paaA...111 -e "SHOW VARIABLES LIKE 'read_only'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+

mha部署

77主机作为管理主机（mha manager ）来管理 74主库 75从库 76从库

所有主机安装依赖软件包

yum -y install perl-DBD-MySQL perl-ExtUtils-MakeMaker perl-CPAN perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager

配置ssh免密登录 77 免密登录 74 75 76 ， 三台数据库相互免密登录

# 77免密登录 74 75 76
ssh-keygen
ssh-cpoy-id 192.168.3.74
ssh-cpoy-id 192.168.3.75
ssh-cpoy-id 192.168.3.76# 74 75 76 数据库相互免密登录
ssh-keygen
ssh-cpoy-id 192.168.3.74
ssh-cpoy-id 192.168.3.75
ssh-cpoy-id 192.168.3.76

在所有数据库节点 安装软件包 mha4mysql-node-0.58.tar.gz

# 上传软件包
rz mha4mysql-node-0.58.tar.gz
tar zxf mha4mysql-node-0.58.tar.gz -C /usr/local/src/
cd /usr/local/src/mha4mysql-node-0.58/
perl Makefile.PL && make && make install# 查看安装成功后的脚本文件
ll /usr/local/bin/
总用量 48
-r-xr-xr-x 1 root root 17639 9月  19 17:01 apply_diff_relay_logs
-r-xr-xr-x 1 root root  4807 9月  19 17:01 filter_mysqlbinlog
-r-xr-xr-x 1 root root  8337 9月  19 17:01 purge_relay_logs
-r-xr-xr-x 1 root root  7525 9月  19 17:01 save_binary_logs

在管理节点 需要安装两个软件包：mha4mysql-node-0.58.tar.gz 和 mha4mysql-manager-0.58.tar.gz

# 安装mha4mysql-node-0.58.tar.gz 和 mha4mysql-manager-0.58.tar.gzrz mha4mysql-manager-0.58.tar.gz mha4mysql-node-0.58.tar.gz
tar zxf mha4mysql-node-0.58.tar.gz -C /usr/local/src/
tar zxf mha4mysql-manager-0.58.tar.gz -C /usr/local/src/
cd /usr/local/src/mha4mysql-node-0.58/
perl Makefile.PL && make && make install
cd /usr/local/src/mha4mysql-manager-0.58
perl Makefile.PL && make && make install# 查看安装成功后的脚本文件
ll /usr/local/bin/
总用量 88
-r-xr-xr-x 1 root root 17639 9月  19 17:01 apply_diff_relay_logs
-r-xr-xr-x 1 root root  4807 9月  19 17:01 filter_mysqlbinlog
-r-xr-xr-x 1 root root  1995 9月  19 17:04 masterha_check_repl
-r-xr-xr-x 1 root root  1779 9月  19 17:04 masterha_check_ssh
-r-xr-xr-x 1 root root  1865 9月  19 17:04 masterha_check_status
-r-xr-xr-x 1 root root  3201 9月  19 17:04 masterha_conf_host
-r-xr-xr-x 1 root root  2517 9月  19 17:04 masterha_manager
-r-xr-xr-x 1 root root  2165 9月  19 17:04 masterha_master_monitor
-r-xr-xr-x 1 root root  2373 9月  19 17:04 masterha_master_switch
-r-xr-xr-x 1 root root  5172 9月  19 17:04 masterha_secondary_check
-r-xr-xr-x 1 root root  1739 9月  19 17:04 masterha_stop
-r-xr-xr-x 1 root root  8337 9月  19 17:01 purge_relay_logs
-r-xr-xr-x 1 root root  7525 9月  19 17:01 save_binary_logs

配置77主机（mha manager ）

拷贝脚本文件至/usr/local/bin/

# 拷贝脚本文件至/usr/local/bin/
cp /usr/local/src/mha4mysql-manager-0.58/samples/scripts/master_ip_failover /usr/local/bin/
cp /usr/local/src/mha4mysql-manager-0.58/samples/scripts/master_ip_online_change /usr/local/bin/# 查看安装成功后的脚本文件
ll /usr/local/bin/
总用量 104
-r-xr-xr-x 1 root root 17639 9月  19 17:01 apply_diff_relay_logs
-r-xr-xr-x 1 root root  4807 9月  19 17:01 filter_mysqlbinlog
-r-xr-xr-x 1 root root  1995 9月  19 17:04 masterha_check_repl
-r-xr-xr-x 1 root root  1779 9月  19 17:04 masterha_check_ssh
-r-xr-xr-x 1 root root  1865 9月  19 17:04 masterha_check_status
-r-xr-xr-x 1 root root  3201 9月  19 17:04 masterha_conf_host
-r-xr-xr-x 1 root root  2517 9月  19 17:04 masterha_manager
-r-xr-xr-x 1 root root  2165 9月  19 17:04 masterha_master_monitor
-r-xr-xr-x 1 root root  2373 9月  19 17:04 masterha_master_switch
-r-xr-xr-x 1 root root  5172 9月  19 17:04 masterha_secondary_check
-r-xr-xr-x 1 root root  1739 9月  19 17:04 masterha_stop
-rwxr-xr-x 1 root root  3648 9月  19 17:09 master_ip_failover
-rwxr-xr-x 1 root root  9870 9月  19 17:09 master_ip_online_change
-r-xr-xr-x 1 root root  8337 9月  19 17:01 purge_relay_logs
-r-xr-xr-x 1 root root  7525 9月  19 17:01 save_binary_logs

77主机（mha manager ）创建MHA Manager配置文件

# 配置 MHA Manager
mkdir /etc/mhacat > /etc/mha/app1.cnf << EOF
[server default]
manager_workdir=/etc/mha
manager_log=/etc/mha/manager.log
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_changeping_interval=2
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.3.75 -s 192.168.3.76ssh_user=root
ssh_port=22user=mha
password=mhA1234...repl_user=repl
repl_password=repl1234A...[server1]
hostname=192.168.3.74
candidate_master=1
port=3306[server2]
hostname=192.168.3.75
port=3306
candidate_master=1[server3]
hostname=192.168.3.76
candidate_master=1
port=3306EOF

修改故障切换脚本，添加vip地址的设置

vim /usr/local/bin/master_ip_failover#!/usr/bin/env perluse strict;
use warnings FATAL => 'all';use Getopt::Long;
use MHA::DBHelper;my ($command,        $ssh_user,         $orig_master_host,$orig_master_ip, $orig_master_port, $new_master_host,$new_master_ip,  $new_master_port,  $new_master_user,$new_master_password
);##############该部分按实际修改##############
my $vip = '192.168.3.177/24';
my $ifdev = 'ens33';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig $ifdev:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig $ifdev:$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,'new_master_user=s'     => \$new_master_user,'new_master_password=s' => \$new_master_password,
);exit &main();sub main {print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$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();# updating global catalog, etc$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();$exit_code = 0;};if ($@) {warn $@;# If you want to continue failover, exit 10.exit $exit_code;}exit $exit_code;}elsif ( $command eq "status" ) {print "Checking the Status of the script.. OK \n";# do nothingexit 0;}else {&usage();exit 1;}
}
sub start_vip() {`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}sub stop_vip() {return 0  unless  ($ssh_user);`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";
}

给mysql主服务器添加虚拟ip地址

# 创建vip地址
ifconfig ens33:1 192.168.3.177/24附：
# 删除
ifconfig ens33:1 del 192.168.3.177
# 停止
ifconfig ens33:1 down

启动 77主机（mha manager ）前验证配置是否正确

masterha_check_ssh --conf=/etc/mha/app1.cnf
masterha_check_repl --conf=/etc/mha/app1.cnf

启动 77主机（mha manager ）

# 前台运行
masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover# 后台运行
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/app1.log 2>&1 &

检查运行状态

masterha_check_status --conf=/etc/mha/app1.cnf

主数据上创建数据库测试

# 创建数据库和表
create database db9;
create table db9.a (id int);
insert into db9.a values(111);
select * from db9.a;# 创建账号devusr
create user 'devusr'@'192.168.%.%' identified with mysql_native_password by 'devusr1234A...';# 授权devusr用户
GRANT select,insert,delete ON db9.* TO 'devusr'@'192.168.%.%';
# 刷新权限，生效
flush privileges;
# 查看用户devusr授权
mysql> show grants for devusr@'192.168.%.%';
+-------------------------------------------------------------------+
| Grants for devusr@192.168.%.%                                     |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'devusr'@'192.168.%.%'                      |
| GRANT SELECT, INSERT, DELETE ON `db9`.* TO 'devusr'@'192.168.%.%' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

测试高可用

# 直接把主库关机，使用其它机器连接vip地址，可以看到正常连接数据库。
[root@mg ~]# mysql -udevusr -pdevusr1234A... -h192.168.3.177MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db9                |
+--------------------+
2 rows in set (0.00 sec)# 插入数据
insert into db9.a values(1234567);# 主库宕机后 77主机（mha manager ）会将 [server1] 字段被删除 ，且mha manager会停止运行
# 每次failover切换后会在管理目录生成文件 /etc/mha/app1.failover.complete 
# 下次在切换的时候会发现有这个文件导致切换不成功，需要手动清理掉
# rm -rf /masterha/app1/app1.failover.complete
# 也可以在启动时加上参数--ignore_last_failover# vip地址也会切换到其它机器上
cat /etc/mha/app1.cnf[server default]
manager_log=/etc/mha/manager.log
manager_workdir=/etc/mha
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=mhA1234...
ping_interval=2
repl_password=repl1234A...
repl_user=repl
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.3.75 -s 192.168.3.76
ssh_port=22
ssh_user=root
user=mha[server2]
candidate_master=1
hostname=192.168.3.75
port=3306[server3]
candidate_master=1
hostname=192.168.3.76
port=3306

故障修复

# 修好宕机的机器，启动mysql服务后，将原来的master主库机器指向新的主数据库
change master to master_host='192.168.3.75', master_user='repl', master_password='repl1234A...' , MASTER_AUTO_POSITION=1;# 开器主从同步
start slave;# 修改mha manager 配置文件
# 重新加以下入字段（74主数据库宕机后以下这段配置会被删除）
vim /etc/mha/app1.cnf
[server1]
hostname=192.168.3.74
candidate_master=1
port=3306# 重新启动mha 后台运行
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/app1.log 2>&1 &# 重新启动mha 前台运行
masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover# 修复完成------------------# 若还需要使用原来的主服务器，可以使用MHA的在线切换功能，如下# 首先，manager节点上停掉MHA监控
masterha_stop --conf=/etc/mha/app1.cnf# 切换回原来的主库
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.3.74 --orig_master_is_new_slave --running_updates_limit=10000 --interactive=0# 如果报错，编辑这个文件
vim /usr/local/bin/master_ip_online_change
# 找到下面这两条将其使用`#`号注释掉
FIXME_xxx_drop_app_user($orig_master_handler);
FIXME_xxx_create_app_user($new_master_handler);# 重新启动mha 后台运行
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/app1.log 2>&1 &# 重新启动mha 前台运行
masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover

常用命令

# MHA 检测ssh状态
masterha_check_ssh --conf=/etc/mha/app1.cnf# MHA 检测mysql集群主从状态
masterha_check_repl --conf=/etc/mha/app1.cnf# 前台运行
masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover# 后台运行
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/app1.log 2>&1 &# MHA Manager停止运行
masterha_stop --conf=/etc/mha/app1.cnf# 直接kill掉也可

搭建完成