一.MHA是什么？能干什么的
(1)以Perl语言写的一套Mysql故障切换方案，一个脚本管理工具
(2)保障数据库的高可用性
(3)修复多个slave之间的差异日志，最终使所有的slave保持数据一致
(4)能在0-30秒内选定新Master，实现mysql故障转移
二.MHA组成部分
MHA manager 管理节点
可以部署在一台单独的机器上，管理多套一主多从的集群，也可以部署在一台slave之上，作用：
(1)运行一些管理和监控工具
(2使用masterha_manager工具自动监控mysql master和实现master故障切换
(3以及手工实现master故障切换、在线master转移、连接检查等
MHA node 数据节点
必须部署在每个独立的Mysql服务器之上，包括master和slave，作用：
(1)保存二进制日志binlog
如果能够访问故障的master，即mysqld挂了，ssh还能连接OS，拷贝master的二进制日志
(2)应用差异中继日志
从拥有最新日志的slave上生成差异中继日志，然后应用差异日志
(3)清除中继日志
在不停止SQL线程的情况下删除中继日志
三.工作原理
(1)当master出现故障时，通过对比slave之间的IO线程读取master binlog的位置，选取最接近的slave作为latest slave
(2)其他slave通过与latest slave对比，生成差异中继日志
(3)在latest slave上应用故障master中保存的binlog(如果能访问ssh)，同时将latest slave提升为master
(4)最后在其他slave上应用差异中继日志并开始从新的master进行主从复制
四.工作流程
(1)从宕机崩溃的master保存二进制日志事件(binlog events)---binlog冗余方案？
(2)识别含有最新更新的slave---如何识别？Master_Log_File、Read_Master_Log_Pos
(3)应用差异中继日志到其他slave---所有slave保持一致
(4)应用从master保存的二进制日志事件---有差异则补差异
(5)提升一个slave作为新的master，并记录binlog file和position
(6)使其他的slave连接新的master进行复制
(7)完成manager主进程offline
五.MHA工具
Manager工具：
masterha_check_ssh 检查MHA的ssh配置
masterha_check_repl 检查mysql复制状态工具
masterha_manager 启动MHA进程
masterha_check_status 检查当前MHA运行状态
masterha_master_monitor 监控master是否宕机
masterha_master_switch 自动或者手动控制故障转移
masterha_conf_host 添加或删除配置的server信息
Node工具
save_binary_logs 保存和复制master的二进制日志
apply_diff_relay_logs 识别差异的中继日志事件并应用于其他salve
filter_mysqlbinlog 去除不必要的rollback事件(obsolete)
purge_relay_logs 清楚中继日志(不会阻塞SQL线程)
六.boost方式安装mysql5.7并配置主从
(1)系统环境
centos7.1
mysql-boost-5.7.21.tar.gz
1主2从
master 172.16.3.155
slave01 172.16.3.153
slave02 172.16.3.154
MHA 172.16.3.152
(2)系统前置配置
1.SSH关闭dns
[root@master ~]# vi /etc/ssh/sshd_config
UseDNS no
[root@master ~]# systemctl restart sshd
[root@master ~]#
2.关闭selinux
[root@master ~]# vi /etc/selinux/config
SELINUX=permissive
[root@master ~]# setenforce 0
[root@master ~]# getenforce
Permissive
3.设置防火墙信任
[root@master ~]# firewall-cmd --set-default-zone=trusted
success
4.设置文件和资源使用
[root@master ~]# vi /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
* soft nproc 65535
* hard nproc 65535
5.设置虚拟内存使用
[root@master ~]# sysctl -a |grep swappiness
vm.swappiness = 30
[root@master ~]# sysctl vm.swappiness=10
vm.swappiness = 10
[root@master ~]# echo 10 > /proc/sys/vm/swappiness
[root@master ~]# sysctl -a |grep swappiness
vm.swappiness = 10
[root@master ~]# echo "vm.swappiness=10" > /etc/sysctl.conf
6.安装YUM源
wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum install -y mysql57-community-release-el7-11.noarch.rpm
yum install get bash-completion.noarch lrzsz -y
7.安装依赖包
yum install -y gcc gcc-c++ ncurses-devel bison zlib libxml openssl openssl-devel ncurses
可以一起yum安装cmake：
yum install -y cmake gcc gcc-c++ ncurses-devel bison zlib libxml openssl openssl-devel ncurses
8.编译安装cmake
wget https://cmake.org/files/v3.4/cmake-3.4.1.tar.gz
tar -zxvf cmake-3.4.1.tar.gz
cd cmake-3.4.1
./bootstrap
gmake && make install
(3)安装mysql
1.创建mysql用户和组
groupadd mysql
useradd -r -M -g mysql mysql -s /sbin/nologin
-r创建系统用户，即不创建home目录
2.创建文件存储目录
mkdir -p /mysqlData/{data,tmp,logs/undolog,logs/RELAY_LOG,logs/binlog}
3.下载mysql源码包并解压
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-boost-5.7.16.tar.gz
tar -xvf mysql-boost-5.7.16.tar.gz
cd mysql-5.7.16
4.编译安装mysql
cmake . \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/mysqlData/data \
-DMYSQL_TCP_PORT=3306 \
-DENABLE_DOWNLOADS=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_BOOST=boost
make && make install
编译时指定ssl
cmake . \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/mysqlData/data \
-DMYSQL_TCP_PORT=3316 \
-DENABLE_DOWNLOADS=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_BOOST=boost \
-DWITH_SSL=yes
make && make install
5.my.cnf
6.修改mysql所有相关属主
chown -R mysql:mysql /usr/local/mysql /mysqlData /etc/my.cnf
7.拷贝启动
cp /dvd/mysql-5.7.16/support-files/mysql.server /etc/init.d/mysqld
vi /etc/profile
PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH
export PATH
source /etc/profile
8.初始化mysql数据库
cd /usr/local/mysql
bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
9.开启SSL新功能
bin/mysql_ssl_rsa_setup --defaults-file=/etc/my.cnf --user=mysql
chown -R mysql:mysql /mysqlData/data
为什么编译的时候不开启？如果编译的时候添加了
10.加入自动启动
1.chkconfig启动 6.5
chkconfig mysqld on
2.systemctl启动
cat "/usr/lib/systemd/system/mysqld.service"
[Unit]
Description=MySQL DBMS
[Service]
LimitNOFILE=65535
Type=simple
User=mysql
Group=mysql
PIDFile=/mysqlData/tmp/master.pid
ExecStart=/usr/local/mysql/bin/mysqld_safe --datadir=/mysqlData/data/
ExecStop=/bin/kill -9 $MAINPID
[Install]
WantedBy=multi-user.target
systemctl enable mysqld
systemctl list-unit-files |grep mysqld
11.初始化root密码
alter user 'root'@'localhost' identified by '123456';
12.删除testDB和移除匿名用户
/usr/local/mysql/bin/mysql_secure_installation
(4)配置主从
1.主库创建复制账号
grant replication slave,replication client on *.* to 'repl'@'172.16.3.%' identified by 'Rep12#3@';
2.从库指向master
CHANGE MASTER TO
MASTER_HOST='172.16.3.155',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='Rep12#3@',
master_auto_position=1;
记得更改server_id
MASTER_LOG_FILE='binlog.00000x',
MASTER_LOG_POS=xxx;
3.从库启动复制
start slave;
show slave status\G
set global read_only=1;
4.master.info和relay-log.info
master和relay-log的信息可以存放在文件FILE，也可以存放在table中
以明文方式存储
查看复制信息存储方式
SHOW VARIABLES WHERE variable_name IN ('relay_log_recovery','relay_log_info_repository','master_info_repository');
修改存储方式为table
#命令号临时修改
stop slave;
set GLOBAL relay_log_info_repository='TABLE';
#my.cnf修改重启生效
relay_log_info_repository = TABLE
master_info_repository = TABLE
relay_log_recovery = on
SQL查看主从同步方式
select * from mysql.slave_master_info;
select * from mysql.slave_relay_log_info;
(5)启动半同步复制
1.加入semi参数
###################semi sync replication settings##############
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 5000 # 5 second
2.master安装半同步插件
root@master 15:36: [(none)]> show global variables like '%rpl%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| rpl_stop_slave_timeout | 31536000 |
+------------------------+----------+
1 row in set (0.01 sec)
root@master 15:36: [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.04 sec)
root@master 15:36: [(none)]> show global variables like '%rpl%';
+-------------------------------------------+------------+
| 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_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
7 rows in set (0.01 sec)
3.slave安装半同步插件
root@slave01 15:44: [(none)]> show variables like '%rpl%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| rpl_stop_slave_timeout | 31536000 |
+------------------------+----------+
1 row in set (0.00 sec)
root@slave01 15:44: [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
root@slave01 15:44: [(none)]> show variables like '%rpl%';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
3 rows in set (0.01 sec)
4.master启用半同步插件
root@master 15:37: [(none)]> set global rpl_semi_sync_master_enabled=on;
Query OK, 0 rows affected (0.00 sec)
root@master 15:38: [(none)]> show global variables like '%rpl%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| 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_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
7 rows in set (0.00 sec)
5.slave启用半同步插件
root@slave01 15:55: [(none)]> set global rpl_semi_sync_slave_enabled=on;
Query OK, 0 rows affected (0.00 sec)
root@slave01 15:55: [(none)]> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.00 sec)
6.master 配置半同步超时
root@master 15:38: [(none)]> set global rpl_semi_sync_master_timeout = 5000;
Query OK, 0 rows affected (0.00 sec)
root@master 15:38: [(none)]> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| 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 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
Rpl_semi_sync_master_status | ON 表示启用半同步
Rpl_semi_sync_master_clients | 0 表示启用半同步的从库有几个
7.重启mysqld
插件目录位置：/usr/local/mysql/lib/plugin
master 安装semisync_master.so
slave 装semisync_slave.so
master my.cnf启用：rpl_semi_sync_master_enabled = 1，默认10秒超时，10秒超时转为异步同步
slave my.cnf启用：rpl_semi_sync_slave_enabled=on
8.slave查看半同步状态
slave01
root@slave01 16:14: [(none)]> show variables like '%rpl%';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
3 rows in set (0.00 sec)
slave02
root@slave01 16:13: [(none)]> show variables like '%rpl%';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
3 rows in set (0.00 sec)
9.因为MHA切换之后master和slave会互换角色，因此所有的msyql节点都需要安装半同步插件
master
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_master_enabled=on ;
slave
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled=on ;
10.semi解释
Rpl_semi_sync_master_clients # 显示成有多少个从服务器配置成了半同步复制模式
Rpl_semi_sync_master_net_avg_wait_time #平均等待时间(默认毫秒)
Rpl_semi_sync_master_net_wait_time #总共等待时间
Rpl_semi_sync_master_net_waits #等待次数
Rpl_semi_sync_master_no_times #关闭半同步复制的次数
Rpl_semi_sync_master_no_tx #显示从服务器确认的不成功提交数量
Rpl_semi_sync_master_status #表示当前是异步模式还是半同步模式，on为半同步
Rpl_semi_sync_master_timefunc_failures #调用时间函数失败的次数
Rpl_semi_sync_master_tx_avg_wait_time #事务的平均传输时间
Rpl_semi_sync_master_tx_wait_time #事务的总共传输时间
Rpl_semi_sync_master_tx_waits #事务等待次数
Rpl_semi_sync_master_wait_pos_backtraverse # 网上有人理解为"后来的先到了，而先来的还没有到的次数"
Rpl_semi_sync_master_wait_sessions #当前有多少个session因为slave的回复而造成等待
Rpl_semi_sync_master_yes_tx #显示从服务器确认的成功提交数量
七.安装MHA
centos7.1
mysql-boost-5.7.21.tar.gz
1主2从
master 172.16.3.155
slave01 172.16.3.153
slave02 172.16.3.154
1.所有节点安装perl依赖包
yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes cpan perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
2.manager节点安装工具
wget https://raw.githubusercontent.com/yotoobo/linux/master/mha/mha4mysql-manager-0.56.tar.gz
tar xzvf mha4mysql-manager-0.56.tar.gz
cd mha4mysql-manager-0.56
perl Makefile.PL
make && make install
命令文件：
Installing /usr/local/bin/masterha_stop
Installing /usr/local/bin/masterha_conf_host
Installing /usr/local/bin/masterha_check_repl
Installing /usr/local/bin/masterha_check_status
Installing /usr/local/bin/masterha_master_monitor
Installing /usr/local/bin/masterha_check_ssh
Installing /usr/local/bin/masterha_master_switch
Installing /usr/local/bin/masterha_secondary_check
Installing /usr/local/bin/masterha_manager
3.node节点安装工具(所有节点，包括manager节点)
wget https://raw.githubusercontent.com/yotoobo/linux/master/mha/mha4mysql-node-0.56.tar.gz
tar xzvf mha4mysql-node-0.56.tar.gz
cd mha4mysql-node-0.56
perl Makefile.PL
make && make install
yum安装
# yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
# #根据MHA角色安装对应的软件包即可
# yum -y --nogpgcheck install mha4mysql-node-0.57-0.el7.noarch.rpm
# yum -y install --nogpgcheck mha4mysql-manager-0.57-0.el7.noarch.rpm
本地安装rpm
# yum -y --nogpgcheck install perl-DBD-MySQL*
# yum -y --nogpgcheck install perl-Config-Tiny*
# yum -y --nogpgcheck install perl-Parallel-ForkManager*
# yum -y --nogpgcheck install perl-MailTools*
# yum -y --nogpgcheck install perl-Email-Date-Format*
# yum -y --nogpgcheck install perl-Mail-Sender*
# yum -y --nogpgcheck install perl-MIME-Types*
# yum -y --nogpgcheck install perl-MIME-Lite*
# yum -y --nogpgcheck install perl-Mail-Sendmail*
# yum -y --nogpgcheck install perl-Log-Dispatch*
# #根据MHA角色安装对应的软件包即可
# yum -y --nogpgcheck install mha4mysql-node-0.57-0.el7.noarch.rpm
# yum -y install --nogpgcheck mha4mysql-manager-0.57-0.el7.noarch.rpm
作者：BruceLiu1
链接：https://www.jianshu.com/p/6173dae5ed7a
4.在master上创建MHA用户
grant all privileges on *.* to 'mha'@'172.16.3.%' identified by 'Mha12#3@';
5.7更改密码
update mysql.user set authentication_string=password('') where User=”test” and Host=”localhost”
flush privileges;
5.节点间配置SSH互信
(1)基于mysql用户，配置mysql用户的sudo权限
添加普通用户登录tty终端权限
vi /etc/sudoers
#Defaults requiretty
开放普通用户执行sudo权限
vi /etc/sudoers.d/mysql
User_Alias MYSQL_USERS = ALL
Runas_Alias MYSQL_RUNAS = root
Cmnd_Alias MYSQL_CMNDS = ALL
MYSQL_USERS ALL = (MYSQL_RUNAS) NOPASSWD: MYSQL_CMNDS
(2)配置rsa免密码登录
要求：
1.mha管理节点需要ssh免密码到所有mysql节点
2.各个mysql节点需要互相ssh免密码
配置：
mha管理节点：
# 管理节点生成公私钥
ssh-keygen -t rsa
# 将公钥拷贝到其他3个节点
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.155
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.154
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.153
mysql节点各自生成公私钥，并将公钥拷贝给其他mysql节点
# 172.16.3.155
ssh-keygen -t rsa
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.154
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.153
# 172.16.3.154
ssh-keygen -t rsa
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.155
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.153
# 172.16.3.153
ssh-keygen -t rsa
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.155
ssh-copy-id -i /home/mysql/.ssh/id_rsa.pub mysql@172.16.3.154
测试ssh是否免密码登录
6.配置MHA配置
(1)manager上创建配置目录和配置文件
vi /mysqlData/mha/etc/app.cnf
[server default]
user = mha
password = Mha12#3@
ssh_user = root
repl_user = repl
repl_password = Rep12#3@
ping_interval = 1
ping_type = SELECT
manager_workdir=/mysqlData/mha/etc/app
manager_log=/mysqlData/mha/log/manager.log
remote_workdir=/mysqlData/mha/etc/app
master_binlog_dir="/mysqlData/logs/binlog/"
secondary_check_script="masterha_secondary_check -s 172.16.3.151 -s 172.16.3.151"
master_ip_failover_script="/mysqlData/mha/etc/master_ip_failover"
master_ip_online_change_script="/mysqlData/mha/etc/master_ip_failover"
shutdown_script=""
report_script=""
#check_repl_delay=0
[server1]
hostname=master
port=3306
master_binlog_dir="/mysqlData/logs/binlog/"
candidate_master=1
ignore_fail=1
[server2]
hostname=slave01
port=3306
master_binlog_dir="/mysqlData/logs/binlog/"
candidate_master=1
ignore_fail=1
[server3]
hostname=slave02
port=3306
master_binlog_dir="/mysqlData/logs/binlog/"
candidate_master=0
ignore_fail=1
vim /mysqlData/mha/etc/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '172.16.3.151'; # Virtual IP
my $gateway = '172.16.3.154'; # virtual IP gateway
my $key = "1";
my $int = "eth0";
my $ssh_start_vip = "/sbin/ifconfig $int:$key $vip/24";
my $ssh_stop_vip = "/sbin/ifconfig $int:$key down";
my $arp_effect = "/sbin/arping -f -q -c 5 -w 5 -I $int -s $vip -U $gateway"; # Virtual IP and gateway
#my $test = "echo successfull >/tmp/test.txt";
$ssh_user = "root";
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,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_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();
$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 {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
#`ssh $ssh_user\@cluster1 \" $ssh_start_vip \"`;
&status();
exit 0;
}
else {
&usage();
exit 1;
}
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
`ssh $ssh_user\@$new_master_host \" $arp_effect \"`;
# `ssh $ssh_user\@$new_master_host \" $test \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub status() {
print `ssh $ssh_user\@$orig_master_host \" ip add show $int \"`;
}
sub usage {
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_maste
r_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
(2)启动MHA管理节点
/usr/local/bin/masterha_manager --conf=/mysqlData/mha/etc/app.cnf &
/usr/local/bin/masterha_manager --conf=/mysqlData/mha/etc/app.cnf --ignore_last_failover &
1.报错
Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).
2.检查
masterha_check_ssh --conf=/mysqlData/mha/etc/app.cnf
3.检查结果
from mysql@master(172.16.3.155:22) to mysql@slave01(172.16.3.153:22)
from mysql@slave01(172.16.3.153:22) to mysql@master(172.16.3.155:22)
from mysql@slave02(172.16.3.154:22) to mysql@master(172.16.3.155:22)
4.很奇怪，测试是可免密码ssh的
# master to slave01
[mysql@master ~]$ ssh slave01
Last login: Tue Mar 6 14:13:57 2018 from master
[mysql@slave01 ~]$ exit
# slave01 to master
[mysql@slave01 ~]$ ssh master
Last login: Tue Mar 6 14:14:29 2018 from master
[mysql@master ~]$
# slave02 to master
[mysql@slave02 ~]$ ssh master
Last login: Tue Mar 6 16:40:12 2018 from slave01
[mysql@master ~]$
5.mysql节点重新设置权限也不行
chown -R mysql:mysql /home/mysql
chmod 700 /home/mysql
chmod 700 /home/mysql/.ssh
chmod 644 /home/mysql/.ssh/authorized_keys
chmod 600 /home/mysql/.ssh/id_rsa
ssh_user改成root用户
此时VIP不会自己起来，作为一个后安装的管理工具，不会去更改现有的架构
7.在master上启动vip
# /sbin/ifconfig eth0:1 172.16.3.151 broadcast 172.16.3.255 netmask 255.255.255.0
# /sbin/arping -f -q -c 5 -w 5 -I eth0 -s 172.16.3.151 -U 172.16.3.254
8.MHA常用命令
# 检查ssh是成功免密码登录
/usr/local/bin/masterha_check_ssh --conf=/mysqlData/mha/etc/app.cnf
# 检查主从复制是否正常
/usr/local/bin/masterha_check_repl --conf=/mysqlData/mha/etc/app.cnf
# 启动MHA进程
/usr/local/bin/masterha_manager --conf=/mysqlData/mha/etc/app.cnf &
# 检查MHA(app)状态
/usr/local/bin/masterha_check_status --conf=/mysqlData/mha/etc/app.cnf
# 停止MHA进程
/usr/local/bin/masterha_stop --conf=/mysqlData/mha/etc/app.cnf
八.模拟主库宕机failover
1.切换VIP
(1)主库kill进程
[root@master ~]# killall -r mysqld
[root@master ~]# ps -ef|grep mysql
root 1465 10243 0 17:13 pts/1 00:00:00 grep --color=auto mysql
(2)查看mha日志
Tue Mar 6 17:13:16 2018 - [info] Master failover to slave01(172.16.3.153:3306) completed successfully.
Tue Mar 6 17:13:16 2018 - [info]
#
----- Failover Report -----
#
app: MySQL Master failover master(172.16.3.155:3306) to slave01(172.16.3.153:3306) succeeded
#
Master master(172.16.3.155:3306) is down!
#
Check MHA Manager logs at mha:/mysqlData/mha/log/manager.log for details.
#
Started automated(non-interactive) failover.
Invalidated master IP address on master(172.16.3.155:3306)
Selected slave01(172.16.3.153:3306) as a new master.
slave01(172.16.3.153:3306): OK: Applying all logs succeeded.
slave01(172.16.3.153:3306): OK: Activated master IP address.
slave02(172.16.3.154:3306): OK: Slave started, replicating from slave01(172.16.3.153:3306)
slave01(172.16.3.153:3306): Resetting slave info succeeded.
Master failover to slave01(172.16.3.153:3306) completed successfully.
(3)vip
[root@mha log]# cat manager.log |grep -i vip
Disabling the VIP on old master: master
Enabling the VIP - 172.16.3.151/24 on the new master - slave01
(4)slave01上已经看到VIP过来了
[root@slave01 home]# ip a
1: lo: mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: mtu 1500 qdisc mq state UP qlen 1000
link/ether 00:50:56:a3:30:bb brd ff:ff:ff:ff:ff:ff
inet 172.16.3.153/24 brd 172.16.3.255 scope global eth0
valid_lft forever preferred_lft forever
inet 172.16.3.151/24 brd 172.16.3.255 scope global secondary eth0:1
valid_lft forever preferred_lft forever
inet6 fe80::250:56ff:fea3:30bb/64 scope link
valid_lft forever preferred_lft forever
2.将原来的master配置成slave
(1)查看新的master(slave01)
root@slave01 17:18: [(none)]> show master status\G
*************************** 1. row ***************************
File: binlog.000130
Position: 1898
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 73c029dc-2034-11e8-90a5-005056a365b6:1-12,
856d79f8-2038-11e8-b511-005056a330bb:1-3,
b658767f-2044-11e8-951f-005056a330bb:1
1 row in set (0.00 sec)
root@slave01 17:18: [(none)]> select @@read_only;
+-------------+
| @@read_only |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
(2)开启新的slave(master)，打开MySQL
[root@master ~]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
(3)检查数据库
root@master 17:21: [(none)]> show master status\G
*************************** 1. row ***************************
File: binlog.000010
Position: 194
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 73c029dc-2034-11e8-90a5-005056a365b6:1-12
1 row in set (0.00 sec)
#
root@master 17:21: [(none)]> show slave status\G
Empty set (0.00 sec)
(4)在管理节点日志中查主库的日志文件和位置
[root@mha log]# cat manager.log |grep -i change
Tue Mar 6 17:13:11 2018 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='slave01 or 172.16.3.153', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Tue Mar 6 17:13:14 2018 - [info] Executed CHANGE MASTER.
(5)在slave连接master
CHANGE MASTER TO
MASTER_HOST='172.16.3.153',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_USER='repl',
MASTER_PASSWORD='Rep12#3@';
(6)查看状态
root@master 17:27: [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.3.153
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000130
Read_Master_Log_Pos: 1898
Relay_Log_File: relaylog.000130
Relay_Log_Pos: 485
Relay_Master_Log_File: binlog.000130
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
(7)启动管理节点
/usr/local/bin/masterha_manager --conf=/mysqlData/mha/etc/app.cnf &
# 跳过切换错误启动
/usr/local/bin/masterha_manager --conf=/mysqlData/mha/etc/app.cnf --ignore_last_failover &
九.线上切换switchover
1.master：关闭event_scheduler(即172.16.3.153)set global event_scheduler=off;
root@slave01 17:29: [(none)]> show variables like '%event_schedu%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.02 sec)
本来就是OFF的，如果不是off，切换的时候报错如下
Wed Mar 7 11:24:41 2018 - [error][/usr/local/share/perl5/MHA/MasterRotate.pm, ln161] We should not start online master switch when one of connections are running long updates on the current master(slave01(172.16.3.153:3306)). Currently 1 update thread(s) are running.
Details:
{'Time' => '453','db' => undef,'Id' => '101','User' => 'event_scheduler','State' => 'Waiting on empty queue','Command' => 'Daemon','Info' => undef,'Host' => 'localhost'}
Wed Mar 7 11:24:41 2018 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR: at /usr/local/bin/masterha_master_switch line 53.
2.manager：关闭管理进程 (即172.16.3.152)
/usr/local/bin/masterha_stop --conf=/mysqlData/mha/etc/app.cnf
日志
Tue Mar 6 17:33:01 2018 - [info] Got terminate signal. Exit.
3.manager：检查配置文件
[server1]
hostname=master
port=3306
master_binlog_dir="/mysqlData/logs/binlog/"
candidate_master=1
ignore_fail=1
#
[server2]
hostname=slave01
port=3306
master_binlog_dir="/mysqlData/logs/binlog/"
#candidate_master=1
#ignore_fail=1
#
[server3]
hostname=slave02
port=3306
master_binlog_dir="/mysqlData/logs/binlog/"
candidate_master=0
ignore_fail=1
/mysqlData/mha/etc/app.cnf 有没有被修改破坏。如果破坏需要重新编辑正确配置文件：/mysqlData/mha/etc/app.cnf
cp /mysqlData/mha/etc/app.cnf.bak /mysqlData/mha/etc/app.cnf
server1现在是从库
4.开始切换：/usr/local/bin/masterha_master_switch --master_state=alive --conf=/mysqlData/mha/etc/app.cnf
[root@mha mysqlData]# /usr/local/bin/masterha_master_switch --master_state=alive --conf=/mysqlData/mha/etc/app.cnf
Tue Mar 6 17:35:20 2018 - [info] MHA::MasterRotate version 0.56.
Tue Mar 6 17:35:20 2018 - [info] Starting online master switch..
Tue Mar 6 17:35:20 2018 - [info]
Tue Mar 6 17:35:20 2018 - [info] * Phase 1: Configuration Check Phase..
Tue Mar 6 17:35:20 2018 - [info]
Tue Mar 6 17:35:20 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Mar 6 17:35:20 2018 - [info] Reading application default configuration from /mysqlData/mha/etc/app.cnf..
Tue Mar 6 17:35:20 2018 - [info] Reading server configuration from /mysqlData/mha/etc/app.cnf..
Tue Mar 6 17:35:21 2018 - [info] GTID failover mode = 1
Tue Mar 6 17:35:21 2018 - [info] Current Alive Master: slave01(172.16.3.153:3306)
Tue Mar 6 17:35:21 2018 - [info] Alive Slaves:
Tue Mar 6 17:35:21 2018 - [info] master(172.16.3.155:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled
Tue Mar 6 17:35:21 2018 - [info] GTID ON
Tue Mar 6 17:35:21 2018 - [info] Replicating from 172.16.3.153(172.16.3.153:3306)
Tue Mar 6 17:35:21 2018 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Mar 6 17:35:21 2018 - [info] slave02(172.16.3.154:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled
Tue Mar 6 17:35:21 2018 - [info] GTID ON
Tue Mar 6 17:35:21 2018 - [info] Replicating from 172.16.3.153(172.16.3.153:3306)
#
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on slave01(172.16.3.153:3306)? (YES/no): YES
Tue Mar 6 17:35:40 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Tue Mar 6 17:35:40 2018 - [info] ok.
Tue Mar 6 17:35:40 2018 - [info] Checking MHA is not monitoring or doing failover..
Tue Mar 6 17:35:40 2018 - [info] Checking replication health on master..
Tue Mar 6 17:35:40 2018 - [info] ok.
Tue Mar 6 17:35:40 2018 - [info] Checking replication health on slave02..
Tue Mar 6 17:35:40 2018 - [info] ok.
Tue Mar 6 17:35:40 2018 - [info] Searching new master from slaves..
Tue Mar 6 17:35:40 2018 - [info] Candidate masters from the configuration file:
Tue Mar 6 17:35:40 2018 - [info] master(172.16.3.155:3306) Version=5.7.16-log (oldest major version between slaves) log-bin:enabled
Tue Mar 6 17:35:40 2018 - [info] GTID ON
Tue Mar 6 17:35:40 2018 - [info] Replicating from 172.16.3.153(172.16.3.153:3306)
Tue Mar 6 17:35:40 2018 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Mar 6 17:35:40 2018 - [info] Non-candidate masters:
Tue Mar 6 17:35:40 2018 - [info] Searching from candidate_master slaves which have received the latest relay log events..
Tue Mar 6 17:35:40 2018 - [info]
From:
slave01(172.16.3.153:3306) (current master)
+--master(172.16.3.155:3306)
+--slave02(172.16.3.154:3306)
#
To:
master(172.16.3.155:3306) (new master)
+--slave02(172.16.3.154:3306)
#
Starting master switch from slave01(172.16.3.153:3306) to master(172.16.3.155:3306)? (yes/NO): yes
Tue Mar 6 17:35:48 2018 - [info] Checking whether master(172.16.3.155:3306) is ok for the new master..
Tue Mar 6 17:35:48 2018 - [info] ok.
Tue Mar 6 17:35:48 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Tue Mar 6 17:35:48 2018 - [info]
Tue Mar 6 17:35:48 2018 - [info] * Phase 2: Rejecting updates Phase..
Tue Mar 6 17:35:48 2018 - [info]
Tue Mar 6 17:35:48 2018 - [info] Executing master ip online change script to disable write on the current master:
Tue Mar 6 17:35:48 2018 - [info] /mysqlData/mha/etc/master_ip_failover --command=stop --orig_master_host=slave01 --orig_master_ip=172.16.3.153 --orig_master_port=3306 --orig_master_user='mha' --orig_master_password='Mha12\#3@' --new_master_host=master --new_master_ip=172.16.3.155 --new_master_port=3306 --new_master_user='mha' --new_master_password='Mha12\#3@' --orig_master_ssh_user=root --new_master_ssh_user=root
Unknown option: orig_master_user
Unknown option: orig_master_password
Unknown option: new_master_user
Unknown option: new_master_password
Unknown option: orig_master_ssh_user
Unknown option: new_master_ssh_user
#
#
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.3.151/24===
#
Disabling the VIP on old master: slave01
mysql@slave01's password:
SIOCSIFFLAGS: Operation not permitted
Tue Mar 6 17:35:53 2018 - [info] ok.
Tue Mar 6 17:35:53 2018 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Tue Mar 6 17:35:53 2018 - [info] Executing FLUSH TABLES WITH READ LOCK..
Tue Mar 6 17:35:53 2018 - [info] ok.
Tue Mar 6 17:35:53 2018 - [info] Orig master binlog:pos is binlog.000130:1898.
Tue Mar 6 17:35:53 2018 - [info] Waiting to execute all relay logs on master(172.16.3.155:3306)..
Tue Mar 6 17:35:53 2018 - [info] master_pos_wait(binlog.000130:1898) completed on master(172.16.3.155:3306). Executed 0 events.
Tue Mar 6 17:35:53 2018 - [info] done.
Tue Mar 6 17:35:53 2018 - [info] Getting new master's binlog name and position..
Tue Mar 6 17:35:53 2018 - [info] binlog.000010:994
Tue Mar 6 17:35:53 2018 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='master or 172.16.3.155', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Tue Mar 6 17:35:53 2018 - [info] Executing master ip online change script to allow write on the new master:
Tue Mar 6 17:35:53 2018 - [info] /mysqlData/mha/etc/master_ip_failover --command=start --orig_master_host=slave01 --orig_master_ip=172.16.3.153 --orig_master_port=3306 --orig_master_user='mha' --orig_master_password='Mha12\#3@' --new_master_host=master --new_master_ip=172.16.3.155 --new_master_port=3306 --new_master_user='mha' --new_master_password='Mha12\#3@' --orig_master_ssh_user=root --new_master_ssh_user=root
Unknown option: orig_master_user
Unknown option: orig_master_password
Unknown option: new_master_user
Unknown option: new_master_password
Unknown option: orig_master_ssh_user
Unknown option: new_master_ssh_user
#
#
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.16.3.151/24===
#
Enabling the VIP - 172.16.3.151/24 on the new master - master
mysql@master's password:
SIOCSIFADDR: Operation not permitted
SIOCSIFFLAGS: Operation not permitted
SIOCSIFNETMASK: Operation not permitted
mysql@master's password:
bind: Cannot assign requested address
Tue Mar 6 17:35:58 2018 - [info] ok.
Tue Mar 6 17:35:58 2018 - [info]
Tue Mar 6 17:35:58 2018 - [info] * Switching slaves in parallel..
Tue Mar 6 17:35:58 2018 - [info]
Tue Mar 6 17:35:58 2018 - [info] -- Slave switch on host slave02(172.16.3.154:3306) started, pid: 3631
Tue Mar 6 17:35:58 2018 - [info]
Tue Mar 6 17:36:00 2018 - [info] Log messages from slave02 ...
Tue Mar 6 17:36:00 2018 - [info]
Tue Mar 6 17:35:58 2018 - [info] Waiting to execute all relay logs on slave02(172.16.3.154:3306)..
Tue Mar 6 17:35:58 2018 - [info] master_pos_wait(binlog.000130:1898) completed on slave02(172.16.3.154:3306). Executed 0 events.
Tue Mar 6 17:35:58 2018 - [info] done.
Tue Mar 6 17:35:58 2018 - [info] Resetting slave slave02(172.16.3.154:3306) and starting replication from the new master master(172.16.3.155:3306)..
Tue Mar 6 17:35:58 2018 - [info] Executed CHANGE MASTER.
Tue Mar 6 17:35:59 2018 - [info] Slave started.
Tue Mar 6 17:36:00 2018 - [info] End of log messages from slave02 ...
Tue Mar 6 17:36:00 2018 - [info]
Tue Mar 6 17:36:00 2018 - [info] -- Slave switch on host slave02(172.16.3.154:3306) succeeded.
Tue Mar 6 17:36:00 2018 - [info] Unlocking all tables on the orig master:
Tue Mar 6 17:36:00 2018 - [info] Executing UNLOCK TABLES..
Tue Mar 6 17:36:00 2018 - [info] ok.
Tue Mar 6 17:36:00 2018 - [info] All new slave servers switched successfully.
Tue Mar 6 17:36:00 2018 - [info]
Tue Mar 6 17:36:00 2018 - [info] * Phase 5: New master cleanup phase..
Tue Mar 6 17:36:00 2018 - [info]
Tue Mar 6 17:36:00 2018 - [info] master: Resetting slave info succeeded.
Tue Mar 6 17:36:00 2018 - [info] Switching master to master(172.16.3.155:3306) completed successfully.
5.new master(old slave)
root@master 17:36: [(none)]> show master status\G
*************************** 1. row ***************************
File: binlog.000010
Position: 994
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 73c029dc-2034-11e8-90a5-005056a365b6:1-12,
856d79f8-2038-11e8-b511-005056a330bb:1-3,
b658767f-2044-11e8-951f-005056a330bb:1
1 row in set (0.00 sec)
6.new slave(old master)
日志内容
CHANGE MASTER TO MASTER_HOST='master or 172.16.3.155', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx
启动slave复制
root@slave01 17:40: [(none)]> CHANGE MASTER TO
-> MASTER_HOST='172.16.3.155',
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION=1,
-> MASTER_USER='repl',
-> MASTER_PASSWORD='Rep12#3@';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
#
root@slave01 17:40: [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
#
root@slave01 17:42: [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.3.155
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000010
Read_Master_Log_Pos: 994
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 405
Relay_Master_Log_File: binlog.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
7.启动管理节点/usr/local/bin/masterha_manager --conf=/mysqlData/mha/etc/app.cnf &
Tue Mar 6 17:43:45 2018 - [info] OK.
Tue Mar 6 17:43:45 2018 - [warning] shutdown_script is not defined.
Tue Mar 6 17:43:45 2018 - [info] Set master ping interval 1 seconds.
Tue Mar 6 17:43:45 2018 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Tue Mar 6 17:43:45 2018 - [info] Starting ping health check on master(172.16.3.155:3306)..
Tue Mar 6 17:43:45 2018 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
1053
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
