2019-05-10MHA高可用技術(shù)

1. 主從復(fù)制架構(gòu)演變介紹

1.1 基本結(jié)構(gòu)
(1)一主一從
(2)一主多從
(3)多級主從
(4)雙主
(5)循環(huán)復(fù)制

1.2 高級應(yīng)用架構(gòu)演變

1.2.1 高性能架構(gòu)

讀寫分離架構(gòu)(讀性能較高)
代碼級別
MySQL proxy (Atlas,mysql router,proxySQL(percona),maxscale)、
amoeba(taobao)
xx-dbproxy等。
分布式架構(gòu)(讀寫性能都提高):
分庫分表——cobar--->TDDL(頭都大了),DRDS
Mycat--->DBLE自主研發(fā)等。
NewSQL-->TiDB

1.2.2 高可用架構(gòu)

(3)單活:MMM架構(gòu)——mysql-mmm(google)
(4)單活:MHA架構(gòu)——mysql-master-ha(日本DeNa),T-MHA
(5)多活:MGR ——5.7 新特性 MySQL Group replication(5.7.17) --->Innodb Cluster  
(6)多活:MariaDB Galera Cluster架構(gòu),(PXC)Percona XtraDB Cluster、MySQL Cluster(Oracle rac)架構(gòu)

3. 高可用MHA *****

3.1 架構(gòu)工作原理
主庫宕機(jī)處理過程
1. 監(jiān)控節(jié)點(diǎn) (通過配置文件獲取所有節(jié)點(diǎn)信息)
   系統(tǒng),網(wǎng)絡(luò),SSH連接性
   主從狀態(tài),重點(diǎn)是主庫

2. 選主
(1) 如果判斷從庫(position或者GTID),數(shù)據(jù)有差異,最接近于Master的slave,成為備選主
(2) 如果判斷從庫(position或者GTID),數(shù)據(jù)一致,按照配置文件順序,選主.
(3) 如果設(shè)定有權(quán)重(candidate_master=1),按照權(quán)重強(qiáng)制指定備選主.
    1. 默認(rèn)情況下如果一個slave落后master 100M的relay logs的話,即使有權(quán)重,也會失效.
    2. 如果check_repl_delay=0的化,即使落后很多日志,也強(qiáng)制選擇其為備選主
3. 數(shù)據(jù)補(bǔ)償
(1) 當(dāng)SSH能連接,從庫對比主庫GTID 或者position號,立即將二進(jìn)制日志保存至各個從節(jié)點(diǎn)并且應(yīng)用(save_binary_logs )
(2) 當(dāng)SSH不能連接, 對比從庫之間的relaylog的差異(apply_diff_relay_logs) 
4. Failover
將備選主進(jìn)行身份切換,對外提供服務(wù)
其余從庫和新主庫確認(rèn)新的主從關(guān)系
5. 應(yīng)用透明(VIP)
6. 故障切換通知(send_reprt)
7. 二次數(shù)據(jù)補(bǔ)償(binlog_server)
8. 自愈自治(待開發(fā)...)

3.2 架構(gòu)介紹:

1主2從,master:db01   slave:db02   db03 ):
MHA 高可用方案軟件構(gòu)成
Manager軟件:選擇一個從節(jié)點(diǎn)安裝
Node軟件:所有節(jié)點(diǎn)都要安裝

3.3 MHA軟件構(gòu)成

Manager工具包主要包括以下幾個工具:
masterha_manger             啟動MHA 
masterha_check_ssh      檢查MHA的SSH配置狀況 
masterha_check_repl         檢查MySQL復(fù)制狀況 
masterha_master_monitor     檢測master是否宕機(jī) 
masterha_check_status       檢測當(dāng)前MHA運(yùn)行狀態(tài) 
masterha_master_switch  控制故障轉(zhuǎn)移(自動或者手動)
masterha_conf_host      添加或刪除配置的server信息

Node工具包主要包括以下幾個工具:
這些工具通常由MHA Manager的腳本觸發(fā),無需人為操作
save_binary_logs            保存和復(fù)制master的二進(jìn)制日志 
apply_diff_relay_logs       識別差異的中繼日志事件并將其差異的事件應(yīng)用于其他的
purge_relay_logs            清除中繼日志(不會阻塞SQL線程)

3.4 MHA環(huán)境搭建

