mysql高可用（MHA-7）
商业常用EMC 稳定可靠、安全
MMM数据库高可用架构有意外的切换（3个master）
MHA架构
会有一定的浪费
这里进行搭建
因为本人server123虚拟机用到别的，所以这里用server 4 5 6 7虚拟机
master server4
slave1 server5
slave2 server6
monitor server7
server4 5 6都要做好基于GTID的主从复制，作主从同步前要保证数据库一致，可以都清空
主从切换，两个模块都要加载，主从都要加载
alter user [email protected] identified by 'Yakexi_007';
GTID只负责添加后的数据同步，不会同步开启之前的数据。
server4 master
grant all on *.* to [email protected]'%' identified by 'Yakexi+007';
grant replication slave on *.* to [email protected]'%' identified by 'Yakexi+007';
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog
下面三行在装完插件后添加
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=100000
rpl_semi_sync_slave_enabled=1
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_slave_enabled=1;
SET GLOBAL rpl_semi_sync_master_timeout=100000;
show variables like '%rpl%';
show master status\G; #查看
server 5 6
server_id=2 # 3
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
log_bin=binlog
下面三行在装完插件后添加
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=100000
rpl_semi_sync_slave_enabled=1
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_master_enabled=1;
SET GLOBAL rpl_semi_sync_master_timeout=100000;
show variables like '%rpl%';
stop slave;
change master to master_host='172.25.11.4',master_user='repl',master_password='Yakexi+007',master_auto_position=543;
stop slave io_thread;
start slave io_thread;
SET GLOBAL rpl_semi_sync_slave_enabled=1;
start slave;
show slave status\G;
set GLOBAL read_only=1;
安装MHA-7
server 456
yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
监视端server7
yum install *
ssh-******
ssh-copy-id server 4 5 6
mkdir /etc/masterha
vim /etc/masterha/app1.cnf
[server default]
manager_workdir=/etc/masterha
manager_log=/var/log/masterha.log
master_binlog_dir=/var/lib/mysql
#master_ip_failover_script= /usr/local/bin/master_ip_failover
#master_ip_online_change_script= /usr/local/bin/master_ip_online_change
password=Yakexi+007
user=root
ping_interval=1
remote_workdir=/tmp
repl_password=Yakexi+007
repl_user=repl
#report_script=/usr/local/send_report
#secondary_check_script=/usr/local/bin/masterha_secondary_check -s server03 -s server02
#shutdown_script=""
ssh_user=root
[server4]
hostname=172.25.11.4
port=3306
[server5]
hostname=172.25.11.5
port=3306
candidate_master=1
check_repl_delay=0
[server6]
hostname=172.25.11.6
port=3306
no_master=1
7免密456的检测
masterha_check_ssh --conf=/etc/masterha/app1.cnf
foundation上 ssh-******
scp /root/id_rsa* server4 5 6:/root/.ssh
主从之间要免密登陆 7-4 7-5 7-6也要免密
然后server7测试
masterha_check_ssh --conf=/etc/masterha/app1.cnf
server7 继续
masterha_check_repl --conf=/etc/masterha/app1.cnf
Tue Feb 26 14:33:50 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Feb 26 14:33:50 2019 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Tue Feb 26 14:33:50 2019 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Tue Feb 26 14:33:50 2019 - [info] MHA::MasterMonitor version 0.58.
Tue Feb 26 14:33:51 2019 - [info] GTID failover mode = 1
Tue Feb 26 14:33:51 2019 - [info] Dead Servers:
Tue Feb 26 14:33:51 2019 - [info] Alive Servers:
Tue Feb 26 14:33:51 2019 - [info] 172.25.11.4(172.25.11.4:3306)
Tue Feb 26 14:33:51 2019 - [info] 172.25.11.5(172.25.11.5:3306)
Tue Feb 26 14:33:51 2019 - [info] 172.25.11.6(172.25.11.6:3306)
Tue Feb 26 14:33:51 2019 - [info] Alive Slaves:
Tue Feb 26 14:33:51 2019 - [info] 172.25.11.5(172.25.11.5:3306) Version=5.7.24-log (oldest major version between slaves) log-bin:enabled
Tue Feb 26 14:33:51 2019 - [info] GTID ON
Tue Feb 26 14:33:51 2019 - [info] Replicating from 172.25.11.4(172.25.11.4:3306)
Tue Feb 26 14:33:51 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Tue Feb 26 14:33:51 2019 - [info] 172.25.11.6(172.25.11.6:3306) Version=5.7.24-log (oldest major version between slaves) log-bin:enabled
Tue Feb 26 14:33:51 2019 - [info] GTID ON
Tue Feb 26 14:33:51 2019 - [info] Replicating from 172.25.11.4(172.25.11.4:3306)
Tue Feb 26 14:33:51 2019 - [info] Not candidate for the new Master (no_master is set)
Tue Feb 26 14:33:51 2019 - [info] Current Alive Master: 172.25.11.4(172.25.11.4:3306)
Tue Feb 26 14:33:51 2019 - [info] Checking slave configurations..
Tue Feb 26 14:33:51 2019 - [info] Checking replication filtering settings..
Tue Feb 26 14:33:51 2019 - [info] binlog_do_db= , binlog_ignore_db=
Tue Feb 26 14:33:51 2019 - [info] Replication filtering check ok.
Tue Feb 26 14:33:51 2019 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Tue Feb 26 14:33:51 2019 - [info] Checking SSH publickey authentication settings on the current master..
Tue Feb 26 14:33:52 2019 - [info] HealthCheck: SSH to 172.25.11.4 is reachable.
Tue Feb 26 14:33:52 2019 - [info]
172.25.11.4(172.25.11.4:3306) (current master)
+--172.25.11.5(172.25.11.5:3306)
+--172.25.11.6(172.25.11.6:3306)
Tue Feb 26 14:33:52 2019 - [info] Checking replication health on 172.25.11.5..
Tue Feb 26 14:33:52 2019 - [info] ok.
Tue Feb 26 14:33:52 2019 - [info] Checking replication health on 172.25.11.6..
Tue Feb 26 14:33:52 2019 - [info] ok.
Tue Feb 26 14:33:52 2019 - [warning] master_ip_failover_script is not defined.
Tue Feb 26 14:33:52 2019 - [warning] shutdown_script is not defined.
Tue Feb 26 14:33:52 2019 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
检查配置状态
masterha_check_status --conf=/etc/mha/app1.cnf
数据库压侧
yum install sysbench -y
如果stop server4 mysql
将三个数据库中半同步中的配置文件删掉，
重起数据库
stop slave io_thread;
start slave io_thread;
server7开启转移master-->server5
masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.11.4 --dead_master_ip=172.25.11.4 --dead_master_port=3306 --new_master_host=172.25.11.5 --new_master_port=3306 --ignore_last_failover
然后server4 开启mysql
转移成为master5的slave
change master to master_host='172.25.11.5',master_port=3306,master_user='repl',master_password='Yakexi+007',master_auto_position=1;
start slave;
热转移
master
FLUSH NO_WRITE_TO_BINLOG TABLES;
server7
masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.11.5 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
自动切换
server7
nohup masterha_manager --conf=/etc/masterha/app1.cnf &> /dev/null &
masterha_check_status --conf=/etc/masterha/app1.cnf
masterha_stop --conf=/etc/masterha/app1.cnf
打入后台并且不接受信息
server4
killall mysql
会发现server7的进程自动退出，因为完成了自动切换
并且生 成 failover文件 (/etc/masterha/)
VIP模式
真机
scp master_ip_* [email protected]:/usr/local/bin
mha4mysql-manager-0.58.tar.gz
server7 monitor
chmod +x /usr/local/bin/*
vim app1.cnf
master_ip_failover_script= /usr/local/bin/master_ip_failover
master_ip_online_change_script= /usr/local/bin/master_ip_online_change
vim /usr/local/bin/master_ip_failover
my $vip = '172.25.11.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
vim /usr/local/bin/master_ip_online
my $vip = '172.25.11.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
my $exit_code = 0;
然后server7
masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.11.5 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
因为切换到了server5 所以5拥有vip
自动切换
server7
nohup masterha_manager --conf=/etc/masterha/app1.cnf &> /dev/null &
masterha_check_status --conf=/etc/masterha/app1.cnf
server4
killall mysqld
masterha_stop --conf=/etc/masterha/app1.cnf
出现问题
Slave is not configured or failed to initialize properly.
Slave is not configured or failed to initialize properly.Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs t
删除5张表，并重新导入脚本
use mysql
drop table slave_master_info;
drop table slave_relay_log_info;
drop table slave_worker_info;
drop table innodb_index_stats;
drop table innodb_table_stats;
source /usr/share/mysql/mysql_system_tables.sql;
systemctl restart mysqld