我们在 MYSQL高可用架构之MHA实战一数据库主从配置（真实可用）

上已经完成数据库主从的配置，接下来我们就要做MHA配置

一：配置SSH免密登录

MHA集群中的各节点彼此之间均需要基于ssh互信通信，以实现远程控制及数据管理功能。简单起见，可在Manager节点生成密钥对儿，并设置其可远程连接本地主机后，将私钥文件及 authorized_keys文件复制给余下的所有节点即可。四个服务器都需要执行如下脚本：

#生成公钥和私钥
ssh-keygen -t rsa
#将公钥复制到manager服务器的author-zed_keys文件中
ssh-copy-id -i /root/.ssh/id_rsa.pub root@manager

具体意思是：在/root/.ssh目录下，执行ssh-keygen -t rsa后，生成了两个文件：id_rsa.pub(公钥) 和 id_rsa(私钥)。

没有执行ssh-keyygen-t rsa

执行ssh-keyygen-t rsa

私钥文件放在本地机器，并且要保密。公钥要复制一份放到远程服务器上面（复制操作就是第二个命令）。

第二个命令，执行得结果是公钥复制到了 /root/.ssh/authorized_keys 文件中，改文件和公钥私钥默认都是保存在 .ssh文件夹中。

1.1 manager 生成公私钥

在manager中（192.168.0.1）

首先执行：生成公钥和私钥
ssh-keygen -t rsa

接着：将公钥复制到manager服务器的author-zed_keys文件中
ssh-copy-id -i /root/.ssh/id_rsa.pub root@manager

执行返回的信息是：

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed:
"/root/.ssh/id_rsa.pub"
The authenticity of host 'manager (172.30.11.33)' can't be established.
ECDSA key fingerprint is SHA256:ET5KsaZKOQvsYxYxJk3AHp8yZryMMlZMw5mfFEAI6AA.
ECDSA key fingerprint is MD5:4c:3c:d4:0a:d5:5d:2d:9e:e4:1f:04:14:36:97:06:e9.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter
out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted
now it is to install the new keys
root@manager's password:
Number of key(s) added: 1
Now try logging into the machine, with: "ssh 'root@manager'"
and check to make sure that only the key(s) you wanted were added.

其中root@manager’s password是系统密码

1.2 master,slave1,slave2生成公私钥

参照1.1

1.3 公钥汇总

在 manager 上面分别执行如下命令：

将公钥集合复制到其他三个服务器中，默认是复制追加到authorized_keys文件中的，可以执行一个命令之后，查看manager服务器中该文件是否新增了公钥。

scp /root/.ssh/authorized_keys root@master:/root/.ssh/
scp /root/.ssh/authorized_keys root@slave1:/root/.ssh/
scp /root/.ssh/authorized_keys root@slave2:/root/.ssh/

结果如下：

[root@Server-0192aa31-7526-4b12-b2f7-86d147415b03 .ssh]# scp authorized_keys
root@master:~/.ssh/
root@master's password:
authorized_keys
100% 1654 588.2KB/s 00:00
[root@Server-0192aa31-7526-4b12-b2f7-86d147415b03 .ssh]# scp authorized_keys
root@salve1:~/.ssh/
ssh: Could not resolve hostname salve1: Name or service not known
lost connection
[root@Server-0192aa31-7526-4b12-b2f7-86d147415b03 .ssh]# scp authorized_keys
root@slave1:~/.ssh/
The authenticity of host 'slave1 (172.30.11.99)' can't be established.
ECDSA key fingerprint is SHA256:ET5KsaZKOQvsYxYxJk3AHp8yZryMMlZMw5mfFEAI6AA.
ECDSA key fingerprint is MD5:4c:3c:d4:0a:d5:5d:2d:9e:e4:1f:04:14:36:97:06:e9.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'slave1,172.30.11.99' (ECDSA) to the list of known
hosts.
root@slave1's password:
authorized_keys
100% 1654 697.1KB/s 00:00
[root@Server-0192aa31-7526-4b12-b2f7-86d147415b03 .ssh]# scp authorized_keys
root@slave2:~/.ssh/
The authenticity of host 'slave2 (172.30.11.240)' can't be established.
ECDSA key fingerprint is SHA256:dOyHFOpWdCfRY7ibGu08OZRvcNk9F6Kq1F4I8brK0HM.
ECDSA key fingerprint is MD5:0a:ac:fa:96:6d:6b:62:95:c0:81:2b:1b:84:c3:fb:6a.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'slave2,172.30.11.240' (ECDSA) to the list of known
hosts.
root@slave2's password:
authorized_keys
100% 1654 2.4MB/s 00:00
[root@Server-0192aa31-7526-4b12-b2f7-86d147415b03 .ssh]#