3.4.1 規(guī)劃:
主庫: 51    node 
從庫: 
52      node
53      node    manager

3.4.2 準(zhǔn)備環(huán)境(略。1主2從GTID)

3.4.3 配置關(guān)鍵程序軟連接
ln -s /data/mysql/bin/mysqlbinlog    /usr/bin/mysqlbinlog
ln -s /data/mysql/bin/mysql          /usr/bin/mysql

3.4.4  配置各節(jié)點(diǎn)互信
db01:
rm -rf /root/.ssh 
ssh-keygen
cd /root/.ssh 
mv id_rsa.pub authorized_keys
scp  -r  /root/.ssh  10.0.0.52:/root 
scp  -r  /root/.ssh  10.0.0.53:/root 
各節(jié)點(diǎn)驗(yàn)證
db01:
ssh 10.0.0.51 date
ssh 10.0.0.52 date
ssh 10.0.0.53 date
db02:
ssh 10.0.0.51 date
ssh 10.0.0.52 date
ssh 10.0.0.53 date
db03:
ssh 10.0.0.51 date
ssh 10.0.0.52 date
ssh 10.0.0.53 date

3.4.5 安裝軟件

下載mha軟件
mha官網(wǎng):https://code.google.com/archive/p/mysql-master-ha/
github下載地址:https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

所有節(jié)點(diǎn)安裝Node軟件依賴包
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

在db01主庫中創(chuàng)建mha需要的用戶
 grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';

Manager軟件安裝(db03)
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

3.4.6 配置文件準(zhǔn)備(db03)

創(chuàng)建配置文件目錄
 mkdir -p /etc/mha
創(chuàng)建日志目錄
 mkdir -p /var/log/mha/app1
編輯mha配置文件
vim /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager        
manager_workdir=/var/log/mha/app1            
master_binlog_dir=/data/binlog       
user=mha                                   
password=mha                               
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root                               
[server1]                                   
hostname=10.0.0.51
port=3306                                  
[server2]            
hostname=10.0.0.52
port=3306
[server3]
hostname=10.0.0.53
port=3306

3.4.7 狀態(tài)檢查

互信檢查

masterha_check_ssh  --conf=/etc/mha/app1.cnf 
Fri Apr 19 16:39:34 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Apr 19 16:39:34 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Fri Apr 19 16:39:34 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..
Fri Apr 19 16:39:34 2019 - [info] Starting SSH connection tests..
Fri Apr 19 16:39:35 2019 - [debug] 
Fri Apr 19 16:39:34 2019 - [debug]  Connecting via SSH from root@10.0.0.51(10.0.0.51:22) to root@10.0.0.52(10.0.0.52:22)..
Fri Apr 19 16:39:34 2019 - [debug]   ok.
Fri Apr 19 16:39:34 2019 - [debug]  Connecting via SSH from root@10.0.0.51(10.0.0.51:22) to root@10.0.0.53(10.0.0.53:22)..
Fri Apr 19 16:39:35 2019 - [debug]   ok.
Fri Apr 19 16:39:36 2019 - [debug] 
Fri Apr 19 16:39:35 2019 - [debug]  Connecting via SSH from root@10.0.0.52(10.0.0.52:22) to root@10.0.0.51(10.0.0.51:22)..
Fri Apr 19 16:39:35 2019 - [debug]   ok.
Fri Apr 19 16:39:35 2019 - [debug]  Connecting via SSH from root@10.0.0.52(10.0.0.52:22) to root@10.0.0.53(10.0.0.53:22)..
Fri Apr 19 16:39:35 2019 - [debug]   ok.
Fri Apr 19 16:39:37 2019 - [debug] 
Fri Apr 19 16:39:35 2019 - [debug]  Connecting via SSH from root@10.0.0.53(10.0.0.53:22) to root@10.0.0.51(10.0.0.51:22)..
Fri Apr 19 16:39:35 2019 - [debug]   ok.
Fri Apr 19 16:39:35 2019 - [debug]  Connecting via SSH from root@10.0.0.53(10.0.0.53:22) to root@10.0.0.52(10.0.0.52:22)..
Fri Apr 19 16:39:36 2019 - [debug]   ok.
Fri Apr 19 16:39:37 2019 - [info] All SSH connection tests passed successfully.

