MySQL高可用架構之MHA（4）- 完結

七、通過vip實現mysql的高可用

1）修改/usr/local/mha/mha.cnf

[server default]user=mha_reppassword=123456manager_workdir=/usr/local/mhamanager_log=/usr/local/mha/manager.logssh_user=rootmaster_ip_failover_script=/usr/local/mha/scripts/master_ip_failover #添加管理vip的腳本repl_user=backuprepl_password=backupping_interval=1[server1]hostname=172.16.1.241master_binlog_dir=/application/mysql/data/candidate_master=1port=3306[server2]hostname=172.16.1.242master_binlog_dir=/application/mysql/data/candidate_master=1port=3306[server3]hostname=172.16.1.243master_binlog_dir=/application/mysql/data/port=3306no_master=1

2）修改腳本/usr/local/mha/scripts/master_ip_failover

#!/usr/bin/env perluse strict;use warnings FATAL => all; use Getopt::Long;my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port); my $vip = 172.16.1.240; #vip地址my $key = 1;my $ssh_start_vip = "/sbin/ifconfig eth1:$key $vip"; #綁定在指定的網卡上面my $ssh_stop_vip = "/sbin/ifconfig eth1:$key down"; #我的機器有兩塊網卡eth1是172網段的所有我把vip綁定在eth1上，我的eth0網段是10.0.0.%。 GetOptions( command=s => $command, ssh_user=s => $ssh_user, orig_master_host=s => $orig_master_host, orig_master_ip=s => $orig_master_ip, orig_master_port=i => $orig_master_port, new_master_host=s => $new_master_host, new_master_ip=s => $new_master_ip, new_master_port=i => $new_master_port,); exit &main(); sub main { print "

"; if ( $command eq "stop" || $command eq "stopssh" ) { my $exit_code = 1; eval { print "Disabling the VIP on old master: $orig_master_host
"; &stop_vip(); $exit_code = 0; }; if ($@) { warn "Got Error: $@
"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) { my $exit_code = 10; eval { print "Enabling the VIP - $vip on the new master - $new_master_host
"; &start_vip(); $exit_code = 0; }; if ($@) { warn $@; exit $exit_code; } exit $exit_code; } elsif ( $command eq "status" ) { print "Checking the Status of the script.. OK
"; exit 0; } else { &usage(); exit 1; }}sub start_vip() { `ssh $ssh_user@$new_master_host " $ssh_start_vip "`;}# A simple system call that disable the VIP on the old_master sub stop_vip() { `ssh $ssh_user@$orig_master_host " $ssh_stop_vip "`;} sub usage { print "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
";}

3）模擬故障進行切換

#停止master的mysql服務

[root@master ~]# service mysqld stopShutting down MySQL... SUCCESS!

#查看slave02的同步信息

[root@slave02 ~]# mysql -e show slave statusG |egrep Master_Host|Slave_IO_Running:|Slave_SQL_Running: Master_Host: 172.16.1.242 Slave_IO_Running: Yes Slave_SQL_Running: Yes

#查看slave01的IP信息

[root@slave01 ~]# ifconfigeth0 Link encap:Ethernet HWaddr 00:1C:42:58:08:EF inet addr:10.0.0.242 Bcast:10.0.0.255 Mask:255.255.255.0 inet6 addr: fe80::21c:42ff:fe58:8ef/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:6925 errors:0 dropped:0 overruns:0 frame:0 TX packets:2869 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:679548 (663.6 KiB) TX bytes:420365 (410.5 KiB)eth0:1 Link encap:Ethernet HWaddr 00:1C:42:58:08:EF inet addr:172.16.1.240 Bcast:172.16.255.255 Mask:255.255.0.0 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1eth1 Link encap:Ethernet HWaddr 00:1C:42:F4:DF:3E inet addr:172.16.1.242 Bcast:172.16.1.255 Mask:255.255.255.0 inet6 addr: fe80::21c:42ff:fef4:df3e/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:10272 errors:0 dropped:0 overruns:0 frame:0 TX packets:7875 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:1575148 (1.5 MiB) TX bytes:1644494 (1.5 MiB)eth1:1 Link encap:Ethernet HWaddr 00:1C:42:F4:DF:3E inet addr:172.16.1.240 Bcast:172.16.255.255 Mask:255.255.0.0 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1# 這可以看到我們添加的VIP已經自動添加了lo Link encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 inet6 addr: ::1/128 Scope:Host UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:640 errors:0 dropped:0 overruns:0 frame:0 TX packets:640 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:51251 (50.0 KiB) TX bytes:51251 (50.0 KiB)

