Mysql高可用MHA部署

发表于 | 分类于 ， | 阅读次数：
本文字数： 27k | 阅读时长 ≈ 25 分钟

MySQL的MHA（Master High Availability）是一种用于MySQL高可用性的解决方案，旨在提高MySQL数据库集群的可用性和故障恢复能力。MHA主要通过自动故障切换来确保数据库的高可用性，它能够在主数据库出现故障时，自动将从数据库提升为主数据库，以避免长时间的服务中断。

MHA组件介绍

MHA Manager（MHA 管理器）

功能：MHA Manager 是 MHA 的控制中心，负责监控 MySQL 主从数据库的健康状态，并在主服务器故障时触发自动故障切换。
角色：
监控数据库主从节点的状态。
在主服务器发生故障时，选择健康的从服务器并提升为新的主服务器。
管理数据库节点的配置（如：主库和从库的切换）。
在主库恢复后，自动将恢复的主库重新配置为从库。
部署：MHA Manager 通常运行在一个独立的服务器上，不与数据库实例共享。

MHA Node（MHA 节点）

功能：MHA Node 是 MHA 管理器和 MySQL 数据库之间的中介，负责与 MHA Manager 进行通信，并执行主从切换操作。
角色：
向 MHA Manager 上报数据库节点（主库和从库）的健康状态。
参与故障切换流程，包括主库的提升和从库的降级。
部署：每个 MySQL 实例（主服务器和从服务器）都需要安装和配置 MHA Node，以便与 MHA Manager 通信。

集群规划

服务器初始化

服务器免密

MHA Manager使用SSH登录到主从数据库节点，执行故障检测、切换主库等操作，包括在故障切换过程中通过SSH访问数据库服务器执行切换操作（例如停止服务、修改配置文件等）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#MHA Manager免密
[root@test-server-01 ~]# ssh-keygen -t rsa
[root@test-server-01 ~]# ssh-copy-id 192.168.40.180
[root@test-server-01 ~]# ssh-copy-id 192.168.40.181
[root@test-server-01 ~]# ssh-copy-id 192.168.40.182
[root@test-server-01 ~]# ssh-copy-id 192.168.40.183

#MySQL Master
[root@test-server-02 ~]# ssh-keygen -t rsa
[root@test-server-02 ~]# ssh-copy-id 192.168.40.180
[root@test-server-02 ~]# ssh-copy-id 192.168.40.181
[root@test-server-02 ~]# ssh-copy-id 192.168.40.182
[root@test-server-02 ~]# ssh-copy-id 192.168.40.183

#MySQL Slave 1
[root@test-server-03 ~]# ssh-keygen -t rsa
[root@test-server-03 ~]# ssh-copy-id 192.168.40.180
[root@test-server-03 ~]# ssh-copy-id 192.168.40.181
[root@test-server-03 ~]# ssh-copy-id 192.168.40.182
[root@test-server-03 ~]# ssh-copy-id 192.168.40.183

#MySQL Slave 2
[root@test-server-04 ~]# ssh-keygen -t rsa
[root@test-server-04 ~]# ssh-copy-id 192.168.40.180
[root@test-server-04 ~]# ssh-copy-id 192.168.40.181
[root@test-server-04 ~]# ssh-copy-id 192.168.40.182
[root@test-server-04 ~]# ssh-copy-id 192.168.40.183

关闭防火墙

1
2
#所有机器 
[root@test-server-01 ~]# systemctl stop firewalld ; systemctl disable firewalld

配置时间同步

1
2
3
4
5
6
7
8
9
10
#所有机器
#安装ntpdate命令
[root@test-server-01 ~]# yum install ntpdate -y

#跟网络时间做同步
[root@test-server-01 ~]# ntpdate cn.pool.ntp.org

#把时间同步做成计划任务
[root@test-server-01 ~]# crontab -e
* *  * * * /usr/sbin/ntpdate   cn.pool.ntp.org