主從狀態(tài)檢查
[root@db03 ~]# masterha_check_ssh  --conf=/etc/mha/app1.cnf 
Fri Apr 19 16:39:34 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Apr 19 16:39:34 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Fri Apr 19 16:39:34 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..
Fri Apr 19 16:39:34 2019 - [info] Starting SSH connection tests..
Fri Apr 19 16:39:35 2019 - [debug] 
Fri Apr 19 16:39:34 2019 - [debug]  Connecting via SSH from root@10.0.0.51(10.0.0.51:22) to root@10.0.0.52(10.0.0.52:22)..
Fri Apr 19 16:39:34 2019 - [debug]   ok.
Fri Apr 19 16:39:34 2019 - [debug]  Connecting via SSH from root@10.0.0.51(10.0.0.51:22) to root@10.0.0.53(10.0.0.53:22)..
Fri Apr 19 16:39:35 2019 - [debug]   ok.
Fri Apr 19 16:39:36 2019 - [debug] 
Fri Apr 19 16:39:35 2019 - [debug]  Connecting via SSH from root@10.0.0.52(10.0.0.52:22) to root@10.0.0.51(10.0.0.51:22)..
Fri Apr 19 16:39:35 2019 - [debug]   ok.
Fri Apr 19 16:39:35 2019 - [debug]  Connecting via SSH from root@10.0.0.52(10.0.0.52:22) to root@10.0.0.53(10.0.0.53:22)..
Fri Apr 19 16:39:35 2019 - [debug]   ok.
Fri Apr 19 16:39:37 2019 - [debug] 
Fri Apr 19 16:39:35 2019 - [debug]  Connecting via SSH from root@10.0.0.53(10.0.0.53:22) to root@10.0.0.51(10.0.0.51:22)..
Fri Apr 19 16:39:35 2019 - [debug]   ok.
Fri Apr 19 16:39:35 2019 - [debug]  Connecting via SSH from root@10.0.0.53(10.0.0.53:22) to root@10.0.0.52(10.0.0.52:22)..
Fri Apr 19 16:39:36 2019 - [debug]   ok.
Fri Apr 19 16:39:37 2019 - [info] All SSH connection tests passed successfully.
[root@db03 ~]# masterha_check_repl  --conf=/etc/mha/app1.cnf 
Fri Apr 19 16:40:50 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Apr 19 16:40:50 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Fri Apr 19 16:40:50 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..
Fri Apr 19 16:40:50 2019 - [info] MHA::MasterMonitor version 0.56.
Fri Apr 19 16:40:51 2019 - [info] GTID failover mode = 1
Fri Apr 19 16:40:51 2019 - [info] Dead Servers:
Fri Apr 19 16:40:51 2019 - [info] Alive Servers:
Fri Apr 19 16:40:51 2019 - [info]   10.0.0.51(10.0.0.51:3306)
Fri Apr 19 16:40:51 2019 - [info]   10.0.0.52(10.0.0.52:3306)
Fri Apr 19 16:40:51 2019 - [info]   10.0.0.53(10.0.0.53:3306)
Fri Apr 19 16:40:51 2019 - [info] Alive Slaves:
Fri Apr 19 16:40:51 2019 - [info]   10.0.0.52(10.0.0.52:3306)  Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Fri Apr 19 16:40:51 2019 - [info]     GTID ON
Fri Apr 19 16:40:51 2019 - [info]     Replicating from 10.0.0.51(10.0.0.51:3306)
Fri Apr 19 16:40:51 2019 - [info]   10.0.0.53(10.0.0.53:3306)  Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Fri Apr 19 16:40:51 2019 - [info]     GTID ON
Fri Apr 19 16:40:51 2019 - [info]     Replicating from 10.0.0.51(10.0.0.51:3306)
Fri Apr 19 16:40:51 2019 - [info] Current Alive Master: 10.0.0.51(10.0.0.51:3306)
Fri Apr 19 16:40:51 2019 - [info] Checking slave configurations..
Fri Apr 19 16:40:51 2019 - [info]  read_only=1 is not set on slave 10.0.0.52(10.0.0.52:3306).
Fri Apr 19 16:40:51 2019 - [info]  read_only=1 is not set on slave 10.0.0.53(10.0.0.53:3306).
Fri Apr 19 16:40:51 2019 - [info] Checking replication filtering settings..
Fri Apr 19 16:40:51 2019 - [info]  binlog_do_db= , binlog_ignore_db= 
Fri Apr 19 16:40:51 2019 - [info]  Replication filtering check ok.
Fri Apr 19 16:40:51 2019 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Fri Apr 19 16:40:51 2019 - [info] Checking SSH publickey authentication settings on the current master..
Fri Apr 19 16:40:51 2019 - [info] HealthCheck: SSH to 10.0.0.51 is reachable.
Fri Apr 19 16:40:51 2019 - [info] 
10.0.0.51(10.0.0.51:3306) (current master)
 +--10.0.0.52(10.0.0.52:3306)
 +--10.0.0.53(10.0.0.53:3306)

