MySQL/MariaDB数据库的MHA实现高可用实战
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.MySQL高可用常见的解决方案
1>.Multi-Master Replication Manager for MySQL(简称MMM)
Mysql主主复制管理器是一套灵活的脚本程序，基于perl实现，用来对mysql replication进行监控和故障迁移，并能管理mysql Master-Master复制的配置(同一时间只有一个节点是可写的)
博主推荐阅读:
http://www.mysql-mmm.org https://code.google.com/archive/p/mysql-master-master/downloads
2>.Master High Availability(简称MHA)
对主节点进行监控，可实现自动故障转移至其它从节点；通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库，出于机器成本的考虑，淘宝进行了改造，目前淘宝TMHA已经支持一主一从
博主推荐阅读:
https://code.google.com/archive/p/mysql-master-ha/
3>.MySQL extended with the Write Set Replication(简称Galera Cluster：wsrep)
通过wsrep协议在全局实现复制；任何一节点都可读写，不需要主从复制，实现多主读写
4>.Group Replication(简称GR)
MySQL官方提供的组复制技术(MySQL 5.7.17引入的技术)，基于原生复制技术Paxos算法
二.MHA概述
1>.MHA集群架构
如下图所示:
MHA是基于perl语言开发的,它需要单独部署到一台服务器上,我们称之为manager。
MHA可用监控多组主从复制集群(建议使用半同步复制),当任何一组中的master节点宕机，可用迅速该组中的slave节点提升为master，这个过程是自动切换的,无需我们手动切换。
2>.MHA选择master过程
(1)从宕机崩溃的master保存二进制日志事件（binlog events） (2)识别含有最新更新的slave (3)应用差异的中继日志（relay log）到其他的slave (4)应用从master保存的二进制日志事件（binlog events） (5)提升一个slave为新的master (6)使其他的slave连接新的master进行复制
3>.MHA的组件
MHA软件由两部分组成，Manager工具包和Node工具包。 Manager工具包主要包括以下几个工具： masterha_check_ssh:
检查MHA的SSH配置状况 masterha_check_repl:
检查MySQL复制状况 masterha_manger:
启动MHA masterha_check_status:
检测当前MHA运行状态 masterha_master_monitor:
检测master是否宕机 masterha_master_switch:
故障转移（自动或手动） masterha_conf_host:
添加或删除配置的server信息 Node工具包：这些工具通常由MHA Manager的脚本触发，无需人为操作）主要包括以下几个工具： save_binary_logs:
保存和复制master的二进制日志 apply_diff_relay_logs:
识别差异的中继日志事件并将其差异的事件应用于其他的slave filter_mysqlbinlog:
去除不必要的ROLLBACK事件（MHA已不再使用此工具） purge_relay_logs:
清除中继日志（不会阻塞SQL线程）
注意：
为了尽可能的减少主库硬件损坏宕机造成的数据丢失，因此在配置MHA的同时建议配置成MySQL 5.5版本及以上的半同步复制
4>.自定义扩展
secondary_check_script：
通过多条网络路由检测master的可用性
master_ip_ailover_script：
更新Application使用的masterip
shutdown_script：
强制关闭master节点
report_script：
发送报告
init_conf_load_script：
加载初始配置参数
master_ip_online_change_script：
更新master节点ip地址
5>.配置文件
global配置:
为各application提供默认配置
application配置:
为每个主从复制集群
三.MHA部署实战
1>.搭建半同步复制
博主推荐阅读: https://www.cnblogs.com/yinzhengjie/p/11828475.html 试验环境说明(搭建步骤如上所述,配置文件需要稍作修改): node102.yinzhengjie.org.cn: master节点 node103.yinzhengjie.org.cn: slave节点 node104.yinzhengjie.org.cn: MHA节点
[root@node102.yinzhengjie.org.cn ~]# cat /etc/my.cnf [mysqld] server-id = 102 binlog_format = row log_bin = /data/mysql/logbin/master-102 skip_name_resolve = 1 #跳过名称反向解析，建议生产环境也开启它！ character-set-server = utf8mb4 default_storage_engine = InnoDB datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock [mysqld_safe] log-error = /var/log/mariadb/mariadb.log pid-file = /var/run/mariadb/mariadb.pid !includedir /etc/my.cnf.d [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf [mysqld] server-id = 103 binlog_format = row read-only = on #大家是否有疑问该参数是否应该使用默认的OFF呢？没错，的确是应该为OFF，但是我们这里设置为ON也没有关系，当MHA将它提升为主时会自动将其关闭！我们一会通过试验来验证一下~ relay_log = relay-log-103 relay_log_index = relay-log-103.index log_bin = /data/mysql/logbin/master-103 #既然slave将来可能要被提升为master,因此我们务必要开启二进制功能哟 skip_name_resolve = 1 #跳过名称解析，生产环境建议开启 relay_log_purge = 0 #不清空中继日志，如果默认情况 relay_log_purge=1 时，SQL线程就会自动将之前的relay log全部删除。而当relay_log_purge=0 时，旧的relay log则会被保留。 character-set-server = utf8mb4 default_storage_engine = InnoDB datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock [mysqld_safe] log-error = /var/log/mariadb/mariadb.log pid-file = /var/run/mariadb/mariadb.pid !includedir /etc/my.cnf.d [root@node103.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 4 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SELECT user,host,password FROM mysql.user; +---------+----------------------------+-------------------------------------------+ | user | host | password | +---------+----------------------------+-------------------------------------------+ | root | localhost | | | root | node102.yinzhengjie.org.cn | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | node102.yinzhengjie.org.cn | | | copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | monitor | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | sqluser | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +---------+----------------------------+-------------------------------------------+ 9 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> GRANT ALL ON *.* TO mhauser@'172.30.1.10%' IDENTIFIED BY 'yinzhengjie'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SELECT user,host,password FROM mysql.user; +---------+----------------------------+-------------------------------------------+ | user | host | password | +---------+----------------------------+-------------------------------------------+ | root | localhost | | | root | node102.yinzhengjie.org.cn | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | node102.yinzhengjie.org.cn | | | copy | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | monitor | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | sqluser | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | mhauser | 172.30.1.10% | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +---------+----------------------------+-------------------------------------------+ 10 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> QUIT Bye [root@node102.yinzhengjie.org.cn ~]#
2>.同步集群时间(凡是涉及到集群部署的服务，建议第一步配置ntpd服务)
[root@node104.yinzhengjie.org.cn ~]# yum -y install ntp Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.zju.edu.cn * extras: mirrors.163.com * updates: mirrors.163.com base | 3.6 kB 00:00:00 extras | 2.9 kB 00:00:00 proxysql_repo | 2.9 kB 00:00:00 updates | 2.9 kB 00:00:00 Resolving Dependencies --> Running transaction check ---> Package ntp.x86_64 0:4.2.6p5-28.el7.centos will be updated ---> Package ntp.x86_64 0:4.2.6p5-29.el7.centos will be an update --> Processing Dependency: ntpdate = 4.2.6p5-29.el7.centos for package: ntp-4.2.6p5-29.el7.centos.x86_64 --> Running transaction check ---> Package ntpdate.x86_64 0:4.2.6p5-28.el7.centos will be updated ---> Package ntpdate.x86_64 0:4.2.6p5-29.el7.centos will be an update --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================================= Package Arch Version Repository Size ================================================================================================================================================= Updating: ntp x86_64 4.2.6p5-29.el7.centos base 548 k Updating for dependencies: ntpdate x86_64 4.2.6p5-29.el7.centos base 86 k Transaction Summary ================================================================================================================================================= Upgrade 1 Package (+1 Dependent package) Total download size: 635 k Downloading packages: Delta RPMs disabled because /usr/bin/applydeltarpm not installed. (1/2): ntpdate-4.2.6p5-29.el7.centos.x86_64.rpm | 86 kB 00:00:00 (2/2): ntp-4.2.6p5-29.el7.centos.x86_64.rpm | 548 kB 00:00:00 ------------------------------------------------------------------------------------------------------------------------------------------------- Total 2.3 MB/s | 635 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Updating : ntpdate-4.2.6p5-29.el7.centos.x86_64 1/4 Updating : ntp-4.2.6p5-29.el7.centos.x86_64 2/4 Cleanup : ntp-4.2.6p5-28.el7.centos.x86_64 3/4 Cleanup : ntpdate-4.2.6p5-28.el7.centos.x86_64 4/4 Verifying : ntp-4.2.6p5-29.el7.centos.x86_64 1/4 Verifying : ntpdate-4.2.6p5-29.el7.centos.x86_64 2/4 Verifying : ntpdate-4.2.6p5-28.el7.centos.x86_64 3/4 Verifying : ntp-4.2.6p5-28.el7.centos.x86_64 4/4 Updated: ntp.x86_64 0:4.2.6p5-29.el7.centos Dependency Updated: ntpdate.x86_64 0:4.2.6p5-29.el7.centos Complete! [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# sed -i 's@#restrict@restrict@' /etc/ntp.conf [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# sed -i 's#192.168.1.0#172.30.1.0#' /etc/ntp.conf [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# sed -i 's@^server@#server@' /etc/ntp.conf [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# echo "#Add by yinzhengjie" >> /etc/ntp.conf [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# echo "server 127.127.1.0" >> /etc/ntp.conf [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# echo "fudge 127.127.1.0 stratum 10" >> /etc/ntp.conf [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# echo SYNC_HWCLOCK=yes >> /etc/sysconfig/ntpd [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# systemctl enable ntpd Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service. [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# systemctl start ntpd #我们将该节点作为ntpd服务器 [root@node104.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# crontab -l */1 * * * * /usr/sbin/ntpdate node104.yinzhengjie.org.cn [root@node102.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# crontab -l */1 * * * * /usr/sbin/ntpdate node104.yinzhengjie.org.cn [root@node103.yinzhengjie.org.cn ~]#
3>.在所有节点实现相互之间ssh key验证
[root@node104.yinzhengjie.org.cn ~]# ssh-keygen -t rsa -P '' -f ~/.ssh/id_rsa Generating public/private rsa key pair. Your identification has been saved in /root/.ssh/id_rsa. Your public key has been saved in /root/.ssh/id_rsa.pub. The key fingerprint is: SHA256:0Z9W+Tcm9JZubmusNmNPcDqcXmDidatAyki30ei6UtM root@node104.yinzhengjie.org.cn The key's randomart image is: +---[RSA 2048]----+ | | | . . | | . . .o | | .o..o...| | .S+ ++* Oo| | .o=E*.= % +| | ...= o =.* | | . . o=B+ | | .o. o+B+.| +----[SHA256]-----+ [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# ssh-copy-id 172.30.1.104 /usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub" The authenticity of host '172.30.1.104 (172.30.1.104)' can't be established. ECDSA key fingerprint is SHA256:F3IVf82keybIystuO6PYRfwr0o5dTftrmAHJWzqO4IA. ECDSA key fingerprint is MD5:02:5d:d8:0a:4a:b4:70:0f:61:be:2c:97:56:db:24:e7. 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@172.30.1.104's password: Number of key(s) added: 1 Now try logging into the machine, with: "ssh '172.30.1.104'" and check to make sure that only the key(s) you wanted were added. [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# cat ~/.ssh/authorized_keys ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDGyN4MPfwSHf1jHHGoKYnIfrHtdmZUVaxh/uC/f872v1ZsQb1GH1P+VTWN35xEgHkPvqBDO3iqzU4SIhcu9bkfzpkSlrvr3m/3vLx9Zxpxv Nu++VdISIU6RQ+dtiUHeaF9vOSG7JzDIjd+2duruIyxwVZ40Ldwt13QXw83vYDuG5jnR7eYrnW4yV6lVs8UifxXLYOXvdKXX3xF8TIcT2j3VJESkOzT1XtHaCyVjVfRot9RRSDXP0mATPJUvny/1ptOYhTqJETNdTGA2yM5IOy20gjQ7PStTVIJ4PQ4V4MtiVoNjj3pecUX84Kii0tAdsr5fwcRII1GYRs7H03BgK9J root@node104.yinzhengjie.org.cn[root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# scp -rp /root/.ssh 172.30.1.102:/root/ The authenticity of host '172.30.1.102 (172.30.1.102)' can't be established. ECDSA key fingerprint is SHA256:ol1jiTSkfqSxf3SNjS0ACh+P53IUItM5HIgn2gkPj5Q. ECDSA key fingerprint is MD5:2e:44:d3:4e:9c:b2:9b:02:49:b4:71:12:e1:6d:3f:b7. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '172.30.1.102' (ECDSA) to the list of known hosts. root@172.30.1.102's password: id_rsa 100% 1675 2.5MB/s 00:00 id_rsa.pub 100% 413 843.3KB/s 00:00 known_hosts 100% 348 645.4KB/s 00:00 authorized_keys 100% 413 857.9KB/s 00:00 [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# scp -rp /root/.ssh 172.30.1.103:/root/ The authenticity of host '172.30.1.103 (172.30.1.103)' can't be established. ECDSA key fingerprint is SHA256:auvfSu0iwTr185exd1pfdhwkXf9wkA8VTXwYx8JrM1c. ECDSA key fingerprint is MD5:ae:6b:0f:b6:0c:05:bb:b8:a4:fe:02:dc:70:42:4f:1e. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '172.30.1.103' (ECDSA) to the list of known hosts. root@172.30.1.103's password: id_rsa 100% 1675 2.0MB/s 00:00 id_rsa.pub 100% 413 568.5KB/s 00:00 known_hosts 100% 522 848.6KB/s 00:00 authorized_keys 100% 413 1.1MB/s 00:00 [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# ssh 172.30.1.102 Last login: Tue Nov 12 07:57:26 2019 from 172.30.1.104 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ssh 172.30.1.104 Last login: Tue Nov 12 07:58:28 2019 from 172.30.1.102 [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# exit logout Connection to 172.30.1.104 closed. [root@node102.yinzhengjie.org.cn ~]# exit logout Connection to 172.30.1.102 closed. [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# ssh 172.30.1.103 Last login: Tue Nov 12 05:59:19 2019 from 172.30.1.254 [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# ssh 172.30.1.104 Last login: Tue Nov 12 07:58:44 2019 from 172.30.1.102 [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# exit logout Connection to 172.30.1.104 closed. [root@node103.yinzhengjie.org.cn ~]# exit logout Connection to 172.30.1.103 closed. [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]#
4>.安装MHA工具包
[root@node104.yinzhengjie.org.cn ~]# ll #下载MHA软件包，并将被管理节点软件包拷贝到MySQL集群 total 124 -rw-r--r-- 1 root root 87119 Oct 24 08:50 mha4mysql-manager-0.56-0.el6.noarch.rpm -rw-r--r-- 1 root root 36326 Oct 24 08:50 mha4mysql-node-0.56-0.el6.noarch.rpm [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# scp mha4mysql-node-0.56-0.el6.noarch.rpm 172.30.1.102:~ mha4mysql-node-0.56-0.el6.noarch.rpm 100% 35KB 27.0MB/s 00:00 [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# scp mha4mysql-node-0.56-0.el6.noarch.rpm 172.30.1.103:~ mha4mysql-node-0.56-0.el6.noarch.rpm 100% 35KB 26.4MB/s 00:00 [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# ll /etc/yum.repos.d/ total 36 -rw-r--r--. 1 root root 1664 Nov 23 2018 CentOS-Base.repo -rw-r--r--. 1 root root 1309 Nov 23 2018 CentOS-CR.repo -rw-r--r--. 1 root root 649 Nov 23 2018 CentOS-Debuginfo.repo -rw-r--r--. 1 root root 314 Nov 23 2018 CentOS-fasttrack.repo -rw-r--r--. 1 root root 630 Nov 23 2018 CentOS-Media.repo -rw-r--r--. 1 root root 1331 Nov 23 2018 CentOS-Sources.repo -rw-r--r--. 1 root root 5701 Nov 23 2018 CentOS-Vault.repo -rw-r--r-- 1 root root 187 Nov 11 18:59 proxysql.repo [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# yum -y install epel-release Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.zju.edu.cn * extras: mirrors.zju.edu.cn * updates: mirrors.zju.edu.cn Resolving Dependencies --> Running transaction check ---> Package epel-release.noarch 0:7-11 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================================= Package Arch Version Repository Size ================================================================================================================================================= Installing: epel-release noarch 7-11 extras 15 k Transaction Summary ================================================================================================================================================= Install 1 Package Total download size: 15 k Installed size: 24 k Downloading packages: epel-release-7-11.noarch.rpm | 15 kB 00:00:01 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : epel-release-7-11.noarch 1/1 Verifying : epel-release-7-11.noarch 1/1 Installed: epel-release.noarch 0:7-11 Complete! [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# ll /etc/yum.repos.d/ total 44 -rw-r--r--. 1 root root 1664 Nov 23 2018 CentOS-Base.repo -rw-r--r--. 1 root root 1309 Nov 23 2018 CentOS-CR.repo -rw-r--r--. 1 root root 649 Nov 23 2018 CentOS-Debuginfo.repo -rw-r--r--. 1 root root 314 Nov 23 2018 CentOS-fasttrack.repo -rw-r--r--. 1 root root 630 Nov 23 2018 CentOS-Media.repo -rw-r--r--. 1 root root 1331 Nov 23 2018 CentOS-Sources.repo -rw-r--r--. 1 root root 5701 Nov 23 2018 CentOS-Vault.repo -rw-r--r-- 1 root root 951 Oct 3 2017 epel.repo -rw-r--r-- 1 root root 1050 Oct 3 2017 epel-testing.repo -rw-r--r-- 1 root root 187 Nov 11 18:59 proxysql.repo [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# ll total 124 -rw-r--r-- 1 root root 87119 Oct 24 08:50 mha4mysql-manager-0.56-0.el6.noarch.rpm -rw-r--r-- 1 root root 36326 Oct 24 08:50 mha4mysql-node-0.56-0.el6.noarch.rpm [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# yum -y install mha4mysql-* Loaded plugins: fastestmirror Examining mha4mysql-manager-0.56-0.el6.noarch.rpm: mha4mysql-manager-0.56-0.el6.noarch Marking mha4mysql-manager-0.56-0.el6.noarch.rpm to be installed Examining mha4mysql-node-0.56-0.el6.noarch.rpm: mha4mysql-node-0.56-0.el6.noarch Marking mha4mysql-node-0.56-0.el6.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package mha4mysql-manager.noarch 0:0.56-0.el6 will be installed --> Processing Dependency: perl(Config::Tiny) for package: mha4mysql-manager-0.56-0.el6.noarch Loading mirror speeds from cached hostfile epel/x86_64/metalink | 7.9 kB 00:00:00 * base: mirrors.zju.edu.cn * epel: hkg.mirror.rackspace.com * extras: mirrors.163.com * updates: mirrors.163.com epel | 5.3 kB 00:00:00 (1/3): epel/x86_64/group_gz | 90 kB 00:00:00 (2/3): epel/x86_64/updateinfo | 1.0 MB 00:00:00 (3/3): epel/x86_64/primary_db | 6.9 MB 00:00:01 --> Processing Dependency: perl(Config::Tiny) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(Log::Dispatch) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(Log::Dispatch) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(Log::Dispatch::File) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(Log::Dispatch::Screen) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(Parallel::ForkManager) for package: mha4mysql-manager-0.56-0.el6.noarch --> Processing Dependency: perl(Parallel::ForkManager) for package: mha4mysql-manager-0.56-0.el6.noarch ---> Package mha4mysql-node.noarch 0:0.56-0.el6 will be installed --> Running transaction check ---> Package perl-Config-Tiny.noarch 0:2.14-7.el7 will be installed ---> Package perl-Log-Dispatch.noarch 0:2.41-1.el7.1 will be installed --> Processing Dependency: perl(Params::Validate) >= 0.15 for package: perl-Log-Dispatch-2.41-1.el7.1.noarch --> Processing Dependency: perl(Class::Load) for package: perl-Log-Dispatch-2.41-1.el7.1.noarch --> Processing Dependency: perl(MIME::Lite) for package: perl-Log-Dispatch-2.41-1.el7.1.noarch --> Processing Dependency: perl(Mail::Send) for package: perl-Log-Dispatch-2.41-1.el7.1.noarch --> Processing Dependency: perl(Mail::Sender) for package: perl-Log-Dispatch-2.41-1.el7.1.noarch --> Processing Dependency: perl(Mail::Sendmail) for package: perl-Log-Dispatch-2.41-1.el7.1.noarch --> Processing Dependency: perl(Params::Validate) for package: perl-Log-Dispatch-2.41-1.el7.1.noarch ---> Package perl-Parallel-ForkManager.noarch 0:1.18-2.el7 will be installed --> Running transaction check ---> Package perl-Class-Load.noarch 0:0.20-3.el7 will be installed --> Processing Dependency: perl(Package::Stash) >= 0.14 for package: perl-Class-Load-0.20-3.el7.noarch --> Processing Dependency: perl(Module::Runtime) >= 0.012 for package: perl-Class-Load-0.20-3.el7.noarch --> Processing Dependency: perl(Module::Implementation) >= 0.04 for package: perl-Class-Load-0.20-3.el7.noarch --> Processing Dependency: perl(Try::Tiny) for package: perl-Class-Load-0.20-3.el7.noarch --> Processing Dependency: perl(Module::Runtime) for package: perl-Class-Load-0.20-3.el7.noarch --> Processing Dependency: perl(Data::OptList) for package: perl-Class-Load-0.20-3.el7.noarch ---> Package perl-MIME-Lite.noarch 0:3.030-1.el7 will be installed --> Processing Dependency: perl(MIME::Types) >= 1.28 for package: perl-MIME-Lite-3.030-1.el7.noarch --> Processing Dependency: perl(Email::Date::Format) for package: perl-MIME-Lite-3.030-1.el7.noarch ---> Package perl-Mail-Sender.noarch 0:0.8.23-1.el7 will be installed ---> Package perl-Mail-Sendmail.noarch 0:0.79-21.el7 will be installed ---> Package perl-MailTools.noarch 0:2.12-2.el7 will be installed --> Processing Dependency: perl(Net::SMTP::SSL) for package: perl-MailTools-2.12-2.el7.noarch ---> Package perl-Params-Validate.x86_64 0:1.08-4.el7 will be installed --> Running transaction check ---> Package perl-Data-OptList.noarch 0:0.107-9.el7 will be installed --> Processing Dependency: perl(Sub::Install) >= 0.921 for package: perl-Data-OptList-0.107-9.el7.noarch --> Processing Dependency: perl(Params::Util) for package: perl-Data-OptList-0.107-9.el7.noarch ---> Package perl-Email-Date-Format.noarch 0:1.002-15.el7 will be installed ---> Package perl-MIME-Types.noarch 0:1.38-2.el7 will be installed ---> Package perl-Module-Implementation.noarch 0:0.06-6.el7 will be installed ---> Package perl-Module-Runtime.noarch 0:0.013-4.el7 will be installed ---> Package perl-Net-SMTP-SSL.noarch 0:1.01-13.el7 will be installed ---> Package perl-Package-Stash.noarch 0:0.34-2.el7 will be installed --> Processing Dependency: perl(Package::Stash::XS) >= 0.26 for package: perl-Package-Stash-0.34-2.el7.noarch --> Processing Dependency: perl(Package::DeprecationManager) for package: perl-Package-Stash-0.34-2.el7.noarch ---> Package perl-Try-Tiny.noarch 0:0.12-2.el7 will be installed --> Running transaction check ---> Package perl-Package-DeprecationManager.noarch 0:0.13-7.el7 will be installed --> Processing Dependency: perl(List::MoreUtils) for package: perl-Package-DeprecationManager-0.13-7.el7.noarch ---> Package perl-Package-Stash-XS.x86_64 0:0.26-3.el7 will be installed ---> Package perl-Params-Util.x86_64 0:1.07-6.el7 will be installed ---> Package perl-Sub-Install.noarch 0:0.926-6.el7 will be installed --> Running transaction check ---> Package perl-List-MoreUtils.x86_64 0:0.33-9.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================================= Package Arch Version Repository Size ================================================================================================================================================= Installing: mha4mysql-manager noarch 0.56-0.el6 /mha4mysql-manager-0.56-0.el6.noarch 325 k mha4mysql-node noarch 0.56-0.el6 /mha4mysql-node-0.56-0.el6.noarch 102 k Installing for dependencies: perl-Class-Load noarch 0.20-3.el7 base 27 k perl-Config-Tiny noarch 2.14-7.el7 base 25 k perl-Data-OptList noarch 0.107-9.el7 base 23 k perl-Email-Date-Format noarch 1.002-15.el7 epel 17 k perl-List-MoreUtils x86_64 0.33-9.el7 base 58 k perl-Log-Dispatch noarch 2.41-1.el7.1 epel 82 k perl-MIME-Lite noarch 3.030-1.el7 epel 96 k perl-MIME-Types noarch 1.38-2.el7 epel 38 k perl-Mail-Sender noarch 0.8.23-1.el7 epel 59 k perl-Mail-Sendmail noarch 0.79-21.el7 epel 29 k perl-MailTools noarch 2.12-2.el7 base 108 k perl-Module-Implementation noarch 0.06-6.el7 base 17 k perl-Module-Runtime noarch 0.013-4.el7 base 19 k perl-Net-SMTP-SSL noarch 1.01-13.el7 base 9.1 k perl-Package-DeprecationManager noarch 0.13-7.el7 base 18 k perl-Package-Stash noarch 0.34-2.el7 base 34 k perl-Package-Stash-XS x86_64 0.26-3.el7 base 31 k perl-Parallel-ForkManager noarch 1.18-2.el7 epel 28 k perl-Params-Util x86_64 1.07-6.el7 base 38 k perl-Params-Validate x86_64 1.08-4.el7 base 69 k perl-Sub-Install noarch 0.926-6.el7 base 21 k perl-Try-Tiny noarch 0.12-2.el7 base 23 k Transaction Summary ================================================================================================================================================= Install 2 Packages (+22 Dependent packages) Total size: 1.3 M Total download size: 870 k Installed size: 2.0 M Downloading packages: (1/22): perl-Class-Load-0.20-3.el7.noarch.rpm | 27 kB 00:00:00 (2/22): perl-Config-Tiny-2.14-7.el7.noarch.rpm | 25 kB 00:00:00 (3/22): perl-Data-OptList-0.107-9.el7.noarch.rpm | 23 kB 00:00:00 (4/22): perl-List-MoreUtils-0.33-9.el7.x86_64.rpm | 58 kB 00:00:00 warning: /var/cache/yum/x86_64/7/epel/packages/perl-Email-Date-Format-1.002-15.el7.noarch.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: N OKEYPublic key for perl-Email-Date-Format-1.002-15.el7.noarch.rpm is not installed (5/22): perl-Email-Date-Format-1.002-15.el7.noarch.rpm | 17 kB 00:00:01 (6/22): perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm | 82 kB 00:00:03 (7/22): perl-MIME-Lite-3.030-1.el7.noarch.rpm | 96 kB 00:00:06 (8/22): perl-MIME-Types-1.38-2.el7.noarch.rpm | 38 kB 00:00:00 (9/22): perl-Module-Implementation-0.06-6.el7.noarch.rpm | 17 kB 00:00:00 (10/22): perl-Module-Runtime-0.013-4.el7.noarch.rpm | 19 kB 00:00:00 (11/22): perl-Net-SMTP-SSL-1.01-13.el7.noarch.rpm | 9.1 kB 00:00:00 (12/22): perl-MailTools-2.12-2.el7.noarch.rpm | 108 kB 00:00:00 (13/22): perl-Package-DeprecationManager-0.13-7.el7.noarch.rpm | 18 kB 00:00:00 (14/22): perl-Package-Stash-0.34-2.el7.noarch.rpm | 34 kB 00:00:00 (15/22): perl-Params-Util-1.07-6.el7.x86_64.rpm | 38 kB 00:00:00 (16/22): perl-Package-Stash-XS-0.26-3.el7.x86_64.rpm | 31 kB 00:00:00 (17/22): perl-Params-Validate-1.08-4.el7.x86_64.rpm | 69 kB 00:00:00 (18/22): perl-Sub-Install-0.926-6.el7.noarch.rpm | 21 kB 00:00:00 (19/22): perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm | 28 kB 00:00:00 (20/22): perl-Try-Tiny-0.12-2.el7.noarch.rpm | 23 kB 00:00:00 (21/22): perl-Mail-Sendmail-0.79-21.el7.noarch.rpm | 29 kB 00:00:00 (22/22): perl-Mail-Sender-0.8.23-1.el7.noarch.rpm | 59 kB 00:00:00 ------------------------------------------------------------------------------------------------------------------------------------------------- Total 70 kB/s | 870 kB 00:00:12 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7 Importing GPG key 0x352C64E5: Userid : "Fedora EPEL (7) <epel@fedoraproject.org>" Fingerprint: 91e9 7d7c 4a5e 96f1 7f3e 888f 6a2f aea2 352c 64e5 Package : epel-release-7-11.noarch (@extras) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : perl-Sub-Install-0.926-6.el7.noarch 1/24 Installing : perl-Try-Tiny-0.12-2.el7.noarch 2/24 Installing : perl-Params-Util-1.07-6.el7.x86_64 3/24 Installing : perl-Module-Runtime-0.013-4.el7.noarch 4/24 Installing : perl-Module-Implementation-0.06-6.el7.noarch 5/24 Installing : perl-Params-Validate-1.08-4.el7.x86_64 6/24 Installing : perl-Data-OptList-0.107-9.el7.noarch 7/24 Installing : perl-Email-Date-Format-1.002-15.el7.noarch 8/24 Installing : perl-Net-SMTP-SSL-1.01-13.el7.noarch 9/24 Installing : perl-MailTools-2.12-2.el7.noarch 10/24 Installing : perl-Package-Stash-XS-0.26-3.el7.x86_64 11/24 Installing : perl-MIME-Types-1.38-2.el7.noarch 12/24 Installing : perl-MIME-Lite-3.030-1.el7.noarch 13/24 Installing : perl-List-MoreUtils-0.33-9.el7.x86_64 14/24 Installing : perl-Package-DeprecationManager-0.13-7.el7.noarch 15/24 Installing : perl-Package-Stash-0.34-2.el7.noarch 16/24 Installing : perl-Class-Load-0.20-3.el7.noarch 17/24 Installing : perl-Config-Tiny-2.14-7.el7.noarch 18/24 Installing : perl-Mail-Sender-0.8.23-1.el7.noarch 19/24 Installing : perl-Parallel-ForkManager-1.18-2.el7.noarch 20/24 Installing : perl-Mail-Sendmail-0.79-21.el7.noarch 21/24 Installing : perl-Log-Dispatch-2.41-1.el7.1.noarch 22/24 Installing : mha4mysql-node-0.56-0.el6.noarch 23/24 Installing : mha4mysql-manager-0.56-0.el6.noarch 24/24 Verifying : mha4mysql-manager-0.56-0.el6.noarch 1/24 Verifying : mha4mysql-node-0.56-0.el6.noarch 2/24 Verifying : perl-Module-Runtime-0.013-4.el7.noarch 3/24 Verifying : perl-Mail-Sendmail-0.79-21.el7.noarch 4/24 Verifying : perl-MailTools-2.12-2.el7.noarch 5/24 Verifying : perl-Params-Util-1.07-6.el7.x86_64 6/24 Verifying : perl-Try-Tiny-0.12-2.el7.noarch 7/24 Verifying : perl-Parallel-ForkManager-1.18-2.el7.noarch 8/24 Verifying : perl-Mail-Sender-0.8.23-1.el7.noarch 9/24 Verifying : perl-Class-Load-0.20-3.el7.noarch 10/24 Verifying : perl-Config-Tiny-2.14-7.el7.noarch 11/24 Verifying : perl-List-MoreUtils-0.33-9.el7.x86_64 12/24 Verifying : perl-Package-DeprecationManager-0.13-7.el7.noarch 13/24 Verifying : perl-MIME-Types-1.38-2.el7.noarch 14/24 Verifying : perl-Package-Stash-0.34-2.el7.noarch 15/24 Verifying : perl-Package-Stash-XS-0.26-3.el7.x86_64 16/24 Verifying : perl-MIME-Lite-3.030-1.el7.noarch 17/24 Verifying : perl-Params-Validate-1.08-4.el7.x86_64 18/24 Verifying : perl-Net-SMTP-SSL-1.01-13.el7.noarch 19/24 Verifying : perl-Log-Dispatch-2.41-1.el7.1.noarch 20/24 Verifying : perl-Sub-Install-0.926-6.el7.noarch 21/24 Verifying : perl-Module-Implementation-0.06-6.el7.noarch 22/24 Verifying : perl-Data-OptList-0.107-9.el7.noarch 23/24 Verifying : perl-Email-Date-Format-1.002-15.el7.noarch 24/24 Installed: mha4mysql-manager.noarch 0:0.56-0.el6 mha4mysql-node.noarch 0:0.56-0.el6 Dependency Installed: perl-Class-Load.noarch 0:0.20-3.el7 perl-Config-Tiny.noarch 0:2.14-7.el7 perl-Data-OptList.noarch 0:0.107-9.el7 perl-Email-Date-Format.noarch 0:1.002-15.el7 perl-List-MoreUtils.x86_64 0:0.33-9.el7 perl-Log-Dispatch.noarch 0:2.41-1.el7.1 perl-MIME-Lite.noarch 0:3.030-1.el7 perl-MIME-Types.noarch 0:1.38-2.el7 perl-Mail-Sender.noarch 0:0.8.23-1.el7 perl-Mail-Sendmail.noarch 0:0.79-21.el7 perl-MailTools.noarch 0:2.12-2.el7 perl-Module-Implementation.noarch 0:0.06-6.el7 perl-Module-Runtime.noarch 0:0.013-4.el7 perl-Net-SMTP-SSL.noarch 0:1.01-13.el7 perl-Package-DeprecationManager.noarch 0:0.13-7.el7 perl-Package-Stash.noarch 0:0.34-2.el7 perl-Package-Stash-XS.x86_64 0:0.26-3.el7 perl-Parallel-ForkManager.noarch 0:1.18-2.el7 perl-Params-Util.x86_64 0:1.07-6.el7 perl-Params-Validate.x86_64 0:1.08-4.el7 perl-Sub-Install.noarch 0:0.926-6.el7 perl-Try-Tiny.noarch 0:0.12-2.el7 Complete! [root@node104.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll total 36 -rw-r--r-- 1 root root 36326 Nov 12 08:07 mha4mysql-node-0.56-0.el6.noarch.rpm [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm Loaded plugins: fastestmirror Examining mha4mysql-node-0.56-0.el6.noarch.rpm: mha4mysql-node-0.56-0.el6.noarch Marking mha4mysql-node-0.56-0.el6.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package mha4mysql-node.noarch 0:0.56-0.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================================= Package Arch Version Repository Size ================================================================================================================================================= Installing: mha4mysql-node noarch 0.56-0.el6 /mha4mysql-node-0.56-0.el6.noarch 102 k Transaction Summary ================================================================================================================================================= Install 1 Package Total size: 102 k Installed size: 102 k Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : mha4mysql-node-0.56-0.el6.noarch 1/1 Verifying : mha4mysql-node-0.56-0.el6.noarch 1/1 Installed: mha4mysql-node.noarch 0:0.56-0.el6 Complete! [root@node102.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# ll total 36 -rw-r--r-- 1 root root 36326 Nov 12 08:07 mha4mysql-node-0.56-0.el6.noarch.rpm [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm Loaded plugins: fastestmirror Examining mha4mysql-node-0.56-0.el6.noarch.rpm: mha4mysql-node-0.56-0.el6.noarch Marking mha4mysql-node-0.56-0.el6.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package mha4mysql-node.noarch 0:0.56-0.el6 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================================= Package Arch Version Repository Size ================================================================================================================================================= Installing: mha4mysql-node noarch 0.56-0.el6 /mha4mysql-node-0.56-0.el6.noarch 102 k Transaction Summary ================================================================================================================================================= Install 1 Package Total size: 102 k Installed size: 102 k Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : mha4mysql-node-0.56-0.el6.noarch 1/1 Verifying : mha4mysql-node-0.56-0.el6.noarch 1/1 Installed: mha4mysql-node.noarch 0:0.56-0.el6 Complete! [root@node103.yinzhengjie.org.cn ~]#
5>.在管理节点建立配置文件(文件夹自定义即可)
[root@node104.yinzhengjie.org.cn ~]# mkdir /etc/mha #创建存放MHA配置文件 [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# vim /etc/mha/cluster01.conf [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# cat /etc/mha/cluster01.conf [server default] user=mhauser #管理节点的用户 password=yinzhengjie #管理节点的密码 manager_workdir=/data/mastermha/cluster01/ #在本地的工作目录会自动生成 manager_log=/data/mastermha/cluster01/manager.log #指定日志存放 remote_workdir=/data/mastermha/cluster01/ #在远程主机的工作目录 master_binlog_dir=/data/mysql/logbin #如果你将MySQL数据库二进制日志分开存储，这里一定要配置master的二进制日志存储目录，否则下面会检查失败的 ssh_user=root #ssh登录用户，就是我们上面配置免密码登录的账号 repl_user=copy #MySQL复制用户的账号,这个用户应该在master和slave节点应该都存在,否则下面检查会失败 repl_password=yinzhengjie #MySQL复制用户的密码 ping_interval=1 #指定间隔时间ping [server1] #监控服务器列表, hostname=172.30.1.102 #指定主机IP地址 candidate_master=1 #如果配置该项表示它们有资格被选取为主服务器的,如果不配置表示无权限成为master,一般用于一主多从架构,将服务器性能较好的配置该参数,性能较差的就不要配置该参数即可,由于我这里试验总共就2台，因此我都给它们配置上啦! [server2] hostname=172.30.1.103 candidate_master=1 [root@node104.yinzhengjie.org.cn ~]#
6>.MHA验证和启动
[root@node104.yinzhengjie.org.cn ~]# rpm -ql mha4mysql-manager /usr/bin/masterha_check_repl /usr/bin/masterha_check_ssh /usr/bin/masterha_check_status /usr/bin/masterha_conf_host /usr/bin/masterha_manager /usr/bin/masterha_master_monitor /usr/bin/masterha_master_switch /usr/bin/masterha_secondary_check /usr/bin/masterha_stop /usr/share/man/man1/masterha_check_repl.1.gz /usr/share/man/man1/masterha_check_ssh.1.gz /usr/share/man/man1/masterha_check_status.1.gz /usr/share/man/man1/masterha_conf_host.1.gz /usr/share/man/man1/masterha_manager.1.gz /usr/share/man/man1/masterha_master_monitor.1.gz /usr/share/man/man1/masterha_master_switch.1.gz /usr/share/man/man1/masterha_secondary_check.1.gz /usr/share/man/man1/masterha_stop.1.gz /usr/share/perl5/vendor_perl/MHA/Config.pm /usr/share/perl5/vendor_perl/MHA/DBHelper.pm /usr/share/perl5/vendor_perl/MHA/FileStatus.pm /usr/share/perl5/vendor_perl/MHA/HealthCheck.pm /usr/share/perl5/vendor_perl/MHA/ManagerAdmin.pm /usr/share/perl5/vendor_perl/MHA/ManagerAdminWrapper.pm /usr/share/perl5/vendor_perl/MHA/ManagerConst.pm /usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm /usr/share/perl5/vendor_perl/MHA/MasterFailover.pm /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm /usr/share/perl5/vendor_perl/MHA/MasterRotate.pm /usr/share/perl5/vendor_perl/MHA/SSHCheck.pm /usr/share/perl5/vendor_perl/MHA/Server.pm /usr/share/perl5/vendor_perl/MHA/ServerManager.pm [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# masterha_check_ssh --conf=/etc/mha/cluster01.conf #测试SSH协议是否正常访问个节点,从下面的输出可以看出来是来验证上面配置免密码登录是否成功啦! Tue Nov 12 08:31:17 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Nov 12 08:31:17 2019 - [info] Reading application default configuration from /etc/mha/cluster01.conf.. Tue Nov 12 08:31:17 2019 - [info] Reading server configuration from /etc/mha/cluster01.conf.. Tue Nov 12 08:31:17 2019 - [info] Starting SSH connection tests.. Tue Nov 12 08:31:18 2019 - [debug] Tue Nov 12 08:31:17 2019 - [debug] Connecting via SSH from root@172.30.1.102(172.30.1.102:22) to root@172.30.1.103(172.30.1.103:22).. Warning: Permanently added '172.30.1.103' (ECDSA) to the list of known hosts. Tue Nov 12 08:31:17 2019 - [debug] ok. Tue Nov 12 08:31:18 2019 - [debug] Tue Nov 12 08:31:18 2019 - [debug] Connecting via SSH from root@172.30.1.103(172.30.1.103:22) to root@172.30.1.102(172.30.1.102:22).. Tue Nov 12 08:31:18 2019 - [debug] ok. Tue Nov 12 08:31:18 2019 - [info] All SSH connection tests passed successfully. [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# masterha_check_repl --conf=/etc/mha/cluster01.conf #检查主从复制情况是否正常 Tue Nov 12 08:43:14 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Nov 12 08:43:14 2019 - [info] Reading application default configuration from /etc/mha/cluster01.conf.. Tue Nov 12 08:43:14 2019 - [info] Reading server configuration from /etc/mha/cluster01.conf.. Tue Nov 12 08:43:14 2019 - [info] MHA::MasterMonitor version 0.56. Tue Nov 12 08:43:15 2019 - [info] GTID failover mode = 0 Tue Nov 12 08:43:15 2019 - [info] Dead Servers: Tue Nov 12 08:43:15 2019 - [info] Alive Servers: Tue Nov 12 08:43:15 2019 - [info] 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:43:15 2019 - [info] 172.30.1.103(172.30.1.103:3306) Tue Nov 12 08:43:15 2019 - [info] Alive Slaves: Tue Nov 12 08:43:15 2019 - [info] 172.30.1.103(172.30.1.103:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled Tue Nov 12 08:43:15 2019 - [info] Replicating from 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:43:15 2019 - [info] Primary candidate for the new Master (candidate_master is set) Tue Nov 12 08:43:15 2019 - [info] Current Alive Master: 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:43:15 2019 - [info] Checking slave configurations.. Tue Nov 12 08:43:15 2019 - [info] Checking replication filtering settings.. Tue Nov 12 08:43:15 2019 - [info] binlog_do_db= , binlog_ignore_db= Tue Nov 12 08:43:15 2019 - [info] Replication filtering check ok. Tue Nov 12 08:43:15 2019 - [info] GTID (with auto-pos) is not supported Tue Nov 12 08:43:15 2019 - [info] Starting SSH connection tests.. Tue Nov 12 08:43:16 2019 - [info] All SSH connection tests passed successfully. Tue Nov 12 08:43:16 2019 - [info] Checking MHA Node version.. Tue Nov 12 08:43:16 2019 - [info] Version check ok. Tue Nov 12 08:43:16 2019 - [info] Checking SSH publickey authentication settings on the current master.. Tue Nov 12 08:43:16 2019 - [info] HealthCheck: SSH to 172.30.1.102 is reachable. Tue Nov 12 08:43:16 2019 - [info] Master MHA Node version is 0.56. Tue Nov 12 08:43:16 2019 - [info] Checking recovery script configurations on 172.30.1.102(172.30.1.102:3306).. Tue Nov 12 08:43:16 2019 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql/logbin --output_fil e=/data/mastermha/cluster01//save_binary_logs_test --manager_version=0.56 --start_file=master-102.000004 Tue Nov 12 08:43:16 2019 - [info] Connecting to root@172.30.1.102(172.30.1.102:22).. Creating /data/mastermha/cluster01 if not exists.. Creating directory /data/mastermha/cluster01.. done. ok. Checking output directory is accessible or not.. ok. Binlog found at /data/mysql/logbin, up to master-102.000004 Tue Nov 12 08:43:16 2019 - [info] Binlog setting check done. Tue Nov 12 08:43:16 2019 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Tue Nov 12 08:43:16 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=172.30.1.103 --s lave_ip=172.30.1.103 --slave_port=3306 --workdir=/data/mastermha/cluster01/ --target_version=5.5.64-MariaDB --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxxTue Nov 12 08:43:16 2019 - [info] Connecting to root@172.30.1.103(172.30.1.103:22).. Creating directory /data/mastermha/cluster01/.. done. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to relay-log-103.000008 Temporary relay log file is /var/lib/mysql/relay-log-103.000008 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Tue Nov 12 08:43:17 2019 - [info] Slaves settings check done. Tue Nov 12 08:43:17 2019 - [info] 172.30.1.102(172.30.1.102:3306) (current master) +--172.30.1.103(172.30.1.103:3306) Tue Nov 12 08:43:17 2019 - [info] Checking replication health on 172.30.1.103.. Tue Nov 12 08:43:17 2019 - [info] ok. Tue Nov 12 08:43:17 2019 - [warning] master_ip_failover_script is not defined. Tue Nov 12 08:43:17 2019 - [warning] shutdown_script is not defined. Tue Nov 12 08:43:17 2019 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK. [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# masterha_manager --conf=/etc/mha/cluster01.conf #注意这是一个性检查，当出现故障时他会监控到与此同时结束该进程。不推荐直接这样使用，因为所有的监控信息都在当前终端显示，当网络故障的话就凉凉啦~ Tue Nov 12 08:48:40 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Nov 12 08:48:40 2019 - [info] Reading application default configuration from /etc/mha/cluster01.conf.. Tue Nov 12 08:48:40 2019 - [info] Reading server configuration from /etc/mha/cluster01.conf..
[root@node104.yinzhengjie.org.cn ~]# ll /data/mastermha/cluster01/ #这个目录是我们上面启动MHA服务时自动创建出来的，我们无需手动创建 total 12 -rw-r--r-- 1 root root 35 Nov 12 08:52 cluster01.master_status.health #这是健康检查的配置文件 -rw-r--r-- 1 root root 4108 Nov 12 08:48 manager.log [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]# cat /data/mastermha/cluster01/manager.log #查看排错日志 Tue Nov 12 08:48:40 2019 - [info] MHA::MasterMonitor version 0.56. Tue Nov 12 08:48:41 2019 - [info] GTID failover mode = 0 Tue Nov 12 08:48:41 2019 - [info] Dead Servers: Tue Nov 12 08:48:41 2019 - [info] Alive Servers: Tue Nov 12 08:48:41 2019 - [info] 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:48:41 2019 - [info] 172.30.1.103(172.30.1.103:3306) Tue Nov 12 08:48:41 2019 - [info] Alive Slaves: Tue Nov 12 08:48:41 2019 - [info] 172.30.1.103(172.30.1.103:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled Tue Nov 12 08:48:41 2019 - [info] Replicating from 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:48:41 2019 - [info] Primary candidate for the new Master (candidate_master is set) Tue Nov 12 08:48:41 2019 - [info] Current Alive Master: 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:48:41 2019 - [info] Checking slave configurations.. Tue Nov 12 08:48:41 2019 - [info] Checking replication filtering settings.. Tue Nov 12 08:48:41 2019 - [info] binlog_do_db= , binlog_ignore_db= Tue Nov 12 08:48:41 2019 - [info] Replication filtering check ok. Tue Nov 12 08:48:41 2019 - [info] GTID (with auto-pos) is not supported Tue Nov 12 08:48:41 2019 - [info] Starting SSH connection tests.. Tue Nov 12 08:48:42 2019 - [info] All SSH connection tests passed successfully. Tue Nov 12 08:48:42 2019 - [info] Checking MHA Node version.. Tue Nov 12 08:48:43 2019 - [info] Version check ok. Tue Nov 12 08:48:43 2019 - [info] Checking SSH publickey authentication settings on the current master.. Tue Nov 12 08:48:43 2019 - [info] HealthCheck: SSH to 172.30.1.102 is reachable. Tue Nov 12 08:48:43 2019 - [info] Master MHA Node version is 0.56. Tue Nov 12 08:48:43 2019 - [info] Checking recovery script configurations on 172.30.1.102(172.30.1.102:3306).. Tue Nov 12 08:48:43 2019 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql/logbin --output_fil e=/data/mastermha/cluster01//save_binary_logs_test --manager_version=0.56 --start_file=master-102.000004 Tue Nov 12 08:48:43 2019 - [info] Connecting to root@172.30.1.102(172.30.1.102:22).. Creating /data/mastermha/cluster01 if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /data/mysql/logbin, up to master-102.000004 Tue Nov 12 08:48:43 2019 - [info] Binlog setting check done. Tue Nov 12 08:48:43 2019 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Tue Nov 12 08:48:43 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=172.30.1.103 --s lave_ip=172.30.1.103 --slave_port=3306 --workdir=/data/mastermha/cluster01/ --target_version=5.5.64-MariaDB --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxxTue Nov 12 08:48:43 2019 - [info] Connecting to root@172.30.1.103(172.30.1.103:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to relay-log-103.000008 Temporary relay log file is /var/lib/mysql/relay-log-103.000008 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Tue Nov 12 08:48:43 2019 - [info] Slaves settings check done. Tue Nov 12 08:48:43 2019 - [info] 172.30.1.102(172.30.1.102:3306) (current master) +--172.30.1.103(172.30.1.103:3306) Tue Nov 12 08:48:43 2019 - [warning] master_ip_failover_script is not defined. Tue Nov 12 08:48:43 2019 - [warning] shutdown_script is not defined. Tue Nov 12 08:48:43 2019 - [info] Set master ping interval 1 seconds. Tue Nov 12 08:48:43 2019 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.Tue Nov 12 08:48:43 2019 - [info] Starting ping health check on 172.30.1.102(172.30.1.102:3306).. Tue Nov 12 08:48:43 2019 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond.. [root@node104.yinzhengjie.org.cn ~]# [root@node104.yinzhengjie.org.cn ~]#
7>.将master节点服务器重启，观察MHA管理端
[root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 50 *:3306 *:* LISTEN 0 128 *:22 *:* LISTEN 0 128 :::22 :::* [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# reboot Connection closed by foreign host. Disconnected from remote host(node102.yinzhengjie.org.cn) at 08:59:07. Type `help' to learn how to use Xshell prompt. [c:~]$
[root@node104.yinzhengjie.org.cn ~]# masterha_manager --conf=/etc/mha/cluster01.conf #注意，这个终端我们在上一步已经启动了，只不过重启后，发现多出来一些信息且自动终止了当前命令~生产环境建议该命令和"nohup"一起使用! Tue Nov 12 08:48:40 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Nov 12 08:48:40 2019 - [info] Reading application default configuration from /etc/mha/cluster01.conf.. Tue Nov 12 08:48:40 2019 - [info] Reading server configuration from /etc/mha/cluster01.conf.. ssh: connect to host 172.30.1.102 port 22: Connection refused Tue Nov 12 08:59:50 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Nov 12 08:59:50 2019 - [info] Reading application default configuration from /etc/mha/cluster01.conf.. Tue Nov 12 08:59:50 2019 - [info] Reading server configuration from /etc/mha/cluster01.conf.. [root@node104.yinzhengjie.org.cn ~]#
[root@node104.yinzhengjie.org.cn ~]# cat /data/mastermha/cluster01/manager.log #查看日志信息，我们可以看到master的切换过程 Tue Nov 12 08:48:40 2019 - [info] MHA::MasterMonitor version 0.56. Tue Nov 12 08:48:41 2019 - [info] GTID failover mode = 0 Tue Nov 12 08:48:41 2019 - [info] Dead Servers: Tue Nov 12 08:48:41 2019 - [info] Alive Servers: Tue Nov 12 08:48:41 2019 - [info] 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:48:41 2019 - [info] 172.30.1.103(172.30.1.103:3306) Tue Nov 12 08:48:41 2019 - [info] Alive Slaves: Tue Nov 12 08:48:41 2019 - [info] 172.30.1.103(172.30.1.103:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled Tue Nov 12 08:48:41 2019 - [info] Replicating from 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:48:41 2019 - [info] Primary candidate for the new Master (candidate_master is set) Tue Nov 12 08:48:41 2019 - [info] Current Alive Master: 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:48:41 2019 - [info] Checking slave configurations.. Tue Nov 12 08:48:41 2019 - [info] Checking replication filtering settings.. Tue Nov 12 08:48:41 2019 - [info] binlog_do_db= , binlog_ignore_db= Tue Nov 12 08:48:41 2019 - [info] Replication filtering check ok. Tue Nov 12 08:48:41 2019 - [info] GTID (with auto-pos) is not supported Tue Nov 12 08:48:41 2019 - [info] Starting SSH connection tests.. Tue Nov 12 08:48:42 2019 - [info] All SSH connection tests passed successfully. Tue Nov 12 08:48:42 2019 - [info] Checking MHA Node version.. Tue Nov 12 08:48:43 2019 - [info] Version check ok. Tue Nov 12 08:48:43 2019 - [info] Checking SSH publickey authentication settings on the current master.. Tue Nov 12 08:48:43 2019 - [info] HealthCheck: SSH to 172.30.1.102 is reachable. Tue Nov 12 08:48:43 2019 - [info] Master MHA Node version is 0.56. Tue Nov 12 08:48:43 2019 - [info] Checking recovery script configurations on 172.30.1.102(172.30.1.102:3306).. Tue Nov 12 08:48:43 2019 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql/logbin --output_fil e=/data/mastermha/cluster01//save_binary_logs_test --manager_version=0.56 --start_file=master-102.000004 Tue Nov 12 08:48:43 2019 - [info] Connecting to root@172.30.1.102(172.30.1.102:22).. Creating /data/mastermha/cluster01 if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /data/mysql/logbin, up to master-102.000004 Tue Nov 12 08:48:43 2019 - [info] Binlog setting check done. Tue Nov 12 08:48:43 2019 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Tue Nov 12 08:48:43 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=172.30.1.103 --s lave_ip=172.30.1.103 --slave_port=3306 --workdir=/data/mastermha/cluster01/ --target_version=5.5.64-MariaDB --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxxTue Nov 12 08:48:43 2019 - [info] Connecting to root@172.30.1.103(172.30.1.103:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to relay-log-103.000008 Temporary relay log file is /var/lib/mysql/relay-log-103.000008 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Tue Nov 12 08:48:43 2019 - [info] Slaves settings check done. Tue Nov 12 08:48:43 2019 - [info] 172.30.1.102(172.30.1.102:3306) (current master) +--172.30.1.103(172.30.1.103:3306) Tue Nov 12 08:48:43 2019 - [warning] master_ip_failover_script is not defined. Tue Nov 12 08:48:43 2019 - [warning] shutdown_script is not defined. Tue Nov 12 08:48:43 2019 - [info] Set master ping interval 1 seconds. Tue Nov 12 08:48:43 2019 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.Tue Nov 12 08:48:43 2019 - [info] Starting ping health check on 172.30.1.102(172.30.1.102:3306).. Tue Nov 12 08:48:43 2019 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond.. Tue Nov 12 08:59:45 2019 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away) Tue Nov 12 08:59:45 2019 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/mysql/logbin --out put_file=/data/mastermha/cluster01//save_binary_logs_test --manager_version=0.56 --binlog_prefix=master-102Tue Nov 12 08:59:45 2019 - [warning] HealthCheck: SSH to 172.30.1.102 is NOT reachable. Tue Nov 12 08:59:47 2019 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '172.30.1.102' (4)) Tue Nov 12 08:59:47 2019 - [warning] Connection failed 2 time(s).. Tue Nov 12 08:59:48 2019 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '172.30.1.102' (4)) Tue Nov 12 08:59:48 2019 - [warning] Connection failed 3 time(s).. Tue Nov 12 08:59:49 2019 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '172.30.1.102' (4)) Tue Nov 12 08:59:49 2019 - [warning] Connection failed 4 time(s).. Tue Nov 12 08:59:49 2019 - [warning] Master is not reachable from health checker! Tue Nov 12 08:59:49 2019 - [warning] Master 172.30.1.102(172.30.1.102:3306) is not reachable! Tue Nov 12 08:59:49 2019 - [warning] SSH is NOT reachable. Tue Nov 12 08:59:49 2019 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/cluster 01.conf again, and trying to connect to all servers to check server status..Tue Nov 12 08:59:49 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Nov 12 08:59:49 2019 - [info] Reading application default configuration from /etc/mha/cluster01.conf.. Tue Nov 12 08:59:49 2019 - [info] Reading server configuration from /etc/mha/cluster01.conf.. Tue Nov 12 08:59:50 2019 - [info] GTID failover mode = 0 Tue Nov 12 08:59:50 2019 - [info] Dead Servers: Tue Nov 12 08:59:50 2019 - [info] 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:59:50 2019 - [info] Alive Servers: Tue Nov 12 08:59:50 2019 - [info] 172.30.1.103(172.30.1.103:3306) Tue Nov 12 08:59:50 2019 - [info] Alive Slaves: Tue Nov 12 08:59:50 2019 - [info] 172.30.1.103(172.30.1.103:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled Tue Nov 12 08:59:50 2019 - [info] Replicating from 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:59:50 2019 - [info] Primary candidate for the new Master (candidate_master is set) Tue Nov 12 08:59:50 2019 - [info] Checking slave configurations.. Tue Nov 12 08:59:50 2019 - [info] Checking replication filtering settings.. Tue Nov 12 08:59:50 2019 - [info] Replication filtering check ok. Tue Nov 12 08:59:50 2019 - [info] Master is down! Tue Nov 12 08:59:50 2019 - [info] Terminating monitoring script. Tue Nov 12 08:59:50 2019 - [info] Got exit code 20 (Master dead). Tue Nov 12 08:59:50 2019 - [info] MHA::MasterFailover version 0.56. Tue Nov 12 08:59:50 2019 - [info] Starting master failover. Tue Nov 12 08:59:50 2019 - [info] Tue Nov 12 08:59:50 2019 - [info] * Phase 1: Configuration Check Phase.. Tue Nov 12 08:59:50 2019 - [info] Tue Nov 12 08:59:51 2019 - [info] GTID failover mode = 0 Tue Nov 12 08:59:51 2019 - [info] Dead Servers: Tue Nov 12 08:59:51 2019 - [info] 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:59:51 2019 - [info] Checking master reachability via MySQL(double check)... Tue Nov 12 08:59:52 2019 - [info] ok. Tue Nov 12 08:59:52 2019 - [info] Alive Servers: Tue Nov 12 08:59:52 2019 - [info] 172.30.1.103(172.30.1.103:3306) Tue Nov 12 08:59:52 2019 - [info] Alive Slaves: Tue Nov 12 08:59:52 2019 - [info] 172.30.1.103(172.30.1.103:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled Tue Nov 12 08:59:52 2019 - [info] Replicating from 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:59:52 2019 - [info] Primary candidate for the new Master (candidate_master is set) Tue Nov 12 08:59:52 2019 - [info] Starting Non-GTID based failover. Tue Nov 12 08:59:52 2019 - [info] Tue Nov 12 08:59:52 2019 - [info] ** Phase 1: Configuration Check Phase completed. Tue Nov 12 08:59:52 2019 - [info] Tue Nov 12 08:59:52 2019 - [info] * Phase 2: Dead Master Shutdown Phase.. Tue Nov 12 08:59:52 2019 - [info] Tue Nov 12 08:59:52 2019 - [info] Forcing shutdown so that applications never connect to the current master.. Tue Nov 12 08:59:52 2019 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master IP address. Tue Nov 12 08:59:52 2019 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master. Tue Nov 12 08:59:53 2019 - [info] * Phase 2: Dead Master Shutdown Phase completed. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] * Phase 3: Master Recovery Phase.. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] * Phase 3.1: Getting Latest Slaves Phase.. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] The latest binary log file/position on all slaves is master-102.000004:624 Tue Nov 12 08:59:53 2019 - [info] Latest slaves (Slaves that received relay log files to the latest): Tue Nov 12 08:59:53 2019 - [info] 172.30.1.103(172.30.1.103:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled Tue Nov 12 08:59:53 2019 - [info] Replicating from 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:59:53 2019 - [info] Primary candidate for the new Master (candidate_master is set) Tue Nov 12 08:59:53 2019 - [info] The oldest binary log file/position on all slaves is master-102.000004:624 Tue Nov 12 08:59:53 2019 - [info] Oldest slaves: Tue Nov 12 08:59:53 2019 - [info] 172.30.1.103(172.30.1.103:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled Tue Nov 12 08:59:53 2019 - [info] Replicating from 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:59:53 2019 - [info] Primary candidate for the new Master (candidate_master is set) Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase.. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [warning] Dead Master is not SSH reachable. Could not save it's binlogs. Transactions that were not sent to the latest slave (Read_Master_Log_Pos to the tail of the dead master's binlog) were lost.Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] * Phase 3.3: Determining New Master Phase.. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] Finding the latest slave that has all relay logs for recovering other slaves.. Tue Nov 12 08:59:53 2019 - [info] All slaves received relay logs to the same position. No need to resync each other. Tue Nov 12 08:59:53 2019 - [info] Searching new master from slaves.. Tue Nov 12 08:59:53 2019 - [info] Candidate masters from the configuration file: Tue Nov 12 08:59:53 2019 - [info] 172.30.1.103(172.30.1.103:3306) Version=5.5.64-MariaDB (oldest major version between slaves) log-bin:enabled Tue Nov 12 08:59:53 2019 - [info] Replicating from 172.30.1.102(172.30.1.102:3306) Tue Nov 12 08:59:53 2019 - [info] Primary candidate for the new Master (candidate_master is set) Tue Nov 12 08:59:53 2019 - [info] Non-candidate masters: Tue Nov 12 08:59:53 2019 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Tue Nov 12 08:59:53 2019 - [info] New master is 172.30.1.103(172.30.1.103:3306) Tue Nov 12 08:59:53 2019 - [info] Starting master failover.. Tue Nov 12 08:59:53 2019 - [info] From: 172.30.1.102(172.30.1.102:3306) (current master) +--172.30.1.103(172.30.1.103:3306) To: 172.30.1.103(172.30.1.103:3306) (new master) Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] * Phase 3.3: New Master Diff Log Generation Phase.. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] This server has all relay logs. No need to generate diff files from the latest slave. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] * Phase 3.4: Master Log Apply Phase.. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed. Tue Nov 12 08:59:53 2019 - [info] Starting recovery on 172.30.1.103(172.30.1.103:3306).. Tue Nov 12 08:59:53 2019 - [info] This server has all relay logs. Waiting all logs to be applied.. Tue Nov 12 08:59:53 2019 - [info] done. Tue Nov 12 08:59:53 2019 - [info] All relay logs were successfully applied. Tue Nov 12 08:59:53 2019 - [info] Getting new master's binlog name and position.. Tue Nov 12 08:59:53 2019 - [info] master-103.000001:403 Tue Nov 12 08:59:53 2019 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.30 .1.103', MASTER_PORT=3306, MASTER_LOG_FILE='master-103.000001', MASTER_LOG_POS=403, MASTER_USER='copy', MASTER_PASSWORD='xxx';Tue Nov 12 08:59:53 2019 - [warning] master_ip_failover_script is not set. Skipping taking over new master IP address. Tue Nov 12 08:59:53 2019 - [info] Setting read_only=0 on 172.30.1.103(172.30.1.103:3306).. Tue Nov 12 08:59:53 2019 - [info] ok. Tue Nov 12 08:59:53 2019 - [info] ** Finished master recovery successfully. Tue Nov 12 08:59:53 2019 - [info] * Phase 3: Master Recovery Phase completed. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] * Phase 4: Slaves Recovery Phase.. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase.. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] Generating relay diff files from the latest slave succeeded. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase.. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] All new slave servers recovered successfully. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] * Phase 5: New master cleanup phase.. Tue Nov 12 08:59:53 2019 - [info] Tue Nov 12 08:59:53 2019 - [info] Resetting slave info on the new master.. Tue Nov 12 08:59:53 2019 - [info] 172.30.1.103: Resetting slave info succeeded. Tue Nov 12 08:59:53 2019 - [info] Master failover to 172.30.1.103(172.30.1.103:3306) completed successfully. Tue Nov 12 08:59:53 2019 - [info] ----- Failover Report ----- cluster01: MySQL Master failover 172.30.1.102(172.30.1.102:3306) to 172.30.1.103(172.30.1.103:3306) succeeded Master 172.30.1.102(172.30.1.102:3306) is down! Check MHA Manager logs at node104.yinzhengjie.org.cn:/data/mastermha/cluster01/manager.log for details. Started automated(non-interactive) failover. The latest slave 172.30.1.103(172.30.1.103:3306) has all relay logs for recovery. Selected 172.30.1.103(172.30.1.103:3306) as a new master. 172.30.1.103(172.30.1.103:3306): OK: Applying all logs succeeded. Generating relay diff files from the latest slave succeeded. 172.30.1.103(172.30.1.103:3306): Resetting slave info succeeded. Master failover to 172.30.1.103(172.30.1.103:3306) completed successfully. [root@node104.yinzhengjie.org.cn ~]#
[root@node103.yinzhengjie.org.cn ~]# cat /etc/my.cnf [mysqld] server-id = 103 binlog_format = row read-only = on relay_log = relay-log-103 relay_log_index = relay-log-103.index log_bin = /data/mysql/logbin/master-103 skip_name_resolve = 1 relay_log_purge = 0 character-set-server = utf8mb4 default_storage_engine = InnoDB datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock [mysqld_safe] log-error = /var/log/mariadb/mariadb.log pid-file = /var/run/mariadb/mariadb.pid !includedir /etc/my.cnf.d [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 20 Server version: 5.5.64-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> SHOW SLAVE STATUSG #由于当前节点已经被MHA切换为master，因此它就没有slave配置了 Empty set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SHOW VARIABLES LIKE 'read_only'; #尽管我们配置"read_only"功能，但是切换master后MHA会自动将其关闭！ +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+ 1 row in set (0.01 sec) MariaDB [(none)]> MariaDB [(none)]> QUIT Bye [root@node103.yinzhengjie.org.cn ~]# [root@node103.yinzhengjie.org.cn ~]#
8>.温馨提示
虽说我们实现了master自动切换，但ProxySQL调度器依然需要我们手动去修改一下master节点，不过相对来说要简单的多。