簡介：
創(chuàng)新互聯(lián)建站服務(wù)項目包括花溪網(wǎng)站建設(shè)、花溪網(wǎng)站制作、花溪網(wǎng)頁制作以及花溪網(wǎng)絡(luò)營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢、行業(yè)經(jīng)驗、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，花溪網(wǎng)站推廣取得了明顯的社會效益與經(jīng)濟效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到花溪省份的部分城市，未來相信會繼續(xù)擴大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！
MHA（Master High Availability）目前在MySQL高可用方面是一個相對成熟的解決方案，它由日本DeNA公司youshimaton（現(xiàn)就職于Facebook公司）開發(fā)，是一套優(yōu)秀的作為MySQL高可用性環(huán)境下故障切換和主從提升的高可用軟件。在MySQL故障切換過程中，MHA能做到在0~30秒之內(nèi)自動完成數(shù)據(jù)庫的故障切換操作，并且在進行故障切換的過程中，MHA能在最大程度上保證數(shù)據(jù)的一致性，以達到真正意義上的高可用。
該軟件由兩部分組成：MHA Manager（管理節(jié)點）和MHA Node（數(shù)據(jù)節(jié)點）。MHA Manager可以單獨部署在一臺獨立的機器上管理多個master-slave集群，也可以部署在一臺slave節(jié)點上。MHA Node運行在每臺MySQL服務(wù)器上，MHA Manager會定時探測集群中的master節(jié)點，當(dāng)master出現(xiàn)故障時，它可以自動將最新數(shù)據(jù)的slave提升為新的master，然后將所有其他的slave重新指向新的master。整個故障轉(zhuǎn)移過程對應(yīng)用程序完全透明。
在MHA自動故障切換過程中，MHA試圖從宕機的主服務(wù)器上保存二進制日志，最大程度的保證數(shù)據(jù)的不丟失，但這并不總是可行的。例如，如果主服務(wù)器硬件故障或無法通過ssh訪問，MHA沒法保存二進制日志，只進行故障轉(zhuǎn)移而丟失了最新的數(shù)據(jù)。使用MySQL 5.5的半同步復(fù)制，可以大大降低數(shù)據(jù)丟失的風(fēng)險。MHA可以與半同步復(fù)制結(jié)合起來。如果只有一個slave已經(jīng)收到了最新的二進制日志，MHA可以將最新的二進制日志應(yīng)用于其他所有的slave服務(wù)器上，因此可以保證所有節(jié)點的數(shù)據(jù)一致性。
目前MHA主要支持一主多從的架構(gòu)，要搭建MHA,要求一個復(fù)制集群中必須最少有三臺數(shù)據(jù)庫服務(wù)器，一主二從，即一臺充當(dāng)master，一臺充當(dāng)備用master，另外一臺充當(dāng)從庫，因為至少需要三臺服務(wù)器，出于機器成本的考慮，淘寶也在該基礎(chǔ)上進行了改造，目前淘寶TMHA已經(jīng)支持一主一從。另外對于想快速搭建的可以參考：MHA快速搭建
我們自己使用其實也可以使用1主1從，但是master主機宕機后無法切換，以及無法補全binlog。master的mysqld進程crash后，還是可以切換成功，以及補全binlog的。
官方介紹：https://code.google.com/p/mysql-master-ha/
圖01展示了如何通過MHA Manager管理多組主從復(fù)制?？梢詫HA工作原理總結(jié)為如下：
（ 圖01 ）
（1）從宕機崩潰的master保存二進制日志事件（binlog events）;
（2）識別含有最新更新的slave；
（3）應(yīng)用差異的中繼日志（relay log）到其他的slave；
（4）應(yīng)用從master保存的二進制日志事件（binlog events）；
（5）提升一個slave為新的master；
（6）使其他的slave連接新的master進行復(fù)制；
MHA軟件由兩部分組成，Manager工具包和Node工具包，具體的說明如下。
Manager工具包主要包括以下幾個工具：
masterha_check_ssh 檢查MHA的SSH配置狀況 masterha_check_repl 檢查MySQL復(fù)制狀況 masterha_manger 啟動MHA masterha_check_status 檢測當(dāng)前MHA運行狀態(tài) masterha_master_monitor 檢測master是否宕機 masterha_master_switch 控制故障轉(zhuǎn)移（自動或者手動） masterha_conf_host 添加或刪除配置的server信息
ode工具包（這些工具通常由MHA Manager的腳本觸發(fā)，無需人為操作）主要包括以下幾個工具：
save_binary_logs 保存和復(fù)制master的二進制日志 apply_diff_relay_logs 識別差異的中繼日志事件并將其差異的事件應(yīng)用于其他的slavefilter_mysqlbinlog 去除不必要的ROLLBACK事件（MHA已不再使用這個工具） purge_relay_logs 清除中繼日志（不會阻塞SQL線程）
環(huán)境：
系統(tǒng)：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 - 監(jiān)控復(fù)制組 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節(jié)點
|-- 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還可以使用對應(yīng) 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
調(diào)用 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
調(diào)用 master_ip_online_change
See online reference
(http://code.google.com/p/mysql-master-ha/wiki/masterha_master_switch)
其中script中有簡單的配置文件及切換，發(fā)送郵件等腳本
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不可連接，選擇性配置腳本，根據(jù)實際情況更改下面配置） ####################################################################################### #master_ip_failover_script= /etc/masterha/script/master_ip_failover_vip # master can reachable and down vip ssh可以連接 ，可以腳本關(guān)掉vip ####################################################################################### ####################################################################################### master_ip_failover_script= /etc/masterha/script/master_ip_failover # master can not reachable and not down vip ssh不可用，無法腳本關(guān)掉vip ####################################################################################### # master online, mysql is alive and ssh is can reachable mysql 可用（分兩種情況 ssh可達，ssh不可達，選擇性配置腳本，根據(jù)實際情況更改下面配置） ####################################################################################### # 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
添加的腳本紅色標(biāo)出
#!/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，而此服務(wù)器mysql當(dāng)?shù)袅?，修改配置文件，將其屏?/p>
[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. 分享標(biāo)題：mha搭建及注意事項 網(wǎng)頁網(wǎng)址：http://weahome.cn/article/gphdig.html 其他資訊 |
重慶分公司
重慶分公司