MySQL集群部署

MySQL组件下载地址

选择对应版本下载：

安装MySQL集群

三台MySQL节点操作

1.上传安装包到/usr/local/src目录

1
2
[root@test-server02 src]# ll mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
-rw-r--r-- 1 root root 641127384 Feb 19 13:39 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

2.解压安装包

1
[root@test-server02 src]# tar -xvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

3.移动安装包，重新命名

1
[root@test-server02 src]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql5.7

4.设置路径环境变量

1
2
[root@test-server02 src]# echo 'export  PATH=/usr/local/mysql5.7/bin:$PATH' >> /etc/profile
[root@test-server02 src]# source /etc/profile

5.创建MySQL用户

1
[root@test-server02 src]# useradd -M -s /sbin/nologin  mysql

6.创建MySQL数据目录

1
[root@test-server02 src]# mkdir -pv  /opt/mysqldb/{temp,log,data}

7.赋予MySQL用户权限

1
[root@test-server02 src]# chown -R mysql:mysql /opt/mysqldb

8.初始化MySQL数据目录

1
2
3
4
5
6
7
8
9
10
11
12
[root@test-server02 src]# mysqld --initialize  --datadir=/opt/mysqldb/data --user=mysql

2025-02-19T06:40:36.493733Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2025-02-19T06:40:36.494435Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2025-02-19T06:40:37.775262Z 0 [Warning] InnoDB: New log files created, LSN=45790
2025-02-19T06:40:37.983917Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2025-02-19T06:40:38.309684Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 6e2db754-ee8c-11ef-b1a1-000c292b8fdc.
2025-02-19T06:40:38.375782Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2025-02-19T06:40:38.378205Z 1 [Note] A temporary password is generated for root@localhost: n-HaNrreh2_t

#ERROR不需要管，出现同上日志代表初始化成功。
n-HaNrreh2_t：MySQL初始化之后的密码，需要保存下来等会使用

9.配置MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
#MySQL Master配置
[root@test-server02 src]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql5.7
user=mysql
port=3306
datadir=/opt/mysqldb/data
log-error=/opt/mysqldb/log/err.log
pid-file=/opt/mysqldb/temp/mysqld.pid
socket=/opt/mysqldb/temp/mysqld.sock
symbolic-links=0
server_id=1
gtid-mode=on
enforce-gtid-consistency=true
relay_log_purge=0
log_slave_updates=ON
log_bin=/opt/mysqldb/log/binlog
binlog_format=ROW

[client]
socket=/opt/mysqldb/temp/mysqld.sock
default-character-set=utf8


#MySQL Slave1配置
[root@test-server03 src]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql5.7
user=mysql
port=3306
datadir=/opt/mysqldb/data
log-error=/opt/mysqldb/log/err.log
pid-file=/opt/mysqldb/temp/mysqld.pid
socket=/opt/mysqldb/temp/mysqld.sock
symbolic-links=0
server_id=2
gtid-mode=on
enforce-gtid-consistency=true
relay_log_purge=0
log_slave_updates=ON
log_bin=/opt/mysqldb/log/binlog
binlog_format=ROW

[client]
socket=/opt/mysqldb/temp/mysqld.sock
default-character-set=utf8


#MySQL Slave2配置
[root@test-server04 src]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql5.7
user=mysql
port=3306
datadir=/opt/mysqldb/data
log-error=/opt/mysqldb/log/err.log
pid-file=/opt/mysqldb/temp/mysqld.pid
socket=/opt/mysqldb/temp/mysqld.sock
symbolic-links=0
server_id=3
gtid-mode=on
enforce-gtid-consistency=true
relay_log_purge=0
log_slave_updates=ON
log_bin=/opt/mysqldb/log/binlog
binlog_format=ROW

[client]
socket=/opt/mysqldb/temp/mysqld.sock
default-character-set=utf8