4、恢復master的mysql服務同開始恢復方法一樣。

八、MHA日常維護命令

1、查看ssh登陸是否成功

masterha_check_ssh --conf=/usr/local/mha/mha.cnf

2、查看複製是否建立好

masterha_check_repl --conf=/usr/local/mha/mha.cnf

3、啟動mha

nohup masterha_manager --conf=/usr/local/mha/mha.cnf > /tmp/mha_manager.log 2>&1 &

4、檢查啟動的狀態

masterha_check_status --conf=/usr/local/mha/mha.cnf

5、停止mha

masterha_stop masterha_check_status --conf=/usr/local/mha/mha.cnf

6、failover後下次重啟

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

rm -rf /usr/local/mha/mha.failover.complete

九、FAQ（常見問題解答）

1、可能報錯1

[root@server02 mha4mysql-node-0.53]# perl Makefile.PLCant 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 307.Cant 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 307.Cant 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 316.

解決辦法:

yum install cpan -yyum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker -ycpan ExtUtils::Install

如果不想用cpan安裝,那就使用下面這條命令

yum install perl-ExtUtils-Embed -y

2、可能報錯2

[root@server02 mha4mysql-node-0.53]# perl Makefile.PLCant locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /usr/local/lib64/perl BEGIN failed--compilation aborted at Makefile.PL line 3.

解決辦法:

yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

3、可能報錯3

[root@server01 ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf

報錯：

Sun Apr 2 18:58:10 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.Sun Apr 2 18:58:10 2017 - [info] Reading application default configurations from /etc/masterha/app1.cnf..Sun Apr 2 18:58:10 2017 - [info] Reading server configurations from /etc/masterha/app1.cnf..Sun Apr 2 18:58:10 2017 - [info] Starting SSH connection tests..Sun Apr 2 18:58:11 2017 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln63] Sun Apr 2 18:58:10 2017 - [debug] Connecting via SSH from root@172.16.1.50(172.16.1.50:22) to root@172.16.1.60(172.16.1.60:22)..Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).Sun Apr 2 18:58:10 2017 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln107] SSH connection from root@172.16.1.50(172.16.1.50:22) to root@172.16.1.60(172.16.1.60:22) failed!Sun Apr 2 18:58:11 2017 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln63] Sun Apr 2 18:58:10 2017 - [debug] Connecting via SSH from root@172.16.1.60(172.16.1.60:22) to root@172.16.1.50(172.16.1.50:22)..Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).Sun Apr 2 18:58:10 2017 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln107] SSH connection from root@172.16.1.60(172.16.1.60:22) to root@172.16.1.50(172.16.1.50:22) failed!Sun Apr 2 18:58:11 2017 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln63] Sun Apr 2 18:58:11 2017 - [debug] Connecting via SSH from root@172.16.1.70(172.16.1.70:22) to root@172.16.1.50(172.16.1.50:22)..Permission denied (publickey,gssapi-keyex,gssapi-with-mic,password).Sun Apr 2 18:58:11 2017 - [error][/usr/local/share/perl5/MHA/SSHCheck.pm, ln107] SSH connection from root@172.16.1.70(172.16.1.70:22) to root@172.16.1.50(172.16.1.50:22) failed!SSH Configuration Check Failed! at /usr/local/bin/masterha_check_ssh line 44

原因分析，程序需要從manage管理ssh連接，所以會從mysql-test3 ssh到 mysql-test 再ssh到 mysql-test2，問題出在第二次連接，需要輸入key的密碼，導致測試失敗。所以全部機器都要相互做密鑰登錄。

4、可能報錯4

mysql> change master to master_host=172.16.1.241,master_user=backup,master_password=backup,master_port=3306,master_log_file=mysql-bin.000007,master_log_pos=333;

報錯：

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

解決方法

mysql> reset slave;Query OK, 0 rows affected (0.00 sec)mysql> change master to master_host=172.16.1.241,master_user=backup,master_password=backup,master_port=3306,master_log_file=mysql-bin.000007,master_log_pos=333;Query OK, 0 rows affected (0.15 sec)mysql> start slave;Query OK, 0 rows affected (0.01 sec)mysql> show slave statusG

