本文使用docker进行相关的安装配置。
整体规划配置
| 容器名称 | 容器IP | 安装服务 | 状态 |
| mysql_master | 172.19.0.2 | Mysql、mhamanager_node | master |
| mysql_slave_1 | 172.19.0.3 | Mysql、mhamanager_node | slave |
| mysql_slave_2 | 172.19.0.4 | Mysql、mhamanager_node | slave |
| mha_master | 172.19.0.5 | mhamanager_master | 主从切换 |
一、安装三台数据库
1. 使用docker容器启动三台centos。
Dockerfile-mysql
#base image FROM centos #MAINTAINER MAINTAINER desperado RUN yum update -y RUN yum install initscripts -y RUN yum install crontabs -y RUN yum install -y net-tools RUN yum install -y telnet-server RUN yum install -y openssh-server nano lsof RUN mkdir /var/run/sshd RUN echo 'root:password' | chpasswd RUN sed -i 's/#PermitRootLogin prohibit-password/PermitRootLogin yes/' /etc/ssh/sshd_config RUN /usr/sbin/sshd -D & RUN cd /usr/local RUN yum install -y mysql RUN yum install -y mysql-server EXPOSE 22 # 设置时区 RUN /bin/cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime && echo 'Asia/Shanghai' >/etc/timezone
mysql-master-compose.yml
version: '2' services: mysql_server: build: context: ./ dockerfile: Dockerfile-mysql container_name: mysql_master privileged: true command: ['/usr/sbin/init'] ports: - "15340:3306" networks: mysql_server_network: ipv4_address: 172.19.0.2 tty: true networks: mysql_server_network: driver: bridge ipam: driver: default config: - subnet: 172.19.0.0/16 gateway: 172.19.0.1
mysql-slave1-compose.yml
mysql_slave_1_server: build: context: ./ dockerfile: Dockerfile-mysql container_name: mysql_slave_1 privileged: true command: ['/usr/sbin/init'] ports: - "15341:3306" networks: mysql_server_network: ipv4_address: 172.19.0.3 tty: true networks: mysql_server_network: driver: bridge ipam: driver: default config: - subnet: 172.19.0.0/16 gateway: 172.19.0.1
mysql-slave2-compose.yml
version: '2' services: mysql_slave_2_server: build: context: ./ dockerfile: Dockerfile-mysql container_name: mysql_slave_2 privileged: true command: ['/usr/sbin/init'] ports: - "15342:3306" networks: mysql_server_network: ipv4_address: 172.19.0.4 tty: true networks: mysql_server_network: driver: bridge ipam: driver: default config: - subnet: 172.19.0.0/16 gateway: 172.19.0.1
通过docker-compose启动三个容器
docker-compose -f mysql-msater-compose.yml up docker-compose -f mysql-slave1-compose.yml up docker-compose -f mysql-slave1-compose.yml up
启动成功，可以看到三个容器 
2. 在三台centos里面都安装MySQL数据库。
进入容器内安装MySQL。
# 查看启动的容器 docker ps # 得到如下信息 CONTAINER ID NAMES 73d2f0308fc4 mysql_slave_2 60992be33db9 mysql_slave_1 1606fbf7a5b5 mysql_master # 进入对应的容器 docker exec -it 容器id /bin/sh
进入之后分别安装MySQL，以master为例（三台分布执行）
# 进入容器 docker exec -it 1606fbf7a5b5 /bin/sh # 启动mysql service mysqld start # 修改默认的数据库密码和权限，原密码为空，执行如下命令，修改密码未root123 mysqladmin -uroot -p password root123 # 登录mysql。修改连接权限 mysql -uroot -proot123 # 切换数据库 use mysql; # 修改root账号允许远程连接 update user set host = '%' where User = 'root'; # 刷新权限 flush privileges;
3. 配置主从同步
在master的数据库里面新建一个用户，用于进行数据的同步
# 设置服务id set GLOBAL server_id = 1; # 安装启动半同步插件 install plugin rpl_semi_sync_master soname 'semisync_master.so'; set global rpl_semi_sync_master_enabled=ON; install plugin rpl_semi_sync_master soname 'semisync_slave.so'; set global rpl_semi_sync_master_enabled=OFF; # 新建用户(ip请根据自己实际情况修改) create user 'backup_user'@'172.19.0.%' identified WITH mysql_native_password by 'backup_123'; grant replication slave on *.* to 'backup_user'@'172.19.0.%'; flush privileges; # 查询master的相关信息 show master status; # 查询等到如下信息，(file即为下面master_log_file的值，Position即为master_log_pos的值) | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000001 | 1533 | | | |
在两个slave的数据库里面配置同步的信息。
# slave1 # 设置服务id set GLOBAL server_id = 2; # 安装启动半同步插件 install plugin rpl_semi_sync_master soname 'semisync_master.so'; set global rpl_semi_sync_master_enabled=OFF; install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; set global rpl_semi_sync_slave_enabled=ON; #关闭自动清理relay log set global relay_log_purge =0; #设置为只读 set global read_only = 1; # 新建用户(ip请根据自己实际情况修改) create user 'backup_user'@'172.19.0.%' identified WITH mysql_native_password by 'backup_123'; grant replication slave on *.* to 'backup_user'@'172.19.0.%'; flush privileges; # 配置master信息(master的IP、账号、密码等信息请根据实际更改，) change master to master_host='172.19.0.2', master_port=3306, master_user='backup_user', master_password='backup_123', master_log_file='binlog.000001', master_log_pos=1533; # 开启复制 start slave; #slave2 # 设置服务id set GLOBAL server_id = 3; # 安装启动半同步插件 install plugin rpl_semi_sync_master soname 'semisync_master.so'; set global rpl_semi_sync_master_enabled=OFF; install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; set global rpl_semi_sync_slave_enabled=ON; #关闭自动清理relay log set global relay_log_purge =0; #设置为只读 set global read_only = 1; # 新建用户(ip请根据自己实际情况修改) create user 'backup_user'@'172.19.0.%' identified WITH mysql_native_password by 'backup_123'; grant replication slave on *.* to 'backup_user'@'172.19.0.%'; flush privileges; # 配置master信息(master的IP、账号、密码等信息请根据实际更改，) change master to master_host='172.19.0.2', master_port=3306, master_user='backup_user', master_password='backup_123', master_log_file='binlog.000001', master_log_pos=1533; # 开启复制 start slave;
查询slave状态是否正常。
# 在slave数据库中执行 show slave status\G # 查看如下两个指标是否都为Yes Slave_IO_Running: Yes Slave_SQL_Running: Yes # 查看是否执行语句报错 Last_IO_Error Last_SQL_Error
到目前为止已经配置好了MySQL一主二从的MySQL复制主从结构。 在master所在的数据库中进行建库、建表以及对表的增删改查都将同步到两个salve数据库中。
如果上面一切都正常，就是没有进行数据的复制，一定要去看master数据的日志，错误一般都会在其中 。日志地址：/var/log/mysql/mysqld.log
二、MySQL高可用
MySQL的高可用使用MHA（Master High Availability）进行实现。
1. 先在上面三个安装了数据库的centos里面安装mha4mysql-node。
# 下载安装包 wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm # 安装 yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
2. 再启动一台全新的centos容器
Dockerfile-mha
#base image FROM centos #MAINTAINER MAINTAINER desperado RUN yum update -y RUN yum install initscripts -y RUN yum install -y net-tools RUN yum install -y wget RUN yum install -y telnet-server # 另外我还希望创建的镜像能够安装ssh并允许密码登录 RUN yum install -y openssh-server nano lsof RUN mkdir /var/run/sshd RUN echo 'root:password' | chpasswd RUN sed -i 's/#PermitRootLogin prohibit-password/PermitRootLogin yes/' /etc/ssh/sshd_config RUN /usr/sbin/sshd -D & EXPOSE 22 # 设置时区 RUN /bin/cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime && echo 'Asia/Shanghai' >/etc/timezone
mha-compose.yml
version: '2' services: mha_server: build: context: ./ dockerfile: Dockerfile-mha container_name: mha_master privileged: true command: ['/usr/sbin/init'] ports: - "15344:3306" networks: mysql_server_network: ipv4_address: 172.19.0.5 tty: true networks: mysql_server_network: driver: bridge ipam: driver: default config: - subnet: 172.19.0.0/16 gateway: 172.19.0.1
通过docker-compose启动新容器
docker-compose -f mha-compose.yml up
启动成功，可以看到一个新容器 
3. 进入当前容器，安装node和manager
# 进入对应的容器 docker exec -it 容器id /bin/sh # 安装node wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
4. 安装mha4mysql-manager
install_manager.sh
mkdir -p /usr/local/tmp_install_manager cd /usr/local/tmp_install_manager wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Sub-Exporter-Progressive-0.001013-5.el8.noarch.rpm yum install -y perl-Sub-Exporter-Progressive-0.001013-5.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/AppStream/x86_64/os/Packages/perl-Module-CPANfile-1.1002-7.module_el8.3.0+445+46ff4549.noarch.rpm yum install -y perl-Module-CPANfile-1.1002-7.module_el8.3.0+445+46ff4549.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Variable-Magic-0.62-3.el8.x86_64.rpm yum install -y perl-Variable-Magic-0.62-3.el8.x86_64.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Module-Implementation-0.09-15.el8.noarch.rpm yum install -y perl-Module-Implementation-0.09-15.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-B-Hooks-EndOfScope-0.21-6.el8.noarch.rpm yum install -y perl-B-Hooks-EndOfScope-0.21-6.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Package-Stash-XS-0.28-17.el8.x86_64.rpm yum install -y perl-Package-Stash-XS-0.28-17.el8.x86_64.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Dist-CheckConflicts-0.11-11.el8.noarch.rpm yum install -y perl-Dist-CheckConflicts-0.11-11.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Package-Stash-0.37-9.el8.noarch.rpm yum install -y perl-Package-Stash-0.37-9.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-namespace-clean-0.27-7.el8.noarch.rpm yum install -y perl-namespace-clean-0.27-7.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Sub-Identify-0.14-6.el8.x86_64.rpm yum install -y perl-Sub-Identify-0.14-6.el8.x86_64.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-namespace-autoclean-0.28-10.el8.noarch.rpm yum install -y perl-namespace-autoclean-0.28-10.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Devel-GlobalDestruction-0.14-5.el8.noarch.rpm yum install -y perl-Devel-GlobalDestruction-0.14-5.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Devel-StackTrace-2.03-2.el8.noarch.rpm yum install -y perl-Devel-StackTrace-2.03-2.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Class-Data-Inheritable-0.08-27.el8.noarch.rpm yum install -y perl-Class-Data-Inheritable-0.08-27.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Exception-Class-1.44-2.el8.noarch.rpm yum install -y perl-Exception-Class-1.44-2.el8.noarch.rpm wget http://www.rpmfind.net/linux/dag/redhat/el5/en/x86_64/dag/RPMS/perl-Perl-Tidy-20090616-1.el5.rf.noarch.rpm yum install -y perl-Perl-Tidy-20090616-1.el5.rf.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-PadWalker-2.3-2.el8.x86_64.rpm yum install -y perl-PadWalker-2.3-2.el8.x86_64.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Devel-Caller-2.06-15.el8.x86_64.rpm yum install -y perl-Devel-Caller-2.06-15.el8.x86_64.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Devel-LexAlias-0.05-16.el8.x86_64.rpm yum install -y perl-Devel-LexAlias-0.05-16.el8.x86_64.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Eval-Closure-0.14-5.el8.noarch.rpm yum install -y perl-Eval-Closure-0.14-5.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8.3.2011/PowerTools/x86_64/os/Packages/perl-Params-ValidationCompiler-0.27-1.el8.noarch.rpm yum install -y perl-Params-ValidationCompiler-0.27-1.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Ref-Util-XS-0.117-2.el8.x86_64.rpm yum install -y perl-Ref-Util-XS-0.117-2.el8.x86_64.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Ref-Util-0.203-4.el8.noarch.rpm yum install -y perl-Ref-Util-0.203-4.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Class-Method-Modifiers-2.12-8.el8.noarch.rpm yum install -y perl-Class-Method-Modifiers-2.12-8.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Role-Tiny-2.000006-2.el8.noarch.rpm yum install -y perl-Role-Tiny-2.000006-2.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Specio-0.42-2.el8.noarch.rpm yum install -y perl-Specio-0.42-2.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-MIME-Types-2.17-3.el8.noarch.rpm yum install -y perl-MIME-Types-2.17-3.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Params-Validate-1.29-5.el8.x86_64.rpm yum install -y perl-Params-Validate-1.29-5.el8.x86_64.rpm wget http://www.rpmfind.net/linux/dag/redhat/el5/en/x86_64/dag/RPMS/perl-Log-Dispatch-2.26-1.el5.rf.noarch.rpm yum install -y perl-Log-Dispatch-2.26-1.el5.rf.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Import-Into-1.002005-7.el8.noarch.rpm yum install -y perl-Import-Into-1.002005-7.el8.noarch.rpm wget http://www.rpmfind.net/linux/dag/redhat/el5/en/x86_64/dag/RPMS/perl-Parallel-ForkManager-0.7.5-2.2.el5.rf.noarch.rpm yum install -y perl-Parallel-ForkManager-0.7.5-2.2.el5.rf.noarch.rpm wget http://www.rpmfind.net/linux/dag/redhat/el6/en/x86_64/extras/RPMS/perl-Net-Telnet-3.03-2.el6.rfx.noarch.rpm yum install -y perl-Net-Telnet-3.03-2.el6.rfx.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-List-MoreUtils-XS-0.428-3.el8.x86_64.rpm yum install -y perl-List-MoreUtils-XS-0.428-3.el8.x86_64.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Exporter-Tiny-1.000000-4.el8.noarch.rpm yum install -y perl-Exporter-Tiny-1.000000-4.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-List-MoreUtils-0.428-2.el8.noarch.rpm yum install -y perl-List-MoreUtils-0.428-2.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Capture-Tiny-0.46-4.el8.noarch.rpm yum install -y perl-Capture-Tiny-0.46-4.el8.noarch.rpm wget http://www.rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/perl-Sys-Hostname-Long-1.4-1.2.el6.rf.noarch.rpm yum install -y perl-Sys-Hostname-Long-1.4-1.2.el6.rf.noarch.rpm wget http://www.rpmfind.net/linux/centos/8.3.2011/PowerTools/x86_64/os/Packages/perl-Sub-Name-0.21-7.el8.x86_64.rpm yum install -y perl-Sub-Name-0.21-7.el8.x86_64.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Package-DeprecationManager-0.17-5.el8.noarch.rpm yum install -y perl-Package-DeprecationManager-0.17-5.el8.noarch.rpm wget http://www.rpmfind.net/linux/dag/redhat/el6/en/x86_64/extras/RPMS/perl-XML-XPathEngine-0.12-1.el6.rfx.noarch.rpm yum install -y perl-XML-XPathEngine-0.12-1.el6.rfx.noarch.rpm wget http://www.rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/perl-HTML-TreeBuilder-XPath-0.11-1.el6.rf.noarch.rpm yum install -y perl-HTML-TreeBuilder-XPath-0.11-1.el6.rf.noarch.rpm wget http://www.rpmfind.net/linux/epel/8/Everything/x86_64/Packages/p/perl-Email-Abstract-3.008-15.el8.noarch.rpm yum install -y perl-Email-Abstract-3.008-15.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Class-Tiny-1.006-6.el8.noarch.rpm yum install -y perl-Class-Tiny-1.006-6.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Sub-Exporter-Progressive-0.001013-5.el8.noarch.rpm yum install -y perl-Sub-Exporter-Progressive-0.001013-5.el8.noarch.rpm http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Devel-GlobalDestruction-0.14-5.el8.noarch.rpm yum install -y perl-Class-Tiny-1.006-6.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-Role-Tiny-2.000006-2.el8.noarch.rpm yum install -y perl-Role-Tiny-2.000006-2.el8.noarch.rpm wget http://www.rpmfind.net/linux/centos/8-stream/PowerTools/x86_64/os/Packages/perl-MIME-Types-2.17-3.el8.noarch.rpm yum install -y perl-MIME-Types-2.17-3.el8.noarch.rpm wget ftp://ftp.pbone.net/mirror/ftp.centos.org/8.3.2011/PowerTools/aarch64/kickstart/Packages/perl-IO-stringy-2.111-9.el8.noarch.rpm yum install -y perl-IO-stringy-2.111-9.el8.noarch.rpm wget ftp://ftp.pbone.net/mirror/ftp.centos.org/8-stream/PowerTools/aarch64/os/Packages/perl-HTML-Tree-5.07-2.el8.noarch.rpm yum install -y perl-HTML-Tree-5.07-2.el8.noarch.rpm wget ftp://ftp.pbone.net/mirror/archive.fedoraproject.org/epel/8.1.2020-04-22/Everything/aarch64/Packages/p/perl-Email-Sender-1.300031-9.el8.noarch.rpm yum install -y perl-Email-Sender-1.300031-9.el8.noarch.rpm yum install -y perl-Log-Dispatch yum install -y perl-Parallel-ForkManager wget https://cpan.metacpan.org/authors/id/F/FA/FAYLAND/Email-Sender-Transport-SMTP-TLS-0.16.tar.gz tar -zxf Email-Sender-Transport-SMTP-TLS-0.16.tar.gz cd Email-Sender-Transport-SMTP-TLS-0.16 perl perl Makefile.PL make & make install cd .. cd .. rm -rf /usr/local/tmp_install_manager
将上面的sh脚本拷贝到服务器
docker cp install_manager.sh 容器id:/usr/local
先执行脚本
# 授权 chmod +x install_manager.sh # 执行 ./install_manager.sh
等待脚本执行完毕，在安装manager
# 安装manager wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm yum install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
安装mha4mysql-manager报错处理方式 在安装mha4mysql-manager的时候会出现各种各样的确实，处理的精髓就是一条，缺啥补啥例如：
sh-4.4# yum install perl-Log-Dispatch-2.26-1.el5.rf.noarch.rpm Last metadata expiration check: 1:43:52 ago on Thu May 13 14:39:47 2021. Error: Problem: conflicting requests - nothing provides perl(Params::Validate) >= 0.15 needed by perl-Log-Dispatch-2.26-1.el5.rf.noarch (try to add '--skip-broken' to skip uninstallable packages or '--nobest' to use not only best candidate packages)
从上面的信息中我们得知缺少Params::Validate这个东西，那么将其组装成固定的格式: perl- + 缺少组件， 及 perl-Params-Validate 这种格式，然后去http://www.rpmfind.net/linux/rpm2html/search.php该网站搜索本组件  搜索之后可以得到各个版本的安装包，根据你的系统下载安装对应的组件即可。本环境优先选用CentOS 8-stream PowerTools for x86_64版本。如果没有选用DAG packages for Red Hat Linux el5 x86_64版本。
三、配置MHA的高可用
1. 在安装了mha4mysql-manager的机器上面，执行如下操作：
# 创建配置文件 mkdir -p /usr/local/mha mkdir -p /etc/mha cd /etc/mha vim mha.conf # 在里面写入如下内容 [server default] user=root # mysql的账号 password=root123 # mysql的密码 manager_workdir=/usr/local/mha # 工作目录 manager_log=/usr/local/mha/manager.log # 日志文件 remote_workdir=/usr/local/mha # 工作目录 ssh_user=root # ssh的账号 repl_user=backup_user # 进行数据复制的账号 repl_password=backup_123 # 进行数据复制的密码 ping_interval=1 # 监控间隔(秒) # 发生故障的时候，切换VIP的脚本，这个脚本不完整，需要自己修改，故障切换必须配置 master_ip_failover_script=/usr/local/script/master_ip_failover #在发生故障切换的时候，关闭主库的脚本，这个脚本要自己写，可不配置 shutdown_script=/etc/masterha/scripts/power_manager #在发生故障切换的时候，发送邮件的脚本，这个脚本不完整，需要自己修改，可不配置 report_script=/etc/masterha/scripts/send_report #在线切换(手动切换)VIP的脚本，这个脚本不完整，需要自己修改，在线切换必须配置 master_ip_online_change_script=/user/local/script/master_ip_online_change secondary_check_script=/usr/bin/masterha_secondary_check -s 172.19.0.2 -s 172.19.0.3 –s 172.19.0.4 # 数据库节点的配置 [server1] hostname=172.19.0.2 # master数据库所在服务器的IP ssh_port=22 # ssh端口 master_binlog_dir=/var/lib/mysql # mysql binlog目录，根据实际修改 port=3306 # mysql端口号 [server2] hostname=172.19.0.3 ssh_port=22 master_binlog_dir=/var/lib/mysql port=3306 [server3] hostname=172.19.0.4 ssh_port=22 master_binlog_dir=/var/lib/mysql port=3306
配置master_ip_failover_script脚本
mkdir -p /usr/local/script vim /usr/local/script/master_ip_failover # 在其中输入如下脚本 #!/usr/bin/env perl use strict; use warnings FATAL => 'all'; use Getopt::Long; my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port ); my $vip = '118.23.114.34'; my $key = '1'; my $refresharp = "arping -c 1 -U -I eth0 118.23.114.34"; my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip ; $refresharp"; my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down"; GetOptions( 'command=s' => \$command, 'ssh_user=s' => \$ssh_user, 'orig_master_host=s' => \$orig_master_host, 'orig_master_ip=s' => \$orig_master_ip, 'orig_master_port=i' => \$orig_master_port, 'new_master_host=s' => \$new_master_host, 'new_master_ip=s' => \$new_master_ip, 'new_master_port=i' => \$new_master_port, ); exit &main(); sub main { print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n"; if ( $command eq "stop" || $command eq "stopssh" ) { my $exit_code = 1; eval { print "Disabling the VIP on old master: $orig_master_host \n"; &stop_vip(); $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) { my $exit_code = 10; eval { print "Enabling the VIP - $vip on the new master - $new_master_host \n"; &start_vip(); $exit_code = 0; }; if ($@) { warn $@; exit $exit_code; } exit $exit_code; } elsif ( $command eq "status" ) { print "Checking the Status of the script.. OK \n"; exit 0; } else { &usage(); exit 1; } } sub start_vip() { `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; } sub stop_vip() { return 0 unless ($ssh_user); `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`; } sub usage { print "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n"; }
配置send_report脚本
#!/usr/bin/perl # Copyright (C) 2011 DeNA Co.,Ltd. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA ## Note: This is a sample script and is not complete. Modify the script based on your environment. use strict; use warnings FATAL => 'all'; use Email::Simple; use Email::Sender::Simple qw(sendmail); use Email::Sender::Transport::SMTP::TLS; use Getopt::Long; #new_master_host and new_slave_hosts are set only when recovering master succeeded my ( $dead_master_host, $new_master_host, $new_slave_hosts, $subject, $body ); my $smtp='smtp.163.com'; my $mail_from='xxx@163.com'; my $mail_user='xxxx@163.com'; my $mail_pass='xxxxx'; my $mail_to= 'xxxx@qq.com'; GetOptions( 'orig_master_host=s' => \$dead_master_host, 'new_master_host=s' => \$new_master_host, 'new_slave_hosts=s' => \$new_slave_hosts, 'subject=s' => \$subject, 'body=s' => \$body, ); mailToContacts($smtp,$mail_from,$mail_user,$mail_pass,$mail_to,$subject,$body); sub mailToContacts { my ( $smtp, $mail_from, $user, $passwd, $mail_to, $subject, $msg ) = @_; open my $DEBUG, "> /tmp/monitormail.log" or die "Can't open the debug file:$!\n"; my $transport = Email::Sender::Transport::SMTP::TLS -> new( host => $smtp, port => 25, username => $mail_user, password => $mail_pass, ); my $message = Email::Simple -> create( header => [ From => $mail_from, TO => $mail_to, Subject => 'Sent from perl!', ], body => $body ); sendmail( $message, {transport => $transport} ); return 1; } # Do whatever you want here exit 0;
power_manager脚本
#!/usr/bin/env perl use strict; use warnings FATAL => 'all'; use Getopt::Long; use Pod::Usage; use Net::Telnet; use MHA::ManagerConst; use MHA::ManagerUtil; my $SSH_STOP_OK = 10; my $COMMAND_NOT_SUPPORTED = 20; my $max_retries = 10; exit &main(); sub get_mysql_pid{ my ($ip) = @_; my $output = `ssh -oConnectTimeout=15 -oConnectionAttempts=3 $ip ps -ef | grep mysql | grep -v grep | awk '{print\$2}'`; } sub stop_mysql{ my ($ip) = @_; my $output = `ssh -oConnectTimeout=15 -oConnectionAttempts=3 $ip mysqladmin -uroot -p root123 shutdown –S /tmp/mysql.sock`; } sub start_mysql{ my ($ip) = @_; my $output = `ssh -oConnectTimeout=15 -oConnectionAttempts=3 $ip service mysqld start`; } sub kill_mysql{ my($ip, $pid_file) = @_; my $command = "\"ssh -oConnectTimeout=15 -oConnectionAttempts=3 $ip if [ ! -e $pid_file ]; then exit 1; fi; pid=\\\`cat $pid_file\\\`; rm -f $pid_file; kill -9 \\\$pid; a=\\\`ps ax | grep $pid_file | grep -v grep | wc | awk {'print \\\$1'}\\\`; if [ \"a\\\$a\" = \"a0\" ]; then exit 10; fi; sleep 1; a=\\\`ps ax | grep $pid_file | grep -v grep | wc | awk {'print \\\$1'}\\\`; if [ \"a\\\$a\" = \"a0\" ]; then exit 10; else exit 1; fi\""; } sub kill_mysqld{ my ($ip) = @_; my $command = "\"ssh -oConnectTimeout=15 -oConnectionAttempts=3 $ip killall -9 mysqld mysqld_safe; a=\\\`pidof mysqld\\\`; if [ \\\"a\\\$a\\\" = \\\"a\\\" ]; then exit 10; fi; sleep 1; a=\\\`pidof mysqld\\\`; if [ \\\"a\\\$a\\\" = \\\"a\\\" ]; then exit 10; else exit 1; fi\""; } sub get_power_status{ my ($ip) = @_; my $result = "off"; eval { my ($output) = get_mysql_pid($ip); if($output){ $result = "on"; } }; if ($@) { $result = "off"; } return $result; } sub status { my ($ip) = @_; my ($power_status) = get_power_status($ip); print "Current power status on $ip : $power_status\n"; if ( $power_status eq "on" ) { return 0; } elsif ( $power_status eq "off" ) { return 0; } else { return $COMMAND_NOT_SUPPORTED; } } sub stop { my ($ip) = @_; my ($power_status) = get_power_status( $ip ); if ( $power_status eq "off" ) { print "Power of $ip was successfully turned off.\n"; return 0; } elsif ( $power_status ne "on" ) { # 开始停止mysql eval { stop_mysql($ip); }; if ($@) { printf $@; } } my $retry_count = 0; while ( $retry_count < $max_retries ) { $power_status = get_power_status( $ip ); last if ( $power_status eq "off" ); print "Waiting until power status becomes 'off'. Current status is $power_status ...\n"; sleep 3; $retry_count++; } if ( $power_status eq "off" ) { print "Power of $ip was successfully turned off.\n"; return 0; } else { print "Power of $ip was not turned off. Check the host for detail.\n"; return 1; } } sub start { my ( $ip ) = @_; my ($power_status) = get_power_status($ip); if ( $power_status eq "on" ) { print "Power of $ip was successfully turned on.\n"; return 0; } elsif ( $power_status ne "off" ) { # 启动mysql eval { start_mysql($ip) }; if ($@) { printf $@; } } my $retry_count = 0; while ( $power_status ne "on" && $retry_count < $max_retries ) { $power_status = get_power_status( $ip ); last if ( $power_status eq "on" ); print "Waiting until power status becomes 'on'. Current status is $power_status ...\n"; sleep 3; $retry_count++; } if ( $power_status eq "on" ) { print "Power of $ip was successfully turned on.\n"; return 0; } else { print "Power of $ip was not turned on. Check the host for detail.\n"; return 1; } } sub stopssh { my ( $ip , $pid_file) = @_; my $result; if ($pid_file) { eval { $result = kill_mysql($ip, $pid_file); }; if ($@) { print "Killing mysqld instance based on $pid_file failed.\n"; } if($result == 0){ print "ssh reachable. mysqld stopped. power off not needed.\n"; return 0; } print "Killing mysqld instance based on $pid_file failed.\n"; } print "Killing all mysqld instances on $ip..\n"; eval { $result = kill_mysqld($ip); }; if ($@) { print "Killing mysqld instance based on $pid_file failed.\n"; } if ( $result == 0 ) { print "ssh reachable. mysqld stopped. power off not needed.\n"; return 0; } else { print "ssh NOT reachable. Power off needed.\n"; return 1; } } # If ssh is reachable and mysqld process does not exist, exit with 2 and # do not power off. If ssh is not reachable, do power off and exit with 0 # if successful. Otherwise exit with 1. sub main { my ( $command, $ssh_user, $host, $ip, $port, $pid_file, $help ); GetOptions( 'command=s' => \$command, 'ssh_user=s' => \$ssh_user, 'host=s' => \$host, 'ip=s' => \$ip, 'port=i' => \$port, 'pid_file=s' => \$pid_file, 'help' => \$help, ); if ($help) { pod2usage(0); } pod2usage(1) unless ($command); my $rc = 1; my $ssh_stop_fail = 0; if ( $command eq "stopssh" || $command eq "stopssh2" ) { pod2usage(1) unless ($ssh_user); pod2usage(1) unless ($host); $rc = stopssh( $ip , $pid_file ); if ( $rc == $SSH_STOP_OK ) { exit $rc; } else { exit 1 if ( $command eq "stopssh2" ); $ssh_stop_fail = 1; } } # Get server type (ilo/drac, etc) and administrative IP address. if ( $command eq "start" ) { $rc = start( $ip ); } elsif ( $command eq "stop" || $ssh_stop_fail ) { $rc = stop( $ip ); } elsif ( $command eq "status" ) { $rc = status( $ip ); } else { pod2usage(1); } # Do other way to stop host if ( $rc == $COMMAND_NOT_SUPPORTED ) { $rc = 1; } if ( $rc == 0 ) { exit 0; } else { exit 1; } }
2. 修改文件/usr/share/perl5/vendor_perl/MHA/NodeUtil.pm
sub parse_mysql_major_version($) { my $str = shift; my $result = sprintf( '%03d%03d', $str =~ m/(\d+)/g ); return $result; } 修改为 sub parse_mysql_major_version($) { my $str = shift; $str =~ /(\d+)\.(\d+)/; my $strmajor = "$1.$2"; my $result = sprintf( '%03d%03d', $strmajor =~ m/(\d+)/g ); return $result; }
3. 使用如下命令启动mha_namager
nohup /usr/bin/masterha_manager --conf=/etc/mha/mha.conf --ignore_last_failover < /dev/null > /tmp/mha_manager.log 2>&1 &
查看启动日志是否成功
cd /usr/local/mha cat manager.log 当看到最后一句为： Mon May 17 10:02:53 2021 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond.. 表示启动成功
查询运行状态
/usr/bin/masterha_check_status --conf=/etc/mha/mha.conf 线上如下状态表示其正在运行 mha (pid:271) is running(0:PING_OK), master:172.19.0.2
设置从mysql的定时任务清除relay_log 新建脚本purge_relay_log.sh，其内容如下
#!/bin/bash user=root # mysql的账号 passwd=root123 # mysql的密码 port=3306 #MySQL的端口号 log_dir='/usr/local/mha/purge_log' work_dir='/var/lib/mysql' # mysql的relay日志目录 purge='/usr/bin/purge_relay_logs' if [ ! -d $log_dir ] then mkdir $log_dir -p fi echo "$date : start purging relay log" $purge --user=$user --password=$passwd --disable_relay_log_purge --port=$port --workdir=$work_dir >> $log_dir/purge_relay_logs.log 2>&1
新增定时任务，定时清理旧的日志信息
# 启动定时任务 service crond start # 新增定时任务 crontab -e 然后新增如下一行记录 0 4 * * * /usr/local/script/purge_relay_log.sh 保存退出。 查看任务列表 crontab -l 查看任务运行状态 service crond status
四、测试故障转移
将master172.19.0.2上面的数据库停机
mysqladmin -uroot -proot123 shutdown –S /tmp/mysql.sock
查看日志。
cat /usr/local/mha/manager.log # 可以看到下面的切换日志信息 Tue May 18 13:55:00 2021 - [warning] Got error on MySQL select ping: 1053 (Server shutdown in progress) Tue May 18 13:55:00 2021 - [info] Executing secondary network check script: /usr/bin/masterha_secondary_check -s 172.19.0.2 -s 172.19.0.3 -s 172.19.0.4 --user=root --master_host=172.19.0.2 --master_ip=172.19.0.2 --master_port=3306 --master_user=root --master_password=root123 --ping_type=SELECT Tue May 18 13:55:00 2021 - [info] Executing SSH check script: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/usr/local/mha/save_binary_logs_test --manager_version=0.58 --binlog_prefix=binlog Tue May 18 13:55:00 2021 - [info] HealthCheck: SSH to 172.19.0.2 is reachable. Monitoring server 172.19.0.2 is reachable, Master is not reachable from 172.19.0.2. OK. perl: warning: Setting locale failed. perl: warning: Please check that your locale settings: LANGUAGE = (unset), LC_ALL = (unset), LANG = "en_US.UTF-8" are supported and installed on your system. perl: warning: Falling back to the standard locale ("C"). Monitoring server 172.19.0.3 is reachable, Master is not reachable from 172.19.0.3. OK. perl: warning: Setting locale failed. perl: warning: Please check that your locale settings: LANGUAGE = (unset), LC_ALL = (unset), LANG = "en_US.UTF-8" are supported and installed on your system. perl: warning: Falling back to the standard locale ("C"). Monitoring server 172.19.0.4 is reachable, Master is not reachable from 172.19.0.4. OK. Tue May 18 13:55:00 2021 - [info] Master is not reachable from all other monitoring servers. Failover should start. Tue May 18 13:55:01 2021 - [warning] Got error on MySQL connect: 2002 (Can't connect to MySQL server on '172.19.0.2' (115)) Tue May 18 13:55:01 2021 - [warning] Connection failed 2 time(s).. Tue May 18 13:55:02 2021 - [warning] Got error on MySQL connect: 2002 (Can't connect to MySQL server on '172.19.0.2' (115)) Tue May 18 13:55:02 2021 - [warning] Connection failed 3 time(s).. Tue May 18 13:55:03 2021 - [warning] Got error on MySQL connect: 2002 (Can't connect to MySQL server on '172.19.0.2' (115)) Tue May 18 13:55:03 2021 - [warning] Connection failed 4 time(s).. Tue May 18 13:55:03 2021 - [warning] Master is not reachable from health checker! Tue May 18 13:55:03 2021 - [warning] Master 172.19.0.2(172.19.0.2:3306) is not reachable! Tue May 18 13:55:03 2021 - [warning] SSH is reachable. Tue May 18 13:55:03 2021 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/mha.conf again, and trying to connect to all servers to check server status.. Tue May 18 13:55:03 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue May 18 13:55:03 2021 - [info] Reading application default configuration from /etc/mha/mha.conf.. Tue May 18 13:55:03 2021 - [info] Reading server configuration from /etc/mha/mha.conf.. Tue May 18 13:55:04 2021 - [info] GTID failover mode = 0 Tue May 18 13:55:04 2021 - [info] Dead Servers: Tue May 18 13:55:04 2021 - [info] 172.19.0.2(172.19.0.2:3306) Tue May 18 13:55:04 2021 - [info] Alive Servers: Tue May 18 13:55:04 2021 - [info] 172.19.0.3(172.19.0.3:3306) Tue May 18 13:55:04 2021 - [info] 172.19.0.4(172.19.0.4:3306) Tue May 18 13:55:04 2021 - [info] Alive Slaves: Tue May 18 13:55:04 2021 - [info] 172.19.0.3(172.19.0.3:3306) Version=8.0.21 (oldest major version between slaves) log-bin:enabled Tue May 18 13:55:04 2021 - [info] Replicating from 172.19.0.2(172.19.0.2:3306) Tue May 18 13:55:04 2021 - [info] 172.19.0.4(172.19.0.4:3306) Version=8.0.21 (oldest major version between slaves) log-bin:enabled Tue May 18 13:55:04 2021 - [info] Replicating from 172.19.0.2(172.19.0.2:3306) Tue May 18 13:55:04 2021 - [info] Checking slave configurations.. Tue May 18 13:55:04 2021 - [info] read_only=1 is not set on slave 172.19.0.3(172.19.0.3:3306). Tue May 18 13:55:04 2021 - [warning] relay_log_purge=0 is not set on slave 172.19.0.3(172.19.0.3:3306). Tue May 18 13:55:04 2021 - [info] read_only=1 is not set on slave 172.19.0.4(172.19.0.4:3306). Tue May 18 13:55:04 2021 - [warning] relay_log_purge=0 is not set on slave 172.19.0.4(172.19.0.4:3306). Tue May 18 13:55:04 2021 - [info] Checking replication filtering settings.. Tue May 18 13:55:04 2021 - [info] Replication filtering check ok. Tue May 18 13:55:04 2021 - [info] Master is down! Tue May 18 13:55:04 2021 - [info] Terminating monitoring script. Tue May 18 13:55:04 2021 - [info] Got exit code 20 (Master dead). Tue May 18 13:55:04 2021 - [info] MHA::MasterFailover version 0.58. Tue May 18 13:55:04 2021 - [info] Starting master failover. Tue May 18 13:55:04 2021 - [info] Tue May 18 13:55:04 2021 - [info] * Phase 1: Configuration Check Phase.. Tue May 18 13:55:04 2021 - [info] Tue May 18 13:55:05 2021 - [info] GTID failover mode = 0 Tue May 18 13:55:05 2021 - [info] Dead Servers: Tue May 18 13:55:05 2021 - [info] 172.19.0.2(172.19.0.2:3306) Tue May 18 13:55:05 2021 - [info] Checking master reachability via MySQL(double check)... Tue May 18 13:55:05 2021 - [info] ok. Tue May 18 13:55:05 2021 - [info] Alive Servers: Tue May 18 13:55:05 2021 - [info] 172.19.0.3(172.19.0.3:3306) Tue May 18 13:55:05 2021 - [info] 172.19.0.4(172.19.0.4:3306) Tue May 18 13:55:05 2021 - [info] Alive Slaves: Tue May 18 13:55:05 2021 - [info] 172.19.0.3(172.19.0.3:3306) Version=8.0.21 (oldest major version between slaves) log-bin:enabled Tue May 18 13:55:05 2021 - [info] Replicating from 172.19.0.2(172.19.0.2:3306) Tue May 18 13:55:05 2021 - [info] 172.19.0.4(172.19.0.4:3306) Version=8.0.21 (oldest major version between slaves) log-bin:enabled Tue May 18 13:55:05 2021 - [info] Replicating from 172.19.0.2(172.19.0.2:3306) Tue May 18 13:55:05 2021 - [info] Starting Non-GTID based failover. Tue May 18 13:55:05 2021 - [info] Tue May 18 13:55:05 2021 - [info] ** Phase 1: Configuration Check Phase completed. Tue May 18 13:55:05 2021 - [info] Tue May 18 13:55:05 2021 - [info] * Phase 2: Dead Master Shutdown Phase.. Tue May 18 13:55:05 2021 - [info] Tue May 18 13:55:05 2021 - [info] Forcing shutdown so that applications never connect to the current master.. Tue May 18 13:55:05 2021 - [info] Executing master IP deactivation script: Tue May 18 13:55:05 2021 - [info] /usr/local/script/master_ip_failover --orig_master_host=172.19.0.2 --orig_master_ip=172.19.0.2 --orig_master_port=3306 --command=stopssh --ssh_user=root IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 118.23.114.34 ; arping -c 1 -U -I ens118.23.114.34=== Disabling the VIP on old master: 172.19.0.2 SIOCSIFFLAGS: Cannot assign requested address Tue May 18 13:55:05 2021 - [info] done. Tue May 18 13:55:05 2021 - [info] Executing SHUTDOWN script: Tue May 18 13:55:05 2021 - [info] /usr/local/script/manager_power --command=stopssh --ssh_user=root --host=172.19.0.2 --ip=172.19.0.2 --port=3306 Killing all mysqld instances on 172.19.0.2.. ssh NOT reachable. Power off needed. Waiting until power status becomes 'off'. Current status is on ... Power of 172.19.0.2 was successfully turned off. Tue May 18 13:55:11 2021 - [info] Power off done. Tue May 18 13:55:11 2021 - [info] * Phase 2: Dead Master Shutdown Phase completed. Tue May 18 13:55:11 2021 - [info] Tue May 18 13:55:11 2021 - [info] * Phase 3: Master Recovery Phase.. Tue May 18 13:55:11 2021 - [info] Tue May 18 13:55:11 2021 - [info] * Phase 3.1: Getting Latest Slaves Phase.. Tue May 18 13:55:11 2021 - [info] Tue May 18 13:55:11 2021 - [info] The latest binary log file/position on all slaves is binlog.000011:156 Tue May 18 13:55:11 2021 - [info] Latest slaves (Slaves that received relay log files to the latest): Tue May 18 13:55:11 2021 - [info] 172.19.0.3(172.19.0.3:3306) Version=8.0.21 (oldest major version between slaves) log-bin:enabled Tue May 18 13:55:11 2021 - [info] Replicating from 172.19.0.2(172.19.0.2:3306) Tue May 18 13:55:11 2021 - [info] 172.19.0.4(172.19.0.4:3306) Version=8.0.21 (oldest major version between slaves) log-bin:enabled Tue May 18 13:55:11 2021 - [info] Replicating from 172.19.0.2(172.19.0.2:3306) Tue May 18 13:55:11 2021 - [info] The oldest binary log file/position on all slaves is binlog.000011:156 Tue May 18 13:55:11 2021 - [info] Oldest slaves: Tue May 18 13:55:11 2021 - [info] 172.19.0.3(172.19.0.3:3306) Version=8.0.21 (oldest major version between slaves) log-bin:enabled Tue May 18 13:55:11 2021 - [info] Replicating from 172.19.0.2(172.19.0.2:3306) Tue May 18 13:55:11 2021 - [info] 172.19.0.4(172.19.0.4:3306) Version=8.0.21 (oldest major version between slaves) log-bin:enabled Tue May 18 13:55:11 2021 - [info] Replicating from 172.19.0.2(172.19.0.2:3306) Tue May 18 13:55:11 2021 - [info] Tue May 18 13:55:11 2021 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase.. Tue May 18 13:55:11 2021 - [info] Tue May 18 13:55:11 2021 - [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 May 18 13:55:11 2021 - [info] Tue May 18 13:55:11 2021 - [info] * Phase 3.3: Determining New Master Phase.. Tue May 18 13:55:11 2021 - [info] Tue May 18 13:55:11 2021 - [info] Finding the latest slave that has all relay logs for recovering other slaves.. Tue May 18 13:55:11 2021 - [info] All slaves received relay logs to the same position. No need to resync each other. Tue May 18 13:55:11 2021 - [info] Searching new master from slaves.. Tue May 18 13:55:11 2021 - [info] Candidate masters from the configuration file: Tue May 18 13:55:11 2021 - [info] Non-candidate masters: Tue May 18 13:55:11 2021 - [info] New master is 172.19.0.3(172.19.0.3:3306) Tue May 18 13:55:11 2021 - [info] Starting master failover.. Tue May 18 13:55:11 2021 - [info] From: 172.19.0.2(172.19.0.2:3306) (current master) +--172.19.0.3(172.19.0.3:3306) +--172.19.0.4(172.19.0.4:3306) To: 172.19.0.3(172.19.0.3:3306) (new master) +--172.19.0.4(172.19.0.4:3306) Tue May 18 13:55:11 2021 - [info] Tue May 18 13:55:11 2021 - [info] * Phase 3.4: New Master Diff Log Generation Phase.. Tue May 18 13:55:11 2021 - [info] Tue May 18 13:55:11 2021 - [info] This server has all relay logs. No need to generate diff files from the latest slave. Tue May 18 13:55:11 2021 - [info] Tue May 18 13:55:11 2021 - [info] * Phase 3.5: Master Log Apply Phase.. Tue May 18 13:55:11 2021 - [info] Tue May 18 13:55:11 2021 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed. Tue May 18 13:55:11 2021 - [info] Starting recovery on 172.19.0.3(172.19.0.3:3306).. Tue May 18 13:55:11 2021 - [info] This server has all relay logs. Waiting all logs to be applied.. Tue May 18 13:55:11 2021 - [info] done. Tue May 18 13:55:11 2021 - [info] All relay logs were successfully applied. Tue May 18 13:55:11 2021 - [info] Getting new master's binlog name and position.. Tue May 18 13:55:11 2021 - [info] binlog.000012:156 Tue May 18 13:55:11 2021 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.19.0.3', MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000012', MASTER_LOG_POS=156, MASTER_USER='backup_user', MASTER_PASSWORD='xxx'; Tue May 18 13:55:11 2021 - [info] Executing master IP activate script: Tue May 18 13:55:11 2021 - [info] /usr/local/script/master_ip_failover --command=start --ssh_user=root --orig_master_host=172.19.0.2 --orig_master_ip=172.19.0.2 --orig_master_port=3306 --new_master_host=172.19.0.3 --new_master_ip=172.19.0.3 --new_master_port=3306 --new_master_user='root' --new_master_password=xxx Unknown option: new_master_user Unknown option: new_master_password IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 118.23.114.34 ; arping -c 1 -U -I eth0 118.23.114.34=== Enabling the VIP - 118.23.114.34 on the new master - 172.19.0.3 Tue May 18 13:55:11 2021 - [info] OK. Tue May 18 13:55:11 2021 - [info] ** Finished master recovery successfully. Tue May 18 13:55:11 2021 - [info] * Phase 3: Master Recovery Phase completed. Tue May 18 13:55:11 2021 - [info] Tue May 18 13:55:11 2021 - [info] * Phase 4: Slaves Recovery Phase.. Tue May 18 13:55:11 2021 - [info] Tue May 18 13:55:11 2021 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase.. Tue May 18 13:55:11 2021 - [info] Tue May 18 13:55:11 2021 - [info] -- Slave diff file generation on host 172.19.0.4(172.19.0.4:3306) started, pid: 18945. Check tmp log /usr/local/mha/172.19.0.4_3306_20210518135504.log if it takes time.. Tue May 18 13:55:12 2021 - [info] Tue May 18 13:55:12 2021 - [info] Log messages from 172.19.0.4 ... Tue May 18 13:55:12 2021 - [info] Tue May 18 13:55:11 2021 - [info] This server has all relay logs. No need to generate diff files from the latest slave. Tue May 18 13:55:12 2021 - [info] End of log messages from 172.19.0.4. Tue May 18 13:55:12 2021 - [info] -- 172.19.0.4(172.19.0.4:3306) has the latest relay log events. Tue May 18 13:55:12 2021 - [info] Generating relay diff files from the latest slave succeeded. Tue May 18 13:55:12 2021 - [info] Tue May 18 13:55:12 2021 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase.. Tue May 18 13:55:12 2021 - [info] Tue May 18 13:55:12 2021 - [info] -- Slave recovery on host 172.19.0.4(172.19.0.4:3306) started, pid: 18947. Check tmp log /usr/local/mha/172.19.0.4_3306_20210518135504.log if it takes time.. Tue May 18 13:55:13 2021 - [info] Tue May 18 13:55:13 2021 - [info] Log messages from 172.19.0.4 ... Tue May 18 13:55:13 2021 - [info] Tue May 18 13:55:12 2021 - [info] Starting recovery on 172.19.0.4(172.19.0.4:3306).. Tue May 18 13:55:12 2021 - [info] This server has all relay logs. Waiting all logs to be applied.. Tue May 18 13:55:12 2021 - [info] done. Tue May 18 13:55:12 2021 - [info] All relay logs were successfully applied. Tue May 18 13:55:12 2021 - [info] Resetting slave 172.19.0.4(172.19.0.4:3306) and starting replication from the new master 172.19.0.3(172.19.0.3:3306).. Tue May 18 13:55:12 2021 - [info] Executed CHANGE MASTER. Tue May 18 13:55:12 2021 - [info] Slave started. Tue May 18 13:55:13 2021 - [info] End of log messages from 172.19.0.4. Tue May 18 13:55:13 2021 - [info] -- Slave recovery on host 172.19.0.4(172.19.0.4:3306) succeeded. Tue May 18 13:55:13 2021 - [info] All new slave servers recovered successfully. Tue May 18 13:55:13 2021 - [info] Tue May 18 13:55:13 2021 - [info] * Phase 5: New master cleanup phase.. Tue May 18 13:55:13 2021 - [info] Tue May 18 13:55:13 2021 - [info] Resetting slave info on the new master.. Tue May 18 13:55:13 2021 - [info] 172.19.0.3: Resetting slave info succeeded. Tue May 18 13:55:13 2021 - [info] Master failover to 172.19.0.3(172.19.0.3:3306) completed successfully. Tue May 18 13:55:13 2021 - [info] ----- Failover Report ----- mha: MySQL Master failover 172.19.0.2(172.19.0.2:3306) to 172.19.0.3(172.19.0.3:3306) succeeded Master 172.19.0.2(172.19.0.2:3306) is down! Check MHA Manager logs at 1c72a91c55b1:/usr/local/mha/manager.log for details. Started automated(non-interactive) failover. Invalidated master IP address on 172.19.0.2(172.19.0.2:3306) Power off 172.19.0.2. The latest slave 172.19.0.3(172.19.0.3:3306) has all relay logs for recovery. Selected 172.19.0.3(172.19.0.3:3306) as a new master. 172.19.0.3(172.19.0.3:3306): OK: Applying all logs succeeded. 172.19.0.3(172.19.0.3:3306): OK: Activated master IP address. 172.19.0.4(172.19.0.4:3306): This host has the latest relay log events. Generating relay diff files from the latest slave succeeded. 172.19.0.4(172.19.0.4:3306): OK: Applying all logs succeeded. Slave started, replicating from 172.19.0.3(172.19.0.3:3306) 172.19.0.3(172.19.0.3:3306): Resetting slave info succeeded. Master failover to 172.19.0.3(172.19.0.3:3306) completed successfully. Tue May 18 13:55:13 2021 - [info] Sending mail.. 根据日志中的 Master failover to 172.19.0.3(172.19.0.3:3306) completed successfully. 这句判断，将master从172.19.0.2切换到了192.19.0.3上面
查看邮件，可以看到收到了数据库主从切换的信息。 
查看172.19.0.4的数据库。其主从信息已近切换为了192.168.0.3。
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.19.0.3 Master_User: backup_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000012 Read_Master_Log_Pos: 156 Relay_Log_File: 73d2f0308fc4-relay-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: binlog.000012 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 156 Relay_Log_Space: 537 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: d019e839-b166-11eb-9c39-0242ac130003 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace:
查看172.19.0.3的数据库，已近变为了master。
mysql> show slave status\G Empty set (0.00 sec) mysql> show master status\G *************************** 1. row *************************** File: binlog.000012 Position: 156 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)
开启新master上面的主从半复制设置
#停止定时清理任务 service crond stop mysql>set global rpl_semi_sync_master_enabled=1; Query OK, 0 rows affected (0.00 sec) mysql>set global rpl_semi_sync_master_timeout=10000; Query OK, 0 rows affected (0.00 sec) mysql>set global rpl_semi_sync_slave_enabled=0; Query OK, 0 rows affected (0.00 sec) # 开启自动清理 mysql>set global relay_log_purge=1; Query OK, 0 rows affected (0.00 sec) mysql>set global rpl_semi_sync_slave_enabled=0; Query OK, 0 rows affected (0.00 sec)
然后重启172.19.0.2上面的数据，然后将其作为172.19.0.3数据库的从库加入集群
sh-4.4# service mysqld start #启动定时清理relay日志 # 启动定时任务 service crond start # 新增定时任务 crontab -e 然后新增如下一行记录 0 4 * * * /usr/local/script/purge_relay_log.sh 保存退出。 mysql> set global relay_log_purge=0; Query OK, 0 rows affected (0.00 sec) mysql> set global read_only=1; Query OK, 0 rows affected (0.00 sec) mysql> set global rpl_semi_sync_slave_enabled=1; Query OK, 0 rows affected (0.00 sec) mysql> set GLOBAL server_id = 2; Query OK, 0 rows affected (0.00 sec) mysql> change master to master_host='172.19.0.3', master_port=3306, master_user='backup_user', -> master_password='backup_123', master_log_file='binlog.000012', master_log_pos=156; Query OK, 0 rows affected, 2 warnings (0.06 sec) mysql> START SLAVE; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.19.0.3 Master_User: backup_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000012 Read_Master_Log_Pos: 156 Relay_Log_File: 1606fbf7a5b5-relay-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: binlog.000012 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 156 Relay_Log_Space: 537 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: d019e839-b166-11eb-9c39-0242ac130003 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace:
重启mha_manager即可
nohup /usr/bin/masterha_manager --conf=/etc/mha/mha.conf --ignore_last_failover < /dev/null > /tmp/mha_manager.log 2>&1 &
