MySQL集群-MHA高可用架构

文章目录[隐藏]

前言
架构选型
架构简介
MHA 怎么保证数据不丢失
MHA 优点和限制
搭建
测试
手动切换主从
注意
master_ip_online_change 脚本
send_report 脚本
清理中继日志定时任务
参考

前言

在 MySQL 的高可用方案中，MHA（Master High Availability）可谓是最为成熟、使用最为广泛的方案之一了。其作者 Yoshinori Matsunobu 现就职于 Facebook，该架构采用 perl 语言编写，可完成秒级别的主库故障切换，接下来详细介绍 MHA 在铜板街的上线之路。

架构选型

在开始计划实施 MySQL 数据库高可用时，我们选择了比较流行的几大方案，分别进行了调研。

MHA：适用于一主多从的架构体系，在故障切换过程中，可从宕机的主库上保存二进制日志，最大程度的保证数据不丢失。但是需要 MHA 架构内所有的节点都必须可以 ssh互通。

MMM：（Master-Master replication manager for MySQL）适用于双主架构，是一套支持双主故障切换和日常管理的脚本程序，虽无需架构内 ssh 互通，但是无法保存主库的二进制日志，所以数据一致性不如 MHA 高。

PXC：（Percona XtraDB Cluster）是 percona 公司提供的一套高可用方案，需要三个或以上 percona 版本的 DB 节点，该架构保证了数据强一致，但是同等硬件配置下，性能不如 MHA 和 MMM，尤其木桶效应明显，该方案还需依赖外部组件。

MGR：（MySQL Group Replication）是 MySQL 官方发布的高可用架构，该方案依赖于 MySQL5.7 版本，适用于主从架构，但是需要类似主库全表包含主键等相关依赖，成熟度不如以上方案，该方案同样需要依赖外部组件。

综上，在结合自身的业务场景，最终选择 MHA 作为 MySQL 集群的高可用方案。以下详细介绍 MHA 的体系结构及部署和测试的各项细节。

架构简介

MHA - Master High Availability 是由 Perl 实现的一款高可用程序，出现故障时，MHA 以最小的停机时间(通常10-30秒)执行 master 的故障转移以及 slave 的升级。MHA 可防止复制一致性问题，并且易于安装，不需要改变现有部署。

MHA 由MHA manager和MHA node组成, MHA manager是一个监控管理程序，用于监控MySQL master状态; MHA node是具有故障转移的工具脚本，如解析 MySQL 二进制/中继日志，传输应用事件到Slave, MHA node在每个MySQL服务器上运行。

MHA manager调用MHA node工具脚本的方式是SSH到主机上然后执行命令，所以各节点需要做等效验证。

MHA 怎么保证数据不丢失

当Master宕机后，MHA会尝试保存宕机Master的二进制日志，然后自动判断MySQL集群中哪个实例的中继日志是最新的，并将有最新日志的实例的差异日志传到其他实例补齐，从而实现所有实例数据一致。然后把宕机Master的二进制日志应用到选定节点，并提升为 Master。

具体流程如下：

尝试从宕机Master中保存二进制日志
找到含有最新中继日志的Slave
把最新中继日志应用到其他实例，实现各实例数据一致
应用从Master保存的二进制日志事件
提升一个Slave为Master
其他Slave向该新Master同步

从切换流程流程可以看到，如果宕机Master主机无法SSH登录，那么第一步就没办法实现，对于MySQL5.5以前的版本，数据还是有丢失的风险。对于5.5后的版本，开启半同步复制后，真正有助于避免数据丢失，半同步复制保证至少一个（不是所有）slave 在 master 提交时接收到二进制日志事件。因此，对于可以处理一致性问题的MHA 可以实现”几乎没有数据丢失”和”从属一致性”。

MHA 优点和限制

优点

开源，用Perl编写
方案成熟，故障切换时，MHA会做日志补齐操作，尽可能减少数据丢失，保证数据一
部署不需要改变现有架构

限制

各个节点要打通SSH信任，有一定的安全隐患
没有 Slave 的高可用
自带的脚本不足，例如虚IP配置需要自己写命令或者依赖其他软件
需要手动清理中继日志

搭建

