网站开发前端框架,有经验的常州网站建设,龙华网站推广培训,北京 网站建设 公司本章内容#xff1a;
了解MySQL MHA搭建MySQL MHAMySQL MHA故障切换
1.案例分析
1.1.1案例概述 目前 MySQL 已经成为市场上主流数据库之一#xff0c;考虑到业务的重要性#xff0c;MySQL 数据库 单点问题已成为企业网站架构中最大的隐患。随着技术的发展#xff0c;MHA…本章内容
了解MySQL MHA搭建MySQL MHAMySQL MHA故障切换
1.案例分析
1.1.1案例概述 目前 MySQL 已经成为市场上主流数据库之一考虑到业务的重要性MySQL 数据库 单点问题已成为企业网站架构中最大的隐患。随着技术的发展MHA 的出现就是解决 MySQL 单点的问题。另外随着企业数据量越来越庞大数据库的压力又成为企业的另一个 瓶颈MySQL 多主多从架构的出现可以减轻 MySQL 本身的压力。本章将主要介绍 MHA 的搭建和模拟 MySQL 故障自动切换的过程
1.1.2 前置知识点
1.什么是 MHA MHAMasterHigh Availability目前在 MySQL 高可用方面是一个相对成熟的解决方 案它由日本 DeNA 公司 youshimaton现就职于 Facebook 公司开发是一套优秀的 MySQL 高可用环境下故障切换和主从复制的软件。在 MySQL 故障切换过程中MHA 能做 到在 0~30 秒之内自动完成数据库的故障切换操作并且在进行故障切换的过程中MHA 能在最大程度上保证数据的一致性以达到真正意义上的高可用。
2.MHA 的组成 该软件由两部分组成MHA Manager管理节点和 MHA Node数据节点。MHA Manager 可以单独部署在一台独立的机器上管理多个 master-slave 集群也可以部署在 一台 slave 节点上。MHA Node 运行在每台 MySQL 服务器上MHA Manager 会定时探测 集群中的 master 节点。当 master 出现故障时它可以自动将最新数据的 slave 提升为新的 master然后将所有其他的 slave 重新指向新的 master。整个故障转移过程对应用程序完 全透明。
3.MHA 优势 在 MHA 自动故障切换过程中MHA 试图从宕机的主服务器上保存二进制日志最大 程度的保证数据的不丢失但这并不总是可行的。例如如果主服务器硬件故障或无法通过SSH 访问MHA 没法保存二进制日志就会出现只进行故障转移但丢失了最新的数据的情 况。使用 MySQL 5.5 的半同步复制可以大大降低数据丢失的风险。MHA 可以与半同步复 制结合起来。如果只有一个 slave 已经收到了最新的二进制日志MHA 可以将最新的二进 制日志应用于其他所有的 slave 服务器上因此可以保证所有节点的数据一致性
4.MHA 现状 目前 MHA 主要支持一主多从的架构要搭建 MHA 要求一个复制集群中必须最少有三 台数据库服务器即一台充当 master一台充当备用 master另外一台充当从库。因为至 少需要三台服务器出于机器成本的考虑淘宝在该基础上进行了改造目前淘宝 TMHA 已经支持一主一从。 1.1.3 案例环境
1.本案例环境 上图中 MHA 可以同时监控并管理多个 MySQL 复制组本案例只实验其中的一个复制 组 2.案例需求
本案例要求通过 MHA 监控 MySQL 数据库在故障时进行自动切换不影响业务。
3.案例实现思路
安装MySQL数据库配置MySQL一主两从安装MHA 软件配置SSH无密码登录配置MySQL MHA高可用模拟master 故障切换
1.2案例实施
1.2.1 安装MySQL数据库 在三台 mysql 节点上分别安装数据库MySQL 版本使用 5.7.26 二进制安装。下面只在 master 上面做演示其他机器步骤相同安装过程如下
1.基础环境准备
安装使用前需要安装一些基础软件包
[rootmaster ~]# yum -y install gcc vim wget net-tools lrzsz libaio拉取MySQL软件包
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
创建运行MySQL程序的用户
[rootmaster ~]# useradd -M -s /sbin/nologin mysql
关闭Selinux 和防火墙
[rootmaster ~]# setenforce 0
setenforce: SELinux is disabled
[rootmaster ~]# systemctl stop firewalld2.二进制安装 二进制安装的版本采用跟上面编译安装的版本一样 MySQL 5.7.26。首先需要下载该软 件包或者提前上传然后再解压进行配置。
[rootmaster ~]# tar zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[rootmaster ~]# mv mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql
[rootmaster ~]# mkdir /usr/local/mysql/data
[rootmaster ~]# chown -R mysql:mysql /usr/local/mysql/data/
[rootmaster ~]# cd /usr/local/mysql/bin/
[rootmaster bin]# ./mysqld --usermysql --basedir/usr/local/mysql --datadir/usr/local/mysql/data --initialize2024-03-15T02:47:24.625118Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-03-15T02:47:24.851610Z 0 [Warning] InnoDB: New log files created, LSN45790
2024-03-15T02:47:24.888575Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-03-15T02:47:24.950326Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 5a9fc7dd-e276-11ee-b06c-000c2910f2b6.
2024-03-15T02:47:24.957182Z 0 [Warning] Gtid table is not ready to be used. Table mysql.gtid_executed cannot be opened.
2024-03-15T02:47:24.958231Z 1 [Note] A temporary password is generated for rootlocalhost: kAaWdpuIL4C //密码字段需要保存此字段是MySQL数据库中管理员密码需要保存后续需要修改密码
2024-03-15T02:47:24.958231Z 1 [Note] A temporary password is generated for rootlocalhost: kAaWdpuIL4C //密码字段需要保存 3. 设置配置文件
MySQL 的配置文件跟上面编译安装的配置文件类似
[rootmaster bin]# vim /etc/my.cnf
[mysqld]
[client]
socket/usr/local/mysql/data/mysql.sock[mysqld]
socket/usr/local/mysql/data/mysql.sock
bind-address 0.0.0.0
port 3306
basedir/usr/local/mysql
datadir/usr/local/mysql/data
max_connections2048
character-set-serverutf8
default-storage-engineINNODB将MySQL的可执行文件写入环境变量中
[rootmaster bin]# echo export PATH$PATH:/usr/local/mysql/bin /etc/profile
[rootmaster bin]# . /etc/profile4.配置systemctl 方式启动 将 MySQL 添 加 成 为 系 统 服 务 通 过 使 用 systemctl 来 管 理 。 在/usr/local/mysql/support-files 目 录 下 找 到 mysql.server 文 件 将 其 复 制 到 /etc/rc.d/init.d目录下改名为 mysqld 并赋予可执行权限。
[rootmaster bin]# cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
[rootmaster bin]# chmod x /etc/rc.d/init.d/mysqld
编辑生成mysql.service服务通过systemctl 方式来管理
[rootmaster bin]# vim /lib/systemd/system/mysqld.service[Unit]
Descriptionmysqld
Afternetwork.target[Service]
Typeforking
ExecStart/etc/rc.d/init.d/mysqld start
ExecReload/etc/rc.d/init.d/mysqld reload
ExecStop/etc/rc.d/init.d/mysqld stop[Install]
WantedBymulti-user.target[rootmaster bin]# systemctl daemon-reload
[rootmaster bin]# systemctl enable mysqld.service
[rootmaster bin]# systemctl start mysqld.service
[rootmaster bin]# netstat -anpt | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 11941/mysqld
[rootmaster bin]#
访问数据库修改密码
[rootmaster bin]# mysql -u root -p //登录数据库
Enter password: //输入之前字段中的默认密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type help; or \h for help. Type \c to clear the current input statement.mysql set password password(pwd123); //修改密码为pwd123
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql 5.配置主服务器
[rootmaster ~]# vim /etc/my.cnfserver-id 11
log_bin master-bin
log-slave-updates true[rootmaster ~]# systemctl restart mysqld.service //重启服务
6.配置从服务器
[rootslave02 ~]# vim /etc/my.cnf
[rootslave02 ~]# cat /etc/my.cnf
[mysqld]
[client]
socket/usr/local/mysql/data/mysql.sock[mysqld]
socket/usr/local/mysql/data/mysql.sock
bind-address 0.0.0.0
port 3306
basedir/usr/local/mysql
datadir/usr/local/mysql/data
max_connections2048
character-set-serverutf8
default-storage-engineINNODBserver-id33
relay-log relay-log-bin
relay-log-index slave-relay-bin-index
log-bin mysql-bin
relay_log_purge 0[rootslave01 ~]# systemctl restart mysqld.service //重启服务
注意server-d 不能相同两台slave 服务器的id分别为 22 和 33 其他相同 1.3 配置MySQL一主两从
MySQL 的主从相对比较简单。本章实现配置一个主数据库两个从数据库。
1MySQL授权 在所有数据库节点上授权两个用户一个是从数据库同步使用另一个是 manager使用
mysql grant replication slave on *.* to myslave192.168.182.% identified by pwd123;
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql grant all privileges on *.* to mha192.168.182.% identified by pwd123;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql 下面三条授权按理论是不用添加的但是在做案例实验时通过 MHA 检查 MySQL 主从 有报错报两个从库通过主机名连接不上主库所以三个数据库都添加下面的授权。
mysql grant all privileges on *.* to mhamaster identified by pwd123;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql grant all privileges on *.* to mhaslave01 identified by pwd123
;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql grant all privileges on *.* to mhaslave02 identified by pwd123;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql 2查看二进制文件和同步点 在master 主机上查看二进制文件和同步点 3在slave01 和 slave02分别执行同步
mysql change master to master_host192.168.182.101,master_usermyslave,master_passwordpwd123,master_log_filemaster-bin.000001,master_log_pos
154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql start slave;
Query OK, 0 rows affected (0.00 sec)4查看数据同步结果
查看 IO 和SQL 线程都是yes 代表同步是否正常 注意必须设置两个数据为只读模式在两个从库分别执行
mysql set global read_only1;
Query OK, 0 rows affected (0.01 sec)mysql 5插入数据测试数据库同步 在master 主库创建数据库测试是否同步
mysql create database test_db;两个库分别查询显示出主库创建的库说明主从同步正常 1.4 安装MHA软件
1所有服务器上都安装MHA 依赖环境首先安装 epel源
[rootmanager ~]# yum -y install epel-release[rootmanager ~]# yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExUtils-CBuilder perl-ExUtils-MakeMaker perl-CPAN2在所有服务器上必须安装 node 组件最后在MHA-manager 节点上安装 manager 组件因为 manager 依赖 node 组件。下面实在manager 上操作演示安装node组件
拉取软件包
[rootmanager ~]# wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58.tar.gz编译安装
[rootmanager ~]# tar zxvf mha4mysql-node-0.58.tar.gz
[rootmanager ~]# cd mha4mysql-node-0.58/
[rootmanager mha4mysql-node-0.58]# perl Makefile.PL
[rootmanager mha4mysql-node-0.58]# make make install3在manager 上安装 manager组件
拉取软件包
[rootmanager ~]# wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58.tar.gz编译安装
[rootmanager ~]# tar zxvf mha4mysql-manager-0.58.tar.gz
[rootmanager ~]# cd mha4mysql-manager-0.58/
[rootmanager mha4mysql-manager-0.58]# perl Makefile.PL
[rootmanager mha4mysql-manager-0.58]# make make install
manager 安装后在/usr/local/bin 下面会生成几个工具
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 信息。 4node 安装后也会在/usr/local/bin 下面会生成几个脚本这些工具通常由manager 的
脚本触发无需认为操作
save_binary_logs保存和复制 master 的二进制日志apply_diff_relay_logs识别差异的中继日志事件并将其差异的事件应用于其他的 slave。filter_mysqlbinlog去除不必要的 ROLLBACK 事件MHA 已不再使用这个工具。purge_relay_logs清除中继日志不会阻塞 SQL 线程。 1.4 配置SSH无密码登录
1在manager 上配置所有节点ssh无密码认证
[rootmanager ~]# ssh-keygen -t rsa //一只按回车键最后会出现一串密钥
[rootmanager ~]# ssh 192.168.182.104
[rootmanager ~]# ssh 192.168.182.103
[rootmanager ~]# ssh 192.168.182.101
2在master上配置到数据库所有节点的无密码认证
[rootmaster ~]# ssh-keygen -t rsa
[rootmaster ~]# ssh-copy-id 192.168.182.103
[rootmaster ~]# ssh-copy-id 192.168.182.1043在slave01上配置到所有数据库的无密码认证
[rootslave01 ~]# ssh-keygen -t rsa
[rootslave01 ~]# ssh-copy-id 192.168.182.101
[rootslave01 ~]# ssh-copy-id 192.168.182.1044在slave02从数据库配置到所有数据库节点的无密码认证
[rootslave02 ~]# ssh-keygen -t rsa
[rootslave02 ~]# ssh-copy-id 192.168.182.101
[rootslave02 ~]# ssh-copy-id 192.168.182.104 1.5配置 MHA
1在manager 节点上复制相关脚本到 /usr/local/bin 目录
[rootmanager ~]# ll mha4mysql-manager-0.58/samples/scripts/
总用量 32
-rwxr-xr-x 1 luo luo 3648 3月 23 2018 master_ip_failover
-rwxr-xr-x 1 luo luo 9870 3月 23 2018 master_ip_online_change
-rwxr-xr-x 1 luo luo 11867 3月 23 2018 power_manager
-rwxr-xr-x 1 luo luo 1360 3月 23 2018 send_report
[rootmanager ~]# 具体脚本作用如下
master_ip_failover自动切换时 VIP 管理的脚本master_ip_online_change在线切换时 vip 的管理power_manager故障发生后关闭主机的脚本send_report因故障切换后发送报警的脚本
[rootmanager ~]# cp /root/mha4mysql-manager-0.58/samples/scripts/* /usr/local/bin/复制“master_ip_failover”脚本到/usr/local/bin 目录这里使用脚本管理 VIP也是推荐 的一种方式生产环境不建议使用 Keepalived。
2使用下面内容完整替换 master_ip_failover 文件的内容IP 部分更换为自己的IP 配置文件插入以下内容
[rootmanager ~]# vim /usr/local/bin/master_ip_failover
my $vip 192.168.182.200/24; //配置VIP地址
my $key 1;
my $ssh_start_vip /usr/sbin/ifconfig ens33:$key $vip up;
my $ssh_stop_vip /usr/sbin/ifconfig ens33:$key down;3创建 MHA 软件目录并拷贝配置文件
[server default]
manager_log/var/log/masterha/app1/manager.log
manager_workdir/var/log/masterha/app1
master_binlog_dir/usr/local/mysql/data
master_ip_failover_script/usr/local/bin/master_ip_failover
master_ip_online_change_script/usr/local/bin/master_ip_onilover_change
usermha
passwordpwd123
ping_interval1
remote_workdir/tmp
repl_usermyslave
repl_passwordpwd123
secondary_check_script/usr/local/bin/masterha_secondary_check -s 192.168.182.103 -s 192.168.182.104
shutdown_script
ssh_userroot[server1]
hostname192.168.182.101
port3306[server2]
candidate_master1
check_repl_delay0
hostname192.168.182.103
port3306[server3]
hostname192.168.182.104
port3306candidate_master 与 check_repl_delay 的主要作用如下所示
candidate_master1设置为候选 master如果设置该参数以后发生主从切换以后 会将此从库提升为主库即使这个主库不是集群中最新的 slave。check_repl_delay0默认情况下如果一个 slave 落后 master 100M 的 relay logs 的话 MHA 将不会选择该 slave 作为一个新的 master。因为对于这个 slave 的恢复需要花费 很长时间通过设置 check_repl_delay0,MHA 触发切换在选择一个新的 master 的时 候将会忽略复制延时这个参数对于设置了 candidate_master1 的主机非常有用因 为这个候选主在切换的过程中一定是新的 master。 4测试ssh无密码认证如果正常最后会输出successfully
[rootmanager ~]# masterha_check_ssh -conf/etc/masterha/app1.cnf
Fri Mar 15 15:28:54 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Mar 15 15:28:54 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Fri Mar 15 15:28:54 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Fri Mar 15 15:28:54 2024 - [info] Starting SSH connection tests..
Fri Mar 15 15:28:56 2024 - [debug]
Fri Mar 15 15:28:54 2024 - [debug] Connecting via SSH from root192.168.182.101(192.168.182.101:22) to root192.168.182.103(192.168.182.103:22)..
Fri Mar 15 15:28:55 2024 - [debug] ok.
Fri Mar 15 15:28:55 2024 - [debug] Connecting via SSH from root192.168.182.101(192.168.182.101:22) to root192.168.182.104(192.168.182.104:22)..
Fri Mar 15 15:28:55 2024 - [debug] ok.
Fri Mar 15 15:28:57 2024 - [debug]
Fri Mar 15 15:28:55 2024 - [debug] Connecting via SSH from root192.168.182.103(192.168.182.103:22) to root192.168.182.101(192.168.182.101:22)..
Fri Mar 15 15:28:55 2024 - [debug] ok.
Fri Mar 15 15:28:55 2024 - [debug] Connecting via SSH from root192.168.182.103(192.168.182.103:22) to root192.168.182.104(192.168.182.104:22)..
Fri Mar 15 15:28:56 2024 - [debug] ok.
Fri Mar 15 15:28:57 2024 - [debug]
Fri Mar 15 15:28:55 2024 - [debug] Connecting via SSH from root192.168.182.104(192.168.182.104:22) to root192.168.182.101(192.168.182.101:22)..
Fri Mar 15 15:28:56 2024 - [debug] ok.
Fri Mar 15 15:28:56 2024 - [debug] Connecting via SSH from root192.168.182.104(192.168.182.104:22) to root192.168.182.103(192.168.182.103:22)..
Fri Mar 15 15:28:56 2024 - [debug] ok.
Fri Mar 15 15:28:57 2024 - [info] All SSH connection tests passed successfully.
[rootmanager ~]#
5测试 MySQL 主从连接情况最后出现MySQL Replication Heaith is OK 字样说明正常 如下
[rootmanager ~]# masterha_check_repl -conf/etc/masterha/app1.cnf
Fri Mar 15 15:33:48 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Mar 15 15:33:48 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Fri Mar 15 15:33:48 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Fri Mar 15 15:33:48 2024 - [info] MHA::MasterMonitor version 0.58.
Fri Mar 15 15:33:49 2024 - [info] GTID failover mode 0
Fri Mar 15 15:33:49 2024 - [info] Dead Servers:
Fri Mar 15 15:33:49 2024 - [info] Alive Servers:
Fri Mar 15 15:33:49 2024 - [info] 192.168.182.101(192.168.182.101:3306)
Fri Mar 15 15:33:49 2024 - [info] 192.168.182.103(192.168.182.103:3306)
Fri Mar 15 15:33:49 2024 - [info] 192.168.182.104(192.168.182.104:3306)
Fri Mar 15 15:33:49 2024 - [info] Alive Slaves:
Fri Mar 15 15:33:49 2024 - [info] 192.168.182.103(192.168.182.103:3306) Version5.7.26-log (oldest major version between slaves) log-bin:enabled
Fri Mar 15 15:33:49 2024 - [info] Replicating from 192.168.182.101(192.168.182.101:3306)
Fri Mar 15 15:33:49 2024 - [info] Primary candidate for the new Master (candidate_master is set)
Fri Mar 15 15:33:49 2024 - [info] 192.168.182.104(192.168.182.104:3306) Version5.7.26-log (oldest major version between slaves) log-bin:enabled
Fri Mar 15 15:33:49 2024 - [info] Replicating from 192.168.182.101(192.168.182.101:3306)
Fri Mar 15 15:33:49 2024 - [info] Current Alive Master: 192.168.182.101(192.168.182.101:3306)
Fri Mar 15 15:33:49 2024 - [info] Checking slave configurations..
Fri Mar 15 15:33:49 2024 - [info] Checking replication filtering settings..
Fri Mar 15 15:33:49 2024 - [info] binlog_do_db , binlog_ignore_db
Fri Mar 15 15:33:49 2024 - [info] Replication filtering check ok.
Fri Mar 15 15:33:49 2024 - [info] GTID (with auto-pos) is not supported
Fri Mar 15 15:33:49 2024 - [info] Starting SSH connection tests..
Fri Mar 15 15:33:52 2024 - [info] All SSH connection tests passed successfully.
Fri Mar 15 15:33:52 2024 - [info] Checking MHA Node version..
Fri Mar 15 15:33:52 2024 - [info] Version check ok.
Fri Mar 15 15:33:52 2024 - [info] Checking SSH publickey authentication settings on the current master..
Fri Mar 15 15:33:53 2024 - [info] HealthCheck: SSH to 192.168.182.101 is reachable.
Fri Mar 15 15:33:53 2024 - [info] Master MHA Node version is 0.58.
Fri Mar 15 15:33:53 2024 - [info] Checking recovery script configurations on 192.168.182.101(192.168.182.101:3306)..
Fri Mar 15 15:33:53 2024 - [info] Executing command: save_binary_logs --commandtest --start_pos4 --binlog_dir/usr/local/mysql/data --output_file/tmp/save_binary_logs_test --manager_version0.58 --start_filemaster-bin.000001
Fri Mar 15 15:33:53 2024 - [info] Connecting to root192.168.182.101(192.168.182.101:22).. Creating /tmp if not exists.. ok.Checking output directory is accessible or not..ok.Binlog found at /usr/local/mysql/data, up to master-bin.000001
Fri Mar 15 15:33:53 2024 - [info] Binlog setting check done.
Fri Mar 15 15:33:53 2024 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Fri Mar 15 15:33:53 2024 - [info] Executing command : apply_diff_relay_logs --commandtest --slave_usermha --slave_host192.168.182.103 --slave_ip192.168.182.103 --slave_port3306 --workdir/tmp --target_version5.7.26-log --manager_version0.58 --relay_log_info/usr/local/mysql/data/relay-log.info --relay_dir/usr/local/mysql/data/ --slave_passxxx
Fri Mar 15 15:33:53 2024 - [info] Connecting to root192.168.182.103(192.168.182.103:22).. Checking slave recovery environment settings..Opening /usr/local/mysql/data/relay-log.info ... ok.Relay log found at /usr/local/mysql/data, up to relay-log-bin.000002Temporary relay log file is /usr/local/mysql/data/relay-log-bin.000002Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.done.Testing mysqlbinlog output.. done.Cleaning up test file(s).. done.
Fri Mar 15 15:33:53 2024 - [info] Executing command : apply_diff_relay_logs --commandtest --slave_usermha --slave_host192.168.182.104 --slave_ip192.168.182.104 --slave_port3306 --workdir/tmp --target_version5.7.26-log --manager_version0.58 --relay_log_info/usr/local/mysql/data/relay-log.info --relay_dir/usr/local/mysql/data/ --slave_passxxx
Fri Mar 15 15:33:53 2024 - [info] Connecting to root192.168.182.104(192.168.182.104:22).. Checking slave recovery environment settings..Opening /usr/local/mysql/data/relay-log.info ... ok.Relay log found at /usr/local/mysql/data, up to relay-log-bin.000002Temporary relay log file is /usr/local/mysql/data/relay-log-bin.000002Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.done.Testing mysqlbinlog output.. done.Cleaning up test file(s).. done.
Fri Mar 15 15:34:04 2024 - [info] Slaves settings check done.
Fri Mar 15 15:34:04 2024 - [info]
192.168.182.101(192.168.182.101:3306) (current master)--192.168.182.103(192.168.182.103:3306)--192.168.182.104(192.168.182.104:3306)Fri Mar 15 15:34:04 2024 - [info] Checking replication health on 192.168.182.103..
Fri Mar 15 15:34:04 2024 - [info] ok.
Fri Mar 15 15:34:04 2024 - [info] Checking replication health on 192.168.182.104..
Fri Mar 15 15:34:04 2024 - [info] ok.
Fri Mar 15 15:34:04 2024 - [info] Checking master_ip_failover_script status:
Fri Mar 15 15:34:04 2024 - [info] /usr/local/bin/master_ip_failover --commandstatus --ssh_userroot --orig_master_host192.168.182.101 --orig_master_ip192.168.182.101 --orig_master_port3306
Fri Mar 15 15:34:04 2024 - [info] OK.
Fri Mar 15 15:34:04 2024 - [warning] shutdown_script is not defined.
Fri Mar 15 15:34:04 2024 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK.
[rootmanager ~]#
如果在执行过程中由如下保存信息
Cant exec mysqlbinlog: No such file or directory at
/usr/lib64/perl5/vendor_perl/MHA/BinlogManager.pm line 99
采用设置软连接的方式解决三台MySQL上都需要执行
[rootmaster ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlo
[rootmaster ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysqlbinlog 如果出现如下报错信息
Bareword FIXME_xxx not allowed while strict subs in use at /usr/local/bin/master_ip_failover line
100
在manager 机器上面使用如下解决方法
[rootmanager ~]# vim /usr/local/bin/master_ip_failover ## Update master ip on the catalog database, etc# FIXME_xxx; //97行注释6首次配置 MHA 的 VIP 地址需要手动进行配置在 master 上执行如下命令
[rootmaster ~]# ifconfig ens33:1 192.168.182.200
[rootmaster ~]# ifconfig ens33:1
ens33:1: flags4163UP,BROADCAST,RUNNING,MULTICAST mtu 1500inet 192.168.182.200 netmask 255.255.255.0 broadcast 192.168.182.255ether 00:0c:29:10:f2:b6 txqueuelen 1000 (Ethernet)[rootmaster ~]#
VIP 地址不会应为 manager 节点停止 MHA 服务而消失 7启动 MHA
[rootmanager ~]# nohup masterha_manager --conf/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover /dev/null /var/log/masterha/app1/manager.log 21 配置参数具体作用如下
--remove_dead_master_conf 该参数代表当发生主从切换后老的主库的 IP 地址将会 从配置文件中移除。--ignore_last_failover 在缺省情况下如果 MHA 检测到连续发生宕机且两次宕机间 隔不足 8 小时的话则不会进行 Failover之所以这样限制是为了避免 ping-pong 效应。 该参数代表忽略上次 MHA 触发切换产生的文件默认情况下MHA 发生切换后会在 日志中记录下次再切换的时候如果发现该目录下存在该文件将不允许触发切换除非 在第一次切换后收到删除该文件。为了方便这里设置为–ignore_last_failover。
若要关闭manager 服务可以使用如下命令或使用kill 进程ID方式关闭
[rootmanager ~]# masterha_stop --conf/etc/manager/app1.cnf
8查看 MHA日志也可以看到当前的master 是 192.168.182.101 如下
[rootmanager ~]# cat /var/log/masterha/app1/manager.log 1.6 模拟master故障
1.自动切换
manager动态查看日志
[rootmanager ~]# tailf /var/log/masterha/app1/manager.log --192.168.182.104(192.168.182.104:3306)Fri Mar 15 15:43:56 2024 - [info] Checking master_ip_failover_script status:
Fri Mar 15 15:43:56 2024 - [info] /usr/local/bin/master_ip_failover --commandstatus --ssh_userroot --orig_master_host192.168.182.101 --orig_master_ip192.168.182.101 --orig_master_port3306
Fri Mar 15 15:43:56 2024 - [info] OK.
Fri Mar 15 15:43:56 2024 - [warning] shutdown_script is not defined.
Fri Mar 15 15:43:56 2024 - [info] Set master ping interval 1 seconds.
Fri Mar 15 15:43:56 2024 - [info] Set secondary check script: /usr/local/bin/masterha_secondary_check -s 192.168.182.103 -s 192.168.182.104
Fri Mar 15 15:43:56 2024 - [info] Starting ping health check on 192.168.182.101(192.168.182.101:3306)..
Fri Mar 15 15:43:56 2024 - [info] Ping(SELECT) succeeded, waiting until MySQL doesnt respond..1直接关机master主库可以直接关闭MySQL服务
[rootmaster ~]# poweroff
2观察 MHA 日志如果自动切换成功最后会输出 souccessfuily字样
[rootmanager ~]# tailf /var/log/masterha/app1/manager.log #######忽略大部分日志Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.182.101(192.168.182.101:3306)
The latest slave 192.168.182.103(192.168.182.103:3306) has all relay logs for recovery.
Selected 192.168.182.103(192.168.182.103:3306) as a new master.
192.168.182.103(192.168.182.103:3306): OK: Applying all logs succeeded.
Failed to activate master IP address for 192.168.182.103(192.168.182.103:3306) with return code 10:0
192.168.182.104(192.168.182.104:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.182.104(192.168.182.104:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.182.103(192.168.182.103:3306)
192.168.182.103(192.168.182.103:3306): Resetting slave info succeeded.
Master failover to 192.168.182.103(192.168.182.103:3306) completed successfully.3正常自动切换一次后MHA 进程会退出。MHA 会自动修改 app1.cnf文件内容将宕机的 Mysql节点删除查看 slave01是否接管VIP地址
[rootslave01 ~]# ifconfig也可以进入slave02从库查看是否切换
mysql show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.182.1032.如主库修复后可继续使用如下步骤使其重新加入群集并将master 作为从库
1查看slave01
mysql show master status;
-------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-------------------------------------------------------------------------------
| mysql-bin.000001 | 868 | | | |
-------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql 启动master数据库
[rootmaster ~]# systemctl start mysqld
[rootmaster ~]# mysql -u root -p
Enter password:mysql change master to master_host192.168.182.103,master_usermyslave,master_passwordpwd123,master_log_filemysql-bin.000001,master_log_pos868;
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql 3master启动从库同步并设置为只读
mysql start slave;
Query OK, 0 rows affected (0.01 sec)mysql set global read_only1;
Query OK, 0 rows affected (0.00 sec)mysql
4停掉当前主库slave01的同步进程不然下次作为从库同步会报错
mysql stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql reset slave;
Query OK, 0 rows affected (0.00 sec)mysql
