mysql 高可用集群HMA部署

大家好，我是讯享网，很高兴认识大家。

环境说明：（据说manaer可以安装在slave节点，有兴趣你可以试一下

10.254.40.123	manager
10.254.40.92	master
10.254.40.91	备master
10.254.40.93	slave

配置互信免密登录，过程略

/etc/hosts

安装mysql5.7，三台都装

下载 mysql80-community-release-el7-3.noarch.rpm 地址https://dev.mysql.com/downloads/repo/yum/
rpm -ivh mysql80-community-release-el7-3.noarch.rpm
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community
yum install mysql-community-server

配置mysql

配置master mysql

 grep "password" /var/log/mysqld.log 新装mysql的密码在这里，进去自己修改密码，已经操作的忽略 mysql -uroot -p ALTER USER 'root'@'localhost' IDENTIFIED BY ''; 创建两个用户第一个用来传数据。第二个用来监控 mysql -uroot -p GRANT replication slave ON *.* TO 'slave'@'10.254.%.%' IDENTIFIED BY 'Abc+'; grant all privileges on *.* to 'root'@'10.254.%.%' identified by 'Abc+'; flush privileges; exit vim /etc/my.cnf server-id=1 log-bin=mysql-bin-master systemctl restart mysqld

讯享网

配置备master mysql

讯享网 grep "password" /var/log/mysqld.log 新装mysql的密码在这里，进去自己修改密码，已经操作的忽略 mysql -uroot -p ALTER USER 'root'@'localhost' IDENTIFIED BY ''; 创建两个用户第一个用来传数据。第二个用来监控 mysql -uroot -p GRANT replication slave ON *.* TO 'slave'@'10.254.%.%' IDENTIFIED BY 'Abc+'; grant all privileges on *.* to 'root'@'10.254.%.%' identified by 'Abc+'; flush privileges; exit Vi /etc/my.cnf server-id=2 log-bin=mysql-bin-slave1 log-slave-updates=1 systemctl restart mysqld mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> change master to master_host='10.254.40.92', master_user='slave', master_password='Abc+', master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G mysql> set global read_only=1; Query OK, 0 rows affected (0.00 sec) mysql> set global relay_log_purge=off; Query OK, 0 rows affected (0.00 sec)

配置 slave mysql

 grep "password" /var/log/mysqld.log 新装mysql的密码在这里，进去自己修改密码，已经操作的忽略 mysql -uroot -p ALTER USER 'root'@'localhost' IDENTIFIED BY ''; 创建两个用户第一个用来传数据。第二个用来监控 mysql -uroot -p grant all privileges on *.* to 'root'@'10.254.%.%' identified by 'Abc+'; flush privileges; exit vim /etc/my.cnf server-id=3 systemctl restart mysqld mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> change master to master_host='10.254.40.92' -> master_user='slave', -> ^C mysql> ^C mysql> ^C mysql> change master to master_host='10.254.40.92', -> master_user='slave', -> master_password='Abc+', -> master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G mysql> set global read_only=1; Query OK, 0 rows affected (0.00 sec) mysql> set global relay_log_purge=off; Query OK, 0 rows affected (0.00 sec)

安装perl环境

讯享网 rpm -Uvh http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm yum -y install perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-devel perl-Module-Install.noarch yum install perl-DBD-MySQL -y

下载MHA源码包，你也可以找找rpm包，应该安装更容易

 https://github.com/yoshinorim/mha4mysql-node https://github.com/yoshinorim/mha4mysql-manager

在所有节点安装node包

讯享网Unzip *.zip cd mha4mysql-node perl Makefile.PL && make && make install

在manage节点安装manager并配置

Unzip *.zip # cd ~/mha4mysql-node/ perl Makefile.PL && make && make install

从样例中复制配置文件

讯享网mkdir -p /etc/masterha/scripts # 复制 cp samples/conf/app1.cnf /etc/masterha/ cp samples/conf/masterha_default.cnf /etc/masterha_default.cnf # 复制failover相关脚本 cp samples/scripts/* /usr/local/bin/

配置配置文件

