• MYSQL高可用架构之MHA实战二 安装和配置MHA架构（真实可用）

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

     

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

    一：配置SSH免密登录

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

    1. #生成公钥和私钥
    2. ssh-keygen -t rsa
    3. #将公钥复制到manager服务器的author-zed_keys文件中
    4. 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

    执行返回的信息是：

    1. /usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed:
    2. "/root/.ssh/id_rsa.pub"
    3. The authenticity of host 'manager (172.30.11.33)' can't be established.
    4. ECDSA key fingerprint is SHA256:ET5KsaZKOQvsYxYxJk3AHp8yZryMMlZMw5mfFEAI6AA.
    5. ECDSA key fingerprint is MD5:4c:3c:d4:0a:d5:5d:2d:9e:e4:1f:04:14:36:97:06:e9.
    6. Are you sure you want to continue connecting (yes/no)? yes
    7. /usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter
    8. out any that are already installed
    9. /usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted
    10. now it is to install the new keys
    11. root@manager's password:
    12. Number of key(s) added: 1
    13. Now try logging into the machine, with: "ssh 'root@manager'"
    14. 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服务器中该文件是否新增了公钥。

    1. scp /root/.ssh/authorized_keys root@master:/root/.ssh/
    2. scp /root/.ssh/authorized_keys root@slave1:/root/.ssh/
    3. scp /root/.ssh/authorized_keys root@slave2:/root/.ssh/

    结果如下：

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

    root@master's password:

    root@slave1's password:

    root@slave2's password:

    是系统密码

    1.4 验证SSH连通性

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

    1. #在master 上面执行
    2. [root@master mha]# cd test/
    3. [root@whb2021test test]# scp -r root@192.168.0.3（从db）:/mnt/dgcsb/public/nginx ./
    4. access_json.tar.gz
    5. 100% 13MB 45.6MB/s 00:00
    6. access_json.log
    7. 100% 800MB 46.1MB/s 00:17
    8. error.log
    9. 100% 50MB 54.7MB/s 00:00
    10. nginx.pid
    11. 100% 6 2.5KB/s 00:00
    12. access_json.log
    13. 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+数据库高可用

    执行语句如下：

    1. yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
    2. 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  服务器上面执行如下：

    1. yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm
    2. 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 服务器 上

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

    的 /etc/mha/mha.cnf文件

    1.  
    2. [server default]
    3. user=mharoot
    4. password=123456
    5. manager_workdir=/etc/mha_master/app1
    6. manager_log=/etc/mha_master/manager.log
    7. master_binlog_dir=/mha/mysql/public/mysql/data
    8. client_bindir=/mha/mysql/public/mysql/bin
    9. client_libdir=/mha/mysql/public/mysql/lib
    10. remote_workdir=/mnt/mhadata
    11. ssh_user=root
    12. repl_user=slave
    13. repl_password=123456
    14. ping_interval=1
    15. [server1]
    16. hostname=192.168.0.2
    17. ssh_port=22
    18. port=3307
    19. [server2]
    20. hostname=192.168.0.3
    21. ssh_port=22
    22. port=3307
    23. candidate_master=1
    24. check_repl_delay=0
    25. [server3]
    26. hostname=192.168.0.4
    27. ssh_port=22
    28. port=3307

    参数说明

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

    1. manager_workdir=/var/log/masterha/app1.log              //设置manager的工作目录
    2.  
    3. manager_log=/var/log/masterha/app1/manager.log          //设置manager的日志
    4.  
    5. master_binlog_dir=/data/mysql                         //设置master 保存binlog的位置，以便MHA可以找到master的日志，我这里的也就是mysql的数据目录
    6.  
    7. master_ip_failover_script= /usr/local/bin/master_ip_failover    //设置自动failover时候的切换脚本
    8.  
    9. master_ip_online_change_script= /usr/local/bin/master_ip_online_change  //设置手动切换时候的切换脚本
    10.  
    11. password=123456         //设置mysql中root用户的密码，这个密码是前文中创建监控用户的那个密码
    12.  
    13. user=root               设置监控用户root
    14.  
    15. ping_interval=1         //设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行railover
    16.  
    17. remote_workdir=/tmp     //设置远端mysql在发生切换时binlog的保存位置
    18.  
    19. repl_password=123456    //设置复制用户的密码
    20.  
    21. repl_user=repl          //设置复制环境中的复制用户名
    22.  
    23. report_script=/usr/local/send_report    //设置发生切换后发送的报警的脚本
    24.  
    25. secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02            
    26. shutdown_script=""      //设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用）
    27.  
    28. candidate_master=1   //设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave
    29.  
    30. check_repl_delay=0   //默认情况下如果一个slave落后master 100M的relay logs的话，MHA将不会选择该slave作为一个新的master，因为对于这个slave的恢复需要花费很长时间，通过设置
    31.  
    32. check_repl_delay=0,MHA触发切换在选择一个新的master的时候将会忽略复制延时，这个参数对于设置了
    33.  
    34. candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master

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

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

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

    2.1.2各节点SSH通信检测

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

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

    ssh通的结果如下

    1. Wed Sep  7 17:43:18 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    2. Wed Sep  7 17:43:18 2022 - [info] Reading application default configuration from /etc/mha_master/mha.cnf..
    3. Wed Sep  7 17:43:18 2022 - [info] Reading server configuration from /etc/mha_master/mha.cnf..
    4. Wed Sep  7 17:43:18 2022 - [info] Starting SSH connection tests..
    5. Wed Sep  7 17:43:19 2022 - [debug] 
    6. 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)..
    7. Wed Sep  7 17:43:18 2022 - [debug]   ok.
    8. 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)..
    9. Wed Sep  7 17:43:18 2022 - [debug]   ok.
    10. Wed Sep  7 17:43:19 2022 - [debug] 
    11. 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)..
    12. Wed Sep  7 17:43:18 2022 - [debug]   ok.
    13. 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)..
    14. Wed Sep  7 17:43:19 2022 - [debug]   ok.
    15. Wed Sep  7 17:43:20 2022 - [debug] 
    16. 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)..
    17. Wed Sep  7 17:43:19 2022 - [debug]   ok.
    18. 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)..
    19. Wed Sep  7 17:43:19 2022 - [debug]   ok.
    20. 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

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

    比如会出现下面问题：

    1. Checking if super_read_only is defined and turned on..install_driver(mysql)
    2. failed: Can't load '/usr/lib64/perl5/vendor_perl/auto/DBD/mysql/mysql.so' for
    3. 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：软链接

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

        3：接着需要安装一个

    1. ls -al /usr/lib64/perl5/vendor_perl/auto/DBD/mysql/mysql.so
    2.  
    3. yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-ParallelForkManager perl-CPAN -y
    4.  
    5. updatedb

    在重新执行

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

    成功结果如下：

    1. Wed Sep  7 18:04:09 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
    2. Wed Sep  7 18:04:09 2022 - [info] Reading application default configuration from /etc/mha_master/mha.cnf..
    3. Wed Sep  7 18:04:09 2022 - [info] Reading server configuration from /etc/mha_master/mha.cnf..
    4. Wed Sep  7 18:04:09 2022 - [info] MHA::MasterMonitor version 0.58.
    5. Wed Sep  7 18:04:10 2022 - [info] GTID failover mode = 0
    6. Wed Sep  7 18:04:10 2022 - [info] Dead Servers:
    7. Wed Sep  7 18:04:10 2022 - [info] Alive Servers:
    8. Wed Sep  7 18:04:10 2022 - [info]   主db(192.168.0.2)(主db(192.168.0.2):3307)
    9. Wed Sep  7 18:04:10 2022 - [info]   从db(192.168.0.3)(从db(192.168.0.3):3307)
    10. Wed Sep  7 18:04:10 2022 - [info]   从db2(192.168.0.4)(从db2(192.168.0.4):3307)
    11. Wed Sep  7 18:04:10 2022 - [info] Alive Slaves:
    12. 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
    13. Wed Sep  7 18:04:10 2022 - [info]     Replicating from 主db(192.168.0.2)(主db(192.168.0.2):3307)
    14. Wed Sep  7 18:04:10 2022 - [info]     Primary candidate for the new Master (candidate_master is set)
    15. 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
    16. Wed Sep  7 18:04:10 2022 - [info]     Replicating from 主db(192.168.0.2)(主db(192.168.0.2):3307)
    17. Wed Sep  7 18:04:10 2022 - [info] Current Alive Master: 主db(192.168.0.2)(主db(192.168.0.2):3307)
    18. Wed Sep  7 18:04:10 2022 - [info] Checking slave configurations..
    19. Wed Sep  7 18:04:10 2022 - [info] Checking replication filtering settings..
    20. Wed Sep  7 18:04:10 2022 - [info]  binlog_do_db= , binlog_ignore_db= 
    21. Wed Sep  7 18:04:10 2022 - [info]  Replication filtering check ok.
    22. Wed Sep  7 18:04:10 2022 - [info] GTID (with auto-pos) is not supported
    23. Wed Sep  7 18:04:10 2022 - [info] Starting SSH connection tests..
    24. Wed Sep  7 18:04:13 2022 - [info] All SSH connection tests passed successfully.
    25. Wed Sep  7 18:04:13 2022 - [info] Checking MHA Node version..
    26. Wed Sep  7 18:04:13 2022 - [info]  Version check ok.
    27. Wed Sep  7 18:04:13 2022 - [info] Checking SSH publickey authentication settings on the current master..
    28. Wed Sep  7 18:04:13 2022 - [info] HealthCheck: SSH to 主db(192.168.0.2) is reachable.
    29. Wed Sep  7 18:04:14 2022 - [info] Master MHA Node version is 0.58.
    30. Wed Sep  7 18:04:14 2022 - [info] Checking recovery script configurations on 主db(192.168.0.2)(主db(192.168.0.2):3307)..
    31. 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 
    32. Wed Sep  7 18:04:14 2022 - [info]   Connecting to root@主db(192.168.0.2)(主db(192.168.0.2):22).. 
    33.   Creating /mnt/mhadata if not exists..    ok.
    34.   Checking output directory is accessible or not..
    35.    ok.
    36.   Binlog found at /mha/mysql/public/mysql/data, up to mysql-bin.000004
    37. Wed Sep  7 18:04:14 2022 - [info] Binlog setting check done.
    38. Wed Sep  7 18:04:14 2022 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
    39. 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
    40. Wed Sep  7 18:04:14 2022 - [info]   Connecting to root@从db(192.168.0.3)(从db(192.168.0.3):22).. 
    41.   Checking slave recovery environment settings..
    42.     Opening /mha/mysql/public/mysql/data/relay-log.info ... ok.
    43.     Relay log found at /mha/mysql/public/mysql/data, up to relay-log.000002
    44.     Temporary relay log file is /mha/mysql/public/mysql/data/relay-log.000002
    45.     Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
    46.     Testing mysql connection and privileges..
    47. mysql: [Warning] Using a password on the command line interface can be insecure.
    48.  done.
    49.     Testing mysqlbinlog output.. done.
    50.     Cleaning up test file(s).. done.
    51. 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
    52. Wed Sep  7 18:04:14 2022 - [info]   Connecting to root@从db2(192.168.0.4)(从db2(192.168.0.4):22).. 
    53.   Checking slave recovery environment settings..
    54.     Opening /mha/mysql/public/mysql/data/relay-log.info ... ok.
    55.     Relay log found at /mha/mysql/public/mysql/data, up to relay-log.000002
    56.     Temporary relay log file is /mha/mysql/public/mysql/data/relay-log.000002
    57.     Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
    58.     Testing mysql connection and privileges..
    59. mysql: [Warning] Using a password on the command line interface can be insecure.
    60.  done.
    61.     Testing mysqlbinlog output.. done.
    62.     Cleaning up test file(s).. done.
    63. Wed Sep  7 18:04:14 2022 - [info] Slaves settings check done.
    64. Wed Sep  7 18:04:14 2022 - [info] 
    65. 主db(192.168.0.2)(主db(192.168.0.2):3307) (current master)
    66.  +--从db(192.168.0.3)(从db(192.168.0.3):3307)
    67.  +--从db2(192.168.0.4)(从db2(192.168.0.4):3307)
    68.  
    69. Wed Sep  7 18:04:14 2022 - [info] Checking replication health on 从db(192.168.0.3)..
    70. Wed Sep  7 18:04:14 2022 - [info]  ok.
    71. Wed Sep  7 18:04:14 2022 - [info] Checking replication health on 从db2(192.168.0.4)..
    72. Wed Sep  7 18:04:14 2022 - [info]  ok.
    73. Wed Sep  7 18:04:14 2022 - [warning] master_ip_failover_script is not defined.
    74. Wed Sep  7 18:04:14 2022 - [warning] shutdown_script is not defined.
    75. Wed Sep  7 18:04:14 2022 - [info] Got exit code 0 (Not master dead).
    76.  
    77. 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中执行如下代码

    1. cd /mha/mysql/install/public/mysql/bin
    2.  
    3. ./mysql -h 127.0.0.1 -P 3307 -u root -p\123456
    4.  
    5. show slave status\G

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

    1.     Slave_IO_State: Waiting for master to send event
    2.                   Master_Host: db2(192.168.0.3)
    3.                   Master_User: slave
    4.                   Master_Port: 3307
    5.                 Connect_Retry: 60
    6.               Master_Log_File: master-log.000003
    7.           Read_Master_Log_Pos: 1367
    8.                Relay_Log_File: relay-log.000002
    9.                 Relay_Log_Pos: 321
    10.         Relay_Master_Log_File: master-log.000003
    11.              Slave_IO_Running: Yes
    12.             Slave_SQL_Running: Yes
    13. .....................

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

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

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

    返回信息就是:

    mha is stopped(2:NOT_RUNNING).

    2.2.3恢复故障节点

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

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

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

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

    在原来的master中执行如下：

    1. cd /mha/mysql/backup
    2.  
    3. scp -r root@slave1:/mha/mysql/backup/slave1-myql-all.sql ./

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

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

    1. cd /mha/mysql/install/public/mysql/bin
    2.  
    3. ./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 中执行如下代码

    1. cd /mha/mysql/install/public/mysql/bin
    2.  
    3.  
    4.  
    5. 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;
    6.  
    7. start slave;
    8.  
    9. show slave status\G
    10.  
    11.

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

    1.    Slave_IO_State: Waiting for master to send event
    2.                   Master_Host: 新master(slave1)
    3.                   Master_User: slave
    4.                   Master_Port: 3309
    5.                 Connect_Retry: 60
    6.               Master_Log_File: master-log.000003
    7.           Read_Master_Log_Pos: 1624
    8.                Relay_Log_File: mysql-relay-bin.000002
    9.                 Relay_Log_Pos: 321
    10.         Relay_Master_Log_File: master-log.000003
    11.              Slave_IO_Running: Yes
    12.             Slave_SQL_Running: Yes
    13.               Replicate_Do_DB: 
    14.           Replicate_Ignore_DB: 
    15.            Replicate_Do_Table:

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

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

  • 相关阅读:
    redis哨兵机制
    elasticsearch搜索IK分词器实现单个字搜索
    一分钟带你了解网页升级访问原因
    【RabbitMQ实战】05 RabbitMQ后台管理
    虹科分享|如何保障医疗数据安全？移动目标防御技术给你满意的答案
    异步FIFO设计
    网络安全（黑客）自学
    Java中如何对Set进行遍历呢？
    01背包和完全背包区别
    ONLYOFFICE 8.1版本桌面编辑器测评
  • 原文地址：https://blog.csdn.net/wszhm123/article/details/126749187