mysql中MHA如何搭建

這篇文章主要為大家展示了“mysql中MHA如何搭建”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“mysql中MHA如何搭建”這篇文章吧。

操作系統 ：
debian5.0.2

數據庫版本：
mysql5.0.51a

結構：
manager : 192.168.1.136(M1)

master : 192.168.1.20:3306(N1)
slave1 :  192.168.1.20:3307(N2)
slave2 :  192.168.1.20:3308(N3)
slave3 :  192.168.1.20:3309(N4)

軟件包準備：
mha4mysql-manager_0.55-1_all.deb(還有對應的二進制包，源碼包)
mha4mysql-node_0.54-1_all.deb

準備就緒：
①安裝依賴包
apt-get install libdbd-mysql-perl
apt-get install libconfig-tiny-perl
apt-get install liblog-dispatch-perl
apt-get install libparallel-forkmanager-perl
centos版(yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager)

②安裝mha manager包和node包
M1：dpkg -i mha4mysql-manager_0.55-1_all.deb
N1：dpkg -i mha4mysql-node_0.54-1_all.deb
N2：dpkg -i mha4mysql-node_0.54-1_all.deb
N3：dpkg -i mha4mysql-node_0.54-1_all.deb
N4：dpkg -i mha4mysql-node_0.54-1_all.deb

③主機信任(由于公司環境的原因，這里使用的是mysql用戶)
M1:
#su mysql
#ssh-keygen -t rsa
#cat ~/.ssh/id_rsa.pub
將公鑰內容添加到N1,N2,N3,N4的對應用戶(這里是mysql用戶)下的./ssh/authorized_keys下即可實現M1到N1,N2,N3,N4的免密登錄。
另外，這里踩了個坑，就是免密登錄的server端(N1,N2,N3,N4)即/etc/ssh/sshd_config文件必須開啟公鑰登錄

免密登錄的client端(M1)即/etc/ssh/ssh_config文件不能關閉公鑰登錄(這個參數設置之后在公司環境下貌似還是不能免密登錄，最后用了默認配置就可以了)

N1,N2,N3,N4節點之間也互相信任

2016-12-4 22:44:12 ADD
ssh客戶端得注釋以下參數 才能免密登錄
PreferredAuthentications password

③新建mysql mha管理賬戶，mysql repl復制用戶



④新建mha工作目錄
M1 ：mkdir -p /opt/mha_manager/log  chown -R mysql /opt/mha_manager
N1~N4：mkdir -p /opt/mha_node        chown -R mysql /opt/mha_node

⑤配置manager
M1:
#mkdir  -p /etc/mha
#cat /etc/mha/app1.conf

• manager_workdir=/opt/mha_manager_work
  

• manager_log=/opt/mha_manager_work/log/app1.log
  

• remote_workdir=/opt/mha_node

• 對以上的配置文件更改屬主為ssh信任賬戶(這里是mysql)






• 另外(源碼安裝的mysql注意)：




•     mha遠程登錄操作mysql的時候默認用不到/usr/local/mysql/bin下面的mysql 以及 mysqlbinlog會報相應的錯誤信息，




• 這個時候我們需要做如下操作




• #ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog




• #ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql





• ④開啟manager





• 這里要注意的是，不要后臺啟動，即不要以 masterha_manager --conf=/etc/mha/app1.conf & 形式啟動，不然會報錯退出。我們前臺啟動，然后ctrl+z , bg放入后臺。





• 至此，整個搭建過程完畢。





• 注意事項：




• ①slave最好設置 read_only=1 relay_log_purge=0,利用定時任務刪除relay_log;




• ②mha進行failover之后會在app1.log中記錄相應的chang master to master_xxx等信息，因此我們可以通過這條信息手動將

• 所宕master

• 連接到新主;




• ③可以使用(masterha_master_switch --master_state=alive --conf=/etc/mha/app1.cnf)進行手動切換主，然后將新主手動添加作為老主的從(步驟2)，注意mha manager必須處于關閉狀態(masterha_check_status --conf=xxx);




• ④我們可以在app1.conf配置主庫宕機后vip的飄逸腳本(

• master_ip_failover_script=/usr/bin/masterha_ip_failover

• )，但是第一次必須得自己手動在master添加vip.






• 附 masterha_ip_failover腳本





點擊(此處)折疊或打開

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. 
    

27. my (
    

28.     $command, $ssh_user, $orig_master_host, $orig_master_ip,
    

29.     $orig_master_port, $new_master_host, $new_master_ip, $new_master_port
    

30. );
    

31. 
    

32. my $vip = '192.168.1.111/24';
    

33. my $key = '0';
    

34. my $ssh_start_vip = "sudo /sbin/ifconfig eth2:$key $vip";
    

35. my $ssh_stop_vip = "sudo /sbin/ifconfig eth2:$key down";
    

36. #my $ssh_Bcast_arp = "/usr/bin/arping -c 3 -A 10.1.99.233"; #ARP回復模式，更新鄰居。要是不加則服務器會自動等到vip緩存失效，期間VIP會有一定時間的不可用。
    

37. 
    

38. GetOptions(
    

39.     'command=s' => \$command,
    

40.     'ssh_user=s' => \$ssh_user,
    

41.     'orig_master_host=s' => \$orig_master_host,
    

42.     'orig_master_ip=s' => \$orig_master_ip,
    

43.     'orig_master_port=i' => \$orig_master_port,
    

44.     'new_master_host=s' => \$new_master_host,
    

45.     'new_master_ip=s' => \$new_master_ip,
    

46.     'new_master_port=i' => \$new_master_port,
    

47. );
    

48. 
    

49. exit &main();
    

50. 
    

51. sub main {
    

52. 
    

53.     print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
    

54. 
    

55.     if ( $command eq "stop" || $command eq "stopssh" ) {
    

56. 
    

57.         my $exit_code = 1;
    

58.         eval {
    

59.             print "Disabling the VIP on old master: $orig_master_host \n";
    

60.             &stop_vip();
    

61.             $exit_code = 0;
    

62.         };
    

63.         if ($@) {
    

64.             warn "Got Error: $@\n";
    

65.             exit $exit_code;
    

66.         }
    

67.         exit $exit_code;
    

68.     }
    

69.     elsif ( $command eq "start" ) {
    

70. 
    

71.         my $exit_code = 10;
    

72.         eval {
    

73.             print "Enabling the VIP - $vip on the new master - $new_master_host \n";
    

74.             &start_vip();
    

75.            # &start_arp();
    

76.             $exit_code = 0;
    

77.         };
    

78.         if ($@) {
    

79.             warn $@;
    

80.             exit $exit_code;
    

81.         }
    

82.         exit $exit_code;
    

83.     }
    

84.     elsif ( $command eq "status" ) {
    

85.         print "Checking the Status of the script.. OK \n";
    

86.         exit 0;
    

87.     }
    

88. 
    

89.     else {
    

90.         &usage();
    

91.         exit 1;
    

92.     }
    

93. }
    

94. 
    

95. sub start_vip() {
    

96.     `ssh -p56000 $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
    

97. }
    

98. #sub start_arp() {
    

99. #    `ssh $ssh_user\@$new_master_host \" $ssh_Bcast_arp \"`;
    

100. #}
     

101. sub stop_vip() {
     

102.     `ssh -p56000 $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
     

103. }
     

104. 
     

105. sub usage {
     

106.   print
     

107. "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";

108. }

以上是“mysql中MHA如何搭建”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！