MySQL高可用架构MHA

最新推荐文章于 2024-10-05 10:25:52 发布
最新推荐文章于 2024-10-05 10:25:52 发布
阅读量211 收藏 4
点赞数 7
分类专栏： MySQL 文章标签： mysql 架构 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/evil_wdpp/article/details/132425216
版权

MHA简介

MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本公司的 youshimaton（现就职于 Facebook 公司）开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。
在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。
该软件由两部分组成：MHA Manager（管理节点）和 MHA Node（数据节点）。MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台 slave 节点上。
MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。
目前MHA主要支持一主多从的架构，要搭建MHA, 要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库。

主从规划

IP

主机名

主从

os用户名

os密码

db用户名

db密码

MySQL版本

复制类型

192.168.6.101

node01

主

root

oracle

root

oracle

8.0.33

GTID

192.168.6.102

node02

从

root

oracle

root

oracle

8.0.33

GTID

192.168.6.103

node03

从

root

oracle

root

oracle

8.0.33

GTID

MHA规划

IP

主机名

角色

备注

db用户名

db密码

MySQL版本

MHA版本

192.168.6.101

node01

manager

管理节点

root

oracle

8.0.33

0.58

192.168.6.101

node01

node

数据节点

root

oracle

8.0.33

0.58

192.168.6.102

node02

node

数据节点

root

oracle

8.0.33

0.58

192.168.6.103

node03

node

数据节点

root

oracle

8.0.33

0.58

192.168.6.200

node01

VIP

虚拟IP

 MHA部署与测试

1、配置主机SSH免密登录
2、安装manager和node
3、管理节点配置MHA文件
4、验证SSH互信是否成功
5、验证mysql复制是否成功
6、配置VIP
7、启动MHA manager
8、测试master宕机failover是否成功
9、将原master切换为slave后重新搭建MHA
10、测试主动切换switchover

配置主机SSH免密登录

1、执行ssh-keygen
[root@node01 ~]# ssh-keygen -t rsa
[root@node02 ~]# ssh-keygen -t rsa
[root@node03 ~]# ssh-keygen -t rsa
 
2、执行ssh-copy-id
[root@node01 ~]# ssh-copy-id node01
[root@node02 ~]# ssh-copy-id node02
[root@node03 ~]# ssh-copy-id node03

安装manager和node

1、下载rpm安装包
[root@node01 ~]# wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
[root@node01 ~]# wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm

[root@node02 ~]# wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@node03 ~]# wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm

[root@node01 ~]# ll -h mha4mysql-*
-rw-r--r-- 1 root root 80K Dec  7  2021 mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
-rw-r--r-- 1 root root 36K Dec  7  2021 mha4mysql-node-0.58-0.el7.centos.noarch.rpm

[root@node02 ~]# ll -h mha4mysql-node-0.58-0.el7.centos.noarch.rpm 
-rw-r--r-- 1 root root 36K Dec  7  2021 mha4mysql-node-0.58-0.el7.centos.noarch.rpm

[root@node03 ~]# ll -h mha4mysql-node-0.58-0.el7.centos.noarch.rpm 
-rw-r--r-- 1 root root 36K Dec  7  2021 mha4mysql-node-0.58-0.el7.centos.noarch.rpm

2、添加epel仓库
[root@node01 ~]# yum install epel-release -y
[root@node02 ~]# yum install epel-release -y
[root@node03 ~]# yum install epel-release -y

3、管理节点安装node和manager
先安装node再安装manager
[root@node01 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@node01 ~]# yum install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm -y