[root@repository bin]# cat /etc/masterha/app1.cnf [server default] manager_workdir=/var/log/masterha/app1 manager_log=/var/log/masterha/app1/manager.log master_binlog_dir=/var/lib/mysql #设置master 保存binlog的位置，以便MHA可以找到master的日志，我这里的也就是mysql的数据目录 #master_ip_failover_script= /etc/masterha/scripts/master_ip_failover #设置自动failover时候的切换脚本 master_ip_failover_script= /usr/local/bin/master_ip_failover #设置自动failover时候的切换脚本 master_ip_online_change_script=/usr/local/bin/master_ip_online_change #设置手动切换时候的切换脚本 password=Abc+ #设置mysql中root用户的密码，这个密码是前文中创建监控用户的那个密码 user=root #设置监控用户root ping_interval=1 #设置监控主库，发送ping包的时间间隔，默认是3秒,尝试三次没有回应的时候自动进行railover remote_workdir=/data #设置远端mysql在发生切换时binlog的保存位置 repl_password=Abc+ #设置复制用户的密码 repl_user=slave #设置复制环境中的复制用户名 report_script=/usr/local/bin/send_report #设置发生切换后发送的报警的脚本 #secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02 --user=root --master_host=server02 --master_ip=10.254.40.136 --master_port=3306 secondary_check_script= /usr/local/bin/masterha_secondary_check -s 10.254.40.93 -s 10.254.40.91 --user=root --master_host=10.254.40.92 --master_ip=10.254.40.92 --master_port=3306 -s为非master #一旦MHA到server02的监控之间出现问题，MHA Manager将会尝试从server03登录到server02 shutdown_script="" #设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机放在发生脑裂,这里没有使用） ssh_user=root #设置ssh的登录用户名 [server1] hostname=10.254.40.92 candidate_master=1 [server2] hostname=10.254.40.91 candidate_master=1 check_repl_delay=0 [server3] hostname=10.254.40.93

需要覆盖默认配置文件，在这坑了大半天

讯享网cat app1.cnf > /etc/masterha_default.cnf

测试SSH连接

masterha_check_ssh --conf=/etc/masterha/app1.cnf

配置vip

vip配置可以采用两种方式，一种通过keepalived的方式管理虚拟ip的浮动；另外一种通过脚本方式启动虚拟ip的方式 (即不需要keepalived或者heartbeat类似的软件).

讯享网

本次采用：脚本方式启动虚拟ip

讯享网[root@repository bin]# pwd /usr/local/bin [root@repository bin]# [root@repository bin]# [root@repository bin]# cat master_ip_failover #!/usr/bin/env perl # Copyright (C) 2011 DeNA Co.,Ltd. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA Note: This is a sample script and is not complete. Modify the script based on your environment. use 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 = '10.254.40.136/24'; # Virtual IP my $key = "1"; my $ssh_start_vip = "/sbin/ifconfig ens32:$key $vip"; # Network name my $ssh_arp = "/usr/sbin/arping -q -A -c 1 -I ens32 10.254.40.136"; my $ssh_stop_vip = "/sbin/ifconfig ens32:$key down"; my $exit_code = 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 "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n"; if ( $command eq "stop" || $command eq "stopssh" ) { # $orig_master_host, $orig_master_ip, $orig_master_port are passed. # If you manage master ip address at global catalog database, # invalidate orig_master_ip here. my $exit_code = 1; eval { print "\n\n\n*\n"; print "Disabling the VIP - $vip on old master: $orig_master_host\n"; print "*\n\n\n\n"; &stop_vip(); $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) { # all arguments are passed. # If you manage master ip address at global catalog database, # activate new_master_ip here. # You can also grant write access (create user, set read_only=0, etc) here. my $exit_code = 10; eval { print "\n\n\n*\n"; print "Enabling the VIP - $vip on new master: $new_master_host \n"; print "*\n\n\n\n"; &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 \n"; `ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`; exit 0; } else { &usage(); exit 1; } } # A simple system call that enable the VIP on the new master sub start_vip() { `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; `ssh $ssh_user\@$new_master_host \" $ssh_arp \"`; } # A simple system call that disable the VIP on the old_master sub stop_vip() { `ssh $root\@$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=po rt –new_master_host=host –new_master_ip=ip –new_master_port=port\n"; } [root@repository bin]#

检查复制环境

masterha_check_repl --conf=/etc/masterha/app1.cnf

开启管理节点监控

讯享网/*--remove_dead_master_conf表示发生主从切换后，老的master服务器配置检查配置文件中删除 *--ignore_last_failover在缺省情况下，如果MHA检测到连续宕机，切间隔不足8小时，则不会进行故障切换，避免ping-pong效应。设置该参数表示忽略上次切换 *--manger_log日志存放文职*/ nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 & [1] 9562 检查命令 masterha_check_status --conf=/etc/masterha/app1.cnf 关闭命令 masterha_stop --conf=/etc/masterha/app1.cnf

参考

1. 主动切换：

需要修改 master_ip_online_change