MHA只支持一主多从，即使是备master，也是主库的从库。

目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库，因为至少需要三台服务器。

角色	IP	主机名	类型
Manager	192.168.171.150	manager	管理节点
Master	192.168.171.151	master	主mysql（写入）
CandicateMaster	192.168.171.152	slave1	从mysql（读）
slave	192.168.171.153	slave2	从mysql（读）

其中master对外提供写服务，备选master（实际的slave，主机名slave1）提供读服务，slave也提供相关的读服务，一旦master宕机，将会把备选master提升为新的master，slave指向新的master，manager作为管理服务器。

主机部署

【4个节点】改主机名：（根据上面表格改）

1	hostnamectl set-hostname 主机名

【4个节点】将ip和域名配置到/etc/hosts文件中

cat >> /etc/hosts << EOF

192.168.171.151 master

192.168.171.152 slave1

192.168.171.153 slave2

192.168.171.150 manager

EOF

【4个节点】上的防火墙上加上端口的允许

1	iptables -I INPUT -s 0/0 -p tcp --dport 3306 -j ACCEPT

这条规则的意思是，想要在输入数据INPUT中，protocol为tcp/IP的方式，访问端口3306，都会被允许的

$ iptables -L -n|grep 3306

ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:3306

【4个节点】都配置epel源

# 安装epel源

yum install -y epel-release

# 下载阿里开源镜像的epel源文件

wget -O /etc/yum.repos.d/epel-7.repo http://mirrors.aliyun.com/repo/epel-7.repo

# 清除系统yum缓存

yum clean all

# 重新生成新的yum缓存

yum makecache

用ssh-keygen实现四台主机之间相互免密钥登录

1.生成密钥

【4个节点】执行以下命令

1 2	ssh-keygen -t rsa for i in manager master slave1 slave2;do ssh-copy-id -i $i;done

安装mha4mysql-node

mha4mysql-node地址：Github

【4个节点】执行以下命令：

wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm

rpm -ivh mha4mysql-node*

安装mha4mysql-manager

mha4mysql-manager地址：Github

【manager节点】执行以下命令：

wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

rpm -ivh mha4mysql-manager*

建立master，slave1，slave2之间主从复制

为了尽可能的减少主库硬件损坏宕机造成的数据丢失，因此在配置MHA的同时建议配置成MySQL的半同步复制。

注：mysql半同步插件是由谷歌提供，具体位置/usr/local/mysql/lib/plugin/下，一个是master用的semisync_master.so，一个是slave用的semisync_slave.so。

在MySQL上安装插件需要数据库支持动态载入，检查是否支持，用如下命令检测：

mysql> show variables like '%have_dynamic%';

+----------------------+-------+

| Variable_name | Value |

+----------------------+-------+

| have_dynamic_loading | YES |

+----------------------+-------+

1 row in set (0.01 sec)

所有MySQL都可以成为主节点，所有mysql 都开启插件

install plugin rpl_semi_sync_master soname 'semisync_master.so';

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

检查Plugin是否已正确安装：

mysql> show plugins;

或

mysql> select plugin_name from information_schema.plugins;

查看半同步相关信息

mysql> show variables like '%rpl_semi_sync%';

+-------------------------------------------+------------+

| Variable_name | Value |

+-------------------------------------------+------------+

| rpl_semi_sync_master_enabled | OFF |

| rpl_semi_sync_master_timeout | 10000 |

| 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 |

+-------------------------------------------+------------+

8 rows in set (0.00 sec)

上图可以看到半同复制插件已经安装，只是还没有启用，所以是off。

修改my.cnf文件

注：若主MYSQL服务器已经存在，只是后期才搭建从MYSQL服务器，在置配数据同步前应先将主MYSQL服务器的要同步的数据库拷贝到从MYSQL服务器上（如先在主MYSQL上备份数据库，再用备份在从MYSQL服务器上恢复）

所有MySQL配置：

[mysqld]

#### log ####

server-id=1 # 修改，每个节点不能一样

log_timestamps=system

log-bin=mysql-bin # 开启bin log

log-bin-index=mysql-bin.index

binlog_format=mixed

relay_log_recovery=ON

relay_log_purge=0

