千家信息网

mysql中MHA如何搭建

发表于:2024-10-09 作者:千家信息网编辑
千家信息网最后更新 2024年10月09日,这篇文章主要为大家展示了"mysql中MHA如何搭建",内容简而易懂,条理清晰,希望能够帮助大家解决疑惑,下面让小编带领大家一起研究并学习一下"mysql中MHA如何搭建"这篇文章吧。操作系统 :de
千家信息网最后更新 2024年10月09日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. # Copyright (C) 2011 DeNA Co.,Ltd.

    3. #

    4. # This program is free software; you can redistribute it and/or modify

    5. # it under the terms of the GNU General Public License as published by

    6. # the Free Software Foundation; either version 2 of the License, or

    7. # (at your option) any later version.

    8. #

    9. # This program is distributed in the hope that it will be useful,

    10. # but WITHOUT ANY WARRANTY; without even the implied warranty of

    11. # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

    12. # GNU General Public License for more details.

    13. #

    14. # You should have received a copy of the GNU General Public License

    15. # along with this program; if not, write to the Free Software

    16. # Foundation, Inc.,

    17. # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA


    18. ## Note: This is a sample script and is not complete. Modify the script based on your environment.


    19. use strict;

    20. use warnings FATAL => 'all';


    21. use Getopt::Long;


    22. my (

    23. $command, $ssh_user, $orig_master_host, $orig_master_ip,

    24. $orig_master_port, $new_master_host, $new_master_ip, $new_master_port

    25. );


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

    27. my $key = '0';

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

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

    30. #my $ssh_Bcast_arp = "/usr/bin/arping -c 3 -A 10.1.99.233"; #ARP回复模式,更新邻居。要是不加则服务器会自动等到vip缓存失效,期间VIP会有一定时间的不可用。


    31. GetOptions(

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

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

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

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

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

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

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

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

    40. );


    41. exit &main();


    42. sub main {


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


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


    45. my $exit_code = 1;

    46. eval {

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

    48. &stop_vip();

    49. $exit_code = 0;

    50. };

    51. if ($@) {

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

    53. exit $exit_code;

    54. }

    55. exit $exit_code;

    56. }

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


    58. my $exit_code = 10;

    59. eval {

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

    61. &start_vip();

    62. # &start_arp();

    63. $exit_code = 0;

    64. };

    65. if ($@) {

    66. warn $@;

    67. exit $exit_code;

    68. }

    69. exit $exit_code;

    70. }

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

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

    73. exit 0;

    74. }


    75. else {

    76. &usage();

    77. exit 1;

    78. }

    79. }


    80. sub start_vip() {

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

    82. }

    83. #sub start_arp() {

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

    85. #}

    86. sub stop_vip() {

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

    88. }


    89. sub usage {

    90. print

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

    92. }

以上是"mysql中MHA如何搭建"这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注行业资讯频道!

0