4、数据节点安装node
[root@node02 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@node03 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

管理节点配置MHA文件

1、创建mha工作目录
[root@node01 ~]# mkdir -p /data/mastermha
[root@node01 ~]# mkdir -p /data/mastermha/app1

2、创建配置文件
[root@node01 ~]# vim /data/mastermha/app1.cnf
[server default]

#mha工作目录
manager_workdir=/data/mastermha/app1
manager_log=/data/mastermha/app1/manager.log
master_binlog_dir=/data/mysql/3306/data

#failover
master_ip_failover_script=/data/mastermha/master_ip_failover
#switchover
master_ip_online_change_script=/data/mastermha/master_ip_online_change

#ssh免密
ssh_user=root

#mysql用户
user=root
password=oracle

#每隔3秒检测主库心跳，最多四次机会，如果都没有心跳，主库宕机
ping_interval=3
ping_type=INSERT

#复制用户
repl_user=repl
repl_password=oracle

#主节点
[server1]
hostname=node01
ip=192.168.6.101
port=3306
#设置为候选master，如果设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个主库不是集群中事件最新的slave
candidate_master=1

#从节点
[server2]
hostname=node02
ip=192.168.6.102
port=3306
candidate_master=1
check_repl_delay=0

#从节点
[server3]
hostname=node03
ip=192.168.6.103
port=3306
#不选择作为主库
no_master=1

验证SSH互信是否成功

1、MHA工具
[root@node01 ~]# masterha_
masterha_check_repl       masterha_check_status     masterha_manager          masterha_master_switch    masterha_stop
masterha_check_ssh        masterha_conf_host        masterha_master_monitor   masterha_secondary_check  

2、验证SSH互信是否成功
[root@node01 ~]# masterha_check_ssh --conf=/data/mastermha/app1.cnf 
Mon Aug 21 12:36:14 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Aug 21 12:36:14 2023 - [info] Reading application default configuration from /data/mastermha/app1.cnf..
Mon Aug 21 12:36:14 2023 - [info] Reading server configuration from /data/mastermha/app1.cnf..
Mon Aug 21 12:36:14 2023 - [info] Starting SSH connection tests..
Mon Aug 21 12:36:15 2023 - [debug] 
Mon Aug 21 12:36:14 2023 - [debug]  Connecting via SSH from root@node01(192.168.6.101:22) to root@node02(192.168.6.102:22)..
Mon Aug 21 12:36:14 2023 - [debug]   ok.
Mon Aug 21 12:36:14 2023 - [debug]  Connecting via SSH from root@node01(192.168.6.101:22) to root@node03(192.168.6.103:22)..
Mon Aug 21 12:36:14 2023 - [debug]   ok.
Mon Aug 21 12:36:15 2023 - [debug] 
Mon Aug 21 12:36:14 2023 - [debug]  Connecting via SSH from root@node02(192.168.6.102:22) to root@node01(192.168.6.101:22)..
Mon Aug 21 12:36:14 2023 - [debug]   ok.
Mon Aug 21 12:36:14 2023 - [debug]  Connecting via SSH from root@node02(192.168.6.102:22) to root@node03(192.168.6.103:22)..
Mon Aug 21 12:36:15 2023 - [debug]   ok.
Mon Aug 21 12:36:16 2023 - [debug] 
Mon Aug 21 12:36:15 2023 - [debug]  Connecting via SSH from root@node03(192.168.6.103:22) to root@node01(192.168.6.101:22)..
Mon Aug 21 12:36:15 2023 - [debug]   ok.
Mon Aug 21 12:36:15 2023 - [debug]  Connecting via SSH from root@node03(192.168.6.103:22) to root@node02(192.168.6.102:22)..
Mon Aug 21 12:36:15 2023 - [debug]   ok.
Mon Aug 21 12:36:16 2023 - [info] All SSH connection tests passed successfully.

验证复制是否成功

1、验证复制失败
[root@node01 ~]# masterha_check_repl --conf=/data/mastermha/app1.cnf 
Mon Aug 21 12:39:21 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Aug 21 12:39:21 2023 - [info] Reading application default configuration from /data/mastermha/app1.cnf..
Mon Aug 21 12:39:21 2023 - [info] Reading server configuration from /data/mastermha/app1.cnf..
Mon Aug 21 12:39:21 2023 - [info] MHA::MasterMonitor version 0.58.
Creating directory /data/mastermha/app1.. done.
Mon Aug 21 12:39:22 2023 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln188] There is no alive server. We can't do failover
Mon Aug 21 12:39:22 2023 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 329.
Mon Aug 21 12:39:22 2023 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Mon Aug 21 12:39:22 2023 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

2、解决方案
8.0需要修改mysql用户'root'@'192.168.6.%'和复制用户'repl'@'192.168.6.%'密码插件，5.7不需要修改，插件默认是mysql_native_password
root@node01 [(none)]> select user,host,plugin from mysql.user;
+------------------+-------------+-----------------------+
| user             | host        | plugin                |
+------------------+-------------+-----------------------+
| clone            | %           | caching_sha2_password |
| pmm              | 192.168.6.% | mysql_native_password |
| repl             | 192.168.6.% | caching_sha2_password |
| root             | 192.168.6.% | caching_sha2_password |
| mysql.infoschema | localhost   | caching_sha2_password |
| mysql.session    | localhost   | caching_sha2_password |
| mysql.sys        | localhost   | caching_sha2_password |
| root             | localhost   | mysql_native_password |
+------------------+-------------+-----------------------+
8 rows in set (0.03 sec)