root@master’s password:

root@slave1’s password:

root@slave2’s password:

是系统密码

1.4 验证SSH连通性

验证一下：看看是否还需要输入密码才可以登录其他服务器，找个文件复制来复制去。

#在master 上面执行
[root@master mha]# cd test/
[root@whb2021test test]# scp -r root@192.168.0.3（从db）:/mnt/dgcsb/public/nginx ./
access_json.tar.gz
100% 13MB 45.6MB/s 00:00
access_json.log
100% 800MB 46.1MB/s 00:17
error.log
100% 50MB 54.7MB/s 00:00
nginx.pid
100% 6 2.5KB/s 00:00
access_json.log
67% 1598MB 132.3MB/s 00:05 ETA^CKilled by signal 2.

可以看到scp 无需输入密码了。在三个数据库服务器上面执行

两个服务器第一次互相访问的时候，会出现一个需要输入 “yes”的操作，后续就不会有了，所以需要全部执行下。

二：MHA安装

mha 版本：0.58.0

四个节点都需安装： mha4mysql-node-0.58-0.el7.centos.noarch.rpm

Manager 节点另需要安装： mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

下载地址：数据库+mha+mha+数据库高可用

执行语句如下：

yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

上传压缩包在统一位置： mkdir /mha/mha4mysql

manager 执行上面两个指令，其他服务器只需要执行第一个node的指令即可(manager服务器也需要安装mha的node安装包)。

manager 192.168.0.1 服务器上面执行如下：

yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

依次在 master slave1 slave2服务器上面执行命令

yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

2.1 mha 配置文件设置

2.1.1 文件配置

manager 服务器上

mkdir /etc/mha_master
vim /etc/mha_master/mha.cnf

的 /etc/mha/mha.cnf文件


[server default]
user=mharoot
password=123456
manager_workdir=/etc/mha_master/app1
manager_log=/etc/mha_master/manager.log
master_binlog_dir=/mha/mysql/public/mysql/data
client_bindir=/mha/mysql/public/mysql/bin
client_libdir=/mha/mysql/public/mysql/lib
remote_workdir=/mnt/mhadata
ssh_user=root
repl_user=slave
repl_password=123456
ping_interval=1
[server1]
hostname=192.168.0.2
ssh_port=22
port=3307
[server2]
hostname=192.168.0.3
ssh_port=22
port=3307
candidate_master=1
check_repl_delay=0
[server3]
hostname=192.168.0.4
ssh_port=22
port=3307

参数说明

由于参数说明比较多，我们总结了一些常用的。

manager_workdir=/var/log/masterha/app1.log              //设置manager的工作目录

manager_log=/var/log/masterha/app1/manager.log          //设置manager的日志

master_binlog_dir=/data/mysql                         //设置master 保存binlog的位置，以便MHA可以找到master的日志，我这里的也就是mysql的数据目录

master_ip_failover_script= /usr/local/bin/master_ip_failover    //设置自动failover时候的切换脚本

master_ip_online_change_script= /usr/local/bin/master_ip_online_change  //设置手动切换时候的切换脚本

password=123456         //设置mysql中root用户的密码，这个密码是前文中创建监控用户的那个密码

user=root               设置监控用户root