relay-log=relay-bin # 开启中继日志

relay-log-index=slave-relay-bin.index

# read_only=1 只读

#### replication ####

log_slave_updates = 1

replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####

#plugin_dir=/usr/local/mysql57/lib/plugin

plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

rpl_semi_sync_master_enabled=1： 1表是启用，0表示关闭

rpl_semi_sync_master_timeout=10000：毫秒单位，该参数主服务器等待确认消息10秒后，不再等待，变为异步方式。
relay_log_purge=0：禁止 SQL 线程在执行完一个 relay log 后自动将其删除，对于MHA场景下，对于某些滞后从库的恢复依赖于其他从库的relay log，因此采取禁用自动删除功能。

在所有服务器上重启mysql服务。

1	systemctl restart mysqld

进入mysql，查看半同步相关信息。

mysql> show variables like '%rpl_semi_sync%';

+-------------------------------------------+------------+

| Variable_name | Value |

+-------------------------------------------+------------+

| rpl_semi_sync_master_enabled | ON |

| rpl_semi_sync_master_timeout | 1000 |

| 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 | ON |

| rpl_semi_sync_slave_trace_level | 32 |

+-------------------------------------------+------------+

8 rows in set (0.01 sec)

查看半同步状态

mysql> show status like '%rpl_semi_sync%';

+--------------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------------+-------+

| Rpl_semi_sync_master_clients | 0 |

| Rpl_semi_sync_master_net_avg_wait_time | 0 |

| Rpl_semi_sync_master_net_wait_time | 0 |

| Rpl_semi_sync_master_net_waits | 0 |

| Rpl_semi_sync_master_no_times | 0 |

| Rpl_semi_sync_master_no_tx | 0 |

| Rpl_semi_sync_master_status | ON |

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 0 |

| Rpl_semi_sync_master_tx_wait_time | 0 |

| Rpl_semi_sync_master_tx_waits | 0 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 |

| Rpl_semi_sync_master_yes_tx | 0 |

| Rpl_semi_sync_slave_status | OFF |

+--------------------------------------------+-------+

15 rows in set (0.00 sec)

有几个状态参数值得关注的：

rpl_semi_sync_master_status ：显示主服务是异步复制模式还是半同步复制模式
rpl_semi_sync_master_clients ：显示有多少个从服务器配置为半同步复制模式
rpl_semi_sync_master_yes_tx ：显示从服务器确认成功提交的数量
rpl_semi_sync_master_no_tx ：显示从服务器确认不成功提交的数量
rpl_semi_sync_master_tx_avg_wait_time ：事务因开启 semi_sync ，平均需要额外等待的时间
rpl_semi_sync_master_net_avg_wait_time ：事务进入等待队列后，到网络平均等待时间

所有MySQL创建复制账号并授权复制

1	grant replication slave on . to mharpl@'%' identified by '123456';

所有MySQL创建MHA管理账号并授权管理

1	grant all privileges on . to manager@'%' identified by '123456';

在主库上，查看信息

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 | 746 | | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

第一条grant命令是创建一个用于主从复制的帐号，在master和candicate master的主机上创建即可。

第二条grant命令是创建MHA管理账号，所有mysql服务器上都需要执行。MHA会在配置文件里要求能远程登录到数据库，所以要进行必要的赋权。
所有从库链接到主库

# 从库链接主库（主库不能两两复制，否则mha-manager会报错）

mysql> stop slave;

mysql> change master to master_host='master ip',

master_user='mharpl',

master_password='123456',

master_port=3306,

master_log_file='mysql-bin.000001', # 上面记录的master的log_file

master_log_pos=722, # 上面记录的master的pos

master_connect_retry=30;

mysql> start slave;

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.206.201

Master_User: mharep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 154

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

查看从的状态，以下两个值必须为yes，代表从服务器能正常连接主服务器。

Slave_IO_Running:Yes
Slave_SQL_Running:Yes

查看master服务器的半同步状态

mysql> show status like '%rpl_semi_sync%';

+--------------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------------+-------+

| Rpl_semi_sync_master_clients | 2 |

| Rpl_semi_sync_master_net_avg_wait_time | 0 |

| Rpl_semi_sync_master_net_wait_time | 0 |

