http://ylw6006.blog.51cto.com/470441/890360/

功能

Auto failover—定期ping master(執行select 1)，連續3次失敗則開始failover，將binlog gap匯出並scp至latest slave，恢復後提升為master；

互動式—不監控master，出故障時人工呼叫MHA；

非互動式—不監控master仍能自動故障轉移，使用第3方軟體(heartbeat/pacemaker)檢測master；

線上切換master—期間資料無法寫入，持續0.5-2s，大致步驟：

檢測cnf確定current master；確定new master；阻塞current master寫；等待slave sync；提升new master，恢復寫；

注：MHA每次都是透過檢查cnf確認current master，如何找出當前master? Show slave status

Identifying the current master by connecting all hosts described in a config file. You do not have to specify which host is the current master. MHA automatically checks replication settings and identify the current master.

由此帶來一個問題：應用架構如何自動識別master，以及負載均衡的分配比例

管理節點的角色類似於oracle資料庫中的fast start failover中的observer，但mha上層可以透過keepalive部署VIP，程式連線資料庫使用VIP，從而實現後臺資料庫的故障切換透明化

指令碼

save_binary_logs:儲存和複製當掉的主伺服器二進位制日誌；

apply_diff_relay_logs:識別差異的relay log事件，並應用於其他salve伺服器；

purge_relay_logs:清除relay log檔案；

常見問題

1 manager程式down怎麼辦

不影響mysql，但不會再自動failover；

Current recommended MHA Manager HA solution is using common active/standby solutions like Pacemaker.

2

每次failover切換後會在管理目錄生成檔案app1.failover.complete ，下次在切換的時候會發現有這個檔案導致切換不成功，需要手動清理掉。

rm -rf /masterha/app1/app1.failover.complete

也可以加上引數–ignore_last_failover

3增刪節點

更新application config配置檔案，重啟manager程式

masterha_conf_host透過指定選項自動更新conf

[server_db101]

4

當有slave節點宕掉的情況是啟動不了的，加上-ignore_fail_on_start即使有節點宕掉也能啟動mha

nohup masterha_manager –conf=/etc/app1.cnf –ignore_fail_on_start < /dev/null

前提

所有節點配備root ssh;

不支援3-tier,m1-m2-s3，不監控s3；conf配置m1/m2，所有主機設定multi_tier_slave=1

Slave必須開啟log-bin;

所有伺服器的過濾規則必須相同;

候選slave上必須有複製使用者;

保留relay log(預設自動刪除)，purge_relay_logs指令碼邏輯：

為relay log建立硬連結，relay_log_purge=1(自動清除relay log)；等待sql thread讀取新relay log；relay_log_purge=0(宣告--disable_relay_log_purge)；

設定crontab

[app@slave_host1]$ cat /etc/cron.d/purge_relay_logs

# purge relay logs at 5am

0 5 * * * app /usr/bin/purge_relay_logs --user=root --password=PASSWORD --disable_relay_log_purge >> /var/log/masterha/purge_relay_logs.log 2>&1

安裝

1 配置root ssh等價性

2 安裝perl DBD::mysql

3 安裝MHA node – perl Makefile.PL; make; make install;

4 安裝MHA manager - 同上；可單獨機器，也可與MHA node同機；

5 配置檔案，分為[server default]全域性，以及[server]local

[server default]

manager_workdir=/masterha/app1 --工作目錄

manager_log=/masterha/app1/manager.log --日誌

user=root

password=oracle

ssh_user=root

repl_user=rep

repl_password=rep

ping_interval=1 --檢查間隔

[server1]

hostname=db-181

master_binlog_dir=/data/mysql

candidate_master=1

[server2]

hostname=db-183

master_binlog_dir=/data/mysql

candidate_master=1

[server3]

hostname=db-184

master_binlog_dir=/data/mysql

candidate_master：=1為候選master，如指定多個則[server_1]優先順序高於[server_2]；預設latest slave被提升為new master；