ping_interval=1         //设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railover

remote_workdir=/tmp     //设置远端mysql在发生切换时binlog的保存位置

repl_password=123456    //设置复制用户的密码

repl_user=repl          //设置复制环境中的复制用户名

report_script=/usr/local/send_report    //设置发生切换后发送的报警的脚本

secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02            
shutdown_script=""      //设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用）

candidate_master=1   //设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave

check_repl_delay=0   //默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间，通过设置

check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了

candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master

user=mharoot 是需要在数据库中重新创建一个账号给mha使用。

三个数据库都新建一个mha管理账户，授权后续会用到，

./mysql -h 127.0.0.1 -P 3307 -u root -p\123456
select host,user from mysql.user;
grant all on *.* to mharoot@'%' identified by '123456';
flush privileges;
select host,user from mysql.user;

2.1.2各节点SSH通信检测

检测各节点间 ssh 互信通信配置是否 ok,我们在 Manager 机器上输入下述命令来检测：

masterha_check_ssh -conf=/etc/mha_master/mha.cnf

ssh通的结果如下

Wed Sep  7 17:43:18 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Sep  7 17:43:18 2022 - [info] Reading application default configuration from /etc/mha_master/mha.cnf..
Wed Sep  7 17:43:18 2022 - [info] Reading server configuration from /etc/mha_master/mha.cnf..
Wed Sep  7 17:43:18 2022 - [info] Starting SSH connection tests..
Wed Sep  7 17:43:19 2022 - [debug] 
Wed Sep  7 17:43:18 2022 - [debug]  Connecting via SSH from root@主db(192.168.0.2)(主db(192.168.0.2):22) to root@从db(192.168.0.3)(从db(192.168.0.3):22)..
Wed Sep  7 17:43:18 2022 - [debug]   ok.
Wed Sep  7 17:43:18 2022 - [debug]  Connecting via SSH from root@主db(192.168.0.2)(主db(192.168.0.2):22) to root@从db2(192.168.0.4)(从db2(192.168.0.4):22)..
Wed Sep  7 17:43:18 2022 - [debug]   ok.
Wed Sep  7 17:43:19 2022 - [debug] 
Wed Sep  7 17:43:18 2022 - [debug]  Connecting via SSH from root@从db(192.168.0.3)(从db(192.168.0.3):22) to root@主db(192.168.0.2)(主db(192.168.0.2):22)..
Wed Sep  7 17:43:18 2022 - [debug]   ok.
Wed Sep  7 17:43:18 2022 - [debug]  Connecting via SSH from root@从db(192.168.0.3)(从db(192.168.0.3):22) to root@从db2(192.168.0.4)(从db2(192.168.0.4):22)..
Wed Sep  7 17:43:19 2022 - [debug]   ok.
Wed Sep  7 17:43:20 2022 - [debug] 
Wed Sep  7 17:43:19 2022 - [debug]  Connecting via SSH from root@从db2(192.168.0.4)(从db2(192.168.0.4):22) to root@主db(192.168.0.2)(主db(192.168.0.2):22)..
Wed Sep  7 17:43:19 2022 - [debug]   ok.
Wed Sep  7 17:43:19 2022 - [debug]  Connecting via SSH from root@从db2(192.168.0.4)(从db2(192.168.0.4):22) to root@从db(192.168.0.3)(从db(192.168.0.3):22)..
Wed Sep  7 17:43:19 2022 - [debug]   ok.
Wed Sep  7 17:43:20 2022 - [info] All SSH connection tests passed successfully.

ssh测试成功。

2.1.3Mysql复制集群连接配置检测

检查管理的MySQL复制集群的连接配置参数是否OK，manager服务器上执行命令

masterha_check_repl -conf=/etc/mha_master/mha.cnf

但是这个时候会出现一些问题。

比如会出现下面问题：

Checking if super_read_only is defined and turned on..install_driver(mysql)
failed: Can't load '/usr/lib64/perl5/vendor_perl/auto/DBD/mysql/mysql.so' for
module DBD::mysql: libmysqlclient.so.18: cannot open shared obj

