MHA
主从从 GTID 复制
1rm -rf /data/*
2rm -rf /data/*
3mkdir -p /data/mysql/data
4mkdir -p /data/binlog
5chown -R mysql.mysql /data
6
7# 主库db1配置文件
8cat >/etc/my.cnf <<EOF
9[mysqld]
10user=mysql
11basedir=/application/mysql
12datadir=/data/mysql/data
13socket=/tmp/mysql.sock
14server_id=51
15port=3306
16secure-file-priv=/tmp
17autocommit=0
18log_bin=/data/binlog/mysql-bin
19binlog_format=row
20gtid-mode=on
21enforce-gtid-consistency=true
22log-slave-updates=1
23[mysql]
24prompt=db1 [\\d]>
25EOF
26
27# 主库db2配置文件
28cat >/etc/my.cnf <<EOF
29[mysqld]
30user=mysql
31basedir=/application/mysql
32datadir=/data/mysql/data
33socket=/tmp/mysql.sock
34server_id=52
35port=3306
36secure-file-priv=/tmp
37autocommit=0
38log_bin=/data/binlog/mysql-bin
39binlog_format=row
40gtid-mode=on
41enforce-gtid-consistency=true
42log-slave-updates=1
43[mysql]
44prompt=db2 [\\d]>
45EOF
46
47# 主库db3配置文件
48cat >/etc/my.cnf <<EOF
49[mysqld]
50user=mysql
51basedir=/application/mysql
52datadir=/data/mysql/data
53socket=/tmp/mysql.sock
54server_id=53
55port=3306
56secure-file-priv=/tmp
57autocommit=0
58log_bin=/data/binlog/mysql-bin
59binlog_format=row
60gtid-mode=on
61enforce-gtid-consistency=true
62log-slave-updates=1
63[mysql]
64prompt=db3 [\\d]>
65EOF
66
67# 主库进行创建用户用于从库们的连接
68grant replication slave on *.* to repl@'192.168.31.%' identified by '123';
69
70# 两个从库分别开启主从
71[root@db2 software]# mysql -e "CHANGE MASTER TO MASTER_HOST='192.168.31.180',MASTER_USER='repl',MASTER_PASSWORD='123',MASTER_AUTO_POSITION=1;start slave;"
72[root@db2 software]# mysql -e "show slave status \G"|grep Yes
73 Slave_IO_Running: Yes
74 Slave_SQL_Running: Yes
准备环境
1# 配置关键程序软连接
2ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
3ln -s /application/mysql/bin/mysql /usr/bin/mysql
4
5# 配置各节点互信
6rm -rf /root/.ssh
7ssh-keygen
8cd /root/.ssh
9mv id_rsa.pub authorized_keys
10scp -r /root/.ssh 192.168.31.181:/root
11scp -r /root/.ssh 192.168.31.182:/root
12
13# 各节点验证
14db1：
15ssh 192.168.31.180 date
16ssh 192.168.31.181 date
17ssh 192.168.31.182 date
18
19db2：
20ssh 192.168.31.180 date
21ssh 192.168.31.181 date
22ssh 192.168.31.182 date
23
24db3：
25ssh 192.168.31.180 date
26ssh 192.168.31.181 date
27ssh 192.168.31.182 date
MHA 搭建
1# 所有节点安装Node软件和依赖包
2yum install -y perl-DBD-MySQL
3rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
4
5# db1主库中创建mha需要的用户
6grant all privileges on *.* to mha@'192.168.31.%' identified by 'mha';
7
8# 在db3安装Manager软件和依赖包
9yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
10rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
11
12# db3配置文件准备
13创建配置文件目录
14mkdir -p /etc/mha
15创建日志目录
16mkdir -p /var/log/mha/app1
17编辑mha配置文件
18cat > /etc/mha/app1.cnf<<EOF
19[server default]
20manager_log=/var/log/mha/app1/manager
21manager_workdir=/var/log/mha/app1
22master_binlog_dir=/data/binlog
23user=mha
24password=mha
25ping_interval=2
26repl_password=123
27repl_user=repl
28ssh_user=root
29[server1]
30hostname=192.168.31.180
31port=3306
32[server2]
33hostname=192.168.31.181
34port=3306
35[server3]
36hostname=192.168.31.182
37port=3306
38EOF
39
40# 互信检查(db3)
41masterha_check_ssh --conf=/etc/mha/app1.cnf
42 All SSH connection tests passed successfully.
43masterha_check_repl --conf=/etc/mha/app1.cnf
44 MySQL Replication Health is OK.
45
46# 启动MHA(db3)
47nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
48
49# 查看MHA状态
50masterha_check_status --conf=/etc/mha/app1.cnf
51 app1 (pid:12539) is running(0:PING_OK), master:192.168.31.180
52
额外的功能
1.提供 Binlog Server
2.应用透明（VIP）
3.实时通知管理员（send_report）
4.自愈（待开发。。。）
模拟故障和故障修复
1# 模拟故障：181变为主库
2/etc/init.d/mysqld stop
3
4# 观察 manager 日志 tail -f /var/log/mha/app1/manager,末尾必须显示uccessfully,才算正常切换成功。
5Master failover to 192.168.31.181(192.168.31.181:3306) completed successfully.
6
7# 在182上查看，db3从库指向181主库
8db3 [(none)]>show slave status \G;
9*************************** 1. row ***************************
10 Slave_IO_State: Waiting for master to send event
11 Master_Host: 192.168.31.181
12
13# 修复故障主库
14/etc/init.d/mysqld start
15
16# 恢复主从结构(指向新主库)
17[root@db3 ~]# grep -i 'change master to' /var/log/mha/app1/manager
18Tue May 11 08:43:13 2021 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.31.181', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
19
20CHANGE MASTER TO MASTER_HOST='192.168.31.181', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
21
22start slave;
23
24show slave status\G
25*************************** 1. row ***************************
26 Slave_IO_State: Waiting for master to send event
27 Master_Host: 192.168.31.181
28
29# db3 manager节点修改配置文件,并且添加如下内容
30vim /etc/mha/app1.cnf
31[server1]
32hostname=192.168.31.180
33port=3306
34
35# db3 manager 节点启动MHA
36nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
VIP
脚本如下
1[root@db3 ~]# vim /usr/local/bin/master_ip_failover
2#!/usr/bin/env perl
3use strict;
4use warnings FATAL => 'all';
5
6use Getopt::Long;
7
8my (
9 $command, $ssh_user, $orig_master_host, $orig_master_ip,
10 $orig_master_port, $new_master_host, $new_master_ip, $new_master_port
11);
12
13my $vip = '192.168.31.190/24'; # 修改这里的ip, 找个空闲的ip, 不能是在用的
14my $key = "1";
15my $ssh_start_vip = "/sbin/ifconfig enp0s3:$key $vip"; # 这里和下一行需要注意下网卡信息,我是用的虚拟机网卡是ens32, 需修改成机器的网卡信息
16my $ssh_stop_vip = "/sbin/ifconfig enp0s3:$key down";
17
18GetOptions(
19 'command=s' => \$command,
20 'ssh_user=s' => \$ssh_user,
21 'orig_master_host=s' => \$orig_master_host,
22 'orig_master_ip=s' => \$orig_master_ip,
23 'orig_master_port=i' => \$orig_master_port,
24 'new_master_host=s' => \$new_master_host,
25 'new_master_ip=s' => \$new_master_ip,
26 'new_master_port=i' => \$new_master_port,
27);
28
29exit &main();
30
31sub main {
32
33 print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
34
35 if ( $command eq "stop" || $command eq "stopssh" ) {
36
37 # $orig_master_host, $orig_master_ip, $orig_master_port are passed.
38 # If you manage master ip address at global catalog database,
39 # invalidate orig_master_ip here.
40 my $exit_code = 1;
41 eval {
42 print "Disabling the VIP on old master: $orig_master_host \n";
43 &stop_vip();
44 $exit_code = 0;
45 };
46 if ($@) {
47 warn "Got Error: $@\n";
48 exit $exit_code;
49 }
50 exit $exit_code;
51 }
52 elsif ( $command eq "start" ) {
53
54 # all arguments are passed.
55 # If you manage master ip address at global catalog database,
56 # activate new_master_ip here.
57 # You can also grant write access (create user, set read_only=0, etc) here.
58 my $exit_code = 10;
59 eval {
60 print "Enabling the VIP - $vip on the new master - $new_master_host \n";
61 &start_vip();
62 $exit_code = 0;
63 };
64 if ($@) {
65 warn $@;
66 exit $exit_code;
67 }
68 exit $exit_code;
69 }
70 elsif ( $command eq "status" ) {
71 print "Checking the Status of the script.. OK \n";
72 `ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
73 exit 0;
74 }
75 else {
76 &usage();
77 exit 1;
78 }
79}
80
81# A simple system call that enable the VIP on the new master
82sub start_vip() {
83 `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
84}
85# A simple system call that disable the VIP on the old_master
86sub stop_vip() {
87 `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
88}
89
90sub usage {
91 print
92 "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
93}
脚本中有中文字符
1[root@db3 ~]# dos2unix /usr/local/bin/master_ip_failover
2dos2unix: converting file /usr/local/bin/master_ip_failover to Unix format ...
3
4# 添加执行权限
5[root@db3 ~]# chmod +x /usr/local/bin/master_ip_failover
6
添加参数 master_ip_failover_script=/usr/local/bin/master_ip_failover
1[root@db3 ~]# vim /etc/mha/app1.cnf
2[server default]
3manager_log=/var/log/mha/app1/manager
4manager_workdir=/var/log/mha/app1
5master_binlog_dir=/data/binlog
6password=mha
7ping_interval=2
8repl_password=123
9repl_user=repl
10ssh_user=root
11user=mha
12master_ip_failover_script=/usr/local/bin/master_ip_failover
13[server1]
14hostname=192.168.31.180
15port=3306
16
17[server2]
18hostname=192.168.31.181
19port=3306
20
21[server3]
22hostname=192.168.31.182
23port=3306
主库手工绑定 VIP (确认主节点)
1ifconfig enp0s3:1 192.168.31.190/24
重启 mha 生效
1# 停止mha
2[root@db3 ~]# masterha_stop --conf=/etc/mha/app1.cnf
3
4# 启动mha
5[root@db3 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
6
邮件通知
安装 sendEmail
1#下载安装包
2wget http://caspian.dotconf.net/menu/Software/SendEmail/sendEmail-v1.56.tar.gz
3#创建目录
4mkdir -p /usr/local/bin
5#解压
6tar zxf sendEmail-v1.56.tar.gz -C /usr/src/
7#进入解压目录
8cd /usr/src/sendEmail-v1.56/
9#复制程序到指定目录
10cp -a sendEmail /usr/local/bin/
11#给执行权限
12chmod +x /usr/local/bin/sendEmail
脚本如下
1# 编写脚本
2[root@db3 perl-5.10.0]# vim /usr/local/bin/send
3#!/bin/bash
4/usr/local/bin/sendEmail -f wcsb19900116@126.com -t 410686931@qq.com -s smtp.126.com:25 -xu wcsb19900116 -xp PUTFDYGLEKBPEQDM -u "MHA Waring" -m "Your MHA MAY BE FAILOVER" &>/tmp/sendmail.log
5
6# 添加执行权限
7[root@db3 perl-5.10.0]# chmod +x /usr/local/bin/send
8
9# 将脚本添加到db3 manager配置文件中
10[root@db3 perl-5.10.0]# vim /etc/mha/app1.cnf
11report_script=/usr/local/bin/send
重启 MHA
1# 停止mha
2[root@db3 ~]# masterha_stop --conf=/etc/mha/app1.cnf
3
4# 启动mha
5[root@db3 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
MHA-binlog-server
1# 创建必要目录
2mkdir -p /data/mysql/binlog
3chown -R mysql.mysql /data/*
4
5# db3 manager配置文件 (hostname=192.168.31.185 找一台专门的服务器，这里节省机器写生了db3)
6[root@db3 ~]# /etc/mha/app1.cnf
7[binlog1]
8no_master=1
9hostname=192.168.31.182
10master_binlog_dir=/data/mysql/binlog
11
12# cd /data/mysql/binlog 必须进入到自己创建好的目录，host写主库的地址
13# mysql-bin.000005 是根据从库执行 show slave status 查询到的Master_Log_File的值写的
14mysqlbinlog -R --host=192.168.31.180 --user=mha --raw --stop-never mysql-bin.000005 &
15
16# 重启MHA
17[root@db3 ~]# masterha_stop --conf=/etc/mha/app1.cnf
18[root@db3 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/log/mha/app1/manager.log 2>&1 &
19
最终架构
MHA + VIP + SendReport + BinlogServer