mha搭建及注意事项
简介：
创新互联建站服务项目包括花溪网站建设、花溪网站制作、花溪网页制作以及花溪网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，花溪网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到花溪省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！
MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司youshimaton（现就职于Facebook公司）开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。
该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。
在MHA自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过ssh访问，MHA没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用MySQL 5.5的半同步复制，可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。
目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库，因为至少需要三台服务器，出于机器成本的考虑，淘宝也在该基础上进行了改造，目前淘宝TMHA已经支持一主一从。另外对于想快速搭建的可以参考：MHA快速搭建
我们自己使用其实也可以使用1主1从，但是master主机宕机后无法切换，以及无法补全binlog。master的mysqld进程crash后，还是可以切换成功，以及补全binlog的。
官方介绍：https://code.google.com/p/mysql-master-ha/
图01展示了如何通过MHA Manager管理多组主从复制。可以将MHA工作原理总结为如下：
（ 图01 ）
（1）从宕机崩溃的master保存二进制日志事件（binlog events）;
（2）识别含有最新更新的slave；
（3）应用差异的中继日志（relay log）到其他的slave；
（4）应用从master保存的二进制日志事件（binlog events）；
（5）提升一个slave为新的master；
（6）使其他的slave连接新的master进行复制；
MHA软件由两部分组成，Manager工具包和Node工具包，具体的说明如下。
Manager工具包主要包括以下几个工具：
masterha_check_ssh 检查MHA的SSH配置状况 masterha_check_repl 检查MySQL复制状况 masterha_manger 启动MHA masterha_check_status 检测当前MHA运行状态 masterha_master_monitor 检测master是否宕机 masterha_master_switch 控制故障转移（自动或者手动） masterha_conf_host 添加或删除配置的server信息
ode工具包（这些工具通常由MHA Manager的脚本触发，无需人为操作）主要包括以下几个工具：
save_binary_logs 保存和复制master的二进制日志 apply_diff_relay_logs 识别差异的中继日志事件并将其差异的事件应用于其他的slavefilter_mysqlbinlog 去除不必要的ROLLBACK事件（MHA已不再使用这个工具） purge_relay_logs 清除中继日志（不会阻塞SQL线程）
环境：
系统：CentOS Linux release 7.3.1611 (Core)
mysql :5.7.15-log
mha: mha4mysql-manager-0.57.tar.gz mha4mysql-node-0.57.tar.gz
角色 ip地址 主机名 server_id 类型 mha mangager 170.17.0.6 server01 - 监控复制组 Master 170.17.0.5 server02 1 写入 Candicate master 170.17.0.4 server03 2 读 Candicate master 170.17.0.3 server03 3 读 Slave 170.17.0.2 server05 4 读
node 组件 [root@53a15bac5d70 bin]# ll total 44 -rwxr-xr-x 1 1001 1001 16381 May 31 2015 apply_diff_relay_logs -rwxr-xr-x 1 1001 1001 4807 May 31 2015 filter_mysqlbinlog -rwxr-xr-x 1 1001 1001 8261 May 31 2015 purge_relay_logs -rwxr-xr-x 1 1001 1001 7525 May 31 2015 save_binary_logs [root@53a15bac5d70 bin]#
manger节点
|-- bin
| |-- masterha_check_repl
| |-- masterha_check_ssh
| |-- masterha_check_status
| |-- masterha_conf_host
| |-- masterha_manager
| |-- masterha_master_monitor
| |-- masterha_master_switch
| |-- masterha_secondary_check
| `-- masterha_stop
/soft/mha4mysql-manager-0.57/samples/scripts
|-- master_ip_failover （masterha_master_swith --master_state=dead 主机down掉，3306端口完蛋，将启用此脚本，原始脚本不带vip自动切换，如果主机不可达，ssh不可用，就用原始的，反之，就用自己添加的vip切换脚本）
|-- master_ip_online_change （主机可用，3306还可以使用对应 masterha_master_swith --master_state=alive）
|-- power_manager
`-- send_report
masterha_master_switch --master_state=dead
--global_conf=/etc/masterha_default.cnf
--conf=/usr/local/masterha/conf/app1.cnf --dead_master_host=host1
调用 master_ip_failover
# For online master switch
masterha_master_switch --master_state=alive
--global_conf=/etc/masterha_default.cnf
--conf=/usr/local/masterha/conf/app1.cnf
调用 master_ip_online_change
See online reference
(http://code.google.com/p/mysql-master-ha/wiki/masterha_master_switch)
其中script中有简单的配置文件及切换，发送邮件等脚本
vim /etc/masterha/app01.conf
[server default] ####save binlog failover flag and so on manager_workdir=/var/log/masterha/app1 manager_log=/var/log/masterha/app1/manager.log user=root password=123456 ssh_user=root ## path of master binlog master_binlog_dir= /data/binlog/,/var/lib/mysql,/var/log/mysql # scp the diff binlog to slave's path and save it remote_workdir=/tmp ping_interval=3 # mha will send ping packet to master per interval ,no ping will failover #ping_type='select' #shutdown_script= /script/masterha/power_manager repl_user=repl repl_password=repl # failover happen ,send email to administrator report_script=/etc/masterha/script/send_report secondary_check_script= /usr/local/bin/masterha_secondary_check -s 172.17.0.2 -s 172.17.0.3 -s 172.17.0.4 -s 172.17.0.5 # master is down failover 主机挂掉（分两种情况，ssh可以连接，ssh不可连接，选择性配置脚本，根据实际情况更改下面配置） ####################################################################################### #master_ip_failover_script= /etc/masterha/script/master_ip_failover_vip # master can reachable and down vip ssh可以连接 ，可以脚本关掉vip ####################################################################################### ####################################################################################### master_ip_failover_script= /etc/masterha/script/master_ip_failover # master can not reachable and not down vip ssh不可用，无法脚本关掉vip ####################################################################################### # master online, mysql is alive and ssh is can reachable mysql 可用（分两种情况 ssh可达，ssh不可达，选择性配置脚本，根据实际情况更改下面配置） ####################################################################################### # master can reachable and down vip #master_ip_online_change_script= /etc/masterha/script/master_ip_online_change_vip ####################################################################################### ####################################################################################### # master can not reachable and can't down vip master_ip_online_change_script= /etc/masterha/script/master_ip_online_change ####################################################################################### #[server1] #hostname=172.17.0.5 #candidate_master=1 #check_repl_delay=0 [server2] hostname=172.17.0.4 candidate_master=1 check_repl_delay=0 [server3] hostname=172.17.0.3 #candidate_master=1 [server4] hostname=172.17.0.2 no_master=1 |
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; use MHA::DBHelper; my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port, $new_master_user, $new_master_password ); 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, 'new_master_user=s' => \$new_master_user, 'new_master_password=s' => \$new_master_password, ); exit &main(); sub main { 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 { # updating global catalog, etc $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 { 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 "Set read_only=0 on the new master.\n"; $new_master_handler->disable_read_only(); ## Creating an app user on the new master print "Creating app user on the new master..\n"; #FIXME_xxx_create_user( $new_master_handler->{dbh} ); $new_master_handler->enable_log_bin_local(); $new_master_handler->disconnect(); ## Update master ip on the catalog database, etc # FIXME_xxx; $exit_code = 0; }; if ($@) { warn $@; # If you want to continue failover, exit 10. exit $exit_code; } exit $exit_code; } elsif ( $command eq "status" ) { # do nothing exit 0; } else { &usage(); exit 1; } } sub usage { "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"; } |
cat master_ip_failover_vip
#!/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; use MHA::DBHelper; my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port, $new_master_user, $new_master_password ); my $vip = '172.17.0.100/24'; my $key = '1'; my $ssh_start_vip = "/usr/sbin/ifconfig eth0:$key $vip"; my $ssh_stop_vip = "/usr/sbin/ifconfig eth0:$key down"; 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, 'new_master_user=s' => \$new_master_user, 'new_master_password=s' => \$new_master_password, ); exit &main(); sub main {
print "\n\nIN SCRIPT TEST====$ssh_start_vip==$ssh_stop_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 "Disabling the vip on old master:$orig_master_host \n"; &stop_vip(); # updating global catalog, etc $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 "Enabling the VIP -$vip on the new master - $new_master_host \n"; &start_vip();
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 "Set read_only=0 on the new master.\n"; $new_master_handler->disable_read_only(); ## Creating an app user on the new master #print "Creating app user on the new master..\n"; #FIXME_xxx_create_user( $new_master_handler->{dbh} ); $new_master_handler->enable_log_bin_local(); $new_master_handler->disconnect(); ## Update master ip on the catalog database, etc #FIXME_xxx; $exit_code = 0; }; if ($@) { warn $@; # If you want to continue failover, exit 10. 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 \"`; # do nothing exit 0; } else { &usage(); exit 1; } } sub start_vip(){ `ssh $ssh_user\@$new_master_host \ "$ssh_start_vip \"`; } sub stop_vip(){ `ssh $ssh_user\@$orig_master_host \ "$ssh_stop_vip \"`; } sub usage { "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"; } |
cat master_ip_online_change（原版稍作改动FIXME去掉）
#!/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; 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, ); 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} ); } ## 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 $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; } } sub usage { "Usage: master_ip_online_change --command=start|stop|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"; die; } |
cat master_ip_online_change_vip
#!/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; 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 = '172.17.0.100'; my $key = "1"; my $ssh_start_vip = "/usr/sbin/ifconfig eth0:$key $vip"; my $ssh_stop_vip = "/usr/sbin/ifconfig eth0:$key down"; ########################################################## 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 "Disable 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 "Enable the VIP: $vip on the new master host: $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; } } sub stop_vip { `ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_stop_vip \"`; } sub start_vip { `ssh $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`; } sub usage { "Usage: master_ip_online_change --command=start|stop|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"; die; } |
故障处理：
masterha_master_switch --conf=/etc/masterha/app1.cnf --dead_master_host=172.17.0.3 --master_state=dead
Thu Sep 7 14:19:58 2017 - [warning] SQL Thread is stopped(no error) on 172.17.0.5(172.17.0.5:3306) Thu Sep 7 14:19:58 2017 - [info] GTID failover mode = 0 Thu Sep 7 14:19:58 2017 - [info] Dead Servers: Thu Sep 7 14:19:58 2017 - [info] 172.17.0.4(172.17.0.4:3306) Thu Sep 7 14:19:58 2017 - [info] 172.17.0.3(172.17.0.3:3306) Thu Sep 7 14:19:58 2017 - [info] Checking master reachability via MySQL(double check)... Thu Sep 7 14:19:58 2017 - [info] ok. Thu Sep 7 14:19:58 2017 - [info] Alive Servers: Thu Sep 7 14:19:58 2017 - [info] 172.17.0.5(172.17.0.5:3306) Thu Sep 7 14:19:58 2017 - [info] 172.17.0.2(172.17.0.2:3306) Thu Sep 7 14:19:58 2017 - [info] Alive Slaves: Thu Sep 7 14:19:58 2017 - [info] 172.17.0.5(172.17.0.5:3306) Version=5.7.15-log (oldest major version between slaves) log-bin:enabled Thu Sep 7 14:19:58 2017 - [info] Replicating from 172.17.0.3(172.17.0.3:3306) Thu Sep 7 14:19:58 2017 - [info] Primary candidate for the new Master (candidate_master is set) Thu Sep 7 14:19:58 2017 - [info] 172.17.0.2(172.17.0.2:3306) Version=5.7.15-log (oldest major version between slaves) log-bin:enabled Thu Sep 7 14:19:58 2017 - [info] Replicating from 172.17.0.3(172.17.0.3:3306) Thu Sep 7 14:19:58 2017 - [info] Not candidate for the new Master (no_master is set) Thu Sep 7 14:19:58 2017 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln492] Server 172.17.0.4(172.17.0.4:3306) is dead, but must be alive! Check server settings. Thu Sep 7 14:19:58 2017 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR: at /usr/local/share/perl5/MHA/MasterFailover.pm line 268. |
这是由于配置文件中配置了172.17.0.4，而此服务器mysql当掉了，修改配置文件，将其屏蔽
[server1]
hostname=172.17.0.5
candidate_master=1
check_repl_delay=0
#[server2]
#hostname=172.17.0.4
#candidate_master=1
#check_repl_delay=0
[server3]
hostname=172.17.0.3
#candidate_master=1
[server4]
hostname=172.17.0.2
no_master=1
手动切换：
masterha_master_switch --conf=/etc/masterha/app1.cnf --dead_master_host=172.17.0.3 --master_state=dead
[root@f8dc93c1f02f script]# masterha_master_switch --conf=/etc/masterha/app1.cnf --dead_master_host=172.17.0.3 --master_state=dead --dead_master_ip= --dead_master_port= Thu Sep 7 14:22:41 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu Sep 7 14:22:41 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Thu Sep 7 14:22:41 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Thu Sep 7 14:22:41 2017 - [info] MHA::MasterFailover version 0.57. Thu Sep 7 14:22:41 2017 - [info] Starting master failover. Thu Sep 7 14:22:41 2017 - [info] Thu Sep 7 14:22:41 2017 - [info] * Phase 1: Configuration Check Phase.. Thu Sep 7 14:22:41 2017 - [info] Thu Sep 7 14:22:42 2017 - [warning] SQL Thread is stopped(no error) on 172.17.0.5(172.17.0.5:3306) Thu Sep 7 14:22:42 2017 - [info] GTID failover mode = 0 Thu Sep 7 14:22:42 2017 - [info] Dead Servers: Thu Sep 7 14:22:42 2017 - [info] 172.17.0.3(172.17.0.3:3306) Thu Sep 7 14:22:42 2017 - [info] Checking master reachability via MySQL(double check)... Thu Sep 7 14:22:42 2017 - [info] ok. Thu Sep 7 14:22:42 2017 - [info] Alive Servers: Thu Sep 7 14:22:42 2017 - [info] 172.17.0.5(172.17.0.5:3306) Thu Sep 7 14:22:42 2017 - [info] 172.17.0.2(172.17.0.2:3306) Thu Sep 7 14:22:42 2017 - [info] Alive Slaves: Thu Sep 7 14:22:42 2017 - [info] 172.17.0.5(172.17.0.5:3306) Version=5.7.15-log (oldest major version between slaves) log-bin:enabled Thu Sep 7 14:22:42 2017 - [info] Replicating from 172.17.0.3(172.17.0.3:3306) Thu Sep 7 14:22:42 2017 - [info] Primary candidate for the new Master (candidate_master is set) Thu Sep 7 14:22:42 2017 - [info] 172.17.0.2(172.17.0.2:3306) Version=5.7.15-log (oldest major version between slaves) log-bin:enabled Thu Sep 7 14:22:42 2017 - [info] Replicating from 172.17.0.3(172.17.0.3:3306) Thu Sep 7 14:22:42 2017 - [info] Not candidate for the new Master (no_master is set) Master 172.17.0.3(172.17.0.3:3306) is dead. Proceed? (yes/NO): yes Thu Sep 7 14:22:51 2017 - [error][/usr/local/share/perl5/MHA/MasterFailover.pm, ln309] Last failover was done at 2017/09/07 12:08:18. Current time is too early to do failover again. If you want to do failover, manually remove /var/log/masterha/app1/app1.failover.complete and run this script again. Thu Sep 7 14:22:51 2017 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR: at /usr/local/bin/masterha_master_switch line 53. [root@f8dc93c1f02f script]# |
错误提示，之前做过failover，并留了粑粑
remove /var/log/masterha/app1/app1.failover.complete
Thu Sep 7 14:27:45 2017 - [info] MHA::MasterFailover version 0.57. Thu Sep 7 14:27:45 2017 - [info] Starting master failover. 文章题目：mha搭建及注意事项 浏览地址：http://cdiso.cn/article/gphdig.html 其他资讯 |