root@node01 [mysql]> alter user 'repl'@'192.168.6.%' identified with mysql_native_password by 'oracle';
Query OK, 0 rows affected (1.26 sec)

root@node01 [mysql]> flush privileges;
Query OK, 0 rows affected (0.24 sec)

root@node01 [mysql]> alter user 'root'@'192.168.6.%' identified with mysql_native_password by 'oracle';
Query OK, 0 rows affected (0.05 sec)

root@node01 [mysql]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

root@node01 [mysql]> select user,host,plugin from mysql.user;
+------------------+-------------+-----------------------+
| user             | host        | plugin                |
+------------------+-------------+-----------------------+
| clone            | %           | caching_sha2_password |
| pmm              | 192.168.6.% | mysql_native_password |
| repl             | 192.168.6.% | mysql_native_password |
| root             | 192.168.6.% | mysql_native_password |
| mysql.infoschema | localhost   | caching_sha2_password |
| mysql.session    | localhost   | caching_sha2_password |
| mysql.sys        | localhost   | caching_sha2_password |
| root             | localhost   | mysql_native_password |
+------------------+-------------+-----------------------+
8 rows in set (0.02 sec)

3、验证复制成功
[root@node01 ~]# masterha_check_repl --conf=/data/mastermha/app1.cnf 
Mon Aug 21 13:58:44 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Aug 21 13:58:44 2023 - [info] Reading application default configuration from /data/mastermha/app1.cnf..
Mon Aug 21 13:58:44 2023 - [info] Reading server configuration from /data/mastermha/app1.cnf..
Mon Aug 21 13:58:44 2023 - [info] MHA::MasterMonitor version 0.58.
Mon Aug 21 13:58:46 2023 - [info] GTID failover mode = 1
Mon Aug 21 13:58:46 2023 - [info] Dead Servers:
Mon Aug 21 13:58:46 2023 - [info] Alive Servers:
Mon Aug 21 13:58:46 2023 - [info]   node01(192.168.6.101:3306)
Mon Aug 21 13:58:46 2023 - [info]   node02(192.168.6.102:3306)
Mon Aug 21 13:58:46 2023 - [info]   node03(192.168.6.103:3306)
Mon Aug 21 13:58:46 2023 - [info] Alive Slaves:
Mon Aug 21 13:58:46 2023 - [info]   node02(192.168.6.102:3306)  Version=8.0.33 (oldest major version between slaves) log-bin:enabled
Mon Aug 21 13:58:46 2023 - [info]     GTID ON
Mon Aug 21 13:58:46 2023 - [info]     Replicating from 192.168.6.101(192.168.6.101:3306)
Mon Aug 21 13:58:46 2023 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Aug 21 13:58:46 2023 - [info]   node03(192.168.6.103:3306)  Version=8.0.33 (oldest major version between slaves) log-bin:enabled
Mon Aug 21 13:58:46 2023 - [info]     GTID ON
Mon Aug 21 13:58:46 2023 - [info]     Replicating from 192.168.6.101(192.168.6.101:3306)
Mon Aug 21 13:58:46 2023 - [info]     Not candidate for the new Master (no_master is set)
Mon Aug 21 13:58:46 2023 - [info] Current Alive Master: node01(192.168.6.101:3306)
Mon Aug 21 13:58:46 2023 - [info] Checking slave configurations..
Mon Aug 21 13:58:46 2023 - [info]  read_only=1 is not set on slave node02(192.168.6.102:3306).
Mon Aug 21 13:58:46 2023 - [info]  read_only=1 is not set on slave node03(192.168.6.103:3306).
Mon Aug 21 13:58:46 2023 - [info] Checking replication filtering settings..
Mon Aug 21 13:58:46 2023 - [info]  binlog_do_db= , binlog_ignore_db= 
Mon Aug 21 13:58:46 2023 - [info]  Replication filtering check ok.
Mon Aug 21 13:58:46 2023 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Mon Aug 21 13:58:46 2023 - [info] Checking SSH publickey authentication settings on the current master..
Mon Aug 21 13:58:46 2023 - [info] HealthCheck: SSH to node01 is reachable.
Mon Aug 21 13:58:46 2023 - [info] 
node01(192.168.6.101:3306) (current master)
 +--node02(192.168.6.102:3306)
 +--node03(192.168.6.103:3306)