Fri Apr 19 16:40:51 2019 - [info] Checking replication health on 10.0.0.52..
Fri Apr 19 16:40:51 2019 - [info]  ok.
Fri Apr 19 16:40:51 2019 - [info] Checking replication health on 10.0.0.53..
Fri Apr 19 16:40:51 2019 - [info]  ok.
Fri Apr 19 16:40:51 2019 - [warning] master_ip_failover_script is not defined.
Fri Apr 19 16:40:51 2019 - [warning] shutdown_script is not defined.
Fri Apr 19 16:40:51 2019 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.

3.4.8 開啟MHA(db03):

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover  < /dev/null> /var/log/mha/app1/manager.log 2>&1 &

3.4.9 查看MHA狀態(tài)

[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:4719) is running(0:PING_OK), master:10.0.0.51
[root@db03 ~]# mysql -umha -pmha -h 10.0.0.51 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 51    |
+---------------+-------+
[root@db03 ~]# mysql -umha -pmha -h 10.0.0.52 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 52    |
+---------------+-------+
[root@db03 ~]# mysql -umha -pmha -h 10.0.0.53 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 53    |
+---------------+-------+

3.4.10 故障模擬及處理

停主庫db01:    
/etc/init.d/mysqld stop

觀察manager  日志 tail -f /var/log/mha/app1/manager
末尾必須顯示successfully,才算正常切換成功。                            

修復(fù)主庫
[root@db01 ~]# /etc/init.d/mysqld start

恢復(fù)主從結(jié)構(gòu)
CHANGE MASTER TO 
MASTER_HOST='10.0.0.52',
MASTER_PORT=3306, 
MASTER_AUTO_POSITION=1, 
MASTER_USER='repl', 
MASTER_PASSWORD='123';
start slave ;

修改配置文件
[server1]
hostname=10.0.0.51
port=3306

啟動MHA
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover  < /dev/null> /var/log/mha/app1/manager.log 2>&1 &

3.4.11 Manager額外參數(shù)介紹

說明:
主庫宕機(jī)誰來接管?
1. 所有從節(jié)點(diǎn)日志都是一致的,默認(rèn)會以配置文件的順序去選擇一個新主。
2. 從節(jié)點(diǎn)日志不一致,自動選擇最接近于主庫的從庫
3. 如果對于某節(jié)點(diǎn)設(shè)定了權(quán)重(candidate_master=1),權(quán)重節(jié)點(diǎn)會優(yōu)先選擇。
但是此節(jié)點(diǎn)日志量落后主庫100M日志的話,也不會被選擇。可以配合check_repl_delay=0,關(guān)閉日志量的檢查,強(qiáng)制選擇候選節(jié)點(diǎn)。

(1)  ping_interval=1
#設(shè)置監(jiān)控主庫,發(fā)送ping包的時間間隔,嘗試三次沒有回應(yīng)的時候自動進(jìn)行failover
(2) candidate_master=1
#設(shè)置為候選master,如果設(shè)置該參數(shù)以后,發(fā)生主從切換以后將會將此從庫提升為主庫,即使這個主庫不是集群中事件最新的slave
(3)check_repl_delay=0
#默認(rèn)情況下如果一個slave落后master 100M的relay logs的話,
MHA將不會選擇該slave作為一個新的master,因?yàn)閷τ谶@個slave的恢復(fù)需要花費(fèi)很長時間,通過設(shè)置check_repl_delay=0,MHA觸發(fā)切換在選擇一個新的master的時候?qū)雎詮?fù)制延時,這個參數(shù)對于設(shè)置了candidate_master=1的主機(jī)非常有用,因?yàn)檫@個候選主在切換的過程中一定是新的master

3.4.12 MHA 的vip功能