原因：具体的错误已经在报错中提示，缺少相应的组件包：libmysqlclient.so.18: cannot open shared object file: No such file or directory at /usr/lib64/perl5/DynaLoader.pm line 190

也就是需要有libmysqlclient.so.18

下载地址是：

libmysqlclient.so.18

在master ,slave1 ,slave2上都要执行如下操作

1:把libmysqlclient.so.18放在/usr/lib/和/usr/lib64/下

记得要授权：

chmod -R 777 libmysqlclient.so.18

2：软链接

ln -s /mha/mysql/install/public/mysql/lib/libmysqlclient.so.20 /usr/lib/
ln -s /mha/mysql/install/public/mysql/lib/libmysqlclient.so /usr/lib64/
ln -s /mha/mysql/install/public/mysql/lib/libmysqlclient.so.20 /usr/lib64/

3：接着需要安装一个

ls -al /usr/lib64/perl5/vendor_perl/auto/DBD/mysql/mysql.so

yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-ParallelForkManager perl-CPAN -y

updatedb

在重新执行

masterha_check_repl -conf=/etc/mha_master/mha.cnf

成功结果如下：

Wed Sep  7 18:04:09 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Sep  7 18:04:09 2022 - [info] Reading application default configuration from /etc/mha_master/mha.cnf..
Wed Sep  7 18:04:09 2022 - [info] Reading server configuration from /etc/mha_master/mha.cnf..
Wed Sep  7 18:04:09 2022 - [info] MHA::MasterMonitor version 0.58.
Wed Sep  7 18:04:10 2022 - [info] GTID failover mode = 0
Wed Sep  7 18:04:10 2022 - [info] Dead Servers:
Wed Sep  7 18:04:10 2022 - [info] Alive Servers:
Wed Sep  7 18:04:10 2022 - [info]   主db(192.168.0.2)(主db(192.168.0.2):3307)
Wed Sep  7 18:04:10 2022 - [info]   从db(192.168.0.3)(从db(192.168.0.3):3307)
Wed Sep  7 18:04:10 2022 - [info]   从db2(192.168.0.4)(从db2(192.168.0.4):3307)
Wed Sep  7 18:04:10 2022 - [info] Alive Slaves:
Wed Sep  7 18:04:10 2022 - [info]   从db(192.168.0.3)(从db(192.168.0.3):3307)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Wed Sep  7 18:04:10 2022 - [info]     Replicating from 主db(192.168.0.2)(主db(192.168.0.2):3307)
Wed Sep  7 18:04:10 2022 - [info]     Primary candidate for the new Master (candidate_master is set)
Wed Sep  7 18:04:10 2022 - [info]   从db2(192.168.0.4)(从db2(192.168.0.4):3307)  Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Wed Sep  7 18:04:10 2022 - [info]     Replicating from 主db(192.168.0.2)(主db(192.168.0.2):3307)
Wed Sep  7 18:04:10 2022 - [info] Current Alive Master: 主db(192.168.0.2)(主db(192.168.0.2):3307)
Wed Sep  7 18:04:10 2022 - [info] Checking slave configurations..
Wed Sep  7 18:04:10 2022 - [info] Checking replication filtering settings..
Wed Sep  7 18:04:10 2022 - [info]  binlog_do_db= , binlog_ignore_db= 
Wed Sep  7 18:04:10 2022 - [info]  Replication filtering check ok.
Wed Sep  7 18:04:10 2022 - [info] GTID (with auto-pos) is not supported
Wed Sep  7 18:04:10 2022 - [info] Starting SSH connection tests..
Wed Sep  7 18:04:13 2022 - [info] All SSH connection tests passed successfully.
Wed Sep  7 18:04:13 2022 - [info] Checking MHA Node version..
Wed Sep  7 18:04:13 2022 - [info]  Version check ok.
Wed Sep  7 18:04:13 2022 - [info] Checking SSH publickey authentication settings on the current master..
Wed Sep  7 18:04:13 2022 - [info] HealthCheck: SSH to 主db(192.168.0.2) is reachable.
Wed Sep  7 18:04:14 2022 - [info] Master MHA Node version is 0.58.
Wed Sep  7 18:04:14 2022 - [info] Checking recovery script configurations on 主db(192.168.0.2)(主db(192.168.0.2):3307)..
Wed Sep  7 18:04:14 2022 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/mha/mysql/public/mysql/data --output_file=/mnt/mhadata/save_binary_logs_test --manager_version=0.58 --start_file=mysql-bin.000004 
Wed Sep  7 18:04:14 2022 - [info]   Connecting to root@主db(192.168.0.2)(主db(192.168.0.2):22).. 
  Creating /mnt/mhadata if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /mha/mysql/public/mysql/data, up to mysql-bin.000004