#server_id三台不能相同，数据目录改成自己的

10.创建MySQL启动文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
#三台MySQL节点操作
[root@test-server02 src]# cat /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/opt/mysqldb/temp/mysqld.pid
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables
# ExecStartPre=/usr/bin/mysqld_pre_systemd
# Start main service
ExecStart=/usr/local/mysql5.7/bin/mysqld --daemonize --pid-file=/opt/mysqldb/temp/mysqld.pid $MYSQLD_OPTS
# Use this to switch malloc implementation
# EnvironmentFile=-/etc/sysconfig/mysql
# Sets open_files_limit
LimitNOFILE = 5000
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false

#目录根据自己情况修改
#赋予执行权限
[root@test-server02 src]# chmod 644 /usr/lib/systemd/system/mysqld.service

11.启动MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
#三台MySQL节点操作
#systemd重新加载配置
[root@test-server02 src]# systemctl daemon-reload

#设置开机自启
[root@test-server02 src]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.

#重启MySQL
[root@test-server02 src]# systemctl restart mysqld

#查看MySQL状态
[root@test-server02 src]# systemctl status mysqld

12.修改MySQL密码

1
2
3
4
5
#登录MySQL
[root@test-server02 src]# mysql -uroot -pn-HaNrreh2_t  #每台MySQL的密码不同

#三台MySQL节点操作
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

配置一主两从

1.授予权限

三台MySQL节点操作

1
2
3
4
5
6
7
#授予该用户进行主从复制所需的权限
mysql> grant replication slave on *.* to 'repl_user'@'192.168.40.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

#刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

2.查看二进制文件名和位置信息

master节点操作

1
2
3
4
5
6
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 |     1467 |              |                  | 6e2db754-ee8c-11ef-b1a1-000c292b8fdc:1-6 |
+---------------+----------+--------------+------------------+------------------------------------------+

3.配置从节点

Slave 1和Slave 2执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#根据自己的信息进行修改
mysql> change master to master_host='192.168.40.181',master_user='repl_user',master_password='123456',master_log_file='binlog.000001',master_log_pos=1467;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.40.181
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 1467
               Relay_Log_File: test-server03-relay-bin.000002
                Relay_Log_Pos: 317
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

#两个YES代表MySQL已经成功设置成了主从

4.读写分离

Slave 1和Slave 2执行

1
2
3
#两个从库必须设置为只读模式：
mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

测试MySQL主从

MySQL Master操作

1
2
3
4
5
6
7
8
9
10
11
CREATE DATABASE test_db;

USE test_db;

CREATE TABLE test_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO test_table (id, name) VALUES (1, 'John');
INSERT INTO test_table (id, name) VALUES (2, 'Alice');

MySQL Slave 1和Slave 2操作

1
2
3
4
5
6
7
8
9
mysql> select * from test_db.test_table;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | Alice |
+----+-------+
2 rows in set (0.00 sec)
#出来数据代表主从成功

赋予MHA用户连接权限

MySQL Master操作即可

1
2
3
4
mysql> grant all privileges on *.* to 'mha_user'@'192.168.40.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

#主从复制已完成，所以从不用单独操作

安装MHA环境

安装MHA依赖环境

1
2
3
4
5
6
7
8
9
10
#四台机器全部操作
[root@test-server-01 ~]# yum install epel-release --nogpgcheck -y

[root@test-server-01 ~]# yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN

安装MHA Node

MHA Node下载地址

四台机器全部安装MHA Node

1.上传部署包到/opt目录

1
2
[root@test-server-01 opt]# ll mha4mysql-node-0.58.tar.gz
-rw-r--r-- 1 root root 56220 Feb 20 09:09 mha4mysql-node-0.58.tar.gz

2.生成Makefile

