商業常用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 root@'%' identified by 'Yakexi+007';
grant replication slave on *.* to repl@'%' 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-keygen
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-keygen
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