參數(shù)
master_ip_failover_script=/usr/local/bin/master_ip_failover
注意:/usr/local/bin/master_ip_failover,必須事先準(zhǔn)備好

修改腳本內(nèi)容
vi  /usr/local/bin/master_ip_failover
my $vip = '10.0.0.55/24';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

更改manager配置文件:
vi /etc/mha/app1.cnf
添加:
master_ip_failover_script=/usr/local/bin/master_ip_failover
注意:
[root@db03 ~]# dos2unix /usr/local/bin/master_ip_failover 
dos2unix: converting file /usr/local/bin/master_ip_failover to Unix format ...
[root@db03 ~]# chmod +x /usr/local/bin/master_ip_failover 

主庫上,手工生成第一個vip地址
手工在主庫上綁定vip,注意一定要和配置文件中的ethN一致,我的是eth0:1(1是key指定的值)
ifconfig eth0:1 10.0.0.55/24

重啟mha
masterha_stop --conf=/etc/mha/app1.cnf
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

3.4.13 郵件提醒

1. 參數(shù):
report_script=/usr/local/bin/send
2. 準(zhǔn)備郵件腳本
send_report
(1)準(zhǔn)備發(fā)郵件的腳本(上傳 email_2019-最新.zip中的腳本,到/usr/local/bin/中)
(2)將準(zhǔn)備好的腳本添加到mha配置文件中,讓其調(diào)用

3. 修改manager配置文件,調(diào)用郵件腳本
vi /etc/mha/app1.cnf
report_script=/usr/local/bin/send

(3)停止MHA
masterha_stop --conf=/etc/mha/app1.cnf
(4)開啟MHA    
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
        
(5) 關(guān)閉主庫,看警告郵件  

故障修復(fù):

1. 恢復(fù)故障節(jié)點(diǎn)
(1)實(shí)例宕掉
/etc/init.d/mysqld start 
(2)主機(jī)損壞,有可能數(shù)據(jù)也損壞了
備份并恢復(fù)故障節(jié)點(diǎn)。
2.恢復(fù)主從環(huán)境
看日志文件:
CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
start slave ;
3.恢復(fù)manager
3.1 修好的故障節(jié)點(diǎn)配置信息,加入到配置文件
[server1]
hostname=10.0.0.51
port=3306
3.2 啟動manager   
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

3.4.14 binlog server(db03)
參數(shù):
binlogserver配置:
找一臺額外的機(jī)器,必須要有5.6以上的版本,支持gtid并開啟,我們直接用的第二個slave(db03)
vim /etc/mha/app1.cnf 
[binlog1]
no_master=1
hostname=10.0.0.53
master_binlog_dir=/data/mysql/binlog

創(chuàng)建必要目錄
mkdir -p /data/mysql/binlog
chown -R mysql.mysql /data/*
修改完成后,將主庫binlog拉過來(從000001開始拉,之后的binlog會自動按順序過來)

拉取主庫binlog日志
cd /data/mysql/binlog     -----》必須進(jìn)入到自己創(chuàng)建好的目錄
mysqlbinlog  -R --host=10.0.0.52 --user=mha --password=mha --raw  --stop-never mysql-bin.000001 &
注意:
拉取日志的起點(diǎn),需要按照目前從庫的已經(jīng)獲取到的二進(jìn)制日志點(diǎn)為起點(diǎn)


重啟MHA
masterha_stop --conf=/etc/mha/app1.cnf
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

故障處理

主庫宕機(jī),binlogserver 自動停掉,manager 也會自動停止。
處理思路:
1、重新獲取新主庫的binlog到binlogserver中
2、重新配置文件binlog server信息
3、最后再啟動MHA

4.管理員在高可用架構(gòu)維護(hù)的職責(zé)
1. 搭建:MHA+VIP+SendReport+BinlogServer
2. 監(jiān)控及故障處理
3.  高可用架構(gòu)的優(yōu)化
 核心是:盡可能降低主從的延時,讓MHA花在數(shù)據(jù)補(bǔ)償上的時間盡量減少。
5.7 版本,開啟GTID模式,開啟從庫SQL并發(fā)復(fù)制。
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成,瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明:文章內(nèi)容(如有圖片或視頻亦包括在內(nèi))由作者上傳并發(fā)布,文章內(nèi)容僅代表作者本人觀點(diǎn),簡書系信息發(fā)布平臺,僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容