MySQL高可用方案MHA如何部署-創新互聯
這篇文章將為大家詳細講解有關MySQL高可用方案MHA如何部署，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。
成都創新互聯是一家專業提供中衛企業網站建設,專注與成都做網站、網站設計、html5、小程序制作等業務。10年已為中衛眾多企業、政府機構等服務。創新互聯專業的建站公司優惠進行中。MHA（Master High Availability）是一套相對成熟的MySQL高可用方案，能做到在0~30s內自動完成數據庫的故障切換操作，在master服務器不宕機的情況下，基本能保證數據的一致性。
它由兩部分組成：MHA Manager（管理節點）和MHA Node（數據節點）。其中，MHA Manager可以單獨部署在一臺獨立的機器上管理多個master-slave集群，也可以部署在一臺slave上。MHA Node則運行在每個mysql節點上，MHA Manager會定時探測集群中的master節點，當master出現故障時，它自動將最新數據的slave提升為master，然后將其它所有的slave指向新的master。
在MHA自動故障切換過程中，MHA試圖保存master的二進制日志，從而大程度地保證數據不丟失，當這并不總是可行的，譬如，主服務器硬件故障或無法通過ssh訪問，MHA就沒法保存二進制日志，這樣就只進行了故障轉移但丟失了最新數據。可結合MySQL 5.5中推出的半同步復制來降低數據丟失的風險。
MHA軟件由兩部分組成：Manager工具包和Node工具包，具體說明如下：
MHA Manager：
1. masterha_check_ssh：檢查MHA的SSH配置狀況
2. masterha_check_repl：檢查MySQL的復制狀況
3. masterha_manager：啟動MHA
4. masterha_check_status：檢測當前MHA運行狀態
5. masterha_master_monitor：檢測master是否宕機
6. masterha_master_switch：控制故障轉移（自動或手動）
7. masterha_conf_host：添加或刪除配置的server信息
8. masterha_stop：關閉MHA
MHA Node:
save_binary_logs：保存或復制master的二進制日志
apply_diff_relay_logs：識別差異的relay log并將差異的event應用到其它slave中
filter_mysqlbinlog：去除不必要的ROLLBACK事件（MHA已不再使用這個工具）
purge_relay_logs：消除中繼日志（不會堵塞SQL線程）
另有如下幾個腳本需自定義：
1. master_ip_failover：管理VIP
2. master_ip_online_change：
3. masterha_secondary_check：當MHA manager檢測到master不可用時，通過masterha_secondary_check腳本來進一步確認，減低誤切的風險。
4. send_report：當發生故障切換時，可通過send_report腳本發送告警信息。
集群信息
角色 IP地址 ServerID 類型
Master 192.168.244.10 1 寫入
Candicate master 192.168.244.20 2 讀
Slave 192.168.244.30 3 讀
Monitor host 192.168.244.40 監控集群組
注：操作系統均為RHEL 6.7
其中，master對外提供寫服務，備選master提供讀服務，slave也提供相關的讀服務，一旦master宕機，將會把備選master提升為新的master，slave指向新的master
一、在所有節點上安裝MHA node
1. 在MySQL服務器上安裝MHA node所需的perl模塊（DBD:mysql）
# yum install perl-DBD-MySQL -y
2. 在所有的節點上安裝mha node
下載地址為：https://code.google.com/p/mysql-master-ha/wiki/Downloads?tm=2
由于該網址在國內被墻，相關文件下載后，放到了個人網盤中，http://pan.baidu.com/s/1boS31vT，有需要的童鞋可自行下載。
# tar xvf mha4mysql-node-0.56.tar.gz
# cd mha4mysql-node-0.56
# perl Makefile.PL
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Can.pm line 6.BEGIN failed--compilation aborted at inc/Module/Install/Can.pm line 6. Compilation failed in require at inc/Module/Install.pm line 283. Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Makefile.pm line 4.BEGIN failed--compilation aborted at inc/Module/Install/Makefile.pm line 4. Compilation failed in require at inc/Module/Install.pm line 283. Can't locate ExtUtils/MM_Unix.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Metadata.pm line 349.
View Code
通過報錯可以看出，是相關依賴包沒有安裝。
# yum install perl-ExtUtils-MakeMaker -y
# perl Makefile.PL
*** Module::AutoInstall version 1.03*** Checking for Perl dependencies... Can't locate CPAN.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/AutoInstall.pm line 277.
# yum install perl-CPAN -y
# perl Makefile.PL
*** Module::AutoInstall version 1.03*** Checking for Perl dependencies... [Core Features]- DBI ...loaded. (1.609)- DBD::mysql ...loaded. (4.013)*** Module::AutoInstall configuration finished. Checking if your kit is complete... Looks good Writing Makefile for mha4mysql::node
View Code
# make
# make install
至此，MHA node節點安裝完畢，會在/usr/local/bin下生成以下腳本文件
# ll /usr/local/bin/total 44-r-xr-xr-x 1 root root 16367 Jul 20 07:00 apply_diff_relay_logs-r-xr-xr-x 1 root root 4807 Jul 20 07:00 filter_mysqlbinlog-r-xr-xr-x 1 root root 8261 Jul 20 07:00 purge_relay_logs-r-xr-xr-x 1 root root 7525 Jul 20 07:00 save_binary_logs
二、在Monitor host節點上部署MHA Manager
# tar xvf mha4mysql-manager-0.56.tar.gz
# cd mha4mysql-manager-0.56
# perl Makefile.PL
*** Module::AutoInstall version 1.03*** Checking for Perl dependencies... [Core Features]- DBI ...loaded. (1.609)- DBD::mysql ...loaded. (4.013)- Time::HiRes ...missing.- Config::Tiny ...missing.- Log::Dispatch ...missing.- Parallel::ForkManager ...missing.- MHA::NodeConst ...missing.==> Auto-install the 5 mandatory module(s) from CPAN? [y] y*** Dependencies will be installed the next time you type 'make'.*** Module::AutoInstall configuration finished. Checking if your kit is complete... Looks good Warning: prerequisite Config::Tiny 0 not found. Warning: prerequisite Log::Dispatch 0 not found. Warning: prerequisite MHA::NodeConst 0 not found. Warning: prerequisite Parallel::ForkManager 0 not found. Warning: prerequisite Time::HiRes 0 not found. Writing Makefile for mha4mysql::manager
View Code
# make
# make install
執行完畢后，會在/usr/local/bin下新增以下幾個文件
# ll /usr/local/bin/total 40-r-xr-xr-x 1 root root 1991 Jul 20 00:50 masterha_check_repl-r-xr-xr-x 1 root root 1775 Jul 20 00:50 masterha_check_ssh-r-xr-xr-x 1 root root 1861 Jul 20 00:50 masterha_check_status-r-xr-xr-x 1 root root 3197 Jul 20 00:50 masterha_conf_host-r-xr-xr-x 1 root root 2513 Jul 20 00:50 masterha_manager-r-xr-xr-x 1 root root 2161 Jul 20 00:50 masterha_master_monitor-r-xr-xr-x 1 root root 2369 Jul 20 00:50 masterha_master_switch-r-xr-xr-x 1 root root 5167 Jul 20 00:50 masterha_secondary_check-r-xr-xr-x 1 root root 1735 Jul 20 00:50 masterha_stop
三、配置SSH登錄無密碼驗證
1. 在manager上配置到所有Node節點的無密碼驗證
# ssh-keygen
一路按“Enter”
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.10
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.20
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.30
2. 在Master（192.168.244.10）上配置
# ssh-keygen
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.20
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.30
3. 在Candicate master（192.168.244.20）上配置
# ssh-keygen
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.10
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.30
4. 在Slave（192.168.244.30）上配置
# ssh-keygen
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.10
# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.244.20
四、搭建主從復制環境
1. 在Master上執行備份
# mysqldump --master-data=2 --single-transaction -R --triggers -A > all.sql
其中，-R是備份存儲過程，--triggers是備份觸發器 -A代表全庫
2. 在Master上創建復制用戶
mysql> grant replication slave on *.* to 'repl'@'192.168.244.%' identified by 'repl'; Query OK, 0 rows affected (0.09 sec)
3. 查看備份文件all.sql中的CHANGE MASTER語句
# head -n 30 all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=120;
4. 將備份文件復制到Candicate master和Slave上
# scp all.sql 192.168.244.20:/root/
# scp all.sql 192.168.244.30:/root/
5. 在Candicate master上搭建從庫
# mysql < all.sql
設置復制信息
mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.244.10', -> MASTER_USER='repl', -> MASTER_PASSWORD='repl', -> MASTER_LOG_FILE='mysql-bin.000002', -> MASTER_LOG_POS=120; Query OK, 0 rows affected, 2 warnings (0.19 sec) mysql> start slave; Query OK, 0 rows affected (0.02 sec) mysql> show slave status\G
6. 在Slave上搭建從庫
7. slave服務器設置為read only
mysql> set global read_only=1; Query OK, 0 rows affected (0.04 sec)
8. 在Master中創建監控用戶
mysql> grant all privileges on *.* to 'monitor'@'%' identified by 'monitor123'; Query OK, 0 rows affected (0.07 sec)
五、 配置MHA
1. 在Monitor host（192.168.244.40）上創建MHA工作目錄，并且創建相關配置文件
# mkdir -p /etc/masterha
# vim /etc/masterha/app1.cnf
[server default] manager_log=/masterha/app1/manager.log //設置manager的日志manager_workdir=/masterha/app1 //設置manager的工作目錄master_binlog_dir=/var/lib/mysql //設置master默認保存binlog的位置，以便MHA可以找到master的日志master_ip_failover_script= /usr/local/bin/master_ip_failover //設置自動failover時候的切換腳本master_ip_online_change_script= /usr/local/bin/master_ip_online_change //設置手動切換時候的切換腳本user=monitor // 設置監控用戶password=monitor123 //設置監控用戶的密碼ping_interval=1 //設置監控主庫，發送ping包的時間間隔，默認是3秒，嘗試三次沒有回應的時候進行自動failoverremote_workdir=/tmp //設置遠端mysql在發生切換時binlog的保存位置repl_user=repl //設置復制環境中的復制用戶名repl_password=repl //設置復制用戶的密碼report_script=/usr/local/bin/send_report //設置發生切換后發送的報警的腳本secondary_check_script= /usr/local/bin/masterha_secondary_check -s 192.168.244.20 -s 192.168.244.30 --user=root --master_host=192.168.244.10 --master_ip=192.168.244.10 --master_port=3306 //一旦MHA到master的監控之間出現問題，MHA Manager將會判斷其它兩個slave是否能建立到master_ip 3306端口的連接shutdown_script="" //設置故障發生后關閉故障主機腳本（該腳本的主要作用是關閉主機防止發生腦裂）ssh_user=root //設置ssh的登錄用戶名[server1]hostname=192.168.244.10port=3306[server2]hostname=192.168.244.20port=3306candidate_master=1 //設置為候選master，如果設置該參數以后，發生主從切換以后將會將此從庫提升為主庫，即使這個主庫不是集群中最新的slavecheck_repl_delay=0 //默認情況下如果一個slave落后master 100M的relay logs的話，MHA將不會選擇該slave作為一個新的master，因為對于這個slave的恢復需要花費很長時間，通過設置check_repl_delay=0,MHA觸發切換在選擇一個新的master的時候將會忽略復制延時，這個參數對于設置了candidate_master=1的主機非常有用，因為它保證了這個候選主在切換過程中一定是最新的master[server3]hostname=192.168.244.30port=3306
注意：
1> 在編輯該文件時，后面的注釋切記要去掉，MHA并不會將后面的內容識別為注釋。
2> 配置文件中設置了master_ip_failover_script，secondary_check_script，master_ip_online_change_script，report_script，對應的文件見文章末 尾。
2. 設置relay log清除方式（在每個Slave上）
mysql> set global relay_log_purge=0; Query OK, 0 rows affected (0.00 sec)
MHA在發生切換過程中，從庫在恢復的過程中，依賴于relay log的相關信息，所以我們這里要將relay log的自動清楚設置為OFF，采用手動清楚relay log的方式。
在默認情況下，從服務器上的中繼日志會在SQL線程執行完后被自動刪除。但是在MHA環境中，這些中繼日志在恢復其它從服務器時可能會被用到，因此需要禁用中繼日志的自動清除。改為定期手動清除SQL線程應用完的中繼日志。
在ext3文件系統下，刪除大的文件需要一定的時間，這樣會導致嚴重的復制延遲，所以在Linux中，一般都是通過硬鏈接的方式來刪除大文件。
3. 設置定期清理relay腳本
MHA節點中包含了purge_relay_logs腳本，它可以為relay log創建硬鏈接，執行set global relay_log_purge=1，等待幾秒鐘以便SQL線程切換到新的中繼日志，再執行set global relay_log_purge=0。
下面看看腳本的使用方法：
# purge_relay_logs --user=monitor --password=monitor123 -disable_relay_log_purge --workdir=/tmp/
2017-04-24 20:27:46: purge_relay_logs script started. Found relay_log.info: /var/lib/mysql/relay-log.info Opening /var/lib/mysql/mysqld-relay-bin.000001 .. Opening /var/lib/mysql/mysqld-relay-bin.000002 .. Opening /var/lib/mysql/mysqld-relay-bin.000003 .. Opening /var/lib/mysql/mysqld-relay-bin.000004 .. Opening /var/lib/mysql/mysqld-relay-bin.000005 .. Opening /var/lib/mysql/mysqld-relay-bin.000006 .. Executing SET GLOBAL relay_log_purge=1; FLUSH LOGS; sleeping a few seconds so that SQL thread can delete older relay log files (if i t keeps up); SET GLOBAL relay_log_purge=0; .. ok.2017-04-24 20:27:50: All relay log purging operations succeeded.
其中，
--user：mysql用戶名
--password：mysql用戶的密碼
--host： mysqlserver地址
--workdir：指定創建relay log的硬鏈接的位置，默認的是/var/tmp。由于系統不同分區創建硬鏈接文件會失敗，故需要指定具體的硬鏈接的位置。
--disable_relay_log_purge：默認情況下，如果relay_log_purge=1，則腳本會直接退出。通過設置這個參數，該腳本會首先將relay_log_purge設置為1，清除掉relay log后，再將該參數設置為0。
設置crontab來定期清理relay log
MHA在切換的過程中會直接調用mysqlbinlog命令，故需要在環境變量中指定mysqlbinlog的具體路徑。
# vim /etc/cron.d/purge_relay_logs
0 4 * * * /usr/local/bin/purge_relay_logs --user=monitor --password=monitor123 -disable_relay_log_purge --workdir=/tmp/ >> /tmp/purge _relay_logs.log 2>&1
注意：最好是每臺slave服務器在不同時間點執行該計劃任務。
4. 將mysqlbinlog的路徑添加到環境變量中
六、 檢查SSH的配置
在Monitor host上執行
# masterha_check_ssh --conf=/etc/masterha/app1.cnf
Wed Jul 20 14:33:36 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Wed Jul 20 14:33:36 2016 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Wed Jul 20 14:33:36 2016 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Wed Jul 20 14:33:36 2016 - [info] Starting SSH connection tests.. Wed Jul 20 14:33:51 2016 - [debug] Wed Jul 20 14:33:36 2016 - [debug] Connecting via SSH from root@192.168.244.10(192.168.244.10:22) to root@192.168.244.20(192.168.244.20:22).. Wed Jul 20 14:33:48 2016 - [debug] ok. Wed Jul 20 14:33:48 2016 - [debug] Connecting via SSH from root@192.168.244.10(192.168.244.10:22) to root@192.168.244.30(192.168.244.30:22).. Wed Jul 20 14:33:50 2016 - [debug] ok. Wed Jul 20 14:33:55 2016 - [debug] Wed Jul 20 14:33:37 2016 - [debug] Connecting via SSH from root@192.168.244.30(192.168.244.30:22) to root@192.168.244.10(192.168.244.10:22).. Wed Jul 20 14:33:49 2016 - [debug] ok. Wed Jul 20 14:33:49 2016 - [debug] Connecting via SSH from root@192.168.244.30(192.168.244.30:22) to root@192.168.244.20(192.168.244.20:22).. Wed Jul 20 14:33:54 2016 - [debug] ok. Wed Jul 20 14:33:55 2016 - [debug] Wed Jul 20 14:33:36 2016 - [debug] Connecting via SSH from root@192.168.244.20(192.168.244.20:22) to root@192.168.244.10(192.168.244.10:22).. Wed Jul 20 14:33:49 2016 - [debug] ok. Wed Jul 20 14:33:49 2016 - [debug] Connecting via SSH from root@192.168.244.20(192.168.244.20:22) to root@192.168.244.30(192.168.244.30:22).. Wed Jul 20 14:33:54 2016 - [debug] ok. Wed Jul 20 14:33:55 2016 - [info] All SSH connection tests passed successfully.
View Code
七、查看整個集群的狀態
在Monitor host上執行
# masterha_check_repl --conf=/etc/masterha/app1.cnf
Wed Jul 20 14:44:30 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Wed Jul 20 14:44:30 2016 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Wed Jul 20 14:44:30 2016 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Wed Jul 20 14:44:30 2016 - [info] MHA::MasterMonitor version 0.56. Wed Jul 20 14:44:31 2016 - [info] GTID failover mode = 0Wed Jul 20 14:44:31 2016 - [info] Dead Servers: Wed Jul 20 14:44:31 2016 - [info] Alive Servers: Wed Jul 20 14:44:31 2016 - [info] 192.168.244.10(192.168.244.10:3306) Wed Jul 20 14:44:31 2016 - [info] 192.168.244.20(192.168.244.20:3306) Wed Jul 20 14:44:31 2016 - [info] 192.168.244.30(192.168.244.30:3306) Wed Jul 20 14:44:31 2016 - [info] Alive Slaves: Wed Jul 20 14:44:31 2016 - [info] 192.168.244.20(192.168.244.20:3306) Version=5.6.31 (oldest major version between slaves) log-bin:disabled Wed Jul 20 14:44:31 2016 - [info] Replicating from 192.168.244.10(192.168.244.10:3306) Wed Jul 20 14:44:31 2016 - [info] Primary candidate for the new Master (candidate_master is set) Wed Jul 20 14:44:31 2016 - [info] 192.168.244.30(192.168.244.30:3306) Version=5.6.31 (oldest major version between slaves) log-bin:disabled Wed Jul 20 14:44:31 2016 - [info] Replicating from 192.168.244.10(192.168.244.10:3306) Wed Jul 20 14:44:31 2016 - [info] Current Alive Master: 192.168.244.10(192.168.244.10:3306) Wed Jul 20 14:44:31 2016 - [info] Checking slave configurations.. Wed Jul 20 14:44:31 2016 - [warning] log-bin is not set on slave 192.168.244.20(192.168.244.20:3306). This host cannot be a master. Wed Jul 20 14:44:31 2016 - [warning] log-bin is not set on slave 192.168.244.30(192.168.244.30:3306). This host cannot be a master. Wed Jul 20 14:44:31 2016 - [info] Checking replication filtering settings.. Wed Jul 20 14:44:31 2016 - [info] binlog_do_db= , binlog_ignore_db= Wed Jul 20 14:44:31 2016 - [info] Replication filtering check ok. Wed Jul 20 14:44:31 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln361] None of slaves can be master. Check failover configuration file or log-bin settings in my.cnf Wed Jul 20 14:44:31 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/local/bin/masterha_check_repl line 48. Wed Jul 20 14:44:31 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers. Wed Jul 20 14:44:31 2016 - [info] Got exit code 1 (Not master dead). MySQL Replication Health is NOT OK!
View Code
報錯很明顯，Candicate master和Slave都沒有啟動log-bin，如果沒有啟動的話，后續就無法提升為主
設置log-bin后，重新執行：
Wed Jul 20 15:49:58 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Wed Jul 20 15:49:58 2016 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Wed Jul 20 15:49:58 2016 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Wed Jul 20 15:49:58 2016 - [info] MHA::MasterMonitor version 0.56. Wed Jul 20 15:49:59 2016 - [info] GTID failover mode = 0Wed Jul 20 15:49:59 2016 - [info] Dead Servers: Wed Jul 20 15:49:59 2016 - [info] Alive Servers: Wed Jul 20 15:49:59 2016 - [info] 192.168.244.10(192.168.244.10:3306) Wed Jul 20 15:49:59 2016 - [info] 192.168.244.20(192.168.244.20:3306) Wed Jul 20 15:49:59 2016 - [info] 192.168.244.30(192.168.244.30:3306) Wed Jul 20 15:49:59 2016 - [info] Alive Slaves: Wed Jul 20 15:49:59 2016 - [info] 192.168.244.20(192.168.244.20:3306) Version=5.6.31-log (oldest major version between slaves) log-bin:enabled Wed Jul 20 15:49:59 2016 - [info] Replicating from 192.168.244.10(192.168.244.10:3306) Wed Jul 20 15:49:59 2016 - [info] Primary candidate for the new Master (candidate_master is set) Wed Jul 20 15:49:59 2016 - [info] 192.168.244.30(192.168.244.30:3306) Version=5.6.31-log (oldest major version between slaves) log-bin:enabled Wed Jul 20 15:49:59 2016 - [info] Replicating from 192.168.244.10(192.168.244.10:3306) Wed Jul 20 15:49:59 2016 - [info] Current Alive Master: 192.168.244.10(192.168.244.10:3306) Wed Jul 20 15:49:59 2016 - [info] Checking slave configurations.. Wed Jul 20 15:49:59 2016 - [info] Checking replication filtering settings.. Wed Jul 20 15:49:59 2016 - [info] binlog_do_db= , binlog_ignore_db= Wed Jul 20 15:49:59 2016 - [info] Replication filtering check ok. Wed Jul 20 15:49:59 2016 - [info] GTID (with auto-pos) is not supported Wed Jul 20 15:49:59 2016 - [info] Starting SSH connection tests.. Wed Jul 20 15:50:17 2016 - [info] All SSH connection tests passed successfully. Wed Jul 20 15:50:17 2016 - [info] Checking MHA Node version.. Wed Jul 20 15:50:18 2016 - [info] Version check ok. Wed Jul 20 15:50:18 2016 - [info] Checking SSH publickey authentication settings on the current master.. Wed Jul 20 15:50:20 2016 - [info] HealthCheck: SSH to 192.168.244.10 is reachable. Wed Jul 20 15:50:21 2016 - [info] Master MHA Node version is 0.56. Wed Jul 20 15:50:21 2016 - [info] Checking recovery script configurations on 192.168.244.10(192.168.244.10:3306).. Wed Jul 20 15:50:21 2016 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/tmp/save_binary_logs_test --manager_version=0.56 --start_file=mysqld-bin.000002 Wed Jul 20 15:50:21 2016 - [info] Connecting to root@192.168.244.10(192.168.244.10:22).. Creating /tmp if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /var/lib/mysql, up to mysqld-bin.000002Wed Jul 20 15:50:23 2016 - [info] Binlog setting check done. Wed Jul 20 15:50:23 2016 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Wed Jul 20 15:50:23 2016 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='monitor' --slave_host=192.168.244.20 --slave_ip=192.168.244.20 --slave_port=3306 --workdir=/tmp --target_version=5.6.31-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Wed Jul 20 15:50:23 2016 - [info] Connecting to root@192.168.244.20(192.168.244.20:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to mysqld-relay-bin.000004 Temporary relay log file is /var/lib/mysql/mysqld-relay-bin.000004 Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Wed Jul 20 15:50:28 2016 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='monitor' --slave_host=192.168.244.30 --slave_ip=192.168.244.30 --slave_port=3306 --workdir=/tmp --target_version=5.6.31-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Wed Jul 20 15:50:28 2016 - [info] Connecting to root@192.168.244.30(192.168.244.30:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to mysqld-relay-bin.000008 Temporary relay log file is /var/lib/mysql/mysqld-relay-bin.000008 Testing mysql connection and privileges..Warning: Using a password on the command line interface can be insecure. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done. Wed Jul 20 15:50:32 2016 - [info] Slaves settings check done. Wed Jul 20 15:50:32 2016 - [info] 192.168.244.10(192.168.244.10:3306) (current master) +--192.168.244.20(192.168.244.20:3306) +--192.168.244.30(192.168.244.30:3306) Wed Jul 20 15:50:32 2016 - [info] Checking replication health on 192.168.244.20.. Wed Jul 20 15:50:32 2016 - [info] ok. Wed Jul 20 15:50:32 2016 - [info] Checking replication health on 192.168.244.30.. Wed Jul 20 15:50:32 2016 - [info] ok. Wed Jul 20 15:50:32 2016 - [info] Checking master_ip_failover_script status: Wed Jul 20 15:50:32 2016 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.244.10 --orig_master_ip=192.168.244.10 --orig_master_port=3306 Wed Jul 20 15:50:32 2016 - [info] OK. Wed Jul 20 15:50:32 2016 - [warning] shutdown_script is not defined. Wed Jul 20 15:50:32 2016 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.
View Code
檢查通過~
八、 檢查MHA Manager的狀態
# masterha_check_status --conf=/etc/masterha/app1.cnf app1 is stopped(2:NOT_RUNNING).
如果正常，會顯示“PING_OK”，否則會顯示“NOT_RUNNING”，代表MHA監控還沒有開啟。
九、開啟MHA Manager監控
# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /masterha/app1/manager.log 2>&1 &
其中，
remove_dead_master_conf：該參數代表當發生主從切換后，老的主庫的IP將會從配置文件中移除。
ignore_last_failover：在默認情況下，MHA發生切換后將會在/masterha/app1下產生app1.failover.complete文件，下次再次切換的時候如果發現該目錄下存在該文件且兩次切換的時間間隔不足8小時的話，將不允許觸發切換。除非在第一次切換后手動rm -rf /masterha/app1/app1.failover.complete。該參數代表忽略上次MHA觸發切換產生的文件。
查看MHA Manager監控是否正常
# masterha_check_status --conf=/etc/masterha/app1.cnf app1 (pid:1873) is running(0:PING_OK), master:192.168.244.10
十、 關閉MHA Manager監控
# masterha_stop --conf=/etc/masterha/app1.cnf Stopped app1 successfully. [1]+ Exit 1 nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /masterha/app1/manager.log 2>&1
至此，MHA部分配置完畢，下面，來配置VIP。
十一、VIP配置
VIP配置可以采用兩種方式，一是通過引入Keepalived來管理VIP，另一種是在腳本中手動管理。
對于keepalived管理VIP，存在腦裂情況，即當主從網絡出現問題時，slave會搶占VIP，這樣會導致主從數據庫都持有VIP，造成IP沖突，所以在網絡不是很好的情況下，不建議采用keepalived服務。
在實際生產中使用較多的也是第二種，即在腳本中手動管理VIP，所以，對keepalived不感興趣的童鞋可直接跳過第一種方式。
1. keepalived管理VIP
1> 安裝keepalived
因為我這里設置了Candicate master，故只在Master和Candicate master上安裝。
如果沒有Candicate master，兩個Slave的地位平等，則兩個Slave上都需安裝keepalived。
# wget http://www.keepalived.org/software/keepalived-1.2.24.tar.gz
# tar xvf keepalived-1.2.24.tar.gz
# cd keepalived-1.2.24
# ./configure --prefix=/usr/local/keepalived
# make
# make install
# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
# mkdir /etc/keepalived
# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
2> 為keepalived設置單獨的日志文件（非必需）
keepalived的日志默認是輸出到/var/log/message中
# vim /etc/sysconfig/keepalived
KEEPALIVED_OPTIONS="-D -d -S 0"
設置syslog
# vim /etc/rsyslog.conf
添加如下內容：
local0.* /var/log/keepalived.log
# service rsyslog restart
2> 配置keepalived
在Master上修改
# vim /etc/keepalived/keepalived.conf
global_defs {
notification_email {
slowtech@qq.com
}
notification_email_from root@localhost.localdomain
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-HA
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 150
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress { 192.168.244.188/24
}
}View Code
關于keepalived的參數的詳細介紹，可參考：
LVS+Keepalived搭建MyCAT高可用負載均衡集群
keepalived工作原理和配置說明
將配置文件scp到Candicate master上
# scp /etc/keepalived/keepalived.conf 192.168.244.20:/etc/keepalived/
只需將配置文件中的priority設置為90
注意：我們為什么在這里設置keepalived為backup模式呢？
在master-backup模式下，如果主庫宕掉，VIP會自動漂移到Slave上，當主庫修復，keepalived啟動后，還會將VIP搶過來，即使設置了nopreempt（不搶占）的方
式，該動作仍會發生。但在backup-backup模式下，當主庫修改，并啟動keepalived后，并不會搶占新主的VIP，即便原主的priority高于新主的。
3> 啟動keepalived
先在Master上啟動
# service keepalived start
env: /etc/init.d/keepalived: Permission denied
# chmod +x /etc/init.d/keepalived
# service keepalived start
查看綁定情況
# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> 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 inet6 ::1/128 scope host valid_lft forever preferred_lft forever2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000 link/ether 00:0c:29:c6:47:04 brd ff:ff:ff:ff:ff:ff inet 192.168.244.10/24 brd 192.168.244.255 scope global eth0 inet 192.168.244.188/24 scope global secondary eth0 inet6 fe80::20c:29ff:fec6:4704/64 scope link valid_lft forever preferred_lft forever
View Code
可見，VIP（192168.244.188）已經綁定到Master的eth0網卡上了。
啟動Candicate master的keepalived
# service keepalived start
4> MHA中引入keepalived
編輯/usr/local/bin/master_ip_failover
相對于原文件，修改地方為93-95行
1 #!/usr/bin/env perl 2 3 # Copyright (C) 2011 DeNA Co.,Ltd. 4 # 5 # This program is free software; you can redistribute it and/or modify 6 # it under the terms of the GNU General Public License as published by 7 # the Free Software Foundation; either version 2 of the License, or 8 # (at your option) any later version. 9 # 10 # This program is distributed in the hope that it will be useful, 11 # but WITHOUT ANY WARRANTY; without even the implied warranty of 12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 13 # GNU General Public License for more details. 14 # 15 # You should have received a copy of the GNU General Public License 16 # along with this program; if not, write to the Free Software 17 # Foundation, Inc., 18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA 19 20 ## Note: This is a sample script and is not complete. Modify the script based on your environment. 21 22 use strict; 23 use warnings FATAL => 'all'; 24 25 use Getopt::Long; 26 use MHA::DBHelper; 27 my ( 28 $command, $ssh_user, $orig_master_host, 29 $orig_master_ip, $orig_master_port, $new_master_host, 30 $new_master_ip, $new_master_port, $new_master_user, 31 $new_master_password 32 ); 33 34 GetOptions( 35 'command=s' => \$command, 36 'ssh_user=s' => \$ssh_user, 37 'orig_master_host=s' => \$orig_master_host, 38 'orig_master_ip=s' => \$orig_master_ip, 39 'orig_master_port=i' => \$orig_master_port, 40 'new_master_host=s' => \$new_master_host, 41 'new_master_ip=s' => \$new_master_ip, 42 'new_master_port=i' => \$new_master_port, 43 'new_master_user=s' => \$new_master_user, 44 'new_master_password=s' => \$new_master_password, 45 ); 46 47 exit &main(); 48 49 sub main { 50 if ( $command eq "stop" || $command eq "stopssh" ) { 51 52 # $orig_master_host, $orig_master_ip, $orig_master_port are passed. 53 # If you manage master ip address at global catalog database, 54 # invalidate orig_master_ip here. 55 my $exit_code = 1; 56 eval { 57 58 # updating global catalog, etc 59 $exit_code = 0; 60 }; 61 if ($@) { 62 warn "Got Error: $@\n"; 63 exit $exit_code; 64 } 65 exit $exit_code; 66 } 67 elsif ( $command eq "start" ) { 68 69 # all arguments are passed. 70 # If you manage master ip address at global catalog database, 71 # activate new_master_ip here. 72 # You can also grant write access (create user, set read_only=0, etc) here. 73 my $exit_code = 10; 74 eval { 75 my $new_master_handler = new MHA::DBHelper(); 76 77 # args: hostname, port, user, password, raise_error_or_not 78 $new_master_handler->connect( $new_master_ip, $new_master_port, 79 $new_master_user, $new_master_password, 1 ); 80 81 ## Set read_only=0 on the new master 82 $new_master_handler->disable_log_bin_local(); 83 print "Set read_only=0 on the new master.\n"; 84 $new_master_handler->disable_read_only(); 85 86 ## Creating an app user on the new master 87 #print "Creating app user on the new master..\n"; 88 #FIXME_xxx_create_user( $new_master_handler->{dbh} ); 89 $new_master_handler->enable_log_bin_local(); 90 $new_master_handler->disconnect(); 91 92 ## Update master ip on the catalog database, etc 93 my $cmd; 94 $cmd = 'ssh '.$ssh_user.'@'.$orig_master_ip.' service keepalived stop'; 95 system($cmd); 96 $exit_code = 0; 97 }; 98 if ($@) { 99 warn $@;100 101 # If you want to continue failover, exit 10.102 exit $exit_code;103 }104 exit $exit_code;105 }106 elsif ( $command eq "status" ) {107 108 # do nothing109 exit 0;110 }111 else {112 &usage();113 exit 1;114 }115 }116 117 sub usage {118 print119 "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";120 }View Code
2. 通過腳本的方式管理VIP
編輯/usr/local/bin/master_ip_failover
#!/usr/bin/ software; you can redistribute it and/ not, Franklin Street, Fifth Floor, Boston, MA -=> = = = = = => => => => => => => => => =>& ( $command eq || $command eq = &= access (create user, set read_only== =->=->->->->->&= & $ssh_user\@$new_master_host \ ` $ssh_user\@$new_master_host \ $ssh_user\@$orig_master_host \
實際生產環境中，推薦這種方式來管理VIP，可有效防止腦裂情況的發生。
至此，MHA高可用環境基本搭建完畢。
關于MHA的常見操作，包括自動Failover，手動Failover，在線切換，可參考另一篇博客：
MHA在線切換的步驟和原理
MHA自動Failover與手動Failover的實踐及原理
總結：
1. 可單獨調試master_ip_failover，master_ip_online_change，send_report等腳本
/usr/local/bin/master_ip_online_change --command=stop --orig_master_ip=192.168.244.10 --orig_master_host=192.168.244.10 --orig_master_port=3306 --orig_master_user=monitor --orig_master_password=monitor123 --orig_master_ssh_user=root --new_master_host=192.168.244.20 --new_master_ip=192.168.244.20 --new_master_port=3306 --new_master_user=monitor --new_master_password=monitor123 --new_master_ssh_user=root
/usr/local/bin/master_ip_failover --command=start --ssh_user=root --orig_master_host=192.168.244.10 --orig_master_ip=192.168.244.10 --orig_master_port=3306 --new_master_host=192.168.244.20 --new_master_ip=192.168.244.20 --new_master_port=3306 --new_master_user='monitor' --new_master_password='monitor123'
2. 官方對于master_ip_failover，master_ip_online_change，send_report腳本，給出的只是sample，切換的邏輯需要自己定義。
很多童鞋對perl并不熟悉，覺得無從下手，其實，完全可以調用其它腳本，譬如python，shell等。
如：
[root@node4 ~]# cat test.pl #!/usr/bin/perluse strict; my $cmd='python /root/test.py'; system($cmd); [root@node4 ~]# cat test.py #!/usr/bin/python print "hello,python"[root@node4 ~]# perl test.pl hello,python
關于“MySQL高可用方案MHA如何部署”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
網站題目：MySQL高可用方案MHA如何部署-創新互聯
路徑分享：http://m.kartarina.com/article12/ccghdc.html
成都網站建設公司_創新互聯，為您提供網頁設計公司、外貿建站、響應式網站、網站收錄、外貿網站建設、網站制作
聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯
- 澳門鮮花電商APP開發主要業務模式分析 2023-03-27
- 上海APP開發如何進行優先級開發 2020-12-03
- 上海哪家APP開發外包公司比較專業？ 2020-11-14
- 平輿代駕app開發功能與方案 2020-12-23
- 武漢手機修圖APP開發有哪些功能？ 2023-02-23
- 登封APP開發如何做好用戶數據分析？ 2020-12-19
- 互聯網宣傳，成都app開發還是平臺開發？ 2022-07-13
- 網絡友好度——APP開發應重視的問題 2022-07-15
- 慘不忍睹！你確定已跟上市場APP開發的腳步？ 2016-11-09
- 面對微信小程序的挑戰，APP開發當如何面對？ 2022-06-30
- 成都原生APP開發的成本為何這么貴？ 2022-07-17
- 真實測評：用uni-app開發小程序，比原生開發好在哪里？ 2022-08-23