MHA高可用集群
1. MHA介绍
MHA由日本DeNA公司开发，是一套优秀的实现MySQL高可用的解决方案，实现0~30秒之内数据库故障自动切换，MHA能确保在故障切换过程中保证数据一致性，达到真正意义上的高可用
下载地址
-
MHA组成
Manager（管理节点） ——可以单独部署在一台独立的机器上管理其他节点 ——也可以部署在一台slave节点上 MHA Node（数据节点） ——运行在每台MySQL服务器上 -
MHA工作过程
Manager定时探测集群中的master节点 当master节点发生故障时，Manager自动将拥有最新数据的slave提升为新的master -
技术关键
从宕机崩溃的master保存二进制日志事件 识别含有最新更新的slave 应用差异的中继日志（relat log）到其他的slave 应用从master保存的二进制日志事件 提升一个slave为新的master 其他的slave连接新的master进行复制 -
重点
配置VIP地址给主节点服务器，供外部服务访问，主节点宕机后MHA管理节点会选举新的master服务器。并配置VIP地址给新的master节点，从而实现master节点的无缝迁移。
2. 部署MHA集群
2.1. 拓扑图
2.2 IP地址规划
| 角色 | IP规划 | 主机名 |
|---|---|---|
| master主节点 | 192.168.0.101 | master01 |
| master备用节点 | 192.168.0.102 | master02 |
| master备用节点 | 192.168.0.103 | master03 |
| slave节点 | 192.168.0.104 | slave04 |
| MHA管理节点 | 192.168.0.105 | manager05 |
2.3 SSH免密登录
各mysql主从节点之间都部署免密登录（不包括管理节点），manager节点开启免密到各个主从节点。
[root@master01 ~]# ssh-keygen
[root@master01 ~]# ssh-copy-id 192.168.0.102
[root@master01 ~]# ssh-copy-id 192.168.0.103
[root@master01 ~]# ssh-copy-id 192.168.0.104
[root@master02 ~]# ssh-keygen
[root@master02 ~]# ssh-copy-id 192.168.0.101
[root@master02 ~]# ssh-copy-id 192.168.0.103
[root@master02 ~]# ssh-copy-id 192.168.0.104
```bash
[root@master03 ~]# ssh-keygen
[root@master03 ~]# ssh-copy-id 192.168.0.101
[root@master03 ~]# ssh-copy-id 192.168.0.102
[root@master03 ~]# ssh-copy-id 192.168.0.104
[root@slave01 ~]# ssh-keygen
[root@slave01 ~]# ssh-copy-id 192.168.0.101
[root@slave01 ~]# ssh-copy-id 192.168.0.102
[root@slave01 ~]# ssh-copy-id 192.168.0.103
[root@manager05 ~]# ssh-keygen
[root@manager05 ~]# ssh-copy-id 192.168.0.101
[root@manager05 ~]# ssh-copy-id 192.168.0.102
[root@manager05 ~]# ssh-copy-id 192.168.0.103
[root@manager05~]# ssh-copy-id 192.168.0.104
2.4. 配置主从结构（一主多从）
- 101 主库 开启半同步复制 只要有一台数据库把数据同步成功，就把结果返回给客户端）
- 102 从库（备用主库）开启半同步复制模式
- 103 从库（备用主库）开启半同步复制模式
- 104 从库只做从库不需要开启半同步
2.4.1. 主库配置101
- 授权从库用户、安装半同步插件、临时启用半同步插件（重启后失效）
mysql> grant replication slave on *.* to repluser@"%" identified by "Admin@123!!";
Query OK, 0 rows affected, 1 warning (0.01 sec)
#授权从库用户
mysql> install plugin rpl_semi_sync_master soname "semisync_master.so";
Query OK, 0 rows affected (0.01 sec)
# 安装半同步主库插件
mysql> install plugin rpl_semi_sync_slave soname "semisync_slave.so";
Query OK, 0 rows affected (0.01 sec)
# 安装半同步从库插件
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
| rpl_semi_sync_slave | ACTIVE |
+----------------------+---------------+
2 rows in set (0.00 sec)
# 查看插件信息
mysql> set global rpl_semi_sync_master_enabled=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> show variables like "rpl_semi_sync_%_enabled";
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_slave_enabled | ON |
+------------------------------+-------+
2 rows in set (0.01 sec)
# 查看半同步状态
- 永久设置半同步
[root@master01 ~]# vim /etc/my.cnf
[mysqld]
log_bin
#启用binlog日志
binlog_format="mixed"
#指定日志格式
plugin-load=rpl_semi_sync_master=semisync_master.so
#开启主库半同步复制
rpl_semi_sync_master_enabled=1
plugin_load=rpl_semi_sync_slave=semisync_slave.so
#开启从库半同步复制
rpl_semi_sync_slave_enabled=1
server_id=101
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@master01 ~]# systemctl restart mysqld
2.4.2. 备用主库（从库）配置102
- 安装半同步插件、启用半同步插件、基本和101相同，注释查看101配置
mysql> install plugin rpl_semi_sync_master soname "semisync_master.so";
Query OK, 0 rows affected (0.01 sec)
mysql> install plugin rpl_semi_sync_slave soname "semisync_slave.so";
Query OK, 0 rows affected (0.00 sec)
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
| rpl_semi_sync_slave | ACTIVE |
+----------------------+---------------+
2 rows in set (0.00 sec)
mysql> set global rpl_semi_sync_master_enabled=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> show variables like "rpl_semi_sync_%_enabled";
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_slave_enabled | ON |
+------------------------------+-------+
2 rows in set (0.00 sec)
- 修改配置文件
[root@master02 ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
erver_id=61
log-bin
binlog_format="mixed"
plugin-load=rpl_semi_sync_master=semisync_master.so
#启用主库半同步复制
rpl_semi_sync_master_enabled=1
plugin_load=rpl_semi_sync_slave=semisync_slave.so
#启用从库半同步复制
rpl_semi_sync_slave_enabled=1
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
- 指定主库信息
mysql> change master to
-> master_user="repluser",
-> master_password="Admin@123!!",
-> master_log_file="master01-bin.000001",
-> master_host="192.168.0.101",
-> 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)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.101
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master01-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: master02-relay-bin.000002
Relay_Log_Pos: 323
Relay_Master_Log_File: master01-bin.000001
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: 154
Relay_Log_Space: 533
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: 101
Master_UUID: 672b420b-ef6b-11ef-9fcd-000c29764b85
Master_Info_File: /var/lib/mysql/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:
1 row in set (0.00 sec)
2.4.3. 备用主库103配置
同备用主库102
2.4.4. 从库104配置
指定server_id
[root@slave04 ~]# vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server_id=104
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
配置主库信息，确认同步线程yes状态
mysql> change master to
-> master_host="192.168.0.101",
-> master_user="repluser",
-> master_password="Admin@123!!",
-> master_log_file="master01-bin.000001",
-> master_log_pos=154;
2.4.5. 主库授权用户
主库为MHA管理主机授权连接数据库用户
仅在主库配置，会自动同步给所有从库
mysql> grant all on *.* to root@"%" identified by "Admin123!!";
Query OK, 0 rows affected, 1 warning (0.00 sec)
重新设置一次主从同步的用户授权，在所有数据节点都要保证有主从同步用户，直接在主库授权，让从库全部同步
mysql> grant replication slave on *.* to repluser@"%" identified by "Admin@123!!";
2.4.6 安装MHA
- 所有节点安装perl依赖和node包依赖
yum -y install perl-*.rpm
yum -y install perl-DBD-mysql perl-DBI
- 所有节点安装node包
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
- 安装管理节点软件包
- 安装依赖
[root@manager mha-soft]# yum -y install perl-ExtUtils-*
[root@manager mha-soft]# yum -y install perl-CPAN-*
- 解压mha4mysql-manager源码包
[root@manager05 mha-soft]# tar -zxvf mha4mysql-manager-0.56.tar.gz
- 进入源码包路径
[root@manager05 mha-soft]# cd mha4mysql-manager-0.56
- 检测安装环境
[root@manager05 mha4mysql-manager-0.56]# perl Makefile.PL
- 编译&&安装源码
[root@manager05 mha4mysql-manager-0.56]# make && make install
- 创键工作目录
[root@manager05 mha4mysql-manager-0.56]# mkdir /etc/mha_manager
- 拷贝模板文件到工作目录
[root@manager05 mha4mysql-manager-0.56]# cp samples/conf/app1.cnf /etc/mha_manager/
- 修改配置文件
[root@manager05 mha4mysql-manager-0.56]# vim /etc/mha_manager/app1.cnf
[server default] //mha本机的配置
manager_workdir=/etc/mha_manager //指定工作目录
manager_log=/etc/mha_manager/manager.log //指定日志文件
master_ip_failover_script=/etc/mha_manager/master_ip_failover //指定监控脚本
ssh_user=root //通过ssh连接数据节点，用户为root
ssh_port=22 //端口
user=root //连接数据库用户
password=1234 //连接密码
repl_user=repluser //主从同步用户
repl_password=1234 //用户密码
[server1] //被监控服务器名称（默认即可）
hostname=192.168.0.101 //被监控数据库IP地址
candidate_master=1 //竞选主库
[server2]
hostname=192.168.0.102
candidate_master=1 //竞选主库
[server3]
hostname=192.168.0.103
candidate_master=1 //竞选主库
[server4]
hostname=192.168.0.104
no_master=1 //纯从库不竞选主库
- 测试ssh登录
[root@manager05 ~]# masterha_check_ssh --conf=/etc/mha_manager/app1.cnf
- 测试主从同步
[root@manager05 ~]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf
- 在主库配置VIP地址
[root@maaster01 ~]# ifconfig ens32:1 192.168.0.110/24
- 启动manager服务
[root@manager05 ~]#masterha_manager --conf=/etc/mha_manager/app1.cnf \
--remove_dead_master_conf \
--ignore_last_failover
- 另开终端查看服务状态
[root@manager05 ~]#masterha_check_status–conf=/etc/mha_manager/app1.cnf
停止主库MySQL服务测试故障迁移
[root@master01 ~]# systemctl stop mysqld
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