no_master：=1該slave永遠不會成為master

ignore_fail：=1即便該slave fails，MHA仍舊繼續failover

master_binlog_dir：binlog生成目錄，一旦master die用於讀取剩餘binlog

manager_workdir：MHA manager生成的各種狀態檔案，預設為/var/tmp

manager_log：MHA manager的日誌目錄

multi_tier_slave：不會abort 3-tier，僅忽略3rd timer；

ping_interval：MHA manager ping master的頻率，連續錯失3次則認為master掛了；預設3s；

ping_type：預設manager對master建立持續連線，每次執行select 1確認master是否down；0.53引入，SELECT選項則每次建立新連線；

secondary_check_script：預設manager只檢查manager-master route，此指令碼可檢查manager – slave1 – master；

master_ip_failover_script：一般HA會為master多分一個VIP，一旦master崩潰，VIP漂移到new master；也可以維護一個catalog資料庫，記錄application同IP之間的對映，一旦failover需要更新；此引數允許使用者自訂製，例項位於/samples/scripts/master_ip_failover

有3個階段會呼叫： 1 進入master monitoring 2 呼叫shutdown_script之前 3 new master應用relay log之後

Master_ip_online_change_script：用於online change；

Shutdown_script: 用於關閉master避免腦裂

MHA manager功能順序

啟動 nohup masterha_manager --conf=/etc/masterha/app1.cnf > /tmp/mha_manager.log &

failover

1 讀取conf連線所有host判斷current master, how(所有host的repl使用者名稱/密碼相同？no,[server1]可定製配置)

2 監控master，若連續3次失敗則failover

3 重新讀取conf

4 關閉master(可選)，shutdown_script/ip_failover_script

5 選定new master，應用relay log後啟用

6 恢復其餘slave

7 通知(可選),report_script

Online switch

1 確定current master

2 選舉new master

2 阻塞current master寫操作，flush tables with read lock

3 等待slave sync， master_log_pos()

4 將new master置為可寫

5 對剩餘slave執行change master/start slave

internal

1 如何獲取relay log position

MHA直接解析binlog header，而mysqlbinlog會連同binlog body一起輸出；

Header儲存event type/length/end pos，可mysqlbinlog –base64-output=always再次確認

2 MHA並非從頭開始順序解析binlog，可直接跳至end pos

$latest_mlf = Master_Log_File on the latest slave

$target_mlf = Master_Log_File on the recovery target slave

$latest_rmlp = Read_Master_Log_Pos on the latest slave

$target_rmlp = Read_Master_Log_Pos on the recovery target slave

$offset = $latest_rmlp - $target_rmlp

$filesize = File size of the latest relay log file on the latest slave

if ($latest_mlf eq $target_mlf) && ($filesize > $offset), and if binlog events can be readable from ($filesize - $offset) position, MHA decides that starting recovery position is from ($filesize - $offset) position from the latest relay log file on the latest slave.

3 整合binlog gap

每個slave恢復有3個過程：應用exec_master_log_pos到read_master_log_pos之間的gap；應用read pos到latest slave’s read pos之間的gap；應用latest slave到master之間binlog的gap；

A target slave的exec pos到read pos

B target slave的read pos到latest slave的read pos(剪除開頭的格式化事件)

C latest slave的read pos到master的tail binlog

D 將上述輸出整合到同一個檔案，執行mysqlbinlog應用到target slave

A-C全是binlog的原始資訊(而非mysqlbinlog output)，此舉是為了支援row-based，同一個row event可能跨binlog儲存(對單個檔案執行mysqlbinlog會得到不完整資訊)

Mysqlbinlog預設輸出結尾會新增rollback，整合成一個檔案避免了跨binlog事務中間被回滾的風險，因為格式化事件可能產生ROLLBACK所以B-C去除；

案例

1

[root@dg55 ~]# tail -f /masterha/app1/manager.log

Wed Jun 6 14:50:48 2012 - [info]