5、可能報錯5

Cant locate Log/Dispatch.pm in @INC(報錯)耗時22個小時才解決

[root@manager ~]# masterha_check_ssh --conf=/usr/local/mha/mha.cnf

Cant locate Log/Dispatch.pm in @INC (@INC contains: /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 /usr/local/share/perl5/MHA/MasterMonitor.pm line 28.BEGIN failed--compilation aborted at /usr/local/share/perl5/MHA/MasterMonitor.pm line 28.Compilation failed in require at /usr/local/bin/masterha_manager line 26.BEGIN failed--compilation aborted at /usr/local/bin/masterha_manager line 26.$ sudo cpancpan[1]> install CPANcpan[2]> install Module::Buildcpan[3]> quit$ sudo cpancpan[1]> install Log::Dispatchcpan[2]> install Log::Dispatch::FileRotatecpan[3]> quit

6、小知識

[root@server02 ~]# mysqldump -uroot -p123456 --master-data=2 --single-transaction -R --triggers -A --event > /server/backup/all_bak_$(date +%F).sql

-u 資料庫登錄用戶名-p 資料庫登錄密碼--event 由於mysql在全量導出時不導出event事件表,故需要在全量導出時忽略事件表,不加此參數會出現告警。 Warning: Skipping the data of table mysql.event. Specify the --events option explicitly--master-data=2代表備份時刻記錄master的Binlog位置和Position（位置點）--single-transaction意思是獲取一致性快照，-R意思是備份存儲過程和函數--triggres的意思是備份觸發器-A代表備份所有的庫更多信息請自行mysqldump --help查看。或http://note.youdao.com/noteshare?id=60607599966788d19a4d46d4ccd2ce9d

查看複製狀態（可以看見複製成功）：

[root@server03 ~]# mysql -uroot -p123456 -e show slave statusG | egrep Slave_IO|Slave_SQL|Until_Log_Pos Slave_IO_State: Waiting for master to send event Slave_IO_Running: Yes # IO線程 Slave_SQL_Running: Yes # SQL線程 Until_Log_Pos: 0 # 主從之間的延遲

修改app1.cnf配置文件，修改後的文件內容如下： 配置參數注釋請看：http://note.youdao.com/noteshare?id=9e18e015bc52e150f56d28851535a514

（2）設置relay log的清除方式（在每個slave節點上）：

server03[root@server03 ~]# mysql -uroot -p123456 -e set global relay_log_purge=0server04[root@server04 ~]# mysql -uroot -p123456 -e set global relay_log_purge=0

注意：

MHA在發生切換的過程中，從庫的恢復過程中依賴於relay log的相關信息，所以這裡要將relay log的自動清除設置為OFF，採用手動清除relay log的方式。

在默認情況下，從伺服器上的中繼日誌會在SQL線程執行完畢後被自動刪除。但是在MHA環境中，這些中繼日誌在恢復其他從伺服器時可能會被用到，因此需要禁用中繼日誌的自動刪除功能。

定期清除中繼日誌需要考慮到複製延時的問題。在ext3的文件系統下，刪除大的文件需要一定的時間，會導致嚴重的複製延時。為了避免複製延時，需要暫時為中繼日誌創建硬鏈接，因為在linux系統中通過硬鏈接刪除大文件速度會很快。（在mysql資料庫中，刪除大表時，通常也採用建立硬鏈接的方式）

MHA節點中包含了pure_relay_logs命令工具，它可以為中繼日誌創建硬鏈接，執行SET GLOBAL relay_log_purge=1,等待幾秒鐘以便SQL線程切換到新的中繼日誌，再執行SET GLOBAL relay_log_purge=0。

pure_relay_logs腳本參數如下所示：

--user mysql 用戶名--password mysql 密碼--port 埠號--workdir 指定創建relaylog的硬鏈接的位置，默認是/var/tmp，由於系統不同分區創建硬鏈接文件會失敗，故需要執行硬鏈接具體位置，成功執行腳本後，硬鏈接的中繼日誌文件被刪除--disable_relay_log_purge 默認情況下，如果relay_log_purge=1，腳本會什麼都不清理，自動退出，通過設定這個參數，當relay_log_purge=1的情況下會將relay_log_purge設置為0。清理relay log之後，最後將參數設置為OFF。