MHA简介
- MHA(Master High Availability)目前在MySQL高可用方面是一个相对成熟的解决方案,它由日本DeNA公司youshimaton(现就职于Facebook公司)开发,是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。
- 在MySQL故障切换过程中,MHA能做到在0~30秒之内自动完成数据库的故障切换操作,并且在进行故障切换的过程中,MHA能在最大程度上保证数据的一致性,以达到真正意义上的高可用。
- 该软件由两部分组成:MHA Manager(管理节点)和MHA Node(数据节点)。MHAManager可以单独部署在一台独立的机器上管理多个master-slave集群,也可以部署在一台slave节点上。MHANode运行在每台MySQL服务器上,MHAManager会定时探测集群中的master节点,当master出现故障时,它可以自动将最新数据的slave提升为新的master,然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。
- 目前MHA主要支持一主多从的架构,要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器,一主二从,即一台充当master,一台充当备用master,另外一台充当从库
实验环境
主机 IP 角色
server3 172.25.26.1 master (主)
server4 172.25.26.2 slave(备master)
server5 172.25.26.3 slave
server6 172.25.26.4 MHA
一、基于MHA的MySQL高可用架构搭建
1.首先配置好,基于GTID的主从复制(一主两从)
(1)编辑配置文件,然后再重启mysqld(server1,server2,server3一样)
[root@server1 ~]# vim /etc/my.cnf
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=binlog
log_slave_updates=ON
[root@server1 ~]# systemctl restart mysqld
[root@server2 ~]# vim /etc/my.cnf
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=binlog
log_slave_updates=ON
[root@server2 ~]# systemctl restart mysqld
[root@server3 ~]# vim /etc/my.cnf
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
log_bin=binlog
log_slave_updates=ON
[root@server3 ~]# systemctl restart mysqld
(2)server3(master)
创建授权用户,安装插件
mysql> grant replication slave on *.* to hui@'172.25.26.%' identified by 'Aa316256.';
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.07 sec)
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.07 sec)
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=10000000000000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%rpl%';
+-------------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------------+----------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000000000000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+----------------+
9 rows in set (0.09 sec)
##创建库,表,并插入信息,用于测试主从复制
mysql> create database westos;
Query OK, 1 row affected (0.05 sec)
mysql> use westos;
Database changed
mysql> create table userlist(
-> username varchar(10) not null,
-> password varchar(15) not null);
Query OK, 0 rows affected (0.28 sec)
mysql> insert into userlist values ('user1','123');
Query OK, 1 row affected (0.07 sec)
mysql> insert into userlist values ('user2','123');
Query OK, 1 row affected (0.08 sec)
mysql> insert into userlist values ('user3','123');
Query OK, 1 row affected (0.08 sec)
(3)server2(slave)
设定从设备,并开启从设备,安装相应插件
mysql> change master to master_host='172.25.26.1',master_user='hui',master_password='Aa316256.',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.36 sec)
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.05 sec)
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.04 sec)
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.19.3
Master_User: hui
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 194
Relay_Log_File: server4-relay-bin.000004
Relay_Log_Pos: 401
Relay_Master_Log_File: binlog.000004
Slave_IO_Running: Yes ##两个YES表示同步成功