mha 搭建及注意事项

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

简介：

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工作原理总结为如下：

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

# 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

# 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

# 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" );

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

# 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" );

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_ip> is not set. Using 172.17.0.3.

--dead_master_port=<dead_master_port> is not set. Using 3306.

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.

Thu Sep 7 14:27:45 2017 - [info]

Thu Sep 7 14:27:45 2017 - [info] * Phase 1: Configuration Check Phase..

Thu Sep 7 14:27:45 2017 - [info]

Thu Sep 7 14:27:46 2017 - [info] GTID failover mode = 0

Thu Sep 7 14:27:46 2017 - [info] Dead Servers:

Thu Sep 7 14:27:46 2017 - [info] 172.17.0.3(172.17.0.3:3306)

Thu Sep 7 14:27:46 2017 - [info] Checking master reachability via MySQL(double check)...

Thu Sep 7 14:27:46 2017 - [info] ok.

Thu Sep 7 14:27:46 2017 - [info] Alive Servers:

Thu Sep 7 14:27:46 2017 - [info] 172.17.0.5(172.17.0.5:3306)

Thu Sep 7 14:27:46 2017 - [info] 172.17.0.2(172.17.0.2:3306)

Thu Sep 7 14:27:46 2017 - [info] Alive Slaves:

Thu Sep 7 14:27:46 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:27:46 2017 - [info] Replicating from 172.17.0.3(172.17.0.3:3306)

Thu Sep 7 14:27:46 2017 - [info] Primary candidate for the new Master (candidate_master is set)

Thu Sep 7 14:27:46 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:27:47 2017 - [info] Replicating from 172.17.0.3(172.17.0.3:3306)

Thu Sep 7 14:27:47 2017 - [info] Not candidate for the new Master (no_master is set)

Thu Sep 7 14:27:47 2017 - [error][/usr/local/share/perl5/MHA/MasterFailover.pm, ln281] Failover error flag file /var/log/masterha/app1/app1.failover.error exists. This means the last failover failed. Check error logs for detail, fix problems, remove /var/log/masterha/app1/app1.failover.error, and restart this script.

Thu Sep 7 14:27:47 2017 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR: at /usr/local/bin/masterha_master_switch line 53.

[root@f8dc93c1f02f script]#

错误提示：上次切换失败，清除/var/log/masterha/app1/app1.failover.error

定期清理relay log

--user mysql 用户名

--password mysql 密码

--port 端口号

--workdir 指定创建relay log的硬链接的位置，默认是/var/tmp，由于系统不同分区创建硬链接文件会失败，故需要执行硬链接具体位置，成功执行脚本后，硬链接的中继日志文件被删除

--disable_relay_log_purge 默认情况下，如果relay_log_purge=1，脚本会什么都不清理，自动退出，通过设定这个参数，当relay_log_purge=1的情况下会将relay_log_purge设置为0。清理relay log之后，最后将参数设置为OFF。

cat purge_relay_log.sh

#!/bin/bash

user=root

passwd=123456

port=3306

log_dir='/data/masterha/log'

work_dir='/data'

purge='/usr/local/bin/purge_relay_logs'

if [ ! -d $log_dir ]

then

mkdir $log_dir -p

$purge --user=$user --password=$passwd --disable_relay_log_purge --port=$port --workdir=$work_dir >> $log_dir/purge_relay_logs.log 2>&1

最后启动MHA Manger监控，查看集群里面现在谁是master（在切换后监控就停止了。。。还有东西没搞对？）后来在官方网站看到这句话就明白了。

Running MHA Manager from daemontools

Currently MHA Manager process does not run as a daemon. If failover completed successfully or the master process was killed by accident, the manager stops working. To run as a daemon, daemontool. or any external daemon program can be used. Here is an example to run from daemontools.

免责声明：

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

阅读原文内容投诉