1
2
3
4
5
6
7
8
9
10
11
12
[root@test-server-01 opt]# tar -xf mha4mysql-node-0.58.tar.gz
[root@test-server-01 opt]# cd mha4mysql-node-0.58
[root@test-server-01 mha4mysql-node-0.58]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node

3.编译安装

1
2
3
4
5
6
7
8
9
10
11
12
[root@test-server-01 mha4mysql-node-0.58]# make && make install

#安装完成之后 /usr/local/bin会生成四个文件
apply_diff_relay_logs
filter_mysqlbinlog
purge_relay_logs
save_binary_logs

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

安装MHA Manager

MHA Manager下载地址

MHA Manager节点操作

1.上传部署包到opt目录

1
2
[root@test-server-01 opt]# ll mha4mysql-manager-0.58.tar.gz
-rw-r--r-- 1 root root 119801 Feb 20 09:37 mha4mysql-manager-0.58.tar.gz

2.生成Makefile

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[root@test-server-01 opt]# tar -xf mha4mysql-manager-0.58.tar.gz
[root@test-server-01 opt]# cd mha4mysql-manager-0.58
[root@test-server-01 mha4mysql-manager-0.58]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI                   ...loaded. (1.627)
- DBD::mysql            ...loaded. (4.023)
- Time::HiRes           ...loaded. (1.9725)
- Config::Tiny          ...loaded. (2.14)
- Log::Dispatch         ...loaded. (2.41)
- Parallel::ForkManager ...loaded. (1.18)
- MHA::NodeConst        ...loaded. (0.58)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::manager

3.编译安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[root@test-server-01 mha4mysql-manager-0.58]# make && make install

#安装完成之后 /usr/local/bin会生成几个文件
masterha_check_repl
masterha_check_ssh
masterha_check_status
masterha_conf_host
masterha_manager
masterha_master_monitor
masterha_master_switch
masterha_secondary_check
masterha_stop

1. masterha_check_repl：用于检查 MySQL 主从复制的状态，确保主从复制正常运行并进行必要的修复操作。
2. masterha_check_ssh：检查 MySQL 主从节点之间的 SSH 连接状态，确保 SSH 连接正常，这对 MHA 工具的正常操作至关重要。
3. masterha_check_status：用于检查 MySQL 主从复制环境的状态，包括检查主从复制延迟等信息。
4. masterha_conf_host：用于配置 MHA 工具中的主机信息，包括主从节点的连接信息等。
5. masterha_manager：主要的 MHA 工具，用于监控和管理 MySQL 主从复制环境的自动故障转移和故障恢复。
6. masterha_master_monitor：用于监控 MySQL 主服务器的状态，以及检测主服务器是否发生故障。
7. masterha_master_switch：用于手动切换 MySQL 主从复制环境中的主服务器，执行主服务器的切换操作。
8. masterha_secondary_check：用于检查 MySQL 主从复制中的辅助节点（Secondary）状态，确保辅助节点正常。
9. masterha_stop：用于停止 MHA 工具或相关服务的操作。

配置MHA环境

1.复制相关脚本到/usr/local/bin目录

1
2
3
4
5
6
7
8
9
10
11
12
[root@test-server-01 mha4mysql-manager-0.58]# cp -r samples/scripts /usr/local/bin/
[root@test-server-01 mha4mysql-manager-0.58]# ll /usr/local/bin/scripts/
total 32
-rwxr-xr-x 1 root root  3648 Feb 20 09:52 master_ip_failover
-rwxr-xr-x 1 root root  9870 Feb 20 09:52 master_ip_online_change
-rwxr-xr-x 1 root root 11867 Feb 20 09:52 power_manager
-rwxr-xr-x 1 root root  1360 Feb 20 09:52 send_report

1.master_ip_failover #自动切换时 VIP 管理的脚本
2.master_ip_online_change #在线切换时 vip 的管理
3.power_manager #故障发生后关闭主机的脚本
4.send_report #因故障切换后发送报警的脚本