| Rpl_semi_sync_master_net_waits | 0 |

| Rpl_semi_sync_master_no_times | 0 |

| Rpl_semi_sync_master_no_tx | 0 |

| Rpl_semi_sync_master_status | ON |

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 0 |

| Rpl_semi_sync_master_tx_wait_time | 0 |

| Rpl_semi_sync_master_tx_waits | 0 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 |

| Rpl_semi_sync_master_yes_tx | 0 |

| Rpl_semi_sync_slave_status | OFF |

+--------------------------------------------+-------+

15 rows in set (0.01 sec)

rpl_semi_sync_master_clients ：显示有2个从服务器配置为半同步复制模式。

配置mha

在manager节点上

# 创建MHA的工作目录

mkdir -p /var/mha/{script}

# 创建MHA的配置目录

mkdir /etc/mha

# 编辑配置文件

vi /etc/masterha/app1.cnf

[server default]

manager_workdir=/var/mha # 设置 manager 的工作目录, 可以自己调整

manager_log=/var/mha/manager.log # 设置 manager 的日志文件

master_binlog_dir=/var/lib/mysql # 设置 master binlog 的日志的位置

# 设置自动 failover 时的切换脚本

master_ip_failover_script= /var/mha/script/master_ip_failover

# 设置手动切换时执行的切换脚本

master_ip_online_change_script= /var/mha/script/master_ip_online_change

user=manager # 设置管理用户, 用来监控、配置 MySQL(STOP SLAVE, CHANGE MASTER, RESET SLAVE), 默认为 root

password=123456 # 设置管理用户密码

repl_user=mharpl # 设置复制环境中的复制用户名

repl_password=123456 # 设置复制用户的密码

ping_interval=1 # 发送 ping 包的时间间隔，三次没有回应就自动进行 failover

remote_workdir=/tmp # 设置远端 MySQL 的工作目录

# report_script=/etc/masterha/script/send_report # 设置发生切换后执行的脚本

# 检查脚本

secondary_check_script= /usr/bin/masterha_secondary_check -s 192.168.171.151 -s 192.168.171.152

# shutdown_script="" #设置故障发生后关闭故障主机脚本(可以用于防止脑裂)

ssh_user=root #设置 ssh 的登录用户名

[server1]

hostname=192.168.171.151

port=3306

candidate_master=1 # 设置为候选 master, 如果发生宕机切换，会把该节点设为新 Master，即使它不是数据最新的节点

check_repl_delay=0 # 默认情况下，一个 Slave 落后 Master 100M 的中继日志，MHA 不会选择它作为新的 Master，因为这对于 Slave 恢复数据要很长时间，check_repl_delay=0 的时候会忽略延迟，可以和 candidate_master=1 配合用

[server2]

hostname=192.168.171.152

port=3306

candidate_master=1

check_repl_delay=0

[server3]

hostname=192.168.171.153

port=3306

no_master=1 # 从不将这台主机升级为 Master

ignore_fail=1 # 默认情况下，如果有 Slave 节点挂了，就不进行切换，设置 ignore_fail=1 可以忽然它

检查SSH配置免密互通

1	masterha_check_ssh --conf=/etc/mha/app1.cnf

最后显示：All SSH connection tests passed successfully.则为成功。
验证配置

1	masterha_check_repl --conf=/etc/mha/app1.cnf

最后显示：MySQL Replication Health is OK.则为成功。
如果NodeUtil.pm报错，则修改如下：

vi /usr/share/perl5/MHA/NodeUtil.pm

# 修改成如下：

sub parse_mysql_major_version($) {

my $str = shift;

$str =~ /(\d+)\.(\d+)/;

my $strmajor = "$1.$2";

my $result = sprintf( '%03d%03d', $strmajor =~ m/(\d+)/g );

return $result;

}

后台运行

1	nohup masterha_manager --conf=/etc/mha/app1.cnf --ignore_last_failover >/dev/null 2>&1 &

状态检查

1	masterha_check_status --conf=/etc/mha/app1.cnf

测试

停掉master上的mysql服务，查看MHA的日志

1	tail -f /var/mha/manager.log

日志文件显示：

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

