In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-03-26 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article focuses on "installation and deployment of MHA". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the installation and deployment of MHA.
MHA 0.56 is now available + 2 Vote Up-0Vote Down
Posted by Yoshinori Matsunobu on Tue 01 Apr 2014 04:50 UTC
Tags: (edit) mysql, MHA
I released MHA version 0.56 today. Downloads are available here. MHA 0.56 includes below features.
Supporting MySQL 5.6 GTID. If GTID and auto position is enabled, MHA automatically does failover with GTID SQL syntax, not using traditional relay log based failover. You don't need any explicit configuration within MHA to use GTID based failover.
Supporting MySQL 5.6 Multi-Threaded slave
Supporting MySQL 5.6 binlog checksum
MHA...
I. Environmental preparation
1. Modify the name of each host
192.168.2.52 virtdb52.gewara.cn # manager
192.168.2.54 virtdb54.gewara.cn # node master
192.168.2.55 virtdb55.gewara.cn # node slave1
192.168.2.56 virtdb56.gewara.cn # node slave2
two。 Configure root trust:
# Host: master executes commands
Ssh-keygen-t rsa
Ssh-copy-id-I / .ssh/id_rsa.pub root@manager
Ssh-copy-id-I / .ssh/id_rsa.pub root@slave01
Ssh-copy-id-I / .ssh/id_rsa.pub root@slave02
# Host: slave01 executes commands
Ssh-keygen-t rsa
Ssh-copy-id-I / .ssh/id_rsa.pub root@manager
Ssh-copy-id-I / .ssh/id_rsa.pub root@master
Ssh-copy-id-I / .ssh/id_rsa.pub root@slave02
3. Configure master and slave
Create a copy account
Grant replication slave on *. * to repl@'%' identified by '123456'
Flush privileges
Stop slave
Configure replication
Change master to MASTER_HOST='192.168.2.54', MASTER_PORT=3306,MASTER_USER='repl', MASTER_PASSWORD='123456',master_log_file='mysql-bin.000005', master_log_pos=120
Start slave
Show slave status\ G
Create a mha monitoring account
Grant all on *. * to mha@'192.168.%' identified by '123456'
Flush privileges
Egrep "log-bin | server_id" / opt/mysql3306/etc/my.cnf
two。 Install and deploy MHA
2.1 install MHA node (on all Mysql servers)
1) install dependency packages
Rpm-Uvh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Rpm--import / etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
Yum-y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Config-IniFiles perl-Time-HiRes perl-Time-HiRes perl-CPAN
2) install mha node on all nodes:
Download: https://downloads.mariadb.com/files/MHA
Wget https://downloads.mariadb.com/files/MHA/mha4mysql-node-0.56.tar.gz
Tar zxvf mha4mysql-node-0.56.tar.gz
Perl Makefile.PL
Make & & make install
Installing / usr/local/bin/apply_diff_relay_logs
Installing / usr/local/bin/save_binary_logs
Installing / usr/local/bin/purge_relay_logs
Installing / usr/local/bin/filter_mysqlbinlog
3) install mha4mysql-manager and mha4mysql-node packages on manager
Wget https://downloads.mariadb.com/files/MHA/mha4mysql-manager-0.56.tar.gz
Tar zxvf mha4mysql-manager-0.56.tar.gz
Perl Makefile.PL
Make & & make install
Installing / usr/local/bin/masterha_stop
Installing / usr/local/bin/masterha_master_monitor
Installing / usr/local/bin/masterha_check_status
Installing / usr/local/bin/masterha_conf_host
Installing / usr/local/bin/masterha_secondary_check
Installing / usr/local/bin/masterha_master_switch
Installing / usr/local/bin/masterha_manager
Installing / usr/local/bin/masterha_check_repl
Installing / usr/local/bin/masterha_check_ssh
Mkdir-p / usr/local/mha/scripts
Cp samples/scripts/* / usr/local/mha/scripts/
[root@virtdb52 mha] # vi / usr/local/mha/mha_app1.cnf
[server default]
Manager_workdir=/usr/local/mha
Manager_log=/usr/local/mha/manager.log
Ssh_user=root
Repl_user=repl
Repl_password=123456
Ping_interval=1
[server1]
Hostname=virtdb54.gewara.cn
Ssh_port=22
Master_binlog_dir=/opt/mysql3306/data/
Candidate_master=1
[server2]
Hostname=virtdb55.gewara.cn
Ssh_port=22
Master_binlog_dir=/opt/mysql3306/data/
Candidate_master=1
[server3]
Hostname=virtdb56.gewara.cn
Ssh_port=22
Master_binlog_dir=/opt/mysql3306/data/
Candidate_master=1
# [server4]
# hostname=host4
# no_master=1
2.2 verify ssh communication
Masterha_check_ssh-conf=/usr/local/mha/mha_app1.cnf
2.3 verify MySQL master-slave replication
Masterha_check_repl-conf=/usr/local/mha/mha_app1.cnf
Can't exec "mysqlbinlog": No such file or directory at / usr/local/share/perl5/MHA/BinlogManager.pm line 106.
Mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options
Resolve:
Which mysqlbinlog
Type mysqlbinlog
Ln-s / opt/mysql3306/bin/mysqlbinlog / usr/bin/mysqlbinlog
Mysqlbinlog: unknown variable 'default-character-set=utf8'
Resolve:
Vi my.cnf
# default-character-set=utf8
Testing mysql connection and privileges..sh: mysql: command not found
Resolve:
Ln-s / opt/mysql3306/bin/mysql / usr/bin/mysql
2.4. Check the status of startup
Masterha_check_status-conf=/usr/local/mha/mha_app1.cnf
2.4 start mha
1) every time we do the mha experiment, we'd better execute the following command to do the test.
Masterha_check_ssh-conf=/usr/local/mha/mha_app1.cnf
Masterha_check_repl-conf=/usr/local/mha/mha_app1.cnf
2) start the mha service on the manager side and monitor the output changes of the log files all the time
Nohup masterha_manager-- conf=/usr/local/mha/mha_app1.cnf > / tmp/mha_manager.log 2 > & 1 &
Ps-ef | grep masterha | grep-v 'grep'
2.5. Stop mha
Masterha_stop masterha_check_status-conf=/usr/local/mha/mha_app1.cnf
2.5 after the test master is down, it will switch automatically.
# check the master-slave synchronization of slave01,slave02
# slave01
Check the master-slave synchronization of slave01,slave02 before testing
Mysql-umha-p123456-h292.168.2.55-e 'show slave status\ G' | egrep' Slave_IO_Running: | Slave_SQL_Running | Master_Host'
Mysql-umha-p123456-h292.168.2.56-e 'show slave status\ G' | egrep' Slave_IO_Running: | Slave_SQL_Running | Master_Host'
Mysql-umha-p123456-h292.168.2.54-e 'show slave status\ G' | egrep' Slave_IO_Running: | Slave_SQL_Running | Master_Host'
# stop the mysql service of master
Service mysqld stop
Check the configuration of the slave library
Mysql-umha-p123456-h292.168.2.55-e 'show slave status\ G' | egrep' Slave_IO_Running: | Slave_SQL_Running | Master_Host'
Mysql-umha-p123456-h292.168.2.56-e 'show slave status\ G' | egrep' Slave_IO_Running: | Slave_SQL_Running | Master_Host'
# shut down the master database
Service mysqld stop
As master shuts down, slave2 redirects the library to the new master
When the original slave1 is changed to master, the slave configuration information will be reset slave
The MHA service will be turned off, but VIP will still be automatically switched to the new master, and MHA needs to be restarted.
After the master-slave switch occurs, the MHAmanager service stops automatically, and the file app1.failover.complete is generated under the manager_workdir directory. To start MHA, you must first make sure that there is no such file)
When a slave node is down, it cannot be started by default, plus-- ignore_fail_on_start can start MHA even if a node is down, as follows:
# nohup masterha_manager-conf=/etc/masterha/app1/app1.cnf-ignore_fail_on_start > / etc/masterha/app1/mha_manager.log 2 > & 1 &
Delete relay logs periodically
Because the parameter relay_log_purge=0 is set on each slave in the first step, the slave node needs to delete the relay log periodically, and it is recommended that each slave node delete the relay log at a staggered time.
Corntab-e
0 5 * / usr/bin/purge_relay_logs-user=root--password=123456-port=3306-disable_relay_log_purge > > / var/lib/mysql/purge_relay.log 2 > & 1
2.6 restore the original master service
# Delete a failover file
[root@manager mha] # rm-rf / usr/local/mha/mha_app1.failover.complete
-rw-r--r-- 1 root root 0 May 17 16:09 mha_app1.failover.complete
-rw-r--r-- 1 root root 143 May 1716: 09 saved_master_binlog_from_virtdb54.gewara.cn_3306_20160517160908.binlog
# restart the mysql service of the original master
Service mysqld start
# find the sql statement synchronized by master and slave in the date file of manager
Grep MASTER_HOST / usr/local/mha/manager.log
Tue May 17 16:09:11 2016-[info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='virtdb55.gewara.cn or 192.168.2.55, MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=120, MASTER_USER='repl', MASTER_PASSWORD='xxx'
Reconfigure the slave library:
CHANGE MASTER TO MASTER_HOST='192.168.2.55', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=120, MASTER_USER='repl', MASTER_PASSWORD='123456'
Start slave
Fifth, realize the high availability of mysql through vip
1. Modify / usr/local/mha/mha_app1.cnf
Vi / usr/local/mha/mha_app1.cnf
Master_ip_failover_script=/usr/local/mha/scripts/master_ip_failover # add scripts to manage vip
2. Modify script / usr/local/mha/scripts/master_ip_failover
[root@virtdb52 scripts] # more master_ip_failover
#! / usr/bin/env perl
Use strict
Use warnings FATAL = > 'all'
Use Getopt::Long
My (
$command, $ssh_user, $orig_master_host, $orig_master_ip
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
My $vip = '192.168.2.220; # Virtual IP
My $gateway = '192.168.2.11; # Gateway IP
My $interface = 'eth0'
My $key = "1"
My $ssh_start_vip = "/ sbin/ifconfig $interface:$key $vip;/sbin/arping-I $interface-c 3-s $vip $gateway > / dev/null 2 > & 1"
My $ssh_stop_vip = "/ sbin/ifconfig $interface:$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
);
Exit & main ()
Sub main {
Print "\ n\ nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\ n\ n"
If ($command eq "stop" | | $command eq "stopssh") {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database
# invalidate orig_master_ip here.
My $exit_code = 1
Eval {
Print "Disabling the VIP on old master: $orig_master_host\ n"
& stop_vip ()
$exit_code = 0
}
If ($@) {
Warn "Got Error: $@\ n"
Exit $exit_code
}
Exit $exit_code
}
Elsif ($command eq "start") {
# all arguments are passed.
# If you manage master ip address at global catalog database
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
My $exit_code = 10
Eval {
Print "Enabling the VIP-$vip on the new master-$new_master_host\ n"
& start_vip ()
$exit_code = 0
}
If ($@) {
Warn $@
Exit $exit_code
}
Exit $exit_code
}
Elsif ($command eq "status") {
Print "Checking the Status of the script.. OK\ n"
`ssh $ssh_user\ @ $orig_master_host\ "$ssh_start_vip\" `
Exit 0
}
Else {
& usage ()
Exit 1
}
}
# A simple system call that enable the VIP on the new master
Sub start_vip () {
`ssh $ssh_user\ @ $new_master_host\ "$ssh_start_vip\" `
}
# A simple system call that disable the VIP on the old_master
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 "
}
Chmod 755 master_ip_failover
Test the binding of the network card
/ sbin/ifconfig eth0:1 192.168.2.220/24
/ sbin/ifconfig eth0:1 down
Restore operation
1.db1 starts mysql
Service mysqld start
-- after switching: rejoin the node
Grep MASTER_HOST / usr/local/mha/manager.log
CHANGE MASTER TO MASTER_HOST='192.168.2.54', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=120, MASTER_USER='repl', MASTER_PASSWORD='123456'
Start slave
Show slave status\ G
4. Start the management of manager
1) every time we do the mha experiment, we'd better execute the following command to do the test.
Masterha_check_ssh-conf=/usr/local/mha/mha_app1.cnf
Masterha_check_repl-conf=/usr/local/mha/mha_app1.cnf
2) start the mha service on the manager side and monitor the output changes of the log files all the time
Nohup masterha_manager-- conf=/usr/local/mha/mha_app1.cnf > / tmp/mha_manager.log 2 > & 1 &
Ps-ef | grep masterha | grep-v 'grep'
Scheduled (Online) Master Switch (manual online master library switching)
In application scenarios, 1:master and slave are normal, MHA is enabled normally, and master is manually switched online to other hosts during maintenance operations (such as replacing new host hardware, adding / removing columns or primary keys).
1. If MHA is running, you need to stop MHA first
Masterha_stop-conf=/usr/local/mha/mha_app1.cnf
two。 Check the MHA current setting
Masterha_check_repl-conf=/usr/local/mha/mha_app1.cnf
3. Manual switching
Masterha_master_switch-master_state=alive-conf=/usr/local/mha/mha_app1.cnf-orig_master_is_new_slave-running_updates_limit=3600-interactive=0
Note: it is not the master_ip_failover_script script that executes the masterha_master_switch call, but the master_ip_online_change_script script. You can put the start and stop VIP into this script. If VIP is not configured, you need to manually perform the VIP switch, as follows:
Ssh root@$orig_master_ip / sbin/ifconfig eth0:1 down
Ssh root@$new_master_ip / sbin/ifconfig eth0:1 10.1.5.21/24
Attached script:
[root@virtdb52 mha] # more mha_app1.cnf
[server default]
User=mha
Password=123456
Manager_workdir=/usr/local/mha
Manager_log=/usr/local/mha/manager.log
Master_ip_failover_script=/usr/local/mha/scripts/master_ip_failover
Master_ip_online_change_script=/usr/local/mha/scripts/master_ip_online_change
Report_script=/usr/local/mha/scripts/send_report
Ssh_user=root
Repl_user=repl
Repl_password=123456
Ping_interval=1
[server1]
Hostname=virtdb54.gewara.cn
Ssh_port=22
Master_binlog_dir=/opt/mysql3306/data/
Candidate_master=1
[server2]
Hostname=virtdb55.gewara.cn
Ssh_port=22
Master_binlog_dir=/opt/mysql3306/data/
Candidate_master=1
[server3]
Hostname=virtdb56.gewara.cn
Ssh_port=22
Master_binlog_dir=/opt/mysql3306/data/
Candidate_master=1
# [server4]
# hostname=host4
# no_master=1
[root@virtdb52 scripts] # more master_ip_failover
#! / usr/bin/env perl
Use strict
Use warnings FATAL = > 'all'
Use Getopt::Long
My (
$command, $ssh_user, $orig_master_host, $orig_master_ip
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
My $vip = '192.168.2.220; # Virtual IP
My $gateway = '192.168.2.11; # Gateway IP
My $interface = 'eth0'
My $key = "1"
My $ssh_start_vip = "/ sbin/ifconfig $interface:$key $vip;/sbin/arping-I $interface-c 3-s $vip $gateway > / dev/null 2 > & 1"
My $ssh_stop_vip = "/ sbin/ifconfig $interface:$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
);
Exit & main ()
Sub main {
Print "\ n\ nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\ n\ n"
If ($command eq "stop" | | $command eq "stopssh") {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database
# invalidate orig_master_ip here.
My $exit_code = 1
Eval {
Print "Disabling the VIP on old master: $orig_master_host\ n"
& stop_vip ()
$exit_code = 0
}
If ($@) {
Warn "Got Error: $@\ n"
Exit $exit_code
}
Exit $exit_code
}
Elsif ($command eq "start") {
# all arguments are passed.
# If you manage master ip address at global catalog database
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
My $exit_code = 10
Eval {
Print "Enabling the VIP-$vip on the new master-$new_master_host\ n"
& start_vip ()
$exit_code = 0
}
If ($@) {
Warn $@
Exit $exit_code
}
Exit $exit_code
}
Elsif ($command eq "status") {
Print "Checking the Status of the script.. OK\ n"
`ssh $ssh_user\ @ $orig_master_host\ "$ssh_start_vip\" `
Exit 0
}
Else {
& usage ()
Exit 1
}
}
# A simple system call that enable the VIP on the new master
Sub start_vip () {
`ssh $ssh_user\ @ $new_master_host\ "$ssh_start_vip\" `
}
# A simple system call that disable the VIP on the old_master
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 "
}
[root@virtdb52 scripts] # more master_ip_online_change
#! / 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_host, $orig_master_ip
$orig_master_port, $orig_master_user
$new_master_host, $new_master_ip, $new_master_port
$new_master_user
);
My $vip = '192.168.2.220 Universe 2400; # Virtual IP
My $key = "1"
My $ssh_start_vip = "/ sbin/ifconfig eth0:$key $vip"
My $ssh_stop_vip = "/ sbin/ifconfig eth0:$key down"
My $ssh_user = "root"
My $new_master_password='123456'
My $orig_master_password='123456'
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
'orig_master_user=s' = >\ $orig_master_user
# 'orig_master_password=s' = >\ $orig_master_password
'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 current_time_us {
My ($sec, $microsec) = gettimeofday ()
My $curdate = localtime ($sec)
Return $curdate. "". Sprintf ("d", $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 * (. *?)\ 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 * 100milliseconds 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 * 100milliseconds so that current update queries can complete
My $time_until_kill_threads = 5
@ threads = get_threads_util ($orig_master_handler- > {dbh})
$orig_master_handler- > {connection_id})
While ($time_until_kill_threads > 0 & & $# threads > = 0) {
If ($time_until_kill_threads% 5 = = 0) {
Printf
"% s Waiting all running d queries are disconnected.. (max% d milliseconds)\ n"
Current_time_us (), $# threads + 1, $time_until_kill_threads * 100
If ($# threads
< 5 ) { print Data::Dumper->New ([$_])-> Indent (0)-> Terse (1)-> Dump. "\ n"
Foreach (@ threads)
}
}
Sleep_until ()
$_ tstart = [gettimeofday]
$time_until_kill_threads--
@ threads = get_threads_util ($orig_master_handler- > {dbh})
$orig_master_handler- > {connection_id})
}
Print "Disabling the VIP on old master: $orig_master_host\ n"
& stop_vip ()
# # Terminating all threads
Print current_time_us (). "Killing all application threads..\ n"
$orig_master_handler- > kill_threads (@ threads) if ($# threads > = 0)
Print current_time_us (). "done."
# $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."
$new_master_handler- > disable_read_only ()
# # Creating an app user on the new master
# print current_time_us (). "Creating app user on the new master..\ n"
# FIXME_xxx_create_app_user ($new_master_handler)
# $new_master_handler- > enable_log_bin_local ()
$new_master_handler- > disconnect ()
# # Update master ip on the catalog database, etc
Print "Enabling the VIP-$vip on the new master-$new_master_host\ n"
& start_vip ()
$exit_code = 0
}
If ($@) {
Warn "Got Error: $@\ n"
Exit $exit_code
}
Exit $exit_code
}
Elsif ($command eq "status") {
# do nothing
Exit 0
}
Else {
& usage ()
Exit 1
}
}
# A simple system call that enable the VIP on the new master
Sub start_vip () {
`ssh $ssh_user\ @ $new_master_host\ "$ssh_start_vip\" `
}
# A simple system call that disable the VIP on the old_master
Sub stop_vip () {
`ssh $ssh_user\ @ $orig_master_host\ "$ssh_stop_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=p
Ort\ n "
Die
}
Vi send_report
#! / usr/bin/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 Mail::Sender
Use Getopt::Long
# new_master_host and new_slave_hosts are set only when recovering master succeeded
My ($dead_master_host, $new_master_host, $new_slave_hosts, $subject, $body)
My $smtp='smtp.163.com'
My $mail_from='xxxx'
My $mail_user='xxxxx'
My $mail_pass='xxxxx'
My $mail_to= ['xxxx','xxxx']
GetOptions (
'orig_master_host=s' = >\ $dead_master_host
'new_master_host=s' = >\ $new_master_host
'new_slave_hosts=s' = >\ $new_slave_hosts
'subject=s' = >\ $subject
'body=s' = >\ $body
);
MailToContacts ($smtp,$mail_from,$mail_user,$mail_pass,$mail_to,$subject,$body)
Sub mailToContacts {
My ($smtp, $mail_from, $user, $passwd, $mail_to, $subject, $msg) = @ _
Open my $DEBUG, "> / tmp/monitormail.log"
Or die "Can't open the debug fileParticipation!\ n"
My $sender = new Mail::Sender {
Ctype = > 'text/plain; charset=utf-8'
Encoding = > 'utf-8'
Smtp = > $smtp
From = > $mail_from
Auth = > 'LOGIN'
TLS_allowed = >'0'
Authid = > $user
Authpwd = > $passwd
To = > $mail_to
Subject = > $subject
Debug = > $DEBUG
}
$sender- > MailMsg (
{msg = > $msg
Debug = > $DEBUG
}
) or print $Mail::Sender::Error
Return 1
}
# Do whatever you want here
Exit 0
-
Mysql 5.6 GTID
Percona server 5.6.25
Master edge settings:
Server_id=1
Log_bin=mysql-bin
Binlog_format=row
Gtid_mode=on
Enforce_gtid_consistency
Log_slave_updates
Slave settings:
Server_id=2
Log_bin=mysql-bin
Binlog_format=row
Skip_slave_start
Gtid_mode=on
Enforce_gtid_consistency
Log_slave_updates
CHANGE MASTER TO
MASTER_HOST='192.168.2.54'
MASTER_PORT=3306
MASTER_USER='repl'
MASTER_PASSWORD='123456'
MASTER_AUTO_POSITION=1
MHA GTID
After changing to MHA GITD, after switching:
Grep-I "CHANGE" manager.log | tail
CHANGE MASTER TO MASTER_HOST='virtdb55.gewara.cn or 192.168.2.55, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx'
MHA GTID switch log
Found that master could not be accessed
Thu May 19 10:04:16 2016-[warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Thu May 19 10:04:16 2016-[info] Executing SSH check script: exit 0
Thu May 19 10:04:16 2016-[info] HealthCheck: SSH to virtdb54.gewara.cn is reachable.
Thu May 19 10:04:17 2016-[warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 2013)
Thu May 19 10:04:17 2016-[warning] Connection failed 1 time (s)..
Thu May 19 10:04:18 2016-[warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 2013)
Thu May 19 10:04:18 2016-[warning] Connection failed 2 time (s)..
Thu May 19 10:04:19 2016-[warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 2013)
Thu May 19 10:04:19 2016-[warning] Connection failed 3 time (s)..
Thu May 19 10:04:19 2016-[warning] Master is not reachable from health checker!
Thu May 19 10:04:19 2016-[warning] Master virtdb54.gewara.cn (192.168.2.54 is not reachable 3306) is not reachable!
Thu May 19 10:04:19 2016-[warning] SSH is reachable.
Thu May 19 10:04:19 2016-[info] Connecting to a master server failed. Reading configuration file / etc/masterha_default.cnf and / usr/local/mha/mha_app1.cnf again, and trying to connect to all servers to check server status..
Check all master-slave server status through the configuration file
Thu May 19 10:04:19 2016-[warning] SQL Thread is stopped (no error) on virtdb56.gewara.cn (192.168.2.56 SQL Thread is stopped 3306)
Thu May 19 10:04:19 2016-[info] Dead Servers:
Thu May 19 10:04:19 2016-[info] virtdb54.gewara.cn (192.168.2.54 3306)
Thu May 19 10:04:19 2016-[info] Alive Servers:
Thu May 19 10:04:19 2016-[info] virtdb55.gewara.cn (192.168.2.55 virtdb55.gewara.cn)
Thu May 19 10:04:19 2016-[info] virtdb56.gewara.cn (192.168.2.56 virtdb56.gewara.cn)
Thu May 19 10:04:19 2016-[info] Alive Slaves:
Thu May 19 10:04:19 2016-[info] virtdb55.gewara.cn (192.168.2.55 Version=5.6.25) Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016-[info] GTID ON
Thu May 19 10:04:19 2016-[info] Replicating from 192.168.2.54 (192.168.2.54 Replicating from 3306)
Thu May 19 10:04:19 2016-[info] Primary candidate for the new Master (candidate_master is set)
Thu May 19 10:04:19 2016-[info] virtdb56.gewara.cn (192.168.2.56 Version=5.6.25 3306) Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016-[info] GTID ON
Thu May 19 10:04:19 2016-[info] Replicating from 192.168.2.54 (192.168.2.54 Replicating from 3306)
Thu May 19 10:04:19 2016-[info] Primary candidate for the new Master (candidate_master is set)
Make sure master down starts the switching action.
Thu May 19 10:04:19 2016-[info] Master is down!
Thu May 19 10:04:19 2016-[info] Terminating monitoring script.
Thu May 19 10:04:19 2016-[info] Got exit code 20 (Master dead).
Thu May 19 10:04:19 2016-[info] MHA::MasterFailover version 0.56.
Thu May 19 10:04:19 2016-[info] Starting master failover.
Phase 1: check the master-salve server configuration role and thread status
Thu May 19 10:04:19 2016-[info] * Phase 1: Configuration Check Phase..
Thu May 19 10:04:19 2016-[info]
Thu May 19 10:04:19 2016-[warning] SQL Thread is stopped (no error) on virtdb56.gewara.cn (192.168.2.56 SQL Thread is stopped 3306)
Thu May 19 10:04:19 2016-[info] Dead Servers:
Thu May 19 10:04:19 2016-[info] virtdb54.gewara.cn (192.168.2.54 3306)
Thu May 19 10:04:19 2016-[info] Checking master reachability via mysql (double check)..
Thu May 19 10:04:19 2016-[info] ok.
Thu May 19 10:04:19 2016-[info] Alive Servers:
Thu May 19 10:04:19 2016-[info] virtdb55.gewara.cn (192.168.2.55 virtdb55.gewara.cn)
Thu May 19 10:04:19 2016-[info] virtdb56.gewara.cn (192.168.2.56 virtdb56.gewara.cn)
Thu May 19 10:04:19 2016-[info] Alive Slaves:
Thu May 19 10:04:19 2016-[info] virtdb55.gewara.cn (192.168.2.55 Version=5.6.25) Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016-[info] GTID ON
Thu May 19 10:04:19 2016-[info] Replicating from 192.168.2.54 (192.168.2.54 Replicating from 3306)
Thu May 19 10:04:19 2016-[info] Primary candidate for the new Master (candidate_master is set)
Thu May 19 10:04:19 2016-[info] virtdb56.gewara.cn (192.168.2.56 Version=5.6.25) Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016-[info] GTID ON
Thu May 19 10:04:19 2016-[info] Replicating from 192.168.2.54 (192.168.2.54 Replicating from 3306)
Thu May 19 10:04:19 2016-[info] Primary candidate for the new Master (candidate_master is set)
Thu May 19 10:04:19 2016-[info] Starting SQL thread on virtdb56.gewara.cn (192.168.2.56 Starting SQL thread on virtdb56.gewara.cn 3306)
Thu May 19 10:04:19 2016-[info] done.
Thu May 19 10:04:19 2016-[info] * * Phase 1: Configuration Check Phase completed.
Phase 2: turn off master shutdown,VIP so that it cannot access master
Thu May 19 10:04:19 2016-[info] * Phase 2: Dead Master Shutdown Phase..
Thu May 19 10:04:19 2016-[info]
Thu May 19 10:04:19 2016-[info] Forcing shutdown so that applications never connect to the current master..
Thu May 19 10:04:19 2016-[info] Executing master IP deactivatation script:
Thu May 19 10:04:19 2016-[info] / usr/local/mha/scripts/master_ip_failover-orig_master_host=virtdb54.gewara.cn-orig_master_ip=192.168.2.54-orig_master_port=3306-command=stopssh-ssh_user=root
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 192.168.2.220 eth0 eth0-c 3-s 192.168.2.220 192.168.2.11 > / dev/null 2 > & 1 benchmark =
Disabling the VIP on old master: virtdb54.gewara.cn
Thu May 19 10:04:19 2016-[info] done.
Thu May 19 10:04:19 2016-[warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Thu May 19 10:04:19 2016-[info] * Phase 2: Dead Master Shutdown Phase completed.
Phase 3: discover the Slave of the GITD EVENT and determine that the slave is master
Thu May 19 10:04:19 2016-[info] * Phase 3.1: Getting Latest Slaves Phase..
Thu May 19 10:04:19 2016-[info]
Thu May 19 10:04:19 2016-[info] The latest binary log file/position on all slaves is mysql-bin.000010:3006905
Thu May 19 10:04:19 2016-[info] Retrieved Gtid Set: 8b8cad8e-053c-11e6-b500-5254006f0b84:2-11304
Thu May 19 10:04:19 2016-[info] Latest slaves (Slaves that received relay log files to the latest):
Thu May 19 10:04:19 2016-[info] virtdb55.gewara.cn (192.168.2.55 Version=5.6.25) Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016-[info] GTID ON
Thu May 19 10:04:19 2016-[info] Replicating from 192.168.2.54 (192.168.2.54 Replicating from 3306)
Thu May 19 10:04:19 2016-[info] Primary candidate for the new Master (candidate_master is set)
Thu May 19 10:04:19 2016-[info] The oldest binary log file/position on all slaves is mysql-bin.000010:1616340
Thu May 19 10:04:19 2016-[info] Retrieved Gtid Set: 8b8cad8e-053c-11e6-b500-5254006f0b84:5-6082
Thu May 19 10:04:19 2016-[info] Oldest slaves:
Thu May 19 10:04:19 2016-[info] virtdb56.gewara.cn (192.168.2.56 Version=5.6.25 3306) Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016-[info] GTID ON
Thu May 19 10:04:19 2016-[info] Replicating from 192.168.2.54 (192.168.2.54 Replicating from 3306)
Thu May 19 10:04:19 2016-[info] Primary candidate for the new Master (candidate_master is set)
Thu May 19 10:04:19 2016-[info]
Thu May 19 10:04:19 2016-[info] * Phase 3.3: Determining New Master Phase..
Thu May 19 10:04:19 2016-[info]
Thu May 19 10:04:19 2016-[info] Searching new master from slaves..
Thu May 19 10:04:19 2016-[info] Candidate masters from the configuration file:
Thu May 19 10:04:19 2016-[info] virtdb55.gewara.cn (192.168.2.55 Version=5.6.25) Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016-[info] GTID ON
Thu May 19 10:04:19 2016-[info] Replicating from 192.168.2.54 (192.168.2.54 Replicating from 3306)
Thu May 19 10:04:19 2016-[info] Primary candidate for the new Master (candidate_master is set)
Thu May 19 10:04:19 2016-[info] virtdb56.gewara.cn (192.168.2.56 Version=5.6.25 3306) Version=5.6.25-73.1-log (oldest major version between slaves) log-bin:enabled
Thu May 19 10:04:19 2016-[info] GTID ON
Thu May 19 10:04:19 2016-[info] Replicating from 192.168.2.54 (192.168.2.54 Replicating from 3306)
Thu May 19 10:04:19 2016-[info] Primary candidate for the new Master (candidate_master is set)
Thu May 19 10:04:19 2016-[info] Non-candidate masters:
Thu May 19 10:04:19 2016-[info] Searching from candidate_master slaves which have received the latest relay log events..
Thu May 19 10:04:19 2016-[info] New master is virtdb55.gewara.cn (192.168.2.55 New master is virtdb55.gewara.cn)
Thu May 19 10:04:19 2016-[info] Starting master failover..
To prepare the slave of master, you need to apply and switch.
To:
Virtdb55.gewara.cn (new master)
+-- virtdb56.gewara.cn
Thu May 19 10:04:19 2016-[info]
Thu May 19 10:04:19 2016-[info] * Phase 3.3: New Master Recovery Phase..
Thu May 19 10:04:19 2016-[info]
Thu May 19 10:04:19 2016-[info] Waiting all logs to be applied..
Thu May 19 10:04:19 2016-[info] done.
Thu May 19 10:04:19 2016-[info] Getting new master's binlog name and position..
Thu May 19 10:04:19 2016-[info] mysql-bin.000009:2815604
Thu May 19 10:04:19 2016-[info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='virtdb55.gewara.cn or 192.168.2.55, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx'
Thu May 19 10:04:19 2016-[info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000009, 2815604, 8b3861e6-053c-11e6-b500-525400691d52:1-2
8b8cad8e-053c-11e6-b500-5254006f0b84:1-11304
Thu May 19 10:04:19 2016-[info] Executing master IP activate script:
Thu May 19 10:04:19 2016-[info] / usr/local/mha/scripts/master_ip_failover-- command=start-- ssh_user=root-- orig_master_host=virtdb54.gewara.cn-- orig_master_ip=192.168.2.54-- orig_master_port=3306-- new_master_host=virtdb55.gewara.cn-new_master_ip=192.168.2.55-- new_master_port=3306-- new_master_user='mha'-- new_master_password='123456'
Unknown option: new_master_user
Unknown option: new_master_password
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 192.168.2.220 eth0 eth0-c 3-s 192.168.2.220 192.168.2.11 > / dev/null 2 > & 1 benchmark =
Enabling the VIP-192.168.2.220 on the new master-virtdb55.gewara.cn
Thu May 19 10:04:23 2016-[info] OK.
Thu May 19 10:04:23 2016-[info] Setting read_only=0 on virtdb55.gewara.cn (192.168.2.55 Setting read_only=0 on virtdb55.gewara.cn 3306)
Thu May 19 10:04:23 2016-[info] ok.
Thu May 19 10:04:23 2016-[info] * * Finished master recovery successfully.
Thu May 19 10:04:23 2016-[info] * Phase 3: Master Recovery Phase completed.
Thu May 19 10:04:23 2016-[info]
Thu May 19 10:04:23 2016-[info] * Phase 4: Slaves Recovery Phase..
Thu May 19 10:04:23 2016-[info]
Thu May 19 10:04:23 2016-[info]
Thu May 19 10:04:23 2016-[info] * Phase 4.1: Starting Slaves in parallel..
Thu May 19 10:04:23 2016-[info]
Thu May 19 10:04:23 2016-[info]-- Slave recovery on host virtdb56.gewara.cn (192.168.2.56 started) started, pid: 29244. Check tmp log / usr/local/mha/virtdb56.gewara.cn_3306_20160519100419.log if it takes time..
Thu May 19 10:04:24 2016-[info]
Thu May 19 10:04:24 2016-[info] Log messages from virtdb56.gewara.cn.
Thu May 19 10:04:24 2016-[info]
Thu May 19 10:04:23 2016-[info] Resetting slave virtdb56.gewara.cn (192.168.2.56) and starting replication from the new master virtdb55.gewara.cn (192.168.2.55).
Thu May 19 10:04:24 2016-[info] Executed CHANGE MASTER.
Thu May 19 10:04:24 2016-[info] Slave started.
Thu May 19 10:04:24 2016-[info] End of log messages from virtdb56.gewara.cn.
Thu May 19 10:04:24 2016-[info]-- Slave on host virtdb56.gewara.cn (192.168.2.56 started 3306).
Thu May 19 10:04:24 2016-[info] All new slave servers recovered successfully.
At this point, I believe that everyone has a deeper understanding of the "installation and deployment of MHA", might as well come to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.
12
Report