2.复制自动切换VIP管理脚本到/usr/local/bin目录。使用该脚本管理VIP

1
2
3
[root@test-server-01 mha4mysql-manager-0.58]# cp -r /usr/local/bin/scripts/master_ip_failover /usr/local/bin/
[root@test-server-01 mha4mysql-manager-0.58]# cp -r /usr/local/bin/scripts/master_ip_online_change  /usr/local/bin/
#master_ip_online_change脚本没有修改不知道会不会用到，但是下面配置中指定了该路径

3.修改脚本内容

1
2
my $ssh_start_vip = "/sbin/ifconfig $ifdev:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig $ifdev:$key down";

上面代码中的$ifdev一开始是直接写的网卡名，发现之后直接改成了变量名但是没做实验，vip如果飘逸不成功可以从这地方排查

注：只需要修改$vip $brdc $ifdev即可

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
[root@test-server-01 mha4mysql-manager-0.58]# cd /usr/local/bin/
#删除原有的 复制下面的直接粘贴即可。VIP根据自己的网段修改
#粘贴的时候记得 vim 编辑文件之后：Esc :set paste之后再进行粘贴
[root@test-server-01 bin]# vim master_ip_failover
#!/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 = '192.168.40.200';
my $brdc = '192.168.40.255';
my $ifdev = 'ens33';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig $ifdev:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig $ifdev:$key down";
my $exit_code = 0;
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") {
        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();
            $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 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";
}

4.创建MHA目录并拷贝文件

master_binlog_dir=/opt/mysqldb/data 是MySQL二进制日志文件目录，该目录写错会导致MySQL MHA集群VIP飘逸失败

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
[root@test-server-01 bin]# mkdir /etc/masterha
[root@test-server-01 bin]# cp -r /opt/mha4mysql-manager-0.58/samples/conf/app1.cnf /etc/masterha/
[root@test-server-01 bin]# cat /etc/masterha/app1.cnf
[server default]
manager_log=/etc/masterha/manager.log
manager_workdir=/etc/masterha/mha
master_binlog_dir=/opt/mysqldb/data #指定 MySQL 主服务器的二进制日志文件目录。
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
user=mha_user
password=123456
port=3306
ping_interval=1
remote_workdir=/etc/masterha/mha-node
repl_user=repl_user
repl_password=123456
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.40.182 -s 192.168.40.183
shutdown_script=""
ssh_user=root

[server1]
hostname=192.168.40.181
port=3306