Mon Aug 21 13:58:46 2023 - [info] Checking replication health on node02..
Mon Aug 21 13:58:46 2023 - [info]  ok.
Mon Aug 21 13:58:46 2023 - [info] Checking replication health on node03..
Mon Aug 21 13:58:46 2023 - [info]  ok.
Mon Aug 21 13:58:46 2023 - [warning] master_ip_failover_script is not defined.
Mon Aug 21 13:58:46 2023 - [warning] shutdown_script is not defined.
Mon Aug 21 13:58:46 2023 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

配置VIP

1、手动添加虚拟ip
[root@node01 ~]# ifconfig ens33:88 192.168.6.200
[root@node01 ~]# ip addr | grep ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    inet 192.168.6.101/24 brd 192.168.6.255 scope global noprefixroute ens33
    inet 192.168.6.200/24 brd 192.168.6.255 scope global secondary ens33:88

2、使用VIP连接数据库
[root@node01 ~]# mysql -h192.168.6.200 -uroot -poracle
root@192.168.6.200 [(none)]> select user();
+-------------+
| user()      |
+-------------+
| root@node01 |
+-------------+
1 row in set (0.01 sec)

root@192.168.6.200 [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|        1001 |
+-------------+
1 row in set (0.00 sec)

root@192.168.6.200 [(none)]> show master status;
+---------------+----------+--------------+------------------+---------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+---------------+----------+--------------+------------------+---------------------------------------------+
| binlog.000017 |   341388 |              |                  | b0ae4cca-32af-11ee-8ff1-000c29004cdf:1-1342 |
+---------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.01 sec)

启动MHA manager

1、后台启动manager
[root@node01 ~]# nohup masterha_manager --conf=/data/mastermha/app1.cnf &

2、查看mha状态
[root@node01 ~]# masterha_check_status --conf=/data/mastermha/app1.cnf 
app1 (pid:10126) is running(0:PING_OK), master:node01

3、查看日志
[root@node01 ~]# tail -100f /data/mastermha/app1/manager.log 
Mon Aug 21 14:23:44 2023 - [info] MHA::MasterMonitor version 0.58.
Mon Aug 21 14:23:46 2023 - [info] GTID failover mode = 1
Mon Aug 21 14:23:46 2023 - [info] Dead Servers:
Mon Aug 21 14:23:46 2023 - [info] Alive Servers:
Mon Aug 21 14:23:46 2023 - [info]   node01(192.168.6.101:3306)
Mon Aug 21 14:23:46 2023 - [info]   node02(192.168.6.102:3306)
Mon Aug 21 14:23:46 2023 - [info]   node03(192.168.6.103:3306)
Mon Aug 21 14:23:46 2023 - [info] Alive Slaves:
Mon Aug 21 14:23:46 2023 - [info]   node02(192.168.6.102:3306)  Version=8.0.33 (oldest major version between slaves) log-bin:enabled
Mon Aug 21 14:23:46 2023 - [info]     GTID ON
Mon Aug 21 14:23:46 2023 - [info]     Replicating from 192.168.6.101(192.168.6.101:3306)
Mon Aug 21 14:23:46 2023 - [info]     Primary candidate for the new Master (candidate_master is set)
Mon Aug 21 14:23:46 2023 - [info]   node03(192.168.6.103:3306)  Version=8.0.33 (oldest major version between slaves) log-bin:enabled
Mon Aug 21 14:23:46 2023 - [info]     GTID ON
Mon Aug 21 14:23:46 2023 - [info]     Replicating from 192.168.6.101(192.168.6.101:3306)
Mon Aug 21 14:23:46 2023 - [info]     Not candidate for the new Master (no_master is set)
Mon Aug 21 14:23:46 2023 - [info] Current Alive Master: node01(192.168.6.101:3306)
Mon Aug 21 14:23:46 2023 - [info] Checking slave configurations..
Mon Aug 21 14:23:46 2023 - [info]  read_only=1 is not set on slave node02(192.168.6.102:3306).
Mon Aug 21 14:23:46 2023 - [info]  read_only=1 is not set on slave node03(192.168.6.103:3306).
Mon Aug 21 14:23:46 2023 - [info] Checking replication filtering settings..
Mon Aug 21 14:23:46 2023 - [info]  binlog_do_db= , binlog_ignore_db= 
Mon Aug 21 14:23:46 2023 - [info]  Replication filtering check ok.
Mon Aug 21 14:23:46 2023 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Mon Aug 21 14:23:46 2023 - [info] Checking SSH publickey authentication settings on the current master..
Mon Aug 21 14:23:46 2023 - [info] HealthCheck: SSH to node01 is reachable.
Mon Aug 21 14:23:46 2023 - [info] 
node01(192.168.6.101:3306) (current master)
 +--node02(192.168.6.102:3306)
 +--node03(192.168.6.103:3306)