Master 192.168.171.151(192.168.171.151:3306) is down! # down机

Check MHA Manager logs at manager:/masterha/app1/manager.log for details.

Started automated(non-interactive) failover.

The latest slave 192.168.171.152(192.168.171.152:3306) has all relay logs for recovery.

Selected 192.168.171.152(192.168.171.152:3306) as a new master.

192.168.171.152(192.168.171.152:3306): OK: Applying all logs succeeded.

192.168.171.153(192.168.171.153:3306): This host has the latest relay log events.

Generating relay diff files from the latest slave succeeded.

192.168.171.153(192.168.171.153:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.171.152(192.168.171.152:3306)

192.168.171.152(192.168.171.152:3306): Resetting slave info succeeded.

Master failover to 192.168.171.152(192.168.171.152:3306) completed successfully. # 更改成功

上面的结果表明master成功切换。至此，MHA成功搭建。

手动切换主从

需要停止MHA manager，manager会自动切换。

# 停止MHA manager

masterha_stop --conf=/etc/mha/app1.cnf

# 手动切换主从

masterha_master_switch \

--conf=/etc/mha/app1.cnf \

--master_state=alive \

--new_master_host=新主库ip \

--new_master_port=3306 \

--orig_master_is_new_slave

# 启动MHA manager

nohup masterha_manager --conf=/etc/mha/app1.cnf --ignore_last_failover >/dev/null 2>&1 &

# 查看状态

masterha_check_status --conf=/etc/mha/app1.cnf

注意

宕机的MySQL重回集群

宕机的MySQL重回集群，只能作为现主库的从库。在manager.log上有change master的SQL语句，把SQL语句复制到加入集群的MySQL;

mysql> stop slave;

mysql> CHANGE MASTER TO MASTER_HOST='192.168.171.152', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2717, MASTER_USER='mharpl', MASTER_PASSWORD='123456';

mysql> start slave;

mysql> show slave status\G;

显示如下即为成功：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
重新运行MHA manager

nohup masterha_manager --conf=/etc/mha/app1.cnf --ignore_last_failover >/dev/null 2>&1 &

masterha_check_status --conf=/etc/mha/app1.cnf

从库不能写数据

从库不能写数据，从库的数据不会同步到主库，且主库同步数据到此库，数据不一致，会报错。
先主库导出到从库，再在从库跳过这条数据https://xiaojin21cen.blog.csdn.net/article/details/107503806

master_ip_failover 脚本

100

101

102

103

104

105

106

107

108

109

110

111

112

#!/usr/bin/env perl

use strict;

use warnings FATAL => 'all';

use Getopt::Long;

use MHA::DBHelper;

my (

$command, $ssh_user, $orig_master_host,

$orig_master_ip, $orig_master_port, $new_master_host,

$new_master_ip, $new_master_port, $new_master_user,

$new_master_password

);

my $vip = '10.0.0.237';

my $key = '1';

my $ssh_start_vip = "sudo /sbin/ifconfig eth0:$key $vip netmask 255.255.255.255";

my $ssh_stop_vip = "sudo /sbin/ifconfig eth0:$key down";

GetOptions(

'command=s' => \$command,

'ssh_user=s' => \$ssh_user,

'orig_master_host=s' => \$orig_master_host,

'orig_master_ip=s' => \$orig_master_ip,

'orig_master_port=i' => \$orig_master_port,

'new_master_host=s' => \$new_master_host,

'new_master_ip=s' => \$new_master_ip,

'new_master_port=i' => \$new_master_port,

'new_master_user=s' => \$new_master_user,

'new_master_password=s' => \$new_master_password,

);

exit &main();

sub main {

print "\n\n VIP Command: start=$ssh_start_vip stop=$ssh_stop_vip\n\n";

if ( $command eq "stop" || $command eq "stopssh" ) {

# $orig_master_host, $orig_master_ip, $orig_master_port are passed.

# If you manage master ip address at global catalog database,

# invalidate orig_master_ip here.

my $exit_code = 1;

eval {

print "Disabling the VIP on old master: $orig_master_host \n";

&stop_vip();

# updating global catalog, etc

$exit_code = 0;

};

if ($@) {

warn "Got Error: $@\n";

exit $exit_code;

}

exit $exit_code;

}

elsif ( $command eq "start" ) {

# all arguments are passed.

# If you manage master ip address at global catalog database,

# activate new_master_ip here.

# You can also grant write access (create user, set read_only=0, etc) here.

my $exit_code = 10;

eval {

my $new_master_handler = new MHA::DBHelper();

# args: hostname, port, user, password, raise_error_or_not

$new_master_handler->connect( $new_master_ip, $new_master_port,

$new_master_user, $new_master_password, 1 );

## Set read_only=0 on the new master

$new_master_handler->disable_log_bin_local();

print "Set read_only=0 on the new master.\n";

$new_master_handler->disable_read_only();

$new_master_handler->disconnect();

print "Enabling the VIP - $vip on the new master - $new_master_host \n";

&start_vip();

$exit_code = 0;

};

if ($@) {

warn $@;

# If you want to continue failover, exit 10.

exit $exit_code;

}

exit $exit_code;

}

elsif ( $command eq "status" ) {

print "Check script.. OK \n";

# do nothing

exit 0;

}

else {

&usage();

exit 1;

}

sub start_vip() {

`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;

}

sub stop_vip() {

return 0 unless ($ssh_user);

`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;

}

sub usage {

"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";

}

master_ip_online_change 脚本

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

#!/usr/bin/env perl

# This program is free software; you can redistribute it and/or modify

# it under the terms of the GNU General Public License as published by

# the Free Software Foundation; either version 2 of the License, or

# (at your option) any later version.

# This program is distributed in the hope that it will be useful,

# but WITHOUT ANY WARRANTY; without even the implied warranty of

# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License

# along with this program; if not, write to the Free Software

# Foundation, Inc.,

# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

## Note: This is a sample script and is not complete. Modify the script based on your environment.

use strict;

use warnings FATAL => 'all';

use Getopt::Long;

use MHA::DBHelper;

use MHA::NodeUtil;

use Time::HiRes qw( sleep gettimeofday tv_interval );

use Data::Dumper;

my $_tstart;

my $_running_interval = 0.1;

my (

$command, $orig_master_is_new_slave, $orig_master_host,

$orig_master_ip, $orig_master_port, $orig_master_user,

$orig_master_password, $orig_master_ssh_user, $new_master_host,

$new_master_ip, $new_master_port, $new_master_user,

$new_master_password, $new_master_ssh_user

);

GetOptions(

'command=s' => \$command,

'orig_master_is_new_slave' => \$orig_master_is_new_slave,

'orig_master_host=s' => \$orig_master_host,

'orig_master_ip=s' => \$orig_master_ip,

'orig_master_port=i' => \$orig_master_port,

'orig_master_user=s' => \$orig_master_user,

'orig_master_password=s' => \$orig_master_password,

'orig_master_ssh_user=s' => \$orig_master_ssh_user,

'new_master_host=s' => \$new_master_host,

'new_master_ip=s' => \$new_master_ip,

'new_master_port=i' => \$new_master_port,

'new_master_user=s' => \$new_master_user,

'new_master_password=s' => \$new_master_password,

'new_master_ssh_user=s' => \$new_master_ssh_user,

);

my $vip = '10.0.0.237';

my $key = '1';

my $ssh_start_vip = "sudo /sbin/ifconfig eth0:$key $vip netmask 255.255.255.255";

my $ssh_stop_vip = "sudo /sbin/ifconfig eth0:$key down";

exit &main();

sub current_time_us {

my ( $sec, $microsec ) = gettimeofday();

my $curdate = localtime($sec);

return $curdate . " " . sprintf( "%06d", $microsec );

}

sub sleep_until {

my $elapsed = tv_interval($_tstart);

if ( $_running_interval > $elapsed ) {

sleep( $_running_interval - $elapsed );

}

sub get_threads_util {

my $dbh = shift;

my $my_connection_id = shift;

my $running_time_threshold = shift;

my $type = shift;

$running_time_threshold = 0 unless ($running_time_threshold);

$type = 0 unless ($type);

my @threads;

my $sth = $dbh->prepare("SHOW PROCESSLIST");

$sth->execute();

while ( my $ref = $sth->fetchrow_hashref() ) {

my $id = $ref->{Id};

my $user = $ref->{User};

my $host = $ref->{Host};

my $command = $ref->{Command};

my $state = $ref->{State};

my $query_time = $ref->{Time};

my $info = $ref->{Info};

$info =~ s/^\s*(.*?)\s*$/$1/ if defined($info);

next if ( $my_connection_id == $id );

next if ( defined($query_time) && $query_time < $running_time_threshold );

next if ( defined($command) && $command eq "Binlog Dump" );

next if ( defined($user) && $user eq "system user" );

if ( defined($command)

&& $command eq "Sleep"

&& defined($query_time)

&& $query_time >= 1 );

if ( $type >= 1 ) {

next if ( defined($command) && $command eq "Sleep" );

next if ( defined($command) && $command eq "Connect" );

}

if ( $type >= 2 ) {

next if ( defined($info) && $info =~ m/^select/i );

next if ( defined($info) && $info =~ m/^show/i );

}

push @threads, $ref;

}

return @threads;

}

sub main {

if ( $command eq "stop" ) {

## Gracefully killing connections on the current master

# 1. Set read_only= 1 on the new master

# 2. DROP USER so that no app user can establish new connections

# 3. Set read_only= 1 on the current master

# 4. Kill current queries

# * Any database access failure will result in script die.

my $exit_code = 1;

eval {

## Setting read_only=1 on the new master (to avoid accident)

my $new_master_handler = new MHA::DBHelper();

# args: hostname, port, user, password, raise_error(die_on_error)_or_not

$new_master_handler->connect( $new_master_ip, $new_master_port,

$new_master_user, $new_master_password, 1 );

print current_time_us() . " Set read_only on the new master.. ";

$new_master_handler->enable_read_only();

if ( $new_master_handler->is_read_only() ) {

print "ok.\n";

}

else {

die "Failed!\n";

}

$new_master_handler->disconnect();

# Connecting to the orig master, die if any database error happens

my $orig_master_handler = new MHA::DBHelper();

$orig_master_handler->connect( $orig_master_ip, $orig_master_port,

$orig_master_user, $orig_master_password, 1 );

$orig_master_handler->disable_log_bin_local();

## Waiting for N * 100 milliseconds so that current connections can exit

my $time_until_read_only = 15;

$_tstart = [gettimeofday];

my @threads = get_threads_util( $orig_master_handler->{dbh},

$orig_master_handler->{connection_id} );

while ( $time_until_read_only > 0 && $#threads >= 0 ) {

if ( $time_until_read_only % 5 == 0 ) {

printf

"%s Waiting all running %d threads are disconnected.. (max %d milliseconds)\n",

current_time_us(), $#threads + 1, $time_until_read_only * 100;

if ( $#threads < 5 ) {

print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"

foreach (@threads);

}

sleep_until();

$_tstart = [gettimeofday];

$time_until_read_only--;

@threads = get_threads_util( $orig_master_handler->{dbh},

$orig_master_handler->{connection_id} );

}

## Setting read_only=1 on the current master so that nobody(except SUPER) can write

print current_time_us() . " Set read_only=1 on the orig master.. ";

$orig_master_handler->enable_read_only();

if ( $orig_master_handler->is_read_only() ) {

print "ok.\n";

}

else {

die "Failed!\n";

}

## Waiting for M * 100 milliseconds so that current update queries can complete

my $time_until_kill_threads = 5;

@threads = get_threads_util( $orig_master_handler->{dbh},

$orig_master_handler->{connection_id} );

while ( $time_until_kill_threads > 0 && $#threads >= 0 ) {

if ( $time_until_kill_threads % 5 == 0 ) {

printf

"%s Waiting all running %d queries are disconnected.. (max %d milliseconds)\n",

current_time_us(), $#threads + 1, $time_until_kill_threads * 100;

if ( $#threads < 5 ) {

print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"

foreach (@threads);

}

sleep_until();

$_tstart = [gettimeofday];

$time_until_kill_threads--;

@threads = get_threads_util( $orig_master_handler->{dbh},

$orig_master_handler->{connection_id} );

}

## Terminating all threads

print current_time_us() . " Killing all application threads..\n";

$orig_master_handler->kill_threads(@threads) if ( $#threads >= 0 );

print current_time_us() . " done.\n";

$orig_master_handler->enable_log_bin_local();

$orig_master_handler->disconnect();

print "Disabling the VIP on old master: $orig_master_host \n";

&stop_vip();

## After finishing the script, MHA executes FLUSH TABLES WITH READ LOCK

$exit_code = 0;

};

if ($@) {

warn "Got Error: $@\n";

exit $exit_code;

}

exit $exit_code;

}

elsif ( $command eq "start" ) {

## Activating master ip on the new master

# 1. Create app user with write privileges

# 2. Moving backup script if needed

# 3. Register new master's ip to the catalog database

# We don't return error even though activating updatable accounts/ip failed so that we don't interrupt slaves' recovery.

# If exit code is 0 or 10, MHA does not abort

my $exit_code = 10;

eval {

my $new_master_handler = new MHA::DBHelper();

# args: hostname, port, user, password, raise_error_or_not

$new_master_handler->connect( $new_master_ip, $new_master_port,

$new_master_user, $new_master_password, 1 );

## Set read_only=0 on the new master

$new_master_handler->disable_log_bin_local();

print current_time_us() . " Set read_only=0 on the new master.\n";

$new_master_handler->disable_read_only();

$new_master_handler->enable_log_bin_local();

$new_master_handler->disconnect();

print "Enabling the VIP - $vip on the new master - $new_master_host \n";

&start_vip();

## Update master ip on the catalog database, etc

$exit_code = 0;

};

if ($@) {

warn "Got Error: $@\n";

exit $exit_code;

}

exit $exit_code;

}

elsif ( $command eq "status" ) {

# do nothing

exit 0;

}

else {

&usage();

exit 1;

}

sub start_vip() {

return 0 unless ($new_master_ssh_user);

`ssh $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`;

}

sub stop_vip() {

return 0 unless ($orig_master_ssh_user);

`ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;

}

sub usage {

"Usage: master_ip_online_change --command=start|stop|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";

die;

}

send_report 脚本

#!/usr/bin/perl

use strict;

use warnings FATAL => 'all';

use Getopt::Long;

#new_master_host and new_slave_hosts are set only when recovering master succeeded

my ( $dead_master_host, $new_master_host, $new_slave_hosts, $subject, $body, $title, $content);

GetOptions(

'orig_master_host=s' => \$dead_master_host,

'new_master_host=s' => \$new_master_host,

'new_slave_hosts=s' => \$new_slave_hosts,

'subject=s' => \$subject,

'body=s' => \$body,

);

# 调用外部脚本

$title="[mha switch]";

$content="`date +'%Y-%m-%d %H:%M'` old_master=".$dead_master_host." new_master=".$new_master_host;

system("sh /etc/masterha/script/send_report.sh $title $content");

exit 0;

清理中继日志定时任务

下面是我的定时任务，参数自行替换, workdir 需要和中继日志在同一个盘

1 2	# 每小时清理一次 0 * * * * (/usr/bin/purge_relay_logs --user=mha_manager --password=mha_manager --disable_relay_log_purge --port=3306 --workdir=/tmp/relaylogtmp >> /var/log/purge_relay_logs.log 2>&1)

参考

MySQL集群搭建(5)-MHA高可用架构
 MySQL高可用之MHA
MySQL高可用架构之MHA
docker搭建Mysql主从MHA高可用集群
 MySQL高可用架构之MHA

文章 MySQL集群-MHA高可用架构转载需要注明出处

前言

架构选型

架构简介

MHA 怎么保证数据不丢失

MHA 优点和限制

优点

限制

搭建

主机部署

用ssh-keygen实现四台主机之间相互免密钥登录

1.生成密钥

安装mha4mysql-node

安装mha4mysql-manager

建立master，slave1，slave2之间主从复制

修改my.cnf文件

配置mha

测试

手动切换主从

注意

宕机的MySQL重回集群

从库不能写数据

master_ip_failover 脚本

master_ip_online_change 脚本

send_report 脚本

清理中继日志定时任务

参考

您必须 登录 才能发表评论！

您必须登录才能发表评论！