目录
前言
在 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文件中
1 2 3 4 5 6 |
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,都会被允许的
1 2 3 |
$ iptables -L -n|grep 3306 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:3306 |
【4个节点】都配置epel源
1 2 3 4 5 6 7 8 9 10 11 |
# 安装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个节点】执行以下命令:
1 2 3 |
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节点】执行以下命令:
1 2 3 |
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上安装插件需要数据库支持动态载入,检查是否支持,用如下命令检测:
1 2 3 4 5 6 7 |
mysql> show variables like '%have_dynamic%'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | have_dynamic_loading | YES | +----------------------+-------+ 1 row in set (0.01 sec) |
所有MySQL都可以成为主节点,所有mysql 都开启插件
1 2 3 |
install plugin rpl_semi_sync_master soname 'semisync_master.so'; install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; |
检查Plugin是否已正确安装:
1 2 3 |
mysql> show plugins; 或 mysql> select plugin_name from information_schema.plugins; |
查看半同步相关信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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配置:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[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,查看半同步相关信息。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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) |
查看半同步状态
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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'; |
在主库上,查看信息
1 2 3 4 5 6 7 |
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 746 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) |
第一条grant命令是创建一个用于主从复制的帐号,在master和candicate master的主机上创建即可。
第二条grant命令是创建MHA管理账号,所有mysql服务器上都需要执行。MHA会在配置文件里要求能远程登录到数据库,所以要进行必要的赋权。
所有从库链接到主库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
# 从库链接主库 (主库不能两两复制,否则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服务器的半同步状态
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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节点上
1 2 3 4 5 6 7 8 |
# 创建MHA的工作目录 mkdir -p /var/mha/{script} # 创建MHA的配置目录 mkdir /etc/mha # 编辑配置文件 vi /etc/masterha/app1.cnf |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
[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报错,则修改如下:
1 2 3 4 5 6 7 8 9 10 |
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 |
日志文件显示:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
----- 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会自动切换。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
# 停止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;
1 2 3 4 |
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
1 2 3 |
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 脚本
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 { print "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 脚本
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 # Copyright (C) 2011 DeNA Co.,Ltd. # # 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" ); next 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 { print "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 脚本
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
#!/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