Mon Aug 21 14:23:46 2023 - [info] Checking master_ip_failover_script status:
Mon Aug 21 14:23:46 2023 - [info]   /data/mastermha/master_ip_failover --command=status --ssh_user=root --orig_master_host=node01 --orig_master_ip=192.168.6.101 --orig_master_port=3306 


IN SCRIPT TEST====/sbin/ifconfig ens33:88 down==/sbin/ifconfig ens33:88 192.168.6.111/24===

Checking the Status of the script.. OK 
Mon Aug 21 14:23:46 2023 - [info]  OK.
Mon Aug 21 14:23:46 2023 - [warning] shutdown_script is not defined.
Mon Aug 21 14:23:46 2023 - [info] Set master ping interval 3 seconds.
Mon Aug 21 14:23:46 2023 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Mon Aug 21 14:23:46 2023 - [info] Starting ping health check on node01(192.168.6.101:3306)..
Mon Aug 21 14:23:48 2023 - [info] Ping(INSERT) succeeded, waiting until MySQL doesn't respond..

测试master宕机failover是否成功

1、修改配置文件master_ip_failover
[root@node01 ~]# vim /data/mastermha/master_ip_failover 
my $vip = '192.168.6.200/24';
my $key = '88';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";

2、添加执行权限
[root@node01 ~]# chmod +x /data/mastermha/master_ip_failover

3、关闭MySQL
[root@node01 ~]# systemctl stop mysqld
[root@node01 ~]# masterha_check_status --conf=/data/mastermha/app1.cnf 
app1 is stopped(2:NOT_RUNNING).
  
4、查看日志，failover发生自动故障转移，node02提升为master
[root@node01 ~]# tail -100f /data/mastermha/app1/manager.log 

----- Failover Report -----

app1: MySQL Master failover node01(192.168.6.101:3306) to node02(192.168.6.102:3306) succeeded

Master node01(192.168.6.101:3306) is down!

Check MHA Manager logs at node01:/data/mastermha/app1/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on node01(192.168.6.101:3306)
Selected node02(192.168.6.102:3306) as a new master.
node02(192.168.6.102:3306): OK: Applying all logs succeeded.
node02(192.168.6.102:3306): OK: Activated master IP address.
node03(192.168.6.103:3306): OK: Slave started, replicating from node02(192.168.6.102:3306)
node02(192.168.6.102:3306): Resetting slave info succeeded.
Master failover to node02(192.168.6.102:3306) completed successfully.

