Mysql 5.7 MHA 高可用部署
导读：为节省软件授权成本，故决定将非核心的oracle业务转型至开源化的Mysql中。因业务使用Oracle Rac架构无感知故障转移已熟悉，要求Mysql也需满足无感知的故障转移效果。经团队评估决定使用Mysql Mha高可用架构来完成故障转移功能(虽然有30s左右的延迟，但足已满足非核心系统的要求)。
1.Mysql MHA高可用部署
1.1 主从复制与Manager/Node服务器规划
|
|
主库:
192.168
.
56.101
node
从库:
192.168
.
56.102
node
192.168
.
56.103
node manager
192.168
.
56.104
vip |
1.2 准备环境（略。1主2从基于GTID的部署）
1.3 配置关键程序软连接
|
|
# 使用软连接解决mha源代码中绝对路径的问题
ln -s /data/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /data/mysql/bin/mysql /usr/bin/mysql |
1.4 配置各节点互信
|
|
Mysqldb01：
rm -rf /root/.ssh
ssh-keygen
cd /root/.ssh
mv id_rsa.pub authorized_keys
scp -r /root/.ssh
192.168
.
56.102
:/root
scp -r /root/.ssh
192.168
.
56.103
:/root
各节点验证
Mysqldb01:
ssh
192.168
.
56.101
date
ssh
192.168
.
56.102
date
ssh
192.168
.
56.103
date
Mysqldb02:
ssh
192.168
.
56.101
date
ssh
192.168
.
56.102
date
ssh
192.168
.
56.103
date
Mysqldb03:
ssh
192.168
.
56.101
date
ssh
192.168
.
56.102
date
ssh
192.168
.
56.103
date |
1.5 安装软件
1.6 配置文件准备(Mysqldb03)
|
|
# 创建mha配置文件和日志文件目录
mkdir -p /data/mha
chown mysql:mysql /data/mha/ -R
mkdir -p /data/mha/app1/log
chown mysql:mysql /data/mha/app1/log -R
# 编辑mha配置文件(Mysqldb03)
vi /data/mha/mha.conf
[server
default
]
manager_log=/data/mha/app1/log/manager
manager_workdir=/data/mha/app1/log
master_binlog_dir=/data/binlog
# vip failover scripts
master_ip_failover_script=/usr/local/bin/master_ip_failover
password=mha
ping_interval=
2
repl_password=oracle
repl_user=repl01
ssh_user=root
user=mha
[server1]
hostname=
192.168
.
56.101
port=
3306
[server2]
hostname=
192.168
.
56.102
port=
3306
[server3]
hostname=
192.168
.
56.103
port=
3306 |
1.7 互信与复制(1主2从)状态检查
|
|
masterha_check_ssh --conf=/data/mha/mha.conf
masterha_check_repl --conf=/data/mha/mha.conf |
1.8 开启MHA(Mysqldb03)
|
1 |
nohup masterha_manager --conf=/data/mha/mha.conf --remove_dead_master_conf --ignore_last_failover < /dev/
null
> /data/mha/app1/log/manager.log
2
>&
1
& |
1.9 Mysql MHA状态检查
|
|
# 自动获取MHA状态和主库IP
[root@mysqldb03 opt]# masterha_check_status -conf=/data/mha/mha.conf
mha (pid:
24100
)
is
running(
0
:PING_OK), master:
192.168
.
56.101
# 查看
3
个节点server_id
[root@mysqldb03 mha]# mysql -umha -pmha -h
192.168
.
56.101
-e
"show variables like 'server_id'"
mysql: [Warning] Using a password on the command line
interface
can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id |
101
|
+---------------+-------+
[root@mysqldb03 mha]# mysql -umha -pmha -h
192.168
.
56.102
-e
"show variables like 'server_id'"
mysql: [Warning] Using a password on the command line
interface
can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id |
102
|
+---------------+-------+
[root@mysqldb03 mha]# mysql -umha -pmha -h
192.168
.
56.103
-e
"show variables like 'server_id'"
mysql: [Warning] Using a password on the command line
interface
can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id |
103
| |
1.10 配置MHA 的vip功能
|
|
## Mysql Vip故障转移脚本master_ip_failover修改
vi /usr/local/bin/master_ip_failover
# 修改以下内容，其中vip就是你要使用的vip，其中enp0s8就是vip的物理网卡，所有节点中物理网卡的enp0s8名字要相同
my $vip =
'192.168.56.104/24'
;
my $key =
'1'
;
my $ssh_start_vip =
"/sbin/ifconfig enp0s8:$key $vip"
;
my $ssh_stop_vip =
"/sbin/ifconfig enp0s8:$key down"
;
# 若是脚本中有空格可使用以下命令进行转换
[root@mysqldb03 opt]# dos2unix master_ip_failover.txt
dos2unix: converting file master_ip_failover.txt to Unix format ...
[root@mysqldb03 opt]# chmod +x master_ip_failover.txt
[root@mysqldb03 opt]# mv master_ip_failover.txt master_ip_failover
[root@mysqldb03 opt]# mv master_ip_failover /usr/local/bin/
## Manager配置文件修改：
vi /data/mha/mha.conf
master_ip_failover_script=/usr/local/bin/master_ip_failover
## 主库首次使用vip需要手工开启VIP地址
# 手工在主库上绑定vip，注意一定要和配置文件中的enp0s8一致，我的是enp0s8:
1
(
1
是key指定的值)
ifconfig enp0s8:
1
192.168
.
56.104
/
24
## 重启mha
masterha_stop --conf=/data/mha/mha.conf
nohup masterha_manager --conf=/data/mha/mha.conf --remove_dead_master_conf --ignore_last_failover < /dev/
null
> /data/mha/app1/log/manager.log
2
>&
1
& |
1.11 Mysql MHA故障模拟与VIP偏移功能验证
|
|
## Manager节点监控
# 停库前
[root@mysqldb03 ~]# mysql -umha -pmha -h
192.168
.
56.104
-e
"show variables like 'server_id'"
mysql: [Warning] Using a password on the command line
interface
can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id |
101
|
+---------------+-------+
# 停主库Mysqldb01:
/etc/init.d/mysqld stop
# 停库后，且从库切换主库
[root@mysqldb03 mha]# mysql -umha -pmha -h
192.168
.
56.104
-e
"show variables like 'server_id'"
mysql: [Warning] Using a password on the command line
interface
can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id |
102
|
+---------------+-------+
## Mysql Mha架构中VIP
192.168
.
56.104
始终不变，但主库已从server_id=
101
切换至server_id=
102
（模拟server_id=
101
宕机）。 |
1.12 Mysql MHA 故障后修复步骤
|
|
# 修复主库
[root@Mysqldb01 ~]# /etc/init.d/mysqld start
# 恢复主从结构
CHANGE MASTER TO
MASTER_HOST=
'192.168.56.102'
,
MASTER_PORT=
3306
,
MASTER_AUTO_POSITION=
1
,
MASTER_USER=
'repl01'
,
MASTER_PASSWORD=
'oracle'
;
start slave ;
# 修改mha的配置文件
[server1]
hostname=
192.168
.
56.101
port=
3306
# 启动MHA
nohup masterha_manager --conf=/data/mha/mha.conf --remove_dead_master_conf --ignore_last_failover < /dev/
null
> /data/mha/app1/log/manager.log
2
>&
1
& |
参考：《Oldguo-标杆班级-MySQL-lesson10-MHA高可用技术》