192.168.123.13 (current master)

+--192.168.123.14

Wed Jun 6 14:50:48 2012 - [warning] master_ip_failover_script is not defined.

Wed Jun 6 14:50:48 2012 - [warning] shutdown_script is not defined.

Wed Jun 6 14:50:48 2012 - [info] Set master ping interval 1 seconds.

Wed Jun 6 14:50:48 2012 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.

Wed Jun 6 14:50:48 2012 - [info] Starting ping health check on 192.168.123.13(192.168.123.13:3306)..—定期檢測master mysql,連續3次失敗則讀取conf進行重組

Wed Jun 6 14:50:48 2012 - [info] Ping succeeded, sleeping until it doesn't respond..

Wed Jun 6 14:51:32 2012 - [warning] Got error on MySQL ping: 2006 (MySQL server has gone away)

Wed Jun 6 14:51:32 2012 - [info] HealthCheck: SSH to 192.168.123.13 is reachable.

Wed Jun 6 14:51:33 2012 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)

Wed Jun 6 14:51:33 2012 - [warning] Connection failed 1 time(s)..

Wed Jun 6 14:51:34 2012 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)

Wed Jun 6 14:51:34 2012 - [warning] Connection failed 2 time(s)..

Wed Jun 6 14:51:35 2012 - [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)

Wed Jun 6 14:51:35 2012 - [warning] Connection failed 3 time(s)..

Wed Jun 6 14:51:35 2012 - [warning] Master is not reachable from health checker!

Wed Jun 6 14:51:35 2012 - [warning] Master 192.168.123.13(192.168.123.13:3306) is not reachable!

Wed Jun 6 14:51:35 2012 - [warning] SSH is reachable.

Wed Jun 6 14:51:35 2012 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/masterha/app1.cnf again, and trying to connect to all servers to check server status..

Wed Jun 6 14:51:35 2012 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Wed Jun 6 14:51:35 2012 - [info] Reading application default configurations from /etc/masterha/app1.cnf..

Wed Jun 6 14:51:35 2012 - [info] Reading server configurations from /etc/masterha/app1.cnf..

Wed Jun 6 14:51:35 2012 - [info] Dead Servers:

Wed Jun 6 14:51:35 2012 - [info] 192.168.123.13(192.168.123.13:3306)

Wed Jun 6 14:51:35 2012 - [info] Alive Servers:

Wed Jun 6 14:51:35 2012 - [info] 192.168.123.14(192.168.123.14:3306)

Wed Jun 6 14:51:35 2012 - [info] Alive Slaves:

Wed Jun 6 14:51:35 2012 - [info] 192.168.123.14(192.168.123.14:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled

Wed Jun 6 14:51:35 2012 - [info] Replicating from 192.168.123.13(192.168.123.13:3306)

Wed Jun 6 14:51:35 2012 - [info] Primary candidate for the new Master (candidate_master is set)

Wed Jun 6 14:51:35 2012 - [info] Checking slave configurations..

Wed Jun 6 14:51:35 2012 - [warning] read_only=1 is not set on slave 192.168.123.14(192.168.123.14:3306).

Wed Jun 6 14:51:35 2012 - [warning] relay_log_purge=0 is not set on slave 192.168.123.14(192.168.123.14:3306).

Wed Jun 6 14:51:35 2012 - [info] Checking replication filtering settings..

Wed Jun 6 14:51:35 2012 - [info] Replication filtering check ok.

Wed Jun 6 14:51:35 2012 - [info] Master is down!

Wed Jun 6 14:51:35 2012 - [info] Terminating monitoring script.

Wed Jun 6 14:51:35 2012 - [info] Got exit code 20 (Master dead).

Wed Jun 6 14:51:35 2012 - [info] MHA::MasterFailover version 0.52.

Wed Jun 6 14:51:35 2012 - [info] Starting master failover.

Wed Jun 6 14:51:35 2012 - [info]

Wed Jun 6 14:51:35 2012 - [info] * Phase 1: Configuration Check Phase..—重構分為1檢查配置 2關閉dead master 3 獲取最新slave

Wed Jun 6 14:51:35 2012 - [info]

Wed Jun 6 14:51:35 2012 - [info] Dead Servers:

Wed Jun 6 14:51:35 2012 - [info] 192.168.123.13(192.168.123.13:3306)

Wed Jun 6 14:51:35 2012 - [info] Checking master reachability via mysql(double check)..

Wed Jun 6 14:51:35 2012 - [info] ok.

Wed Jun 6 14:51:35 2012 - [info] Alive Servers:

Wed Jun 6 14:51:35 2012 - [info] 192.168.123.14(192.168.123.14:3306)

Wed Jun 6 14:51:35 2012 - [info] Alive Slaves:

Wed Jun 6 14:51:35 2012 - [info] 192.168.123.14(192.168.123.14:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled

Wed Jun 6 14:51:35 2012 - [info] Replicating from 192.168.123.13(192.168.123.13:3306)

Wed Jun 6 14:51:35 2012 - [info] Primary candidate for the new Master (candidate_master is set)

Wed Jun 6 14:51:35 2012 - [info] ** Phase 1: Configuration Check Phase completed.

Wed Jun 6 14:51:35 2012 - [info]

Wed Jun 6 14:51:35 2012 - [info] * Phase 2: Dead Master Shutdown Phase..

Wed Jun 6 14:51:35 2012 - [info]

Wed Jun 6 14:51:35 2012 - [info] Forcing shutdown so that applications never connect to the current master..

Wed Jun 6 14:51:35 2012 - [warning] master_ip_failover_script is not set. Skipping invalidating dead master ip address.

Wed Jun 6 14:51:35 2012 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.

Wed Jun 6 14:51:35 2012 - [info] * Phase 2: Dead Master Shutdown Phase completed.

Wed Jun 6 14:51:35 2012 - [info]

Wed Jun 6 14:51:35 2012 - [info] * Phase 3: Master Recovery Phase..

Wed Jun 6 14:51:35 2012 - [info]

Wed Jun 6 14:51:35 2012 - [info] * Phase 3.1: Getting Latest Slaves Phase..

Wed Jun 6 14:51:35 2012 - [info]

Wed Jun 6 14:51:35 2012 - [info] The latest binary log file/position on all slaves is mysql-bin.000021:107

Wed Jun 6 14:51:35 2012 - [info] Latest slaves (Slaves that received relay log files to the latest):

Wed Jun 6 14:51:35 2012 - [info] 192.168.123.14(192.168.123.14:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled

Wed Jun 6 14:51:35 2012 - [info] Replicating from 192.168.123.13(192.168.123.13:3306)

Wed Jun 6 14:51:35 2012 - [info] Primary candidate for the new Master (candidate_master is set)

Wed Jun 6 14:51:35 2012 - [info] The oldest binary log file/position on all slaves is mysql-bin.000021:107

Wed Jun 6 14:51:35 2012 - [info] Oldest slaves:

Wed Jun 6 14:51:35 2012 - [info] 192.168.123.14(192.168.123.14:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled

Wed Jun 6 14:51:35 2012 - [info] Replicating from 192.168.123.13(192.168.123.13:3306)

Wed Jun 6 14:51:35 2012 - [info] Primary candidate for the new Master (candidate_master is set)

Wed Jun 6 14:51:35 2012 - [info]

Wed Jun 6 14:51:35 2012 - [info] * Phase 3.2: Saving Dead Master's Binlog Phase.. save_binary_logs指令碼將尚未傳送的slave的binlog部分轉義出來，並scp傳送給slave

Wed Jun 6 14:51:35 2012 - [info]

Wed Jun 6 14:51:35 2012 - [info] Fetching dead master's binary logs..

Wed Jun 6 14:51:35 2012 - [info] Executing command on the dead master 192.168.123.13(192.168.123.13:3306): save_binary_logs --command=save --start_file=mysql-bin.000021 --start_pos=107 --binlog_dir=/mydata --output_file=/var/tmp/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.52

Creating /var/tmp if not exists.. ok.

Concat binary/relay logs from mysql-bin.000021 pos 107 to mysql-bin.000021 EOF into /var/tmp/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog ..

Dumping binlog format description event, from position 0 to 107.. ok.

Dumping effective binlog data from /mydata/mysql-bin.000021 position 107 to tail(126).. ok.

Concat succeeded.

Wed Jun 6 14:51:36 2012 - [info] scp from root@192.168.123.13:/var/tmp/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog to local:/masterha/app1/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog succeeded.

Wed Jun 6 14:51:36 2012 - [info] HealthCheck: SSH to 192.168.123.14 is reachable.

Wed Jun 6 14:51:37 2012 - [info]

Wed Jun 6 14:51:37 2012 - [info] * Phase 3.3: Determining New Master Phase..

Wed Jun 6 14:51:37 2012 - [info]

Wed Jun 6 14:51:37 2012 - [info] Finding the latest slave that has all relay logs for recovering other slaves..

Wed Jun 6 14:51:37 2012 - [info] All slaves received relay logs to the same position. No need to resync each other.

Wed Jun 6 14:51:37 2012 - [info] Searching new master from slaves..

Wed Jun 6 14:51:37 2012 - [info] Candidate masters from the configuration file:

Wed Jun 6 14:51:37 2012 - [info] 192.168.123.14(192.168.123.14:3306) Version=5.5.25-log (oldest major version between slaves) log-bin:enabled

Wed Jun 6 14:51:37 2012 - [info] Replicating from 192.168.123.13(192.168.123.13:3306)

Wed Jun 6 14:51:37 2012 - [info] Primary candidate for the new Master (candidate_master is set)

Wed Jun 6 14:51:37 2012 - [info] Non-candidate masters:

Wed Jun 6 14:51:37 2012 - [info] Searching from candidate_master slaves which have received the latest relay log events..

Wed Jun 6 14:51:37 2012 - [info] New master is 192.168.123.14(192.168.123.14:3306)

Wed Jun 6 14:51:37 2012 - [info] Starting master failover..

Wed Jun 6 14:51:37 2012 - [info]

From:

192.168.123.13 (current master)

+--192.168.123.14

To:

192.168.123.14 (new master)

Wed Jun 6 14:51:37 2012 - [info]

Wed Jun 6 14:51:37 2012 - [info] * Phase 3.3: New Master Diff Log Generation Phase..

Wed Jun 6 14:51:37 2012 - [info]

Wed Jun 6 14:51:37 2012 - [info] This server has all relay logs. No need to generate diff files from the latest slave.

Wed Jun 6 14:51:37 2012 - [info] Sending binlog..

Wed Jun 6 14:51:37 2012 - [info] scp from local:/masterha/app1/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog to root@192.168.123.14:/var/tmp/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog succeeded.

Wed Jun 6 14:51:37 2012 - [info]

Wed Jun 6 14:51:37 2012 - [info] * Phase 3.4: Master Log Apply Phase..應用binlog gap即上步轉義出的binlog

Wed Jun 6 14:51:37 2012 - [info]

Wed Jun 6 14:51:37 2012 - [info] *NOTICE: If any error happens from this phase, manual recovery is needed.

Wed Jun 6 14:51:37 2012 - [info] Starting recovery on 192.168.123.14(192.168.123.14:3306)..

Wed Jun 6 14:51:37 2012 - [info] Generating diffs succeeded.

Wed Jun 6 14:51:37 2012 - [info] Waiting until all relay logs are applied.

Wed Jun 6 14:51:37 2012 - [info] done.

Wed Jun 6 14:51:37 2012 - [info] Getting slave status..

Wed Jun 6 14:51:37 2012 - [info] This slave(192.168.123.14)'s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(mysql-bin.000021:107). No need to recover from Exec_Master_Log_Pos.

Wed Jun 6 14:51:37 2012 - [info] Connecting to the target slave host 192.168.123.14, running recover script..

Wed Jun 6 14:51:37 2012 - [info] Executing command: apply_diff_relay_logs --command=apply --slave_user=root --slave_host=192.168.123.14 --slave_ip=192.168.123.14 --slave_port=3306 --apply_files=/var/tmp/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog --workdir=/var/tmp --target_version=5.5.25-log --timestamp=20120606145135 --handle_raw_binlog=1 --disable_log_bin=0 --manager_version=0.52 --slave_pass=xxx

Wed Jun 6 14:51:37 2012 - [info]

Applying differential binary/relay log files /var/tmp/saved_master_binlog_from_192.168.123.13_3306_20120606145135.binlog on 192.168.123.14:3306. This may take long time...

Applying log files succeeded.

Wed Jun 6 14:51:37 2012 - [info] All relay logs were successfully applied.

Wed Jun 6 14:51:37 2012 - [info] Getting new master's binlog name and position..

Wed Jun 6 14:51:37 2012 - [info] mysql-bin.000023:107

Wed Jun 6 14:51:37 2012 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.123.14', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=107, MASTER_USER='r_test', MASTER_PASSWORD='xxx';

Wed Jun 6 14:51:37 2012 - [warning] master_ip_failover_script is not set. Skipping taking over new master ip address.

Wed Jun 6 14:51:37 2012 - [info] ** Finished master recovery successfully.

Wed Jun 6 14:51:37 2012 - [info] * Phase 3: Master Recovery Phase completed.

Wed Jun 6 14:51:37 2012 - [info]

Wed Jun 6 14:51:37 2012 - [info] * Phase 4: Slaves Recovery Phase.. 利用最新的slave同步剩餘的slave

Wed Jun 6 14:51:37 2012 - [info]

Wed Jun 6 14:51:37 2012 - [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..

Wed Jun 6 14:51:37 2012 - [info]

Wed Jun 6 14:51:37 2012 - [info] Generating relay diff files from the latest slave succeeded.

Wed Jun 6 14:51:37 2012 - [info]

Wed Jun 6 14:51:37 2012 - [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..

Wed Jun 6 14:51:37 2012 - [info]

Wed Jun 6 14:51:37 2012 - [info] All new slave servers recovered successfully.

Wed Jun 6 14:51:37 2012 - [info]

Wed Jun 6 14:51:37 2012 - [info] * Phase 5: New master cleanup phease..提升最新的slave為master

Wed Jun 6 14:51:37 2012 - [info]

Wed Jun 6 14:51:37 2012 - [info] Resetting slave info on the new master..

Wed Jun 6 14:51:37 2012 - [info] Master failover to 192.168.123.14(192.168.123.14:3306) completed successfully.

Wed Jun 6 14:51:37 2012 - [info]

----- Failover Report -----

app1: MySQL Master failover 192.168.123.13 to 192.168.123.14 succeeded

Master 192.168.123.13 is down!

Check MHA Manager logs at dg55.yang.com:/masterha/app1/manager.log for details.

Started automated(non-interactive) failover.

The latest slave 192.168.123.14(192.168.123.14:3306) has all relay logs for recovery.

Selected 192.168.123.14 as a new master.

192.168.123.14: OK: Applying all logs succeeded.

Generating relay diff files from the latest slave succeeded.

192.168.123.14: Resetting slave info succeeded.

Master failover to 192.168.123.14(192.168.123.14:3306) completed successfully.

Pacemaker + DRBD

1 為active/passive架構，passive不承擔任何任務，成本較高；

2 downtime較長，passive的buffer pool為空warm-up從0開始

3 為確保資料一致，innodb_flush_log_at_trx_commit/sync_binlog=1

4 複雜，

MHA知識點參考

相關文章