[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.40.182
port=3306

[server3]
hostname=192.168.40.183
port=3306

#配置解释
1.[server default]：这是默认服务器配置部分，包含了 MHA 管理器的相关配置。
manager_log：指定 MHA 管理器的日志文件路径。
manager_workdir：指定 MHA 管理器的工作目录。
master_binlog_dir：指定 MySQL 主服务器的二进制日志文件目录。
master_ip_failover_script：指定 IP 切换脚本，用于在主服务器发生故障时执行 IP 切换。
master_ip_online_change_script：指定在线 IP 变更脚本。
user：MHA 用户名。
password：MHA 用户密码。
port：MySQL 端口号。
ping_interval：指定 MHA 管理器检测 MySQL 高可用的间隔时间。
remote_workdir：指定节点服务器的工作目录。
repl_user：MySQL 复制用户。
repl_password：MySQL 复制用户密码。
secondary_check_script：指定用于检查备用服务器的脚本。
shutdown_script：指定关闭脚本。
ssh_user：指定用于 SSH 连接的用户名。

2.[server1]：定义了一个 MySQL 服务器节点，其中包含：
hostname：主 MySQL 服务器的 IP 地址。
port：MySQL 端口号。

3.[server2]：定义了另一个 MySQL 服务器节点，标记为候选主服务器，包含：
candidate_master：标记此服务器为候选主服务器。
check_repl_delay：检查复制延迟设置为 0。
hostname：第二个 MySQL 服务器的 IP 地址。
port：MySQL 端口号。

4.[server3]：定义了另一个 MySQL 服务器节点，其中包含：
hostname：第三个 MySQL 服务器的 IP 地址。
port：MySQL 端口号。

5.MySQL Master机器手动开启VIP

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
#没有ifconfig提前安装,全部机器安装
[root@test-server02 ~]# yum install net-tools -y
#Master执行
[root@test-server02 ~]# /sbin/ifconfig ens33:1 192.168.40.200/24
You have new mail in /var/spool/mail/root
[root@test-server02 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:2b:8f:dc brd ff:ff:ff:ff:ff:ff
    inet 192.168.40.181/24 brd 192.168.40.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.40.200/24 brd 192.168.40.255 scope global secondary ens33:1
       valid_lft forever preferred_lft forever
    inet6 fe80::eb22:4e3:6568:88de/64 scope link noprefixroute
       valid_lft forever preferred_lft forever
    inet6 fe80::38d1:dfef:9158:a02b/64 scope link tentative noprefixroute dadfailed
       valid_lft forever preferred_lft forever
3: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default
    link/ether 02:42:d0:ec:ae:7f brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
       valid_lft forever preferred_lft forever
       
#192.168.40.200VIP 已经出现

6.MHA Manager测试

1
2
3
4
5
6
7
8
9
10
11
1.Manager节点测试ssh免密
[root@test-server-01 ~]# masterha_check_ssh -conf=/etc/masterha/app1.cnf

Thu Feb 20 10:53:10 2025 - [info] All SSH connection tests passed successfully.
#最后以上输出以上内容代表正常

2.Manager节点测试MySQL主从状态
[root@test-server-01 ~]# masterha_check_repl -conf=/etc/masterha/app1.cnf

MySQL Replication Health is OK.
#最后以上输出以上内容代表正常

7.启动MHA Manager

1
2
3
4
5
6
7
8
#启动MHA
[root@test-server-01 ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/masterha/manager.log 2>&1 &
[1] 10828

#查看MHA状态
[root@test-server-01 ~]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:10828) is running(0:PING_OK), master:192.168.40.181
#启动成功

MySQL MHA高可用集群测试

通过VIP连接MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#通过VIP连接数据库
[root@test-server-01 ~]# mysql -h192.168.40.200 -umha_user -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

#Master创建数据库
MySQL [(none)]> create database test;
Query OK, 1 row affected (0.01 sec)

#Slaver 1和Slaver 2查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test_db            |
+--------------------+
6 rows in set (0.01 sec)

#成功

模拟故障测试

1.查看manager日志，观察manager状态

1
[root@test-server-01 ~]# tailf -n100 /etc/masterha/manager.log

2.停止Master MySQL

1
[root@test-server02 ~]# systemctl stop mysqld

3.Slave 1节点查看vip是否飘逸

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[root@test-server-03 ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:b9:50:43 brd ff:ff:ff:ff:ff:ff
    inet 192.168.40.182/24 brd 192.168.40.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.40.200/24 brd 192.168.40.255 scope global secondary ens33:1
       valid_lft forever preferred_lft forever
    inet6 fe80::eb22:4e3:6568:88de/64 scope link tentative noprefixroute dadfailed
       valid_lft forever preferred_lft forever
    inet6 fe80::38d1:dfef:9158:a02b/64 scope link tentative noprefixroute dadfailed
       valid_lft forever preferred_lft forever
    inet6 fe80::1c4c:acc5:6453:90f3/64 scope link tentative noprefixroute dadfailed
       valid_lft forever preferred_lft forever
3: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default
    link/ether 02:42:56:61:72:fd brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
       valid_lft forever preferred_lft forever
       
#此时发现vip 200已经飘逸到了182节点 这个是slave 1的节点

4.manager日志

1
2
3
4
5
6
7
8
9
10
11
12
13
#manager日志中最下面出现下方日志就证明已经切换成功了
----- Failover Report -----
app1: MySQL Master failover 192.168.40.181(192.168.40.181:3306) to 192.168.40.182(192.168.40.182:3306) succeeded
Master 192.168.40.181(192.168.40.181:3306) is down!
Check MHA Manager logs at test-server-01:/etc/masterha/manager.log for details.
Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.40.181(192.168.40.181:3306)
Selected 192.168.40.182(192.168.40.182:3306) as a new master.
192.168.40.182(192.168.40.182:3306): OK: Applying all logs succeeded.
192.168.40.182(192.168.40.182:3306): OK: Activated master IP address.
192.168.40.183(192.168.40.183:3306): OK: Slave started, replicating from 192.168.40.182(192.168.40.182:3306)
192.168.40.182(192.168.40.182:3306): Resetting slave info succeeded.
Master failover to 192.168.40.182(192.168.40.182:3306) completed successfully.

5.查看MySQL状态

登录MySQL Slave2查看从状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[root@test-server-04 ~]# mysql -uroot -p123456
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.40.182
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1700
               Relay_Log_File: test-server-04-relay-bin.000003
                Relay_Log_Pos: 744
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

#Master_Host已经成功由192.168.40.181切换成了192.168.40.181。到此MHA集群已经成功搭建了

6.故障修复

恢复MySQL

1
[root@test-server-02 ~]# systemctl start mysqld

主数据库查看master状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                  |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| binlog.000002 |     1700 |              |                  | 7a69b6e0-eff7-11ef-b307-000c292b8fdc:3-7,
7a7fcdf5-eff7-11ef-ab74-000c29b95043:1-2 |
+---------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#正常写入写入数据的情况下Position会一直变，可以和研发沟通空闲时间进行锁库进行配置
1. 锁库命令：FLUSH TABLE WITH READ LOCK;
2. 解锁命令： unlock tables;
#配置完解开即可

#原master做成Slave数据库
mysql> change master to master_host='192.168.40.182',master_user='repl_user',master_password='123456',master_log_file='binlog.000002',master_log_pos=1700;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.40.182
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1700
               Relay_Log_File: test-server-02-relay-bin.000002
                Relay_Log_Pos: 317
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

#已经成功变成了Slave,并且成功连接到了新的Master.

7.修改MHA配置

由于停掉了MySQL， /etc/masterha/app1.cnf配置里面默认删除一开始定义的[server1]，现在新增一下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[root@test-server-01 masterha]# cat /etc/masterha/app1.cnf
[server default]
manager_log=/etc/masterha/manager.log
manager_workdir=/etc/masterha/mha
master_binlog_dir=/opt/mysqldb/data
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
user=mha_user
password=123456
port=3306
ping_interval=1
remote_workdir=/etc/masterha/mha-node
repl_user=repl_user
repl_password=123456
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.40.182 -s 192.168.40.183
shutdown_script=""
ssh_user=root

[server1]
hostname=192.168.40.181
port=3306

[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.40.182
port=3306

[server3]
hostname=192.168.40.183
port=3306

8.MHA测试

1
2
3
4
5
6
7
8
9
10
11
1.Manager节点测试ssh免密
[root@test-server-01 ~]# masterha_check_ssh -conf=/etc/masterha/app1.cnf

Thu Feb 20 10:53:10 2025 - [info] All SSH connection tests passed successfully.
#最后以上输出以上内容代表正常

2.Manager节点测试MySQL主从状态
[root@test-server-01 ~]# masterha_check_repl -conf=/etc/masterha/app1.cnf

MySQL Replication Health is OK.
#最后以上输出以上内容代表正常

9.启动manager

1
2
#启动MHA
[root@test-server-01 ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/masterha/manager.log 2>&1 &

到这部署就成功了。

Thank you for your accept. mua！
-------------本文结束感谢您的阅读-------------