5、可以看到vip连接的是node02节点
root@192.168.6.200 [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|        1002 |
+-------------+
1 row in set (0.00 sec)

root@192.168.6.200 [(none)]> show processlist;
+----+-----------------+--------------+------+------------------+------+-----------------------------------------------------------------+------------------+
| Id | User            | Host         | db   | Command          | Time | State                                                           | Info             |
+----+-----------------+--------------+------+------------------+------+-----------------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost    | NULL | Daemon           | 2593 | Waiting on empty queue                                          | NULL             |
|  8 | root            | node02:56284 | NULL | Sleep            | 1217 |                                                                 | NULL             |
| 29 | repl            | node03:60278 | NULL | Binlog Dump GTID | 1105 | Source has sent all binlog to replica; waiting for more updates | NULL             |
| 31 | root            | node01:41758 | NULL | Query            |    0 | init                                                            | show processlist |
+----+-----------------+--------------+------+------------------+------+-----------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

将原master切换为slave后重新搭建MHA

1、node02升级成主库后，将node01切换为slave
启动mysql
[root@node01 ~]# systemctl start mysqld

2、将node01切换为slave
root@node01 [(none)]> change master to
    ->     master_host='192.168.6.102',
    ->     master_port=3306,
    ->     master_user='repl',
    ->     master_password='oracle',
    ->     master_auto_position=1;
Query OK, 0 rows affected, 7 warnings (0.18 sec)

root@node01 [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)        

root@node01 [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.6.102
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000006
          Read_Master_Log_Pos: 175901
               Relay_Log_File: node01-relay-bin.000002
                Relay_Log_Pos: 411
        Relay_Master_Log_File: binlog.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                       
root@192.168.6.200 [(none)]> show processlist;
+----+-----------------+--------------+------+------------------+------+-----------------------------------------------------------------+------------------+
| Id | User            | Host         | db   | Command          | Time | State                                                           | Info             |
+----+-----------------+--------------+------+------------------+------+-----------------------------------------------------------------+------------------+
|  5 | event_scheduler | localhost    | NULL | Daemon           | 2957 | Waiting on empty queue                                          | NULL             |
|  8 | root            | node02:56284 | NULL | Sleep            |  339 |                                                                 | NULL             |
| 29 | repl            | node03:60278 | NULL | Binlog Dump GTID | 1469 | Source has sent all binlog to replica; waiting for more updates | NULL             |
| 31 | root            | node01:41758 | NULL | Query            |    0 | init                                                            | show processlist |
| 34 | repl            | node01:42764 | NULL | Binlog Dump GTID |   17 | Source has sent all binlog to replica; waiting for more updates | NULL             |
+----+-----------------+--------------+------+------------------+------+-----------------------------------------------------------------+------------------+
5 rows in set (0.01 sec)

3、修改配置文件，当前node02是master，node01是slave，重新搭建MHA
[root@node01 ~]# vim /data/mastermha/app1.cnf 
[server1]
hostname=node02
ip=192.168.6.102

[server2]
hostname=node01
ip=192.168.6.101

4、删除app1.failover.complete文件，否则启动manager报错
[root@node01 ~]# tail -100f /data/mastermha/app1/manager.log
Mon Aug 21 16:06:03 2023 - [error][/usr/share/perl5/vendor_perl/MHA/MasterFailover.pm, ln310] Last failover was done at 2023/08/21 14:48:23. Current time is too early to do failover again. If you want to do failover, manually remove /data/mastermha/app1/app1.failover.complete and run this script again.
Mon Aug 21 16:06:03 2023 - [error][/usr/share/perl5/vendor_perl/MHA/ManagerUtil.pm, ln177] Got ERROR:  at /usr/bin/masterha_manager line 65.

测试主动切换switchover

1、切换前需要关闭manager
[root@node01 ~]# masterha_stop --conf=/data/mastermha/app1.cnf
MHA Manager is not running on app1(2:NOT_RUNNING).

2、修改master_ip_online_change配置文件
[root@node01 ~]# vim/data/mastermha/master_ip_online_change
my $vip = '192.168.6.200/24';
my $key = '88';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";

3、将node02切换为从库，node01提升为主库
[root@node01 ~]# masterha_master_switch --master_state=alive --conf=/data/mastermha/app1.cnf --new_master_host=node01 --orig_master_is_new_slave  --running_updates_limit=10000

IN SCRIPT TEST====/sbin/ifconfig ens33:88 down==/sbin/ifconfig ens33:88 192.168.6.200/24===

Enabling the VIP - 192.168.6.200/24 on the new master - node01 
Mon Aug 21 18:05:48 2023 - [info]  ok.
Mon Aug 21 18:05:48 2023 - [info] Setting read_only=0 on node01(192.168.6.101:3306)..
Mon Aug 21 18:05:48 2023 - [info]  ok.
Mon Aug 21 18:05:48 2023 - [info] 
Mon Aug 21 18:05:48 2023 - [info] * Switching slaves in parallel..
Mon Aug 21 18:05:48 2023 - [info] 
Mon Aug 21 18:05:48 2023 - [info] -- Slave switch on host node03(192.168.6.103:3306) started, pid: 72129
Mon Aug 21 18:05:48 2023 - [info] 
Mon Aug 21 18:05:49 2023 - [info] Log messages from node03 ...
Mon Aug 21 18:05:49 2023 - [info] 
Mon Aug 21 18:05:48 2023 - [info]  Waiting to execute all relay logs on node03(192.168.6.103:3306)..
Mon Aug 21 18:05:48 2023 - [info]  master_pos_wait(binlog.000008:237) completed on node03(192.168.6.103:3306). Executed 0 events.
Mon Aug 21 18:05:48 2023 - [info]   done.
Mon Aug 21 18:05:48 2023 - [info]  Resetting slave node03(192.168.6.103:3306) and starting replication from the new master node01(192.168.6.101:3306)..
Mon Aug 21 18:05:48 2023 - [info]  Executed CHANGE MASTER.
Mon Aug 21 18:05:48 2023 - [info]  Slave started.
Mon Aug 21 18:05:49 2023 - [info] End of log messages from node03 ...
Mon Aug 21 18:05:49 2023 - [info] 
Mon Aug 21 18:05:49 2023 - [info] -- Slave switch on host node03(192.168.6.103:3306) succeeded.
Mon Aug 21 18:05:49 2023 - [info] Unlocking all tables on the orig master:
Mon Aug 21 18:05:49 2023 - [info] Executing UNLOCK TABLES..
Mon Aug 21 18:05:49 2023 - [info]  ok.
Mon Aug 21 18:05:49 2023 - [info] Starting orig master as a new slave..
Mon Aug 21 18:05:49 2023 - [info]  Resetting slave node02(192.168.6.102:3306) and starting replication from the new master node01(192.168.6.101:3306)..
Mon Aug 21 18:05:49 2023 - [info]  Executed CHANGE MASTER.
Mon Aug 21 18:05:49 2023 - [info]  Slave started.
Mon Aug 21 18:05:49 2023 - [info] All new slave servers switched successfully.
Mon Aug 21 18:05:49 2023 - [info] 
Mon Aug 21 18:05:49 2023 - [info] * Phase 5: New master cleanup phase..
Mon Aug 21 18:05:49 2023 - [info] 
Mon Aug 21 18:05:49 2023 - [info]  node01: Resetting slave info succeeded.
Mon Aug 21 18:05:49 2023 - [info] Switching master to node01(192.168.6.101:3306) completed successfully.

root@192.168.6.200 [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|        1001 |
+-------------+
1 row in set (0.00 sec)

root@192.168.6.200 [(none)]> show processlist;
+------+-----------------+--------------+------+------------------+------+-----------------------------------------------------------------+------------------+
| Id   | User            | Host         | db   | Command          | Time | State                                                           | Info             |
+------+-----------------+--------------+------+------------------+------+-----------------------------------------------------------------+------------------+
|    5 | event_scheduler | localhost    | NULL | Daemon           | 4240 | Waiting on empty queue                                          | NULL             |
| 1553 | root            | node01:51308 | NULL | Sleep            | 1177 |                                                                 | NULL             |
| 2831 | repl            | node03:44486 | NULL | Binlog Dump GTID |   38 | Source has sent all binlog to replica; waiting for more updates | NULL             |
| 2832 | repl            | node02:47518 | NULL | Binlog Dump GTID |   37 | Source has sent all binlog to replica; waiting for more updates | NULL             |
| 2835 | root            | node01:58570 | NULL | Query            |    0 | init                                                            | show processlist |
+------+-----------------+--------------+------+------------------+------+-----------------------------------------------------------------+------------------+
5 rows in set (0.00 sec)

4、修改配置文件
[root@node01 ~]# vim /data/mastermha/app1.cnf 

[server1]
hostname=node01
ip=192.168.6.101

[server2]
hostname=node02
ip=192.168.6.102
MySQL高可用架构MHA软件包及脚本
12-21
MySQL高可用架构（MHA，Master High Availability）是一款用于MySQL集群的开源工具，旨在提高数据库系统的可用性和稳定性。MHA的主要目标是通过监控主库（Master）的状态，并在主库出现故障时，自动将从库（Slave）...
分布式设计与开发（五）------数据库高可用架构
热门推荐
走向架构师之路
08-26 1万+
数据库高可用架构对于我们这些应用端开发的人来说是一个比较陌生的领域，是在具体的数据库产品之上搭建的环境，需要像DBA这样对数据库产品有足够的了解才能有所涉及，虽然不能深入其中，但可以通过一些经典的高可用架构学习其中的思想。就我所了解到的有以下几种：MySQL ReplicationMySQL ClusterOracle RACIBM HACMPOracle ASMMySQL ReplicationMySQL Replication就是通过异步复制多个copy以达到提高可用性的目的，常规的复制架构有以下几种：
数据库高可用架构了解一下
飘渺Jam的博客
11-15 3575
是时候关注我们一波了看多了应用服务的高可用架构，我们来看看数据库的高可用吧。数据存储高可用的方案本质都是通过将数据复制到多个存储设备，通过数据冗余的方式来实现高可用。常见...
2024了还有人不知道MySQL的高可用三大架构？？？
最新发布
X2512261315的博客
10-05 2262
什么是高可用？高可用：高可用是系统所能提供无故障服务的一种能力。一般来说，系统至少要达到 4 个 9999，否则用户体验会非常差。4 个 9 对于生产环境影响还比较大，5 个 9 的要求又太高，于是一些云服务厂商提出来 99.995%高可用是系统所能提供无故障服务的一种能力，度量单位是几个 9；线上系统高可用目标应不低于 99.995%，否则系统频繁宕机，用户体验不好；高可用实现基础是：冗余 + 故障转移；无状态服务的高可用设计较为简单，直接故障转移或剔除就行；
数据库高可用架构(MySQL、Oracle、MongoDB、Redis)
weixin_33901641的博客
06-20 391
一、MySQLMySQL小型高可用架构方案：MySQL双主、主从 + Keepalived主从自动切换服务器资源：两台PC Server优点：架构简单，节省资源缺点：无法线性扩展，主从失败之后需要手动恢复主从架构MySQL中型高可用架构方案：MMM + MySQL双主 + 多从高可用方案服务器资源：1、至少五台PC Server，2台MySQL主库，2台MySQL从库...
MySQL高可用架构MHA搭建和测试.docx
07-29
MySQL高可用架构MHA（Master High Availability）是一种用于提高MySQL数据库服务稳定性和容错性的解决方案。在Linux环境下，通过搭建一主两从的三台服务器集群，可以实现当主节点故障时，系统能自动将从节点提升为主...
MySQL高可用架构MHA 搭建测试文档_v1.docx
04-23
总的来说，MHA结合Keepalived构建的MySQL高可用架构提供了强大的数据库冗余和故障恢复能力，对于业务连续性和数据安全性至关重要。在企业环境中，这种架构能有效降低因数据库故障导致的业务损失，提升系统的整体稳定...
MySQL高可用架构MHA部署与配置详解
"MySQL高可用架构MHA通过使用MHA Manager实现主从切换，确保数据库服务的高可用性。" 在MySQL环境中，为了保障数据服务的持续性和稳定性，高可用架构(High Availability, HA)是非常重要的。MHA(MySQL High ...
MySQL数据库高可用性架构
weixin_30709635的博客
05-02 123
转载https://www.2cto.com/database/201612/572250.html 一、MHA MHA ：mysql-master-ha：mysql master 高可用性管理工具 通过管理节点自动化主服务器故障转移，快速将从服务器晋级为主服务器（通常10- 30s），而不影响复制的一致性，使用与任何存储引擎。 管理节点需要ssh连接mysql服务器，高可用方面是一个相...
【酷哥说库】数据库高可用架构
Gauss松鼠会
11-21 1025
计划外宕机？NO！不接受！宕机的后果很严重。使数据库具有高可用性是重中之重。openGauss数据库针对高可用架构做了很多优化，提升了不少能力。那么今天酷哥跟大家聊一聊数据库高可用架构有哪些，各有什么优缺点～
分布式数据库架构：高可用、高性能的数据存储
xuxian的博客
08-22 746
在现代信息时代，数据是企业发展的核心。为了支持海量数据的存储、高并发访问以及保证数据的可靠性，分布式数据库架构应运而生。分布式数据库架构是一种将数据存储在多个物理节点上，并通过一系列复杂的协调和管理机制来提供高可用性和高性能的数据存储方案。它不仅解决了传统单机数据库的瓶颈问题，还保证了数据的安全和可靠性。
数据库高可用架构设计，看这篇就够了！
周沐子
10-12 963
你好，我是沐子。 又赶上一年一度的金九银十的日子，这段期间的招聘岗位相对前几个月会多些，如果在目前公司没有进步、没有前途时，这段时间可以准备一下，去外面看看机会。不过在外面找工作时，可以提前在网上看看招聘信息，看看自己是否达到公司要求。如果多看下高薪资的技术人员招聘要求时，就会发现对三高都有一定的要求，比如下面一家公司的要求就对高并发、高负载和高可用性系统设计要有开发经验。 现实是我们大部分的公司都很少会遇到三高的场景，即很少有这方面的设计开发经验，不过我们可以提前学习三高的方案，尽量把这些方案用在工作上
高可用架构设计
sinat_34814635的博客
04-02 2767
存储高可用方案的本质都是通过将数据复制到多个存储设备，通过数据冗余的方式来实现高可用，其复杂性主要体现在如何应对复制延迟和中断导致的数据不一致问题。因此，对任何一个高可用存储方案，我们需要从以下几个方面去进行思考和分析：数据如何复制？各个节点的职责是什么？如何应对复制延迟？如何应对复制中断？常见的高可用存储架构有主备、主从、主主、集群、分区，每一种又可以根据业务的需求进行一些特殊的定制化功能，由此衍生出更多的变种。
mysql高可用架构mha
08-30
MHA（Master High Availability）是一种用于MySQL数据库的高可用架构解决方案。它由日本DeNA公司开发，旨在实现MySQL主从复制的自动故障转移和故障恢复。 MHA架构通常由以下几个组件组成： 1. MHA Manager：负责...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值
>