Wed Sep  7 18:04:14 2022 - [info] Binlog setting check done.
Wed Sep  7 18:04:14 2022 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Wed Sep  7 18:04:14 2022 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mharoot' --slave_host=从db(192.168.0.3) --slave_ip=从db(192.168.0.3) --slave_port=3307 --workdir=/mnt/mhadata --target_version=5.7.23-log --manager_version=0.58 --client_bindir=/mha/mysql/public/mysql/bin --client_libdir=/mha/mysql/public/mysql/lib --relay_log_info=/mha/mysql/public/mysql/data/relay-log.info  --relay_dir=/mha/mysql/public/mysql/data/  --slave_pass=xxx
Wed Sep  7 18:04:14 2022 - [info]   Connecting to root@从db(192.168.0.3)(从db(192.168.0.3):22).. 
  Checking slave recovery environment settings..
    Opening /mha/mysql/public/mysql/data/relay-log.info ... ok.
    Relay log found at /mha/mysql/public/mysql/data, up to relay-log.000002
    Temporary relay log file is /mha/mysql/public/mysql/data/relay-log.000002
    Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
    Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Wed Sep  7 18:04:14 2022 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mharoot' --slave_host=从db2(192.168.0.4) --slave_ip=从db2(192.168.0.4) --slave_port=3307 --workdir=/mnt/mhadata --target_version=5.7.23-log --manager_version=0.58 --client_bindir=/mha/mysql/public/mysql/bin --client_libdir=/mha/mysql/public/mysql/lib --relay_log_info=/mha/mysql/public/mysql/data/relay-log.info  --relay_dir=/mha/mysql/public/mysql/data/  --slave_pass=xxx
Wed Sep  7 18:04:14 2022 - [info]   Connecting to root@从db2(192.168.0.4)(从db2(192.168.0.4):22).. 
  Checking slave recovery environment settings..
    Opening /mha/mysql/public/mysql/data/relay-log.info ... ok.
    Relay log found at /mha/mysql/public/mysql/data, up to relay-log.000002
    Temporary relay log file is /mha/mysql/public/mysql/data/relay-log.000002
    Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
    Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Wed Sep  7 18:04:14 2022 - [info] Slaves settings check done.
Wed Sep  7 18:04:14 2022 - [info] 
主db(192.168.0.2)(主db(192.168.0.2):3307) (current master)
 +--从db(192.168.0.3)(从db(192.168.0.3):3307)
 +--从db2(192.168.0.4)(从db2(192.168.0.4):3307)

Wed Sep  7 18:04:14 2022 - [info] Checking replication health on 从db(192.168.0.3)..
Wed Sep  7 18:04:14 2022 - [info]  ok.
Wed Sep  7 18:04:14 2022 - [info] Checking replication health on 从db2(192.168.0.4)..
Wed Sep  7 18:04:14 2022 - [info]  ok.
Wed Sep  7 18:04:14 2022 - [warning] master_ip_failover_script is not defined.
Wed Sep  7 18:04:14 2022 - [warning] shutdown_script is not defined.
Wed Sep  7 18:04:14 2022 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

2.1.4 启动mha

在manager中执行查询mha状态

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

结果是：

mha is stopped(2:NOT_RUNNING).

说明mha是没有启动的。

这个时候我们就要启动mha

nohup masterha_manager -conf=/etc/mha_master/mha.cnf > /etc/mha_master/manager.log 2>&1 &

再查询启动状态。则变成了

mha (pid:21600) is running(0:PING_OK), master:192.168.0.2

这样mha就搭建好了。

2.2 验证MHA

2.2.1:验证主从同步

1：在 master db（192.168.0.2）中表中添加一条数据，

2 ：在slave1 和slave2 中就能看到数据同步过来了。

2.2.2：关闭master 数据，验证主从迁移了。

master db 中kill -9 数据库

接下来在slave1和slave2中执行如下代码

cd /mha/mysql/install/public/mysql/bin

./mysql -h 127.0.0.1 -P 3307 -u root -p\123456

show slave status\G

我们会看到某个slave1或者slave2中某个从节点变成了主节点（比如从节点slave1 也就是db2变成主节点了）。

    Slave_IO_State: Waiting for master to send event
                  Master_Host: db2(192.168.0.3)
                  Master_User: slave
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: master-log.000003
          Read_Master_Log_Pos: 1367
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-log.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
.....................

然后在变成主节点得从节点中新增数据，看另外一个数据库是否添加了。如果添加了，就说明成功了。

故障转移完成后， manager将会自动停止此时使用 masterha_check_status 命令检测将会遇到错误提示，执行如下：

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

返回信息就是:

mha is stopped(2:NOT_RUNNING).

2.2.3恢复故障节点

原本的 slave1 已经成为了新的主节点，所以，我们对其进行完全备份，而后把备份的数据发送到我们新添加的机器上：

首先在新主节点slave1备份下数据

[root@mysql bin]# ./mysqldump -h 127.0.0.1 -P 3307 -uroot -p --all-databases >
/mha/mysql/backup/slave1-myql-all.sql
#后续会要求你输入密码 ，Enter password:
Enter password:
[root@mysql bin]# cd /mha/mysql/backup/
[root@mysql backup]# ls -l
total 788
-rw-r--r-- 1 root root 804072 Sep  9 09:46 slave1-myql-all.sql

将slave1(现在的master)数据库备份文件复制到之前的master中的相同文件夹中

在原来的master中执行如下：

cd /mha/mysql/backup

scp -r root@slave1:/mha/mysql/backup/slave1-myql-all.sql ./

启动 master节点，之后会作为从节点使用

启动后导入备份数据，代码如下

cd /mha/mysql/install/public/mysql/bin

./mysql -h 127.0.0.1 -P 3307 -u root -p\123456 </mha/mysql/backup/slave1-myql-all.sql

返回的信息是这样

mysql: [Warning] Using a password on the command line interface can be insecure.

这样数据就同步到原来的master上了。

接下来就要重新设置原来的master为从节点。

在新主节点中（slave1）查看最新数据日志

show master STATUS

发现现在变成了。

日志文件 master-log.000003 列为：1624

则在老master 中执行如下代码

cd /mha/mysql/install/public/mysql/bin



change master to master_host='db1(新master)',master_port=3307,master_user='slave',master_password='123456',master_log_file='master-log.000003',master_log_pos=1624;

start slave;

show slave status\G

发现老master变成了新节点了。

   Slave_IO_State: Waiting for master to send event
                  Master_Host: 新master(slave1)
                  Master_User: slave
                  Master_Port: 3309
                Connect_Retry: 60
              Master_Log_File: master-log.000003
          Read_Master_Log_Pos: 1624
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-log.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table:

这样完全验证了mha主从高可用了。

参考：实验详解MHA搭建过程中的遇到的各种问题

原文链接：https://blog.csdn.net/wszhm123/article/details/126749187

我们在 MYSQL高可用架构之MHA实战一 数据库主从配置（真实可用）