东莞网站建设都找菲凡网络,网站域名有哪些,网站建设备案,一般通过路人文章目录 1.MHA介绍2.MHA组件介绍3.集群规划4.服务器初始化5.MySQL集群部署5.1 安装MySQL集群5.2 配置一主两从5.3 测试MySQL主从5.4 赋予MHA用户连接权限 6.安装MHA环境6.1 安装MHA Node6.2 安装MHA Manager 7.配置MHA环境8.MySQL MHA高可用集群测试8.1 通过VIP连接MySQL8.2模… 文章目录 1.MHA介绍2.MHA组件介绍3.集群规划4.服务器初始化5.MySQL集群部署5.1 安装MySQL集群5.2 配置一主两从5.3 测试MySQL主从5.4 赋予MHA用户连接权限 6.安装MHA环境6.1 安装MHA Node6.2 安装MHA Manager 7.配置MHA环境8.MySQL MHA高可用集群测试8.1 通过VIP连接MySQL8.2模拟故障测试 9.参考文档 1.MHA介绍 MySQL的MHAMaster High Availability是一种用于MySQL高可用性的解决方案旨在提高MySQL数据库集群的可用性和故障恢复能力。MHA主要通过自动故障切换来确保数据库的高可用性它能够在主数据库出现故障时自动将从数据库提升为主数据库以避免长时间的服务中断。 2.MHA组件介绍 MHA ManagerMHA 管理器 功能MHA Manager 是 MHA 的控制中心负责监控 MySQL 主从数据库的健康状态并在主服务器故障时触发自动故障切换。 角色 监控数据库主从节点的状态。 在主服务器发生故障时选择健康的从服务器并提升为新的主服务器。 管理数据库节点的配置如主库和从库的切换。 在主库恢复后自动将恢复的主库重新配置为从库。 部署MHA Manager 通常运行在一个独立的服务器上不与数据库实例共享。 MHA NodeMHA 节点 功能MHA Node 是 MHA 管理器和 MySQL 数据库之间的中介负责与 MHA Manager 进行通信并执行主从切换操作。 角色 向 MHA Manager 上报数据库节点主库和从库的健康状态。 参与故障切换流程包括主库的提升和从库的降级。 部署每个 MySQL 实例主服务器和从服务器都需要安装和配置 MHA Node以便与 MHA Manager 通信。 3.集群规划
主机名IP地址组件(角色)版本test-server-01192.168.40.180MHA Manager0.58test-server-02192.168.40.181MySQLMaster、MHA Node5.7.20、0.58test-server-03192.168.40.182MySQLMaster、MHA Node5.7.20、0.58test-server-04192.168.40.183MySQLMaster、MHA Node5.7.20、0.58
4.服务器初始化
服务器免密 MHA Manager使用SSH登录到主从数据库节点执行故障检测、切换主库等操作包括在故障切换过程中通过SSH访问数据库服务器执行切换操作例如停止服务、修改配置文件等。 #MHA Manager免密
[roottest-server-01 ~]# ssh-keygen -t rsa
[roottest-server-01 ~]# ssh-copy-id 192.168.40.180
[roottest-server-01 ~]# ssh-copy-id 192.168.40.181
[roottest-server-01 ~]# ssh-copy-id 192.168.40.182
[roottest-server-01 ~]# ssh-copy-id 192.168.40.183#MySQL Master
[roottest-server-02 ~]# ssh-keygen -t rsa
[roottest-server-02 ~]# ssh-copy-id 192.168.40.180
[roottest-server-02 ~]# ssh-copy-id 192.168.40.181
[roottest-server-02 ~]# ssh-copy-id 192.168.40.182
[roottest-server-02 ~]# ssh-copy-id 192.168.40.183#MySQL Slave 1
[roottest-server-03 ~]# ssh-keygen -t rsa
[roottest-server-03 ~]# ssh-copy-id 192.168.40.180
[roottest-server-03 ~]# ssh-copy-id 192.168.40.181
[roottest-server-03 ~]# ssh-copy-id 192.168.40.182
[roottest-server-03 ~]# ssh-copy-id 192.168.40.183#MySQL Slave 2
[roottest-server-04 ~]# ssh-keygen -t rsa
[roottest-server-04 ~]# ssh-copy-id 192.168.40.180
[roottest-server-04 ~]# ssh-copy-id 192.168.40.181
[roottest-server-04 ~]# ssh-copy-id 192.168.40.182
[roottest-server-04 ~]# ssh-copy-id 192.168.40.183关闭防火墙
#所有机器
[roottest-server-01 ~]# systemctl stop firewalld ; systemctl disable firewalld配置时间同步
#所有机器
#安装ntpdate命令
[roottest-server-01 ~]# yum install ntpdate -y#跟网络时间做同步
[roottest-server-01 ~]# ntpdate cn.pool.ntp.org#把时间同步做成计划任务
[roottest-server-01 ~]# crontab -e
* * * * * /usr/sbin/ntpdate cn.pool.ntp.org5.MySQL集群部署
MySQL组件下载地址
选择对应版本下载
5.1 安装MySQL集群
三台MySQL节点操作
上传安装包到/usr/local/src目录
[roottest-server02 src]# ll mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
-rw-r--r-- 1 root root 641127384 Feb 19 13:39 mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz解压安装包
[roottest-server02 src]# tar -xvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz移动安装包重新命名
[roottest-server02 src]# mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql5.7设置路径环境变量
[roottest-server02 src]# echo export PATH/usr/local/mysql5.7/bin:$PATH /etc/profile
[roottest-server02 src]# source /etc/profile创建MySQL用户
[roottest-server02 src]# useradd -M -s /sbin/nologin mysql创建MySQL数据目录
[roottest-server02 src]# mkdir -pv /opt/mysqldb/{temp,log,data}赋予MySQL用户权限
[roottest-server02 src]# chown -R mysql:mysql /opt/mysqldb初始化MySQL数据目录
[roottest-server02 src]# mysqld --initialize --datadir/opt/mysqldb/data --usermysql2025-02-19T06:40:36.493733Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2025-02-19T06:40:36.494435Z 0 [ERROR] Cant find error-message file /usr/local/mysql/share/errmsg.sys. Check error-message file location and lc-messages-dir configuration directive.
2025-02-19T06:40:37.775262Z 0 [Warning] InnoDB: New log files created, LSN45790
2025-02-19T06:40:37.983917Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2025-02-19T06:40:38.309684Z 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: 6e2db754-ee8c-11ef-b1a1-000c292b8fdc.
2025-02-19T06:40:38.375782Z 0 [Warning] Gtid table is not ready to be used. Table mysql.gtid_executed cannot be opened.
2025-02-19T06:40:38.378205Z 1 [Note] A temporary password is generated for rootlocalhost: n-HaNrreh2_t#ERROR不需要管出现同上日志代表初始化成功。
n-HaNrreh2_tMySQL初始化之后的密码需要保存下来等会使用配置MySQL
#MySQL Master配置
[roottest-server02 src]# cat /etc/my.cnf
[mysqld]
basedir/usr/local/mysql5.7
usermysql
port3306
datadir/opt/mysqldb/data
log-error/opt/mysqldb/log/err.log
pid-file/opt/mysqldb/temp/mysqld.pid
socket/opt/mysqldb/temp/mysqld.sock
symbolic-links0
server_id1
gtid-modeon
enforce-gtid-consistencytrue
relay_log_purge0
log_slave_updatesON
log_bin/opt/mysqldb/log/binlog
binlog_formatROW[client]
socket/opt/mysqldb/temp/mysqld.sock
default-character-setutf8#MySQL Slave1配置
[roottest-server03 src]# cat /etc/my.cnf
[mysqld]
basedir/usr/local/mysql5.7
usermysql
port3306
datadir/opt/mysqldb/data
log-error/opt/mysqldb/log/err.log
pid-file/opt/mysqldb/temp/mysqld.pid
socket/opt/mysqldb/temp/mysqld.sock
symbolic-links0
server_id2
gtid-modeon
enforce-gtid-consistencytrue
relay_log_purge0
log_slave_updatesON
log_bin/opt/mysqldb/log/binlog
binlog_formatROW[client]
socket/opt/mysqldb/temp/mysqld.sock
default-character-setutf8#MySQL Slave2配置
[roottest-server04 src]# cat /etc/my.cnf
[mysqld]
basedir/usr/local/mysql5.7
usermysql
port3306
datadir/opt/mysqldb/data
log-error/opt/mysqldb/log/err.log
pid-file/opt/mysqldb/temp/mysqld.pid
socket/opt/mysqldb/temp/mysqld.sock
symbolic-links0
server_id3
gtid-modeon
enforce-gtid-consistencytrue
relay_log_purge0
log_slave_updatesON
log_bin/opt/mysqldb/log/binlog
binlog_formatROW[client]
socket/opt/mysqldb/temp/mysqld.sock
default-character-setutf8#server_id三台不能相同数据目录改成自己的创建MySQL启动文件
#三台MySQL节点操作
[roottest-server02 src]# cat /usr/lib/systemd/system/mysqld.service
[Unit]
DescriptionMySQL Server
Documentationman:mysqld(8)
Documentationhttp://dev.mysql.com/doc/refman/en/using-systemd.html
Afternetwork.target
Aftersyslog.target[Install]
WantedBymulti-user.target[Service]
Usermysql
Groupmysql
Typeforking
PIDFile/opt/mysqldb/temp/mysqld.pid
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec0
# Execute pre and post scripts as root
PermissionsStartOnlytrue
# Needed to create system tables
# ExecStartPre/usr/bin/mysqld_pre_systemd
# Start main service
ExecStart/usr/local/mysql5.7/bin/mysqld --daemonize --pid-file/opt/mysqldb/temp/mysqld.pid $MYSQLD_OPTS
# Use this to switch malloc implementation
# EnvironmentFile-/etc/sysconfig/mysql
# Sets open_files_limit
LimitNOFILE 5000
Restarton-failure
RestartPreventExitStatus1
PrivateTmpfalse#目录根据自己情况修改
#赋予执行权限
[roottest-server02 src]# chmod 644 /usr/lib/systemd/system/mysqld.service 启动MySQL
#三台MySQL节点操作
#systemd重新加载配置
[roottest-server02 src]# systemctl daemon-reload#设置开机自启
[roottest-server02 src]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.#重启MySQL
[roottest-server02 src]# systemctl restart mysqld#查看MySQL状态
[roottest-server02 src]# systemctl status mysqld修改MySQL密码
#登录MySQL
[roottest-server02 src]# mysql -uroot -pn-HaNrreh2_t #每台MySQL的密码不同#三台MySQL节点操作
mysql ALTER USER rootlocalhost IDENTIFIED BY 123456;5.2 配置一主两从
授予权限
三台MySQL节点操作
#授予该用户进行主从复制所需的权限
mysql grant replication slave on *.* to repl_user192.168.40.% identified by 123456;
Query OK, 0 rows affected, 1 warning (0.00 sec)#刷新
mysql flush privileges;
Query OK, 0 rows affected (0.01 sec)查看二进制文件名和位置信息
master节点操作
mysql show master status;
---------------------------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
---------------------------------------------------------------------------------------------------
| binlog.000001 | 1467 | | | 6e2db754-ee8c-11ef-b1a1-000c292b8fdc:1-6 |
---------------------------------------------------------------------------------------------------配置从节点
Slave 1和Slave 2执行
#根据自己的信息进行修改
mysql change master to master_host192.168.40.181,master_userrepl_user,master_password123456,master_log_filebinlog.000001,master_log_pos1467;
Query OK, 0 rows affected, 2 warnings (0.03 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 eventMaster_Host: 192.168.40.181Master_User: repl_userMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000001Read_Master_Log_Pos: 1467Relay_Log_File: test-server03-relay-bin.000002Relay_Log_Pos: 317Relay_Master_Log_File: binlog.000001Slave_IO_Running: YesSlave_SQL_Running: Yes#两个YES代表MySQL已经成功设置成了主从读写分离
Slave 1和Slave 2执行
#两个从库必须设置为只读模式
mysql set global read_only1;
Query OK, 0 rows affected (0.00 sec)5.3 测试MySQL主从
MySQL Master操作
CREATE DATABASE test_db;USE test_db;CREATE TABLE test_table (id INT PRIMARY KEY,name VARCHAR(50)
);INSERT INTO test_table (id, name) VALUES (1, John);
INSERT INTO test_table (id, name) VALUES (2, Alice);MySQL Slave 1和Slave 2操作
mysql select * from test_db.test_table;
-----------
| id | name |
-----------
| 1 | John |
| 2 | Alice |
-----------
2 rows in set (0.00 sec)
#出来数据代表主从成功5.4 赋予MHA用户连接权限
Master操作即可
mysql grant all privileges on *.* to mha_user192.168.40.% identified by 123456;
Query OK, 0 rows affected, 1 warning (0.00 sec)#主从复制已完成所以从不用单独操作6.安装MHA环境
安装MHA依赖环境
#四台机器全部操作
[roottest-server-01 ~]# yum install epel-release --nogpgcheck -y[roottest-server-01 ~]# yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN6.1 安装MHA Node
MHA Node下载地址 四台机器全部安装MHA Node
上传部署包到/opt目录
[roottest-server-01 opt]# ll mha4mysql-node-0.58.tar.gz
-rw-r--r-- 1 root root 56220 Feb 20 09:09 mha4mysql-node-0.58.tar.gz生成Makefile
[roottest-server-01 opt]# tar -xf mha4mysql-node-0.58.tar.gz
[roottest-server-01 opt]# cd mha4mysql-node-0.58
[roottest-server-01 mha4mysql-node-0.58]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node编译安装
[roottest-server-01 mha4mysql-node-0.58]# make make install#安装完成之后 /usr/local/bin会生成四个文件
apply_diff_relay_logs
filter_mysqlbinlog
purge_relay_logs
save_binary_logs1.apply_diff_relay_logs #识别差异的中继日志事件并将其差异的事件应用于其他的 slave
2.filter_mysqlbinlog #去除不必要的 ROLLBACK 事件MHA 已不再使用这个工具
3.purge_relay_logs #清除中继日志不会阻塞 SQL 线程
4.save_binary_logs #保存和复制 master 的二进制日志6.2 安装MHA Manager
MHA Manager下载地址
MHA Manager节点操作
上传部署包到opt目录
[roottest-server-01 opt]# ll mha4mysql-manager-0.58.tar.gz
-rw-r--r-- 1 root root 119801 Feb 20 09:37 mha4mysql-manager-0.58.tar.gz生成Makefile
[roottest-server-01 opt]# tar -xf mha4mysql-manager-0.58.tar.gz
[roottest-server-01 opt]# cd mha4mysql-manager-0.58
[roottest-server-01 mha4mysql-manager-0.58]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
- Time::HiRes ...loaded. (1.9725)
- Config::Tiny ...loaded. (2.14)
- Log::Dispatch ...loaded. (2.41)
- Parallel::ForkManager ...loaded. (1.18)
- MHA::NodeConst ...loaded. (0.58)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::manager编译安装
[roottest-server-01 mha4mysql-manager-0.58]# make make install#安装完成之后 /usr/local/bin会生成几个文件
masterha_check_repl
masterha_check_ssh
masterha_check_status
masterha_conf_host
masterha_manager
masterha_master_monitor
masterha_master_switch
masterha_secondary_check
masterha_stop1. masterha_check_repl用于检查 MySQL 主从复制的状态确保主从复制正常运行并进行必要的修复操作。
2. masterha_check_ssh检查 MySQL 主从节点之间的 SSH 连接状态确保 SSH 连接正常这对 MHA 工具的正常操作至关重要。
3. masterha_check_status用于检查 MySQL 主从复制环境的状态包括检查主从复制延迟等信息。
4. masterha_conf_host用于配置 MHA 工具中的主机信息包括主从节点的连接信息等。
5. masterha_manager主要的 MHA 工具用于监控和管理 MySQL 主从复制环境的自动故障转移和故障恢复。
6. masterha_master_monitor用于监控 MySQL 主服务器的状态以及检测主服务器是否发生故障。
7. masterha_master_switch用于手动切换 MySQL 主从复制环境中的主服务器执行主服务器的切换操作。
8. masterha_secondary_check用于检查 MySQL 主从复制中的辅助节点Secondary状态确保辅助节点正常。
9. masterha_stop用于停止 MHA 工具或相关服务的操作。7.配置MHA环境
复制相关脚本到/usr/local/bin目录
[roottest-server-01 mha4mysql-manager-0.58]# cp -r samples/scripts /usr/local/bin/
[roottest-server-01 mha4mysql-manager-0.58]# ll /usr/local/bin/scripts/
total 32
-rwxr-xr-x 1 root root 3648 Feb 20 09:52 master_ip_failover
-rwxr-xr-x 1 root root 9870 Feb 20 09:52 master_ip_online_change
-rwxr-xr-x 1 root root 11867 Feb 20 09:52 power_manager
-rwxr-xr-x 1 root root 1360 Feb 20 09:52 send_report1.master_ip_failover #自动切换时 VIP 管理的脚本
2.master_ip_online_change #在线切换时 vip 的管理
3.power_manager #故障发生后关闭主机的脚本
4.send_report #因故障切换后发送报警的脚本复制自动切换VIP管理脚本到/usr/local/bin目录。使用该脚本管理VIP
[roottest-server-01 mha4mysql-manager-0.58]# cp -r /usr/local/bin/scripts/master_ip_failover /usr/local/bin/
[roottest-server-01 mha4mysql-manager-0.58]# cp -r /usr/local/bin/scripts/master_ip_online_change /usr/local/bin/
#master_ip_online_change脚本没有修改不知道会不会用到但是下面配置中指定了该路径修改脚本内容
注只需要修改$vip $brdc $ifdev即可
[roottest-server-01 mha4mysql-manager-0.58]# cd /usr/local/bin/
#删除原有的 复制下面的直接粘贴即可。VIP根据自己的网段修改
#粘贴的时候记得 vim 编辑文件之后Esc :set paste之后再进行粘贴
[roottest-server-01 bin]# vim 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 192.168.40.200;
my $brdc 192.168.40.255;
my $ifdev ens33;
my $key 1;
my $ssh_start_vip /sbin/ifconfig ens33:$key $vip;
my $ssh_stop_vip /sbin/ifconfig ens33:$key down;
my $exit_code 0;
GetOptions(commands \$command,ssh_users \$ssh_user,orig_master_hosts \$orig_master_host,orig_master_ips \$orig_master_ip,orig_master_porti \$orig_master_port,new_master_hosts \$new_master_host,new_master_ips \$new_master_ip,new_master_porti \$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() {ssh $ssh_user\$orig_master_host $ssh_stop_vip;
}
sub usage {print Usage: master_ip_failover --commandstart|stop|stopssh|status --orig_master_hosthost --orig_master_ipip --orig_master_portport --new_master_hosthost --new_master_ipip --new_master_portport\n;
}创建MHA目录并拷贝文件 master_binlog_dir/opt/mysqldb/data 是MySQL二进制日志文件目录该目录写错会导致MySQL MHA集群VIP飘逸失败 [roottest-server-01 bin]# mkdir /etc/masterha
[roottest-server-01 bin]# cp -r /opt/mha4mysql-manager-0.58/samples/conf/app1.cnf /etc/masterha/
[roottest-server-01 bin]# cat /etc/masterha/app1.cnf
[server default]
manager_log/etc/masterha/manager.log
manager_workdir/etc/masterha/mha
master_binlog_dir/opt/mysqldb/data #指定 MySQL 主服务器的二进制日志文件目录。
master_ip_failover_script/usr/local/bin/master_ip_failover
master_ip_online_change_script/usr/local/bin/master_ip_online_change
usermha_user
password123456
port3306
ping_interval1
remote_workdir/etc/masterha/mha-node
repl_userrepl_user
repl_password123456
secondary_check_script/usr/local/bin/masterha_secondary_check -s 192.168.40.182 -s 192.168.40.183
shutdown_script
ssh_userroot[server1]
hostname192.168.40.181
port3306[server2]
candidate_master1
check_repl_delay0
hostname192.168.40.182
port3306[server3]
hostname192.168.40.183
port3306#配置解释
1.[server default]这是默认服务器配置部分包含了 MHA 管理器的相关配置。
manager_log指定 MHA 管理器的日志文件路径。
manager_workdir指定 MHA 管理器的工作目录。
master_binlog_dir指定 MySQL 主服务器的二进制日志文件目录。
master_ip_failover_script指定 IP 切换脚本用于在主服务器发生故障时执行 IP 切换。
master_ip_online_change_script指定在线 IP 变更脚本。
userMHA 用户名。
passwordMHA 用户密码。
portMySQL 端口号。
ping_interval指定 MHA 管理器检测 MySQL 高可用的间隔时间。
remote_workdir指定节点服务器的工作目录。
repl_userMySQL 复制用户。
repl_passwordMySQL 复制用户密码。
secondary_check_script指定用于检查备用服务器的脚本。
shutdown_script指定关闭脚本。
ssh_user指定用于 SSH 连接的用户名。2.[server1]定义了一个 MySQL 服务器节点其中包含
hostname主 MySQL 服务器的 IP 地址。
portMySQL 端口号。3.[server2]定义了另一个 MySQL 服务器节点标记为候选主服务器包含
candidate_master标记此服务器为候选主服务器。
check_repl_delay检查复制延迟设置为 0。
hostname第二个 MySQL 服务器的 IP 地址。
portMySQL 端口号。4.[server3]定义了另一个 MySQL 服务器节点其中包含
hostname第三个 MySQL 服务器的 IP 地址。
portMySQL 端口号。MySQL Master机器手动开启VIP
#没有ifconfig提前安装,全部机器安装
[roottest-server02 ~]# yum install net-tools -y
#Master执行
[roottest-server02 ~]# /sbin/ifconfig ens33:1 192.168.40.200/24
You have new mail in /var/spool/mail/root
[roottest-server02 ~]# ip a
1: lo: LOOPBACK,UP,LOWER_UP mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope hostvalid_lft forever preferred_lft forever
2: ens33: BROADCAST,MULTICAST,UP,LOWER_UP mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:2b:8f:dc brd ff:ff:ff:ff:ff:ffinet 192.168.40.181/24 brd 192.168.40.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet 192.168.40.200/24 brd 192.168.40.255 scope global secondary ens33:1valid_lft forever preferred_lft foreverinet6 fe80::eb22:4e3:6568:88de/64 scope link noprefixroutevalid_lft forever preferred_lft foreverinet6 fe80::38d1:dfef:9158:a02b/64 scope link tentative noprefixroute dadfailedvalid_lft forever preferred_lft forever
3: docker0: NO-CARRIER,BROADCAST,MULTICAST,UP mtu 1500 qdisc noqueue state DOWN group defaultlink/ether 02:42:d0:ec:ae:7f brd ff:ff:ff:ff:ff:ffinet 172.17.0.1/16 brd 172.17.255.255 scope global docker0valid_lft forever preferred_lft forever#192.168.40.200VIP 已经出现MHA Manager测试
1.Manager节点测试ssh免密
[roottest-server-01 ~]# masterha_check_ssh -conf/etc/masterha/app1.cnfThu Feb 20 10:53:10 2025 - [info] All SSH connection tests passed successfully.
#最后以上输出以上内容代表正常2.Manager节点测试MySQL主从状态
[roottest-server-01 ~]# masterha_check_repl -conf/etc/masterha/app1.cnfMySQL Replication Health is OK.
#最后以上输出以上内容代表正常启动MHA Manager
#启动MHA
[roottest-server-01 ~]# nohup masterha_manager --conf/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover /dev/null /etc/masterha/manager.log 21
[1] 10828#查看MHA状态
[roottest-server-01 ~]# masterha_check_status --conf/etc/masterha/app1.cnf
app1 (pid:10828) is running(0:PING_OK), master:192.168.40.181
#启动成功8.MySQL MHA高可用集群测试
8.1 通过VIP连接MySQL
#通过VIP连接数据库
[roottest-server-01 ~]# mysql -h192.168.40.200 -umha_user -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.20-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type help; or \h for help. Type \c to clear the current input statement.#Master创建数据库
MySQL [(none)] create database test;
Query OK, 1 row affected (0.01 sec)#Slaver 1和Slaver 2查看
mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| test_db |
--------------------
6 rows in set (0.01 sec)#成功8.2模拟故障测试
#查看manager日志观察manager状态
[roottest-server-01 ~]# tailf -n100 /etc/masterha/manager.log停止Master MySQL
[roottest-server02 ~]# systemctl stop mysqldSlave 1节点查看vip是否飘逸
[roottest-server-03 ~]# ip a
1: lo: LOOPBACK,UP,LOWER_UP mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope hostvalid_lft forever preferred_lft forever
2: ens33: BROADCAST,MULTICAST,UP,LOWER_UP mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:b9:50:43 brd ff:ff:ff:ff:ff:ffinet 192.168.40.182/24 brd 192.168.40.255 scope global noprefixroute ens33valid_lft forever preferred_lft foreverinet 192.168.40.200/24 brd 192.168.40.255 scope global secondary ens33:1valid_lft forever preferred_lft foreverinet6 fe80::eb22:4e3:6568:88de/64 scope link tentative noprefixroute dadfailedvalid_lft forever preferred_lft foreverinet6 fe80::38d1:dfef:9158:a02b/64 scope link tentative noprefixroute dadfailedvalid_lft forever preferred_lft foreverinet6 fe80::1c4c:acc5:6453:90f3/64 scope link tentative noprefixroute dadfailedvalid_lft forever preferred_lft forever
3: docker0: NO-CARRIER,BROADCAST,MULTICAST,UP mtu 1500 qdisc noqueue state DOWN group defaultlink/ether 02:42:56:61:72:fd brd ff:ff:ff:ff:ff:ffinet 172.17.0.1/16 brd 172.17.255.255 scope global docker0valid_lft forever preferred_lft forever#此时发现vip 200已经飘逸到了182节点 这个是slave 1的节点manager日志
#manager日志中最下面出现下方日志就证明已经切换成功了
----- Failover Report -----
app1: MySQL Master failover 192.168.40.181(192.168.40.181:3306) to 192.168.40.182(192.168.40.182:3306) succeeded
Master 192.168.40.181(192.168.40.181:3306) is down!
Check MHA Manager logs at test-server-01:/etc/masterha/manager.log for details.
Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.40.181(192.168.40.181:3306)
Selected 192.168.40.182(192.168.40.182:3306) as a new master.
192.168.40.182(192.168.40.182:3306): OK: Applying all logs succeeded.
192.168.40.182(192.168.40.182:3306): OK: Activated master IP address.
192.168.40.183(192.168.40.183:3306): OK: Slave started, replicating from 192.168.40.182(192.168.40.182:3306)
192.168.40.182(192.168.40.182:3306): Resetting slave info succeeded.
Master failover to 192.168.40.182(192.168.40.182:3306) completed successfully.查看MySQL状态
登录MySQL Slave2查看从状态
[roottest-server-04 ~]# mysql -uroot -p123456
mysql show slave status \G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.40.182Master_User: repl_userMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000002Read_Master_Log_Pos: 1700Relay_Log_File: test-server-04-relay-bin.000003Relay_Log_Pos: 744Relay_Master_Log_File: binlog.000002Slave_IO_Running: YesSlave_SQL_Running: Yes#Master_Host已经成功由192.168.40.181切换成了192.168.40.181。到此MHA集群已经成功搭建了故障修复
恢复MySQL
[roottest-server-02 ~]# systemctl start mysqld主数据库查看master状态
mysql show master status;
---------------------------------------------------------------------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
---------------------------------------------------------------------------------------------------------------------------------------------
| binlog.000002 | 1700 | | | 7a69b6e0-eff7-11ef-b307-000c292b8fdc:3-7,
7a7fcdf5-eff7-11ef-ab74-000c29b95043:1-2 |
---------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)#正常写入写入数据的情况下Position会一直变可以和研发沟通空闲时间进行锁库进行配置
1. 锁库命令FLUSH TABLE WITH READ LOCK;
2. 解锁命令 unlock tables;
#配置完解开即可#原master做成Slave数据库
mysql change master to master_host192.168.40.182,master_userrepl_user,master_password123456,master_log_filebinlog.000002,master_log_pos1700;
Query OK, 0 rows affected, 2 warnings (0.03 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 eventMaster_Host: 192.168.40.182Master_User: repl_userMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000002Read_Master_Log_Pos: 1700Relay_Log_File: test-server-02-relay-bin.000002Relay_Log_Pos: 317Relay_Master_Log_File: binlog.000002Slave_IO_Running: YesSlave_SQL_Running: Yes#已经成功变成了Slave,并且成功连接到了新的Master.修改MHA配置 由于停掉了MySQL /etc/masterha/app1.cnf配置里面默认删除一开始定义的[server1]现在新增一下 [roottest-server-01 masterha]# cat /etc/masterha/app1.cnf
[server default]
manager_log/etc/masterha/manager.log
manager_workdir/etc/masterha/mha
master_binlog_dir/opt/mysqldb/data
master_ip_failover_script/usr/local/bin/master_ip_failover
master_ip_online_change_script/usr/local/bin/master_ip_online_change
usermha_user
password123456
port3306
ping_interval1
remote_workdir/etc/masterha/mha-node
repl_userrepl_user
repl_password123456
secondary_check_script/usr/local/bin/masterha_secondary_check -s 192.168.40.182 -s 192.168.40.183
shutdown_script
ssh_userroot[server1]
hostname192.168.40.181
port3306[server2]
candidate_master1
check_repl_delay0
hostname192.168.40.182
port3306[server3]
hostname192.168.40.183
port3306MHA测试
1.Manager节点测试ssh免密
[roottest-server-01 ~]# masterha_check_ssh -conf/etc/masterha/app1.cnfThu Feb 20 10:53:10 2025 - [info] All SSH connection tests passed successfully.
#最后以上输出以上内容代表正常2.Manager节点测试MySQL主从状态
[roottest-server-01 ~]# masterha_check_repl -conf/etc/masterha/app1.cnfMySQL Replication Health is OK.
#最后以上输出以上内容代表正常启动manager
#启动MHA
[roottest-server-01 ~]# nohup masterha_manager --conf/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover /dev/null /etc/masterha/manager.log 21 到这部署就成功了。部署两边就全都懂了 9.参考文档
1.MySQL集群高可用架构之MHA
2.Mysql数据库——MHA高可用配置及故障切换
3.MySQL高可用MHA