#!/usr/bin/env perl use strict; use warnings FATAL => 'all'; use Getopt::Long; use MHA::DBHelper; use MHA::NodeUtil; use Time::HiRes qw( sleep gettimeofday tv_interval ); use Data::Dumper; my $_tstart; my $_running_interval = 0.1; my ( $command, $orig_master_is_new_slave, $orig_master_host, $orig_master_ip, $orig_master_port, $orig_master_user, $orig_master_password, $orig_master_ssh_user, $new_master_host, $new_master_ip, $new_master_port, $new_master_user, $new_master_password, $new_master_ssh_user, ); my $vip = '10.254.40.136'; my $brdc = '10.254.40.255'; my $ifdev = 'ens32'; my $key = '1'; my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;"; my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key"; GetOptions( 'command=s' => \$command, 'orig_master_is_new_slave' => \$orig_master_is_new_slave, 'orig_master_host=s' => \$orig_master_host, 'orig_master_ip=s' => \$orig_master_ip, 'orig_master_port=i' => \$orig_master_port, 'orig_master_user=s' => \$orig_master_user, 'orig_master_password=s' => \$orig_master_password, 'orig_master_ssh_user=s' => \$orig_master_ssh_user, 'new_master_host=s' => \$new_master_host, 'new_master_ip=s' => \$new_master_ip, 'new_master_port=i' => \$new_master_port, 'new_master_user=s' => \$new_master_user, 'new_master_password=s' => \$new_master_password, 'new_master_ssh_user=s' => \$new_master_ssh_user, ); exit &main(); sub current_time_us { my ( $sec, $microsec ) = gettimeofday(); my $curdate = localtime($sec); return $curdate . " " . sprintf( "%06d", $microsec ); } sub sleep_until { my $elapsed = tv_interval($_tstart); if ( $_running_interval > $elapsed ) { sleep( $_running_interval - $elapsed ); } } sub get_threads_util { my $dbh = shift; my $my_connection_id = shift; my $running_time_threshold = shift; my $type = shift; $running_time_threshold = 0 unless ($running_time_threshold); $type = 0 unless ($type); my @threads; my $sth = $dbh->prepare("SHOW PROCESSLIST"); $sth->execute(); while ( my $ref = $sth->fetchrow_hashref() ) { my $id = $ref->{Id}; my $user = $ref->{User}; my $host = $ref->{Host}; my $command = $ref->{Command}; my $state = $ref->{State}; my $query_time = $ref->{Time}; my $info = $ref->{Info}; $info =~ s/^\s*(.*?)\s*$/$1/ if defined($info); next if ( $my_connection_id == $id ); next if ( defined($query_time) && $query_time < $running_time_threshold ); next if ( defined($command) && $command eq "Binlog Dump" ); next if ( defined($user) && $user eq "system user" ); next if ( defined($command) && $command eq "Sleep" && defined($query_time) && $query_time >= 1 ); if ( $type >= 1 ) { next if ( defined($command) && $command eq "Sleep" ); next if ( defined($command) && $command eq "Connect" ); } if ( $type >= 2 ) { next if ( defined($info) && $info =~ m/^select/i ); next if ( defined($info) && $info =~ m/^show/i ); } push @threads, $ref; } return @threads; } sub main { if ( $command eq "stop" ) { Gracefully killing connections on the current master # 1. Set read_only= 1 on the new master # 2. DROP USER so that no app user can establish new connections # 3. Set read_only= 1 on the current master # 4. Kill current queries # * Any database access failure will result in script die. my $exit_code = 1; eval { Setting read_only=1 on the new master (to avoid accident) my $new_master_handler = new MHA::DBHelper(); # args: hostname, port, user, password, raise_error(die_on_error)_or_not $new_master_handler->connect( $new_master_ip, $new_master_port, $new_master_user, $new_master_password, 1 ); print current_time_us() . " Set read_only on the new master.. "; $new_master_handler->enable_read_only(); if ( $new_master_handler->is_read_only() ) { print "ok.\n"; } else { die "Failed!\n"; } $new_master_handler->disconnect(); # Connecting to the orig master, die if any database error happens my $orig_master_handler = new MHA::DBHelper(); $orig_master_handler->connect( $orig_master_ip, $orig_master_port, $orig_master_user, $orig_master_password, 1 ); Drop application user so that nobody can connect. Disabling per-session binlog beforehand #$orig_master_handler->disable_log_bin_local(); #print current_time_us() . " Drpping app user on the orig master..\n"; #FIXME_xxx_drop_app_user($orig_master_handler); Waiting for N * 100 milliseconds so that current connections can exit my $time_until_read_only = 15; $_tstart = [gettimeofday]; my @threads = get_threads_util( $orig_master_handler->{dbh}, $orig_master_handler->{connection_id} ); while ( $time_until_read_only > 0 && $#threads >= 0 ) { if ( $time_until_read_only % 5 == 0 ) { printf "%s Waiting all running %d threads are disconnected.. (max %d milliseconds)\n", current_time_us(), $#threads + 1, $time_until_read_only * 100; if ( $#threads < 5 ) { print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n" foreach (@threads); } } sleep_until(); $_tstart = [gettimeofday]; $time_until_read_only--; @threads = get_threads_util( $orig_master_handler->{dbh}, $orig_master_handler->{connection_id} ); } Setting read_only=1 on the current master so that nobody(except SUPER) can write print current_time_us() . " Set read_only=1 on the orig master.. "; $orig_master_handler->enable_read_only(); if ( $orig_master_handler->is_read_only() ) { print "ok.\n"; } else { die "Failed!\n"; } Waiting for M * 100 milliseconds so that current update queries can complete my $time_until_kill_threads = 5; @threads = get_threads_util( $orig_master_handler->{dbh}, $orig_master_handler->{connection_id} ); while ( $time_until_kill_threads > 0 && $#threads >= 0 ) { if ( $time_until_kill_threads % 5 == 0 ) { printf "%s Waiting all running %d queries are disconnected.. (max %d milliseconds)\n", current_time_us(), $#threads + 1, $time_until_kill_threads * 100; if ( $#threads < 5 ) { print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n" foreach (@threads); } } sleep_until(); $_tstart = [gettimeofday]; $time_until_kill_threads--; @threads = get_threads_util( $orig_master_handler->{dbh}, $orig_master_handler->{connection_id} ); } print "Disabling the VIP on old master: $orig_master_host \n"; &stop_vip(); Terminating all threads print current_time_us() . " Killing all application threads..\n"; $orig_master_handler->kill_threads(@threads) if ( $#threads >= 0 ); print current_time_us() . " done.\n"; #$orig_master_handler->enable_log_bin_local(); $orig_master_handler->disconnect(); After finishing the script, MHA executes FLUSH TABLES WITH READ LOCK $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) { Activating master ip on the new master # 1. Create app user with write privileges # 2. Moving backup script if needed # 3. Register new master's ip to the catalog database # We don't return error even though activating updatable accounts/ip failed so that we don't interrupt slaves' recovery. # If exit code is 0 or 10, MHA does not abort my $exit_code = 10; eval { my $new_master_handler = new MHA::DBHelper(); # args: hostname, port, user, password, raise_error_or_not $new_master_handler->connect( $new_master_ip, $new_master_port, $new_master_user, $new_master_password, 1 ); Set read_only=0 on the new master #$new_master_handler->disable_log_bin_local(); print current_time_us() . " Set read_only=0 on the new master.\n"; $new_master_handler->disable_read_only(); Creating an app user on the new master #print current_time_us() . " Creating app user on the new master..\n"; #FIXME_xxx_create_app_user($new_master_handler); #$new_master_handler->enable_log_bin_local(); $new_master_handler->disconnect(); Update master ip on the catalog database, etc print "Enabling the VIP - $vip on the new master - $new_master_host \n"; &start_vip(); $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "status" ) { # do nothing exit 0; } else { &usage(); exit 1; } } # A simple system call that enable the VIP on the new master sub start_vip() { `ssh $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`; } # A simple system call that disable the VIP on the old_master sub stop_vip() { `ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_stop_vip \"`; } sub usage { print "Usage: master_ip_online_change --command=start|stop|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --orig_master_user=user --orig_master_password=password --orig_master_ssh_user=sshuser --new_master_host=host --new_master_ip=ip --new_master_port=port --new_master_user=user --new_master_password=password --new_master_ssh_user=sshuser \n"; die; }

手动切换命令

讯享网masterha_master_switch --conf=/etc/masterha/app1.cnf -master_state=alive --new_master_host=10.254.40.91 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

报错：

Wed May 23 11:05:54 2018  Set read_only on the new master.. ok. Wed May 23 11:05:54 2018  Drpping app user on the orig master.. Got Error: Undefined subroutine &main::FIXME_xxx_drop_app_user called at /usr/local/bin/master_ip_online_change line 148. Wed May 23 11:05:54 2018 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln178] Got ERROR: at /usr/local/bin/masterha_master_switch line 53. 解决办法： 在master_ip_online_change 中其中需要注释掉的语句为： FIXME_xxx_drop_app_user($orig_master_handler); FIXME_xxx_create_app_user($new_master_handler); ———————————————— 版权声明：本文为CSDN博主「treemini0」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/u0/article/details/

个人理解

HMA就是基于mysql主从复制，使用一组脚本实现自动判断、切换主库位置与VIP。

参考链接

https://www.cnblogs.com/halberd-lee/p/8616851.html#sec-4

https://www.cnblogs.com/diantong/p/11055256.html