Prepare the server :

docker network create --subnet=192.168.0.0/16 staticnet
docker run -d --privileged -v `pwd`/mysql_data:/data -p 3001:3306 --name mysql5-master --hostname mysql5-master --net staticnet --ip 192.168.0.101 eiki/mysql:5.7.23 /usr/sbin/init
docker run -d --privileged -v `pwd`/mysql_data:/data -p 3002:3306 --name mysql5-slave --hostname mysql5-slave --net staticnet --ip 192.168.0.102 eiki/mysql:5.7.23 /usr/sbin/init
docker run -d --privileged -v `pwd`/mysql_data:/data -p 3003:3306 --name mysql5-s2 --hostname mysql5-s2 --net staticnet --ip 192.168.0.103 eiki/mysql:5.7.23 /usr/sbin/init docker run -d --privileged -v `pwd`/mysql_data:/data -p 7032:6032 -p 6080:6080 --name proxysql2 --hostname proxysql2 --net staticnet --ip 192.168.0.202 eiki/proxysql:latest /usr/sbin/init
docker run -d --privileged -v `pwd`/mysql_data:/data -p 6032:6032 -p 7080:6080 --name proxysql --hostname proxysql --net staticnet --ip 192.168.0.201 eiki/proxysql:latest /usr/sbin/init
among 6032 It's the management port ,6033 It's the program port ,6080 yes http port

Master slave building ( One master and two slaves )

MySQL The installation process is a little bit

Main library /etc/my.cnf

[mysql]
prompt = [\\u@\\h][\\d]>\\_
port = 3306
socket = /usr/local/mysql/mysql.sock [mysqld]
user = mysql
port = 3306
server-id = 1
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
log-bin = master-bin
log-bin-index = master-bin.index
relay_log_purge = 0 # The following two parameters are not added , From execution change You can't specify channel(ERROR 3077 (HY000))
master_info_repository =table
relay_log_info_repository =table

Slave Library /etc/my.cnf

[mysql]
prompt = [\\u@\\h][\\d]>\\_
port = 3306
socket = /usr/local/mysql/mysql.sock [mysqld]
user = mysql
port = 3306
server-id = 101
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
relay-log-index = slave-relay-bin.index
relay-log = slave-relay-bin
relay_log_purge = 0
# The following two parameters are not added , From execution change You can't specify channel(ERROR 3077 (HY000))
master_info_repository =table
relay_log_info_repository =table

Create a copy account on the main database

create user repl@'192.168.0.%' identified by 'repl';
grant replication slave on *.* to repl@'%';
flush privileges;

Perform a backup on the main library

mysqldump --master-data=2 --single-transaction -R --triggers -A > all.sql
among --master-data=2 Represents the backup time record master Of Binlog Location and Position,--single-transaction It means taking a consistent snapshot ,-R It means backing up stored procedures and functions ,--triggres Backup trigger means backup trigger ,-A Represents backing up all libraries . For more information, please help yourself mysqldump --help see .

Check the main library backup binlog Name and location ,MASTER_LOG_FILE and MASTER_LOG_POS

[root@mysql5-master ~]# head -n 30 all.sql | grep 'CHANGE MASTER TO'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=112;

Copy the backup to s1 and s2, That is to say 192.168.0.102 and 192.168.0.103

scp all.sql root@192.168.0.102:/data/
scp all.sql root@192.168.0.102:/data/

Import backup to s1,s2, Execute copy related commands

mysql -uroot -p < /data/all.sql
CHANGE MASTER TO MASTER_HOST='192.168.0.101',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='master-bin.000007',MASTER_LOG_POS=739 for channel 's1';
start slave for channel 's1';
show slave status\G;

View replication status ( You can see the copy is successful ):

[root@s1 ~]# mysql -e 'show slave status\G' | egrep 'Slave_IO|Slave_SQL'
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Another is to build a replication environment from the node , The operation is the same as above

Two sets of slave server setting read_only( Provide reading service from the library , So it's not written into the configuration file , Because At any time slave Will be promoted to master

[root@s1 ~]# mysql -e 'set global read_only=1'
[root@s1 ~]#
[root@s2 ~]# mysql -e 'set global read_only=1'
[root@s2 ~]#

Create an administrative account ( Main library execution )

grant replication slave,reload,create user,super on *.* to mats@'%' identified by 'mats' with grant option;

So far, the one master two slave architecture has been built

Configure mutual trust

Make sure all nodes are installed openssh And start the

openssh Installation steps of :
yum -y install openssh*
systemctl start sshd


ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.102
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.103
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.201
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.202 ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.101
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.103
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.201
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.202 ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.101
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.102
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.201
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.202 ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.101
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.102
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.103
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.202 ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.101
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.102
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.103
ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.0.201

Deploy MHA

 role ip Address Host name server_id type 
Monitor host 192.168.0.201 ProxySQL - Monitor replication groups
Master 192.168.0.101 master 1 write in
Candicate master 192.168.0.102 s1 101 read
Slave 192.168.0.103 s2 102 read

among master Write service to the outside world , alternative master( Actually slave, Host name s1) Provide read service ,slave It also provides related reading services , once master Downtime , Will put the alternative master Upgrade to a new master,slave Point to the new master

Official website
Manager:https://github.com/yoshinorim/mha4mysql-manager
Node: https://github.com/yoshinorim/mha4mysql-node
https://github.com/yoshinorim/mha4mysql-manager/wiki

Reference source :https://www.cnblogs.com/gomysql/p/3675429.html

Use git clone from github Copy project

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

Install dependency packages ( Install on all nodes MHA node The required perl modular )

yum install glib2-devel libpcap-devel libnet-devel cmake gc++ gcc gcc-c++ make git
yum install gcc gcc-c++ kernel-devel perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-Module-Install -y

Install... On all nodes mha node

cd mha4mysql-node-0.58
perl Makefile.PL
make && make install

After the installation, it will be in /usr/local/bin Generate the following script file under the directory :

[root@mysql5-master src]# ll /usr/local/bin
total 80
-r-xr-xr-x. 1 root root 17639 Nov 14 15:46 apply_diff_relay_logs
-r-xr-xr-x. 1 root root 7138 Nov 14 15:43 config_data
-r-xr-xr-x. 1 root root 4807 Nov 14 15:46 filter_mysqlbinlog
-r-xr-xr-x. 1 root root 4371 Nov 14 15:43 json_pp
lrwxrwxrwx. 1 root root 26 Nov 14 17:02 mysql -> /usr/local/mysql/bin/mysql
lrwxrwxrwx. 1 root root 32 Nov 14 17:02 mysqlbinlog -> /usr/local/mysql/bin/mysqlbinlog
-r-xr-xr-x. 1 root root 13649 Nov 14 15:43 prove
-r-xr-xr-x. 1 root root 8337 Nov 14 15:46 purge_relay_logs
-r-xr-xr-x. 1 root root 7525 Nov 14 15:46 save_binary_logs

install mha manager

cd mha4mysql-node-0.58
perl Makefile.PL
make && make install

After the installation, it will be in /usr/local/bin Generate the following script file under the directory

[root@proxysql 3306]# ll /usr/local/bin
total 92
-r-xr-xr-x. 1 root root 17639 Nov 14 15:52 apply_diff_relay_logs
-r-xr-xr-x. 1 root root 4807 Nov 14 15:52 filter_mysqlbinlog
-rw-r--r--. 1 root root 74 Nov 14 16:36 load_cnf
-r-xr-xr-x. 1 root root 1995 Nov 14 15:54 masterha_check_repl
-r-xr-xr-x. 1 root root 1779 Nov 14 15:54 masterha_check_ssh
-r-xr-xr-x. 1 root root 1865 Nov 14 15:54 masterha_check_status
-r-xr-xr-x. 1 root root 3201 Nov 14 15:54 masterha_conf_host
-r-xr-xr-x. 1 root root 2517 Nov 14 15:54 masterha_manager
-r-xr-xr-x. 1 root root 2165 Nov 14 15:54 masterha_master_monitor
-r-xr-xr-x. 1 root root 2373 Nov 14 15:54 masterha_master_switch
-r-xr-xr-x. 1 root root 5172 Nov 14 15:54 masterha_secondary_check
-r-xr-xr-x. 1 root root 1739 Nov 14 15:54 masterha_stop
-r-xr-xr-x. 1 root root 8337 Nov 14 15:52 purge_relay_logs
-r-xr-xr-x. 1 root root 7525 Nov 14 15:52 save_binary_logs

Copy related scripts to /etc/mha/scripts Catalog ( After the package is decompressed, there is , It's not necessary , Because these scripts are incomplete , I need to modify myself , This is what software development leaves to us , If you turn on any of the following script's parameters , And the script here hasn't been modified , It's a mistake , I'm in a terrible situation )

mkdir -p /data/mha/3306/log
mkdir -p /etc/mha/scripts
cp /data/mha4mysql-manager-master/samples/scripts/* /etc/mha/scripts
[root@proxysql 3306]# ll /etc/mha/scripts
total 36
-rwxr-xr-x. 1 root root 150 Nov 14 17:53 manager.sh
-rwxr-xr-x. 1 root root 2169 Nov 14 17:27 master_ip_failover # When switching automatically vip Managed scripts , It's not necessary , If we use keepalived Of , We can write our own scripts to complete vip Management of , For example monitoring mysql, If mysql abnormal , Let's stop keepalived Just go , such vip It will drift automatically
-rwxr-xr-x. 1 root root 10369 Nov 14 17:38 master_ip_online_change # When switching online vip Management of , It's not necessary , You can also write your own simple shell complete
-rwxr-xr-x. 1 root root 11867 Nov 14 16:41 power_manager # Script to shut down the host after the failure , It's not necessary
-rwxr-xr-x. 1 root root 1360 Nov 14 16:41 send_report # Because the script that sends the alarm after the failover , It's not necessary , You can write your own simple shell complete .

To configure /etc/masterha_default.cnf

cat >> /etc/masterha_default.cnf << EOF
[server default]
user=root
password=abc123
ssh_user=root
repl_user=repl
repl_password=repl
ping_interval=1
#master_binlog_dir= /var/lib/mysql,/var/log/mysql
secondary_check_script=masterha_secondary_check -s 192.168.0.101 -s 192.168.0.102 -s 192.168.0.103
master_ip_failover_script="/etc/mha/scripts/master_ip_failover"
master_ip_online_change_script="/etc/mha/scripts/master_ip_online_change"
report_script="/etc/mha/scripts/send_report"
EOF

To configure /data/mha/3306/mha.cnf

cat >>/data/mha/3306/mha.cnf << EOF
[server default]
manager_workdir=/data/mha/3306
manager_log=/data/mha/3306/manager.log [server1]
hostname=192.168.0.101
candidate_master=1
master_binlog_dir="/usr/local/mysql/data" [server2]
hostname=192.168.0.102
candidate_master=1
master_binlog_dir="/usr/local/mysql/data" [server3]
hostname=192.168.0.103
master_binlog_dir="/usr/local/mysql/data"
# There is no chance to be master
no_master=1
EOF
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

To configure /etc/mha/scripts/master_ip_failover

cat >> /etc/mha/scripts/master_ip_failover << EOF
#!/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.0.88/24'; # Set up VIP
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip"; # Pay attention to the network card , Rewrite according to the actual situation
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down"; # Pay attention to the network card , Rewrite according to the actual situation 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" ) { 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" ) { 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";
exit 0;
}
else {
&usage();
exit 1;
}
} sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
return 0 unless ($ssh_user);
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
} sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
EOF

To configure /etc/mha/scripts/master_ip_online_change

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

To configure /etc/mha/scripts/send_report

cat >> /etc/mha/scripts/send_report << EOF
#!/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 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 );
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,
); # Do whatever you want here exit 0; EOF

Set up relay log How to clean it up ( At every slave Node ):

[root@s1 ~]# mysql -e 'set global relay_log_purge=0'
[root@s2 ~]# mysql -e 'set global relay_log_purge=0'

Be careful :

MHA In the process of switching , Recovery from the library depends on relay log Information about , So here's going to be relay log The auto clear of is set to OFF, Use manual cleaning relay log The way . By default , The relay log from the server will be in SQL The thread is automatically deleted after execution . But in MHA Environment , These relay logs may be used to recover other slave servers , Therefore, it is necessary to disable the automatic deletion function of relay logs . The problem of replication latency needs to be considered when clearing relay logs regularly . stay ext3 Under the file system of , It takes time to delete large files , Can cause severe replication latency . To avoid replication latency , You need to create a hard link for the relay log temporarily , Because in linux In the system, it is very fast to delete large files through hard links .( stay mysql In the database , When deleting a large table , Hard links are also used )

MHA The node contains pure_relay_logs Command tool , It can create hard links for relay logs , perform SET GLOBAL relay_log_purge=1, Wait a few seconds for SQL Thread switches to new relay log , Re execution SET GLOBAL relay_log_purge=0.

pure_relay_logs The script parameters are as follows :

--user mysql user name 
--password mysql password
--port Port number
--workdir Specify create relay log The location of the hard link , The default is /var/tmp, Due to different partitions of the system, creating hard link files will fail , Therefore, it is necessary to perform hard link specific location , After successfully executing the script , Hard linked relay log files are deleted
--disable_relay_log_purge By default , If relay_log_purge=1, Scripts don't clean up anything , Automatically quit , By setting this parameter , When relay_log_purge=1 In this case relay_log_purge Set to 0. clear relay log after , Finally, set the parameter to OFF.

Set up regular cleanup relay Script ( Two sets of slave The server )

cat >> purge_relay_log.sh << EOF
#!/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
fi $purge --user=$user --password=$passwd --disable_relay_log_purge --port=$port --workdir=$work_dir >> $log_dir/purge_relay_logs.log 2>&1
EOF

Add to crontab Do it regularly

[root@s1 ~]# crontab -l
0 4 * * * /bin/bash /root/purge_relay_log.sh

purge_relay_logs Script delete relay log will not block SQL Threads . Now let's do it manually to see what happens .

[root@s1 ~]# purge_relay_logs --user=root --password=123456 --port=3306 -disable_relay_log_purge --workdir=/data/
2014-04-20 15:47:24: purge_relay_logs script started.
Found relay_log.info: /data/mysql/relay-log.info
Removing hard linked relay log files server03-relay-bin* under /data/.. done.
Current relay log file: /data/mysql/server03-relay-bin.000002
Archiving unused relay log files (up to /data/mysql/server03-relay-bin.000001) ...
Creating hard link for /data/mysql/server03-relay-bin.000001 under /data//server03-relay-bin.000001 .. ok.
Creating hard links for unused relay log files completed.
Executing SET GLOBAL relay_log_purge=1; FLUSH LOGS; sleeping a few seconds so that SQL thread can delete older relay log files (if it keeps up); SET GLOBAL relay_log_purge=0; .. ok.
Removing hard linked relay log files server03-relay-bin* under /data/.. done.
2014-04-20 15:47:27: All relay log purging operations succeeded.

Check SSH To configure

Check MHA Manger To all MHA Node Of SSH Connection status

[root@proxysql 3306]# masterha_check_ssh --conf=/data/mha/3306/mha.cnf
Fri Nov 16 11:26:15 2018 - [info] Reading default configuration from /etc/masterha_default.cnf..
Fri Nov 16 11:26:15 2018 - [info] Reading application default configuration from /data/mha/3306/mha.cnf..
Fri Nov 16 11:26:15 2018 - [info] Reading server configuration from /data/mha/3306/mha.cnf..
Fri Nov 16 11:26:15 2018 - [info] Starting SSH connection tests..
Fri Nov 16 11:26:16 2018 - [debug]
Fri Nov 16 11:26:15 2018 - [debug] Connecting via SSH from root@192.168.0.101(192.168.0.101:22) to root@192.168.0.102(192.168.0.102:22)..
Fri Nov 16 11:26:16 2018 - [debug] ok.
Fri Nov 16 11:26:16 2018 - [debug] Connecting via SSH from root@192.168.0.101(192.168.0.101:22) to root@192.168.0.103(192.168.0.103:22)..
Fri Nov 16 11:26:16 2018 - [debug] ok.
Fri Nov 16 11:26:17 2018 - [debug]
Fri Nov 16 11:26:16 2018 - [debug] Connecting via SSH from root@192.168.0.102(192.168.0.102:22) to root@192.168.0.101(192.168.0.101:22)..
Fri Nov 16 11:26:16 2018 - [debug] ok.
Fri Nov 16 11:26:16 2018 - [debug] Connecting via SSH from root@192.168.0.102(192.168.0.102:22) to root@192.168.0.103(192.168.0.103:22)..
Fri Nov 16 11:26:16 2018 - [debug] ok.
Fri Nov 16 11:26:18 2018 - [debug]
Fri Nov 16 11:26:16 2018 - [debug] Connecting via SSH from root@192.168.0.103(192.168.0.103:22) to root@192.168.0.101(192.168.0.101:22)..
Fri Nov 16 11:26:17 2018 - [debug] ok.
Fri Nov 16 11:26:17 2018 - [debug] Connecting via SSH from root@192.168.0.103(192.168.0.103:22) to root@192.168.0.102(192.168.0.102:22)..
Fri Nov 16 11:26:17 2018 - [debug] ok.
Fri Nov 16 11:26:18 2018 - [info] All SSH connection tests passed successfully.

Check the entire replication environment

adopt masterha_check_repl The script looks at the status of the entire cluster

[root@proxysql 3306]# masterha_check_repl --conf=/data/mha/3306/mha.cnf
Fri Nov 16 11:27:25 2018 - [info] Reading default configuration from /etc/masterha_default.cnf..
Fri Nov 16 11:27:25 2018 - [info] Reading application default configuration from /data/mha/3306/mha.cnf..
Fri Nov 16 11:27:25 2018 - [info] Reading server configuration from /data/mha/3306/mha.cnf..
Fri Nov 16 11:27:25 2018 - [info] MHA::MasterMonitor version 0.58.
Fri Nov 16 11:27:26 2018 - [info] GTID failover mode = 0
Fri Nov 16 11:27:26 2018 - [info] Dead Servers:
Fri Nov 16 11:27:26 2018 - [info] Alive Servers:
Fri Nov 16 11:27:26 2018 - [info] 192.168.0.101(192.168.0.101:3306)
Fri Nov 16 11:27:26 2018 - [info] 192.168.0.102(192.168.0.102:3306)
Fri Nov 16 11:27:26 2018 - [info] 192.168.0.103(192.168.0.103:3306)
Fri Nov 16 11:27:26 2018 - [info] Alive Slaves:
Fri Nov 16 11:27:26 2018 - [info] 192.168.0.102(192.168.0.102:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Fri Nov 16 11:27:26 2018 - [info] Replicating from 192.168.0.101(192.168.0.101:3306)
Fri Nov 16 11:27:26 2018 - [info] Primary candidate for the new Master (candidate_master is set)
Fri Nov 16 11:27:26 2018 - [info] 192.168.0.103(192.168.0.103:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Fri Nov 16 11:27:26 2018 - [info] Replicating from 192.168.0.101(192.168.0.101:3306)
Fri Nov 16 11:27:26 2018 - [info] Not candidate for the new Master (no_master is set)
Fri Nov 16 11:27:26 2018 - [info] Current Alive Master: 192.168.0.101(192.168.0.101:3306)
Fri Nov 16 11:27:26 2018 - [info] Checking slave configurations..
Fri Nov 16 11:27:26 2018 - [info] Checking replication filtering settings..
Fri Nov 16 11:27:26 2018 - [info] binlog_do_db= , binlog_ignore_db=
Fri Nov 16 11:27:26 2018 - [info] Replication filtering check ok.
Fri Nov 16 11:27:26 2018 - [info] GTID (with auto-pos) is not supported
Fri Nov 16 11:27:26 2018 - [info] Starting SSH connection tests..
Fri Nov 16 11:27:29 2018 - [info] All SSH connection tests passed successfully.
Fri Nov 16 11:27:29 2018 - [info] Checking MHA Node version..
Fri Nov 16 11:27:29 2018 - [info] Version check ok.
Fri Nov 16 11:27:29 2018 - [info] Checking SSH publickey authentication settings on the current master..
Fri Nov 16 11:27:29 2018 - [info] HealthCheck: SSH to 192.168.0.101 is reachable.
Fri Nov 16 11:27:30 2018 - [info] Master MHA Node version is 0.58.
Fri Nov 16 11:27:30 2018 - [info] Checking recovery script configurations on 192.168.0.101(192.168.0.101:3306)..
Fri Nov 16 11:27:30 2018 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/usr/local/mysql/data --output_file=/var/tmp/save_binary_logs_test --manager_version=0.58 --start_file=master-bin.000003
Fri Nov 16 11:27:30 2018 - [info] Connecting to root@192.168.0.101(192.168.0.101:22)..
Creating /var/tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /usr/local/mysql/data, up to master-bin.000003
Fri Nov 16 11:27:30 2018 - [info] Binlog setting check done.
Fri Nov 16 11:27:30 2018 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Fri Nov 16 11:27:30 2018 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.0.102 --slave_ip=192.168.0.102 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.23-log --manager_version=0.58 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx
Fri Nov 16 11:27:30 2018 - [info] Connecting to root@192.168.0.102(192.168.0.102:22)..
Checking slave recovery environment settings..
Opening /usr/local/mysql/data/relay-log.info ... ok.
Relay log found at /usr/local/mysql/data, up to slave-relay-bin.000002
Temporary relay log file is /usr/local/mysql/data/slave-relay-bin.000002
Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Fri Nov 16 11:27:30 2018 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.0.103 --slave_ip=192.168.0.103 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.23-log --manager_version=0.58 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx
Fri Nov 16 11:27:30 2018 - [info] Connecting to root@192.168.0.103(192.168.0.103:22)..
Checking slave recovery environment settings..
Opening /usr/local/mysql/data/relay-log.info ... ok.
Relay log found at /usr/local/mysql/data, up to slave-relay-bin.000002
Temporary relay log file is /usr/local/mysql/data/slave-relay-bin.000002
Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Fri Nov 16 11:27:30 2018 - [info] Slaves settings check done.
Fri Nov 16 11:27:30 2018 - [info]
192.168.0.101(192.168.0.101:3306) (current master)
+--192.168.0.102(192.168.0.102:3306)
+--192.168.0.103(192.168.0.103:3306) Fri Nov 16 11:27:30 2018 - [info] Checking replication health on 192.168.0.102..
Fri Nov 16 11:27:30 2018 - [info] ok.
Fri Nov 16 11:27:30 2018 - [info] Checking replication health on 192.168.0.103..
Fri Nov 16 11:27:30 2018 - [info] ok.
Fri Nov 16 11:27:30 2018 - [info] Checking master_ip_failover_script status:
Fri Nov 16 11:27:30 2018 - [info] /etc/mha/scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.0.101 --orig_master_ip=192.168.0.101 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 192.168.0.88/24=== Checking the Status of the script.. OK
Fri Nov 16 11:27:30 2018 - [info] OK.
Fri Nov 16 11:27:30 2018 - [warning] shutdown_script is not defined.
Fri Nov 16 11:27:30 2018 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.
[root@proxysql 3306]#

Check MHA Manager The state of

[root@proxysql 3306]# masterha_check_status --conf=/data/mha/3306/mha.cnf
mha is stopped(2:NOT_RUNNING).

Turn on MHA Manager monitor

vi manager.sh
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /data/mha/3306/manager.log 2>&1 & ./manager.sh

Introduction to startup parameters :

--remove_dead_master_conf This parameter represents when a master-slave switch occurs , Old master library ip Will be removed from the configuration file .

--manger_log Log storage location

--ignore_last_failover By default , If MHA Continuous downtime detected , And the interval between two downtime is not enough 8 In an hour , It won't go on Failover, The reason for this restriction is to avoid ping-pong effect . This parameter means to ignore the last MHA Trigger the file generated by switching , By default ,MHA After switching, it will be in the log directory , That's what I set up above /data produce app1.failover.complete file , The next time you switch again, if you find the file in the directory, you will not be allowed to trigger the switch , Unless you receive a delete file after the first switch , For convenience , I'm going to set it to --ignore_last_failover.

To configure VIP

vip There are two ways to configure , A through keepalived The way to manage virtual ip Floating of ; Another way is to start the virtual machine by script ip The way ( I.e. not required keepalived perhaps heartbeat Similar software ).

( One ) Use script management VIP The way ,  modify master_ip_failover Script , Use script management VIP:

By configuring /etc/mha/scripts/master_ip_failover Realization

( Two )keepalived How to manage virtual ip

Download the software and install it ( Two sets of master, To be exact, one is master, The other is an alternative master, Before switching, it was slave):

[root@master ~]# wget http://www.keepalived.org/software/keepalived-1.2.12.tar.gz
tar xf keepalived-1.2.12.tar.gz
cd keepalived-1.2.12
./configure --prefix=/usr/local/keepalived
make && make install
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
mkdir /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

(2) To configure keepalived Configuration file for , stay master On the configuration (192.168.0.101)

[root@master ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived global_defs {
notification_email {
saltstack@163.com
}
notification_email_from dba@dbserver.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-HA
} vrrp_instance VI_1 {
state BACKUP
interface eth1
virtual_router_id 51
priority 150
advert_int 1
nopreempt authentication {
auth_type PASS
auth_pass 1111
} virtual_ipaddress {
192.168.0.88
}
}

among router_id MySQL HA Indicates the setting keepalived The name of the group , take 192.168.0.88 This virtual ip Bound to the eth1 NIC , And set the state to backup Pattern , take keepalived The mode of is set to non preemptive mode (nopreempt),priority 150 Indicates that the priority set is 150. The following configuration is slightly different , But they all mean the same thing .
In the candidate master On the configuration (192.168.0.60)

[root@s1 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived global_defs {
notification_email {
saltstack@163.com
}
notification_email_from dba@dbserver.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-HA
} vrrp_instance VI_1 {
state BACKUP
interface eth1
virtual_router_id 51
priority 120
advert_int 1
nopreempt authentication {
auth_type PASS
auth_pass 1111
} virtual_ipaddress {
192.168.0.88
}
}

(3) start-up keepalived service , stay master Start and view logs on

[root@master ~]# /etc/init.d/keepalived start
Starting keepalived: [ OK ]
[root@master ~]# tail -f /var/log/messages
Apr 20 20:22:16 192 Keepalived_healthcheckers[15334]: Opening file '/etc/keepalived/keepalived.conf'.
Apr 20 20:22:16 192 Keepalived_healthcheckers[15334]: Configuration is using : 7231 Bytes
Apr 20 20:22:16 192 kernel: IPVS: Connection hash table configured (size=4096, memory=64Kbytes)
Apr 20 20:22:16 192 kernel: IPVS: ipvs loaded.
Apr 20 20:22:16 192 Keepalived_healthcheckers[15334]: Using LinkWatch kernel netlink reflector...
Apr 20 20:22:19 192 Keepalived_vrrp[15335]: VRRP_Instance(VI_1) Transition to MASTER STATE
Apr 20 20:22:20 192 Keepalived_vrrp[15335]: VRRP_Instance(VI_1) Entering MASTER STATE
Apr 20 20:22:20 192 Keepalived_vrrp[15335]: VRRP_Instance(VI_1) setting protocol VIPs.
Apr 20 20:22:20 192 Keepalived_vrrp[15335]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88
Apr 20 20:22:20 192 Keepalived_healthcheckers[15334]: Netlink reflector reports IP 192.168.0.88 added
Apr 20 20:22:25 192 Keepalived_vrrp[15335]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88

Discovery has been virtual ip 192.168.0.88 The network card is bound eth1 On .
(4) Look at the binding

[root@master ~]# ip addr | grep eth1
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
inet 192.168.0.50/24 brd 192.168.0.255 scope global eth1
inet 192.168.0.88/32 scope global eth1

On another server , The candidate master Start the keepalived service , And observe

[root@s1 ~]# /etc/init.d/keepalived start ; tail -f /var/log/messages
Starting keepalived: [ OK ]
Apr 20 20:26:18 192 Keepalived_vrrp[9472]: Registering gratuitous ARP shared channel
Apr 20 20:26:18 192 Keepalived_vrrp[9472]: Opening file '/etc/keepalived/keepalived.conf'.
Apr 20 20:26:18 192 Keepalived_vrrp[9472]: Configuration is using : 62976 Bytes
Apr 20 20:26:18 192 Keepalived_vrrp[9472]: Using LinkWatch kernel netlink reflector...
Apr 20 20:26:18 192 Keepalived_vrrp[9472]: VRRP_Instance(VI_1) Entering BACKUP STATE
Apr 20 20:26:18 192 Keepalived_vrrp[9472]: VRRP sockpool: [ifindex(3), proto(112), unicast(0), fd(10,11)]
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Netlink reflector reports IP 192.168.80.138 added
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Netlink reflector reports IP 192.168.0.60 added
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Netlink reflector reports IP fe80::20c:29ff:fe9d:6a9e added
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Netlink reflector reports IP fe80::20c:29ff:fe9d:6aa8 added
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Registering Kernel netlink reflector
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Registering Kernel netlink command channel
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Opening file '/etc/keepalived/keepalived.conf'.
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Configuration is using : 7231 Bytes
Apr 20 20:26:18 192 kernel: IPVS: Registered protocols (TCP, UDP, AH, ESP)
Apr 20 20:26:18 192 kernel: IPVS: Connection hash table configured (size=4096, memory=64Kbytes)
Apr 20 20:26:18 192 kernel: IPVS: ipvs loaded.
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Using LinkWatch kernel netlink reflector...

As you can see from the information above keepalived Configured successfully .
Be careful :

The two servers above keepalived It's all set to BACKUP Pattern , stay keepalived in 2 Patterns , Namely master->backup Patterns and backup->backup Pattern . There's a big difference between the two models . stay master->backup In mode , Once the main database goes down , fictitious ip It will automatically drift to the slave library , When the main library is repaired ,keepalived After starting , And I'll put the virtual ip Grab it , Even if non preemptive mode is set (nopreempt) preemption ip It's going to happen . stay backup->backup In mode , When the main library goes down, virtual ip It will automatically drift to the slave library , When the original main library is restored and keepalived After the service starts , It doesn't preempt the new owner's virtual ip, Even if the priority is higher than that of the slave Library , There will be no preemption . In order to reduce the ip The number of drifts , Usually, the repaired primary database is used as a new standby database .

(5)MHA introduce keepalived(MySQL When the service process hangs up, it passes MHA stop it keepalived):

Want to put keepalived Service Introduction MHA, We just need to modify the script file where the switch is triggered master_ip_failover that will do , In this script, add in master In the event of an outage keepalived To deal with .

Edit script /usr/local/bin/master_ip_failover, Revised as follows , I am right. perl Not familiar with , So I post the script here in its entirety ( Operation on main database ,192.168.0.50).

stay MHA Manager The content of the modified script is as follows ( There are few references ):

#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all'; use Getopt::Long;

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.0.88/24';
my $ssh_start_vip = "/etc/init.d/keepalived start";
my $ssh_stop_vip = "/etc/init.d/keepalived stop";

my $ssh_user = "root";
my $new_master_password='abc123';
my $orig_master_password='abc123';

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" ) { 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" ) { 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\@cluster1 \" $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() {
return 0  unless  ($ssh_user);
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
} sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}

Now the script has been modified , Let's now turn on the parameters mentioned above , Check the cluster status again , See if there's a mistake .

[root@192.168.0.20 ~]# grep 'master_ip_failover_script' /etc/masterha/app1.cnf
master_ip_failover_script= /usr/local/bin/master_ip_failover
[root@192.168.0.20 ~]#
[root@proxysql 3306]# masterha_check_repl --conf=/data/mha/3306/mha.cnf
Fri Nov 16 14:42:25 2018 - [info] Reading default configuration from /etc/masterha_default.cnf..
Fri Nov 16 14:42:25 2018 - [info] Reading application default configuration from /data/mha/3306/mha.cnf..
Fri Nov 16 14:42:25 2018 - [info] Reading server configuration from /data/mha/3306/mha.cnf..
Fri Nov 16 14:42:25 2018 - [info] MHA::MasterMonitor version 0.58.
Fri Nov 16 14:42:26 2018 - [info] GTID failover mode = 0
Fri Nov 16 14:42:26 2018 - [info] Dead Servers:
Fri Nov 16 14:42:26 2018 - [info] Alive Servers:
Fri Nov 16 14:42:26 2018 - [info] 192.168.0.101(192.168.0.101:3306)
Fri Nov 16 14:42:26 2018 - [info] 192.168.0.102(192.168.0.102:3306)
Fri Nov 16 14:42:26 2018 - [info] 192.168.0.103(192.168.0.103:3306)
Fri Nov 16 14:42:26 2018 - [info] Alive Slaves:
Fri Nov 16 14:42:26 2018 - [info] 192.168.0.102(192.168.0.102:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Fri Nov 16 14:42:26 2018 - [info] Replicating from 192.168.0.101(192.168.0.101:3306)
Fri Nov 16 14:42:26 2018 - [info] Primary candidate for the new Master (candidate_master is set)
Fri Nov 16 14:42:26 2018 - [info] 192.168.0.103(192.168.0.103:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Fri Nov 16 14:42:26 2018 - [info] Replicating from 192.168.0.101(192.168.0.101:3306)
Fri Nov 16 14:42:26 2018 - [info] Not candidate for the new Master (no_master is set)
Fri Nov 16 14:42:26 2018 - [info] Current Alive Master: 192.168.0.101(192.168.0.101:3306)
Fri Nov 16 14:42:26 2018 - [info] Checking slave configurations..
Fri Nov 16 14:42:26 2018 - [info] Checking replication filtering settings..
Fri Nov 16 14:42:26 2018 - [info] binlog_do_db= , binlog_ignore_db=
Fri Nov 16 14:42:26 2018 - [info] Replication filtering check ok.
Fri Nov 16 14:42:26 2018 - [info] GTID (with auto-pos) is not supported
Fri Nov 16 14:42:26 2018 - [info] Starting SSH connection tests..
Fri Nov 16 14:42:29 2018 - [info] All SSH connection tests passed successfully.
Fri Nov 16 14:42:29 2018 - [info] Checking MHA Node version..
Fri Nov 16 14:42:29 2018 - [info] Version check ok.
Fri Nov 16 14:42:29 2018 - [info] Checking SSH publickey authentication settings on the current master..
Fri Nov 16 14:42:29 2018 - [info] HealthCheck: SSH to 192.168.0.101 is reachable.
Fri Nov 16 14:42:29 2018 - [info] Master MHA Node version is 0.58.
Fri Nov 16 14:42:29 2018 - [info] Checking recovery script configurations on 192.168.0.101(192.168.0.101:3306)..
Fri Nov 16 14:42:29 2018 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/usr/local/mysql/data --output_file=/var/tmp/save_binary_logs_test --manager_version=0.58 --start_file=master-bin.000003
Fri Nov 16 14:42:29 2018 - [info] Connecting to root@192.168.0.101(192.168.0.101:22)..
Creating /var/tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /usr/local/mysql/data, up to master-bin.000003
Fri Nov 16 14:42:29 2018 - [info] Binlog setting check done.
Fri Nov 16 14:42:29 2018 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Fri Nov 16 14:42:29 2018 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.0.102 --slave_ip=192.168.0.102 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.23-log --manager_version=0.58 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx
Fri Nov 16 14:42:29 2018 - [info] Connecting to root@192.168.0.102(192.168.0.102:22)..
Checking slave recovery environment settings..
Opening /usr/local/mysql/data/relay-log.info ... ok.
Relay log found at /usr/local/mysql/data, up to slave-relay-bin.000002
Temporary relay log file is /usr/local/mysql/data/slave-relay-bin.000002
Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Fri Nov 16 14:42:30 2018 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.0.103 --slave_ip=192.168.0.103 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.23-log --manager_version=0.58 --relay_log_info=/usr/local/mysql/data/relay-log.info --relay_dir=/usr/local/mysql/data/ --slave_pass=xxx
Fri Nov 16 14:42:30 2018 - [info] Connecting to root@192.168.0.103(192.168.0.103:22)..
Checking slave recovery environment settings..
Opening /usr/local/mysql/data/relay-log.info ... ok.
Relay log found at /usr/local/mysql/data, up to slave-relay-bin.000002
Temporary relay log file is /usr/local/mysql/data/slave-relay-bin.000002
Checking if super_read_only is defined and turned on.. not present or turned off, ignoring.
Testing mysql connection and privileges..
mysql: [Warning] Using a password on the command line interface can be insecure.
done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Fri Nov 16 14:42:30 2018 - [info] Slaves settings check done.
Fri Nov 16 14:42:30 2018 - [info]
192.168.0.101(192.168.0.101:3306) (current master)
+--192.168.0.102(192.168.0.102:3306)
+--192.168.0.103(192.168.0.103:3306) Fri Nov 16 14:42:30 2018 - [info] Checking replication health on 192.168.0.102..
Fri Nov 16 14:42:30 2018 - [info] ok.
Fri Nov 16 14:42:30 2018 - [info] Checking replication health on 192.168.0.103..
Fri Nov 16 14:42:30 2018 - [info] ok.
Fri Nov 16 14:42:30 2018 - [info] Checking master_ip_failover_script status:
Fri Nov 16 14:42:30 2018 - [info] /etc/mha/scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.0.101 --orig_master_ip=192.168.0.101 --orig_master_port=3306 IN SCRIPT TEST====/etc/init.d/keepalived stop==/etc/init.d/keepalived start=== Checking the Status of the script.. OK
Fri Nov 16 14:42:30 2018 - [info] OK.
Fri Nov 16 14:42:30 2018 - [warning] shutdown_script is not defined.
Fri Nov 16 14:42:30 2018 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.

As you can see, there is no error reported . ha-ha
 /usr/local/bin/master_ip_failover The content added or modified means that when the main database fails , Will trigger MHA Switch ,MHA Manager It's going to stop the main library keepalived service , Trigger virtual ip Drift to alternative slave Library , So as to complete the switch . Of course, it can be in keepalived Script is introduced into it , This script monitors mysql Is it working , If it's not normal , Call the script to kill keepalived process .

Common commands

 Check the entire replication environment 
masterha_check_repl --conf=/data/mha/3306/mha.cnf Check SSH To configure
masterha_check_ssh --conf=/data/mha/3306/mha.cnf Detect current MHA Running state
masterha_check_status --conf=/data/mha/3306/mha.cnf start-up MHA
vi manager.sh
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /data/mha/3306/manager.log 2>&1 & stop it MHA
masterha_stop --conf=/data/mha/3306/mha.cnf

That's all MHA The whole process of installation and configuration , Here's a simple test .

(1)failover test

Manual kill 了 master above mysqld process , Check the switch status

The log needs to be completed

The above is the whole log process of switching , We can see MHA Switching mainly goes through the following steps :

1. Profile check phase , This stage will check the configuration of the entire cluster configuration file

2. The downtime master Handle , This stage includes virtual ip Removal operation , Host shutdown operation ( I haven't realized this yet , Need to study )

3. Copy dead maste And the latest slave Differential relay log, And save to MHA Manger Specific directory

4. Identify those with the latest updates slave

5. Application from master Saved binary log events (binlog events)

6. Upgrade one slave For the new master replicate

7. Make others slave Connect new master replicate

(2) Manual switch

in many instances , You need to migrate the existing master server to another server . For example, the main server hardware failure ,RAID The control card needs to be rebuilt , Move the primary server to a better performance server, etc . Maintaining the primary server causes performance degradation , Causes downtime, at least no data to be written . in addition , Blocking or killing the currently running session will cause data inconsistency between hosts . MHA Provides fast switching and elegant blocking writes , This switching process only needs 0.5-2s Time for , Data cannot be written during this period of time . In many cases ,0.5-2s Blocked writes are acceptable . Therefore, there is no need to plan and allocate maintenance time window to switch the master server .

MHA The general process of online switching :
1. Check the replication settings and determine the current master server
2. Determine the new master server
3. Blocking writes to the current master server
4. Wait for all servers to catch up with replication
5. Grant write to new master server
6. Reset from server

Be careful , When switching online, the application architecture needs to consider the following two issues :

1. Automatic identification master and slave The problem of (master Your machine may switch ), If... Is used vip The way , It can basically solve this problem .

2. The problem of load balancing ( You can define the approximate read / write ratio , The proportion of load that each machine can bear , When a machine leaves the cluster , It needs to be considered )

To ensure complete data consistency , Switch in the fastest time ,MHA In order to switch successfully, the following conditions must be met , Otherwise, the switch will fail .

1. all slave Of IO Threads are all running

2. all slave Of SQL Threads are all running

3. be-all show slave status In the output of Seconds_Behind_Master The parameter is less than or equal to running_updates_limit second , If you do not specify running_updates_limit, So by default running_updates_limit by 1 second .

4. stay master End , adopt show processlist Output , No update takes longer than running_updates_limit second .

Execution switching

[root@manager tmp]# masterha_master_switch --conf=/data/mha/3306/mha.cnf --master_state=alive --new_master_host=192.168.0.102 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
[root@proxysql 3306]# masterha_master_switch --conf=/data/mha/3306/mha.cnf --master_state=alive --new_master_host=192.168.0.102 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
Fri Nov 16 14:43:42 2018 - [info] MHA::MasterRotate version 0.58.
Fri Nov 16 14:43:42 2018 - [info] Starting online master switch..
Fri Nov 16 14:43:42 2018 - [info]
Fri Nov 16 14:43:42 2018 - [info] * Phase 1: Configuration Check Phase..
Fri Nov 16 14:43:42 2018 - [info]
Fri Nov 16 14:43:42 2018 - [info] Reading default configuration from /etc/masterha_default.cnf..
Fri Nov 16 14:43:42 2018 - [info] Reading application default configuration from /data/mha/3306/mha.cnf..
Fri Nov 16 14:43:42 2018 - [info] Reading server configuration from /data/mha/3306/mha.cnf..
Fri Nov 16 14:43:43 2018 - [info] GTID failover mode = 0
Fri Nov 16 14:43:43 2018 - [info] Current Alive Master: 192.168.0.101(192.168.0.101:3306)
Fri Nov 16 14:43:43 2018 - [info] Alive Slaves:
Fri Nov 16 14:43:43 2018 - [info] 192.168.0.102(192.168.0.102:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Fri Nov 16 14:43:43 2018 - [info] Replicating from 192.168.0.101(192.168.0.101:3306)
Fri Nov 16 14:43:43 2018 - [info] Primary candidate for the new Master (candidate_master is set)
Fri Nov 16 14:43:43 2018 - [info] 192.168.0.103(192.168.0.103:3306) Version=5.7.23-log (oldest major version between slaves) log-bin:enabled
Fri Nov 16 14:43:43 2018 - [info] Replicating from 192.168.0.101(192.168.0.101:3306)
Fri Nov 16 14:43:43 2018 - [info] Not candidate for the new Master (no_master is set) It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.0.101(192.168.0.101:3306)? (YES/no): yes
Fri Nov 16 14:43:46 2018 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Fri Nov 16 14:43:46 2018 - [info] ok.
Fri Nov 16 14:43:46 2018 - [info] Checking MHA is not monitoring or doing failover..
Fri Nov 16 14:43:46 2018 - [info] Checking replication health on 192.168.0.102..
Fri Nov 16 14:43:46 2018 - [info] ok.
Fri Nov 16 14:43:46 2018 - [info] Checking replication health on 192.168.0.103..
Fri Nov 16 14:43:46 2018 - [info] ok.
Fri Nov 16 14:43:46 2018 - [info] 192.168.0.102 can be new master.
Fri Nov 16 14:43:46 2018 - [info]
From:
192.168.0.101(192.168.0.101:3306) (current master)
+--192.168.0.102(192.168.0.102:3306)
+--192.168.0.103(192.168.0.103:3306) To:
192.168.0.102(192.168.0.102:3306) (new master)
+--192.168.0.103(192.168.0.103:3306)
+--192.168.0.101(192.168.0.101:3306) Starting master switch from 192.168.0.101(192.168.0.101:3306) to 192.168.0.102(192.168.0.102:3306)? (yes/NO): yes
Fri Nov 16 14:43:48 2018 - [info] Checking whether 192.168.0.102(192.168.0.102:3306) is ok for the new master..
Fri Nov 16 14:43:48 2018 - [info] ok.
Fri Nov 16 14:43:48 2018 - [info] 192.168.0.101(192.168.0.101:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Fri Nov 16 14:43:49 2018 - [info] 192.168.0.101(192.168.0.101:3306): Resetting slave pointing to the dummy host.
Fri Nov 16 14:43:49 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Fri Nov 16 14:43:49 2018 - [info]
Fri Nov 16 14:43:49 2018 - [info] * Phase 2: Rejecting updates Phase..
Fri Nov 16 14:43:49 2018 - [info]
Fri Nov 16 14:43:49 2018 - [info] Executing master ip online change script to disable write on the current master:
Fri Nov 16 14:43:49 2018 - [info] /etc/mha/scripts/master_ip_online_change --command=stop --orig_master_host=192.168.0.101 --orig_master_ip=192.168.0.101 --orig_master_port=3306 --orig_master_user='root' --new_master_host=192.168.0.102 --new_master_ip=192.168.0.102 --new_master_port=3306 --new_master_user='root' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx
Unknown option: orig_master_ssh_user
Unknown option: new_master_ssh_user
Unknown option: orig_master_is_new_slave
Unknown option: orig_master_password
Unknown option: new_master_password
Fri Nov 16 14:43:49 2018 106624 Set read_only on the new master.. ok.
Fri Nov 16 14:43:49 2018 108455 Set read_only=1 on the orig master.. ok.
Disabling the VIP on old master: 192.168.0.101
SIOCSIFFLAGS: Cannot assign requested address
Fri Nov 16 14:43:49 2018 235789 Killing all application threads..
Fri Nov 16 14:43:49 2018 235845 done.
Fri Nov 16 14:43:49 2018 - [info] ok.
Fri Nov 16 14:43:49 2018 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Fri Nov 16 14:43:49 2018 - [info] Executing FLUSH TABLES WITH READ LOCK..
Fri Nov 16 14:43:49 2018 - [info] ok.
Fri Nov 16 14:43:49 2018 - [info] Orig master binlog:pos is master-bin.000003:1521385.
Fri Nov 16 14:43:49 2018 - [info] Waiting to execute all relay logs on 192.168.0.102(192.168.0.102:3306)..
Fri Nov 16 14:43:49 2018 - [info] master_pos_wait(master-bin.000003:1521385) completed on 192.168.0.102(192.168.0.102:3306). Executed 0 events.
Fri Nov 16 14:43:49 2018 - [info] done.
Fri Nov 16 14:43:49 2018 - [info] Getting new master's binlog name and position..
Fri Nov 16 14:43:49 2018 - [info] s1-master-bin.000002:125133505
Fri Nov 16 14:43:49 2018 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.0.102', MASTER_PORT=3306, MASTER_LOG_FILE='s1-master-bin.000002', MASTER_LOG_POS=125133505, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Fri Nov 16 14:43:49 2018 - [info] Executing master ip online change script to allow write on the new master:
Fri Nov 16 14:43:49 2018 - [info] /etc/mha/scripts/master_ip_online_change --command=start --orig_master_host=192.168.0.101 --orig_master_ip=192.168.0.101 --orig_master_port=3306 --orig_master_user='root' --new_master_host=192.168.0.102 --new_master_ip=192.168.0.102 --new_master_port=3306 --new_master_user='root' --orig_master_ssh_user=root --new_master_ssh_user=root --orig_master_is_new_slave --orig_master_password=xxx --new_master_password=xxx
Unknown option: orig_master_ssh_user
Unknown option: new_master_ssh_user
Unknown option: orig_master_is_new_slave
Unknown option: orig_master_password
Unknown option: new_master_password
Fri Nov 16 14:43:49 2018 311429 Set read_only=0 on the new master.
Enabling the VIP - 192.168.0.88/24 on the new master - 192.168.0.102
Fri Nov 16 14:43:49 2018 - [info] ok.
Fri Nov 16 14:43:49 2018 - [info]
Fri Nov 16 14:43:49 2018 - [info] * Switching slaves in parallel..
Fri Nov 16 14:43:49 2018 - [info]
Fri Nov 16 14:43:49 2018 - [info] -- Slave switch on host 192.168.0.103(192.168.0.103:3306) started, pid: 19513
Fri Nov 16 14:43:49 2018 - [info]
Fri Nov 16 14:43:50 2018 - [info] Log messages from 192.168.0.103 ...
Fri Nov 16 14:43:50 2018 - [info]
Fri Nov 16 14:43:49 2018 - [info] Waiting to execute all relay logs on 192.168.0.103(192.168.0.103:3306)..
Fri Nov 16 14:43:49 2018 - [info] master_pos_wait(master-bin.000003:1521385) completed on 192.168.0.103(192.168.0.103:3306). Executed 0 events.
Fri Nov 16 14:43:49 2018 - [info] done.
Fri Nov 16 14:43:49 2018 - [info] Resetting slave 192.168.0.103(192.168.0.103:3306) and starting replication from the new master 192.168.0.102(192.168.0.102:3306)..
Fri Nov 16 14:43:49 2018 - [info] Executed CHANGE MASTER.
Fri Nov 16 14:43:49 2018 - [info] Slave started.
Fri Nov 16 14:43:50 2018 - [info] End of log messages from 192.168.0.103 ...
Fri Nov 16 14:43:50 2018 - [info]
Fri Nov 16 14:43:50 2018 - [info] -- Slave switch on host 192.168.0.103(192.168.0.103:3306) succeeded.
Fri Nov 16 14:43:50 2018 - [info] Unlocking all tables on the orig master:
Fri Nov 16 14:43:50 2018 - [info] Executing UNLOCK TABLES..
Fri Nov 16 14:43:50 2018 - [info] ok.
Fri Nov 16 14:43:50 2018 - [info] Starting orig master as a new slave..
Fri Nov 16 14:43:50 2018 - [info] Resetting slave 192.168.0.101(192.168.0.101:3306) and starting replication from the new master 192.168.0.102(192.168.0.102:3306)..
Fri Nov 16 14:43:50 2018 - [info] Executed CHANGE MASTER.
Fri Nov 16 14:43:50 2018 - [info] Slave started.
Fri Nov 16 14:43:50 2018 - [info] All new slave servers switched successfully.
Fri Nov 16 14:43:50 2018 - [info]
Fri Nov 16 14:43:50 2018 - [info] * Phase 5: New master cleanup phase..
Fri Nov 16 14:43:50 2018 - [info]
Fri Nov 16 14:43:50 2018 - [info] 192.168.0.102: Resetting slave info succeeded.
Fri Nov 16 14:43:50 2018 - [info] Switching master to 192.168.0.102(192.168.0.102:3306) completed successfully.

The parameter means :

--orig_master_is_new_slave Adding this parameter when switching is to change the original master Turn into slave node , If you don't add this parameter , The original master Will not start

--running_updates_limit=10000, When you fail over , The candidate master If there's a delay , mha Switch failed , With this parameter, the delay can be switched within this time range ( Unit is s), But the switching time is determined by recover when relay The size of the log determines

Reference source :

http://www.cnblogs.com/gomysql/p/3675429.html

http://www.178linux.com/61111

https://www.cnblogs.com/rayment/p/7355093.html

MySQL And MHA + ProxySQL + keepalived Read and write separation , High availability ( One ) More articles about

  1. MySQL And MHA + ProxySQL + keepalived Read and write separation , High availability ( Two )

    ProxySQL install yum/rpm install stay github Or on the official website rpm package ,wiki Of Getting start The chapter has a detailed introduction . cat <<EOF | tee /etc/yum.r ...

  2. MySQL And MHA + ProxySQL + keepalived Read and write separation , High availability ( 3、 ... and )

    Set up Keepalived VIP Switch email alerts modify keepalived.conf To configure : [root@server01 keepalived]# cat keepalived.conf ! Config ...

  3. MHA+ProxySQL Achieve read-write separation and high availability

    Recent research ProxySQL, I think it's pretty good , So it was a simple toss ,ProxySQL So far Percona In a recommended read-write separation middleware . For a detailed introduction, please refer to the official document .https://github.c ...

  4. MHA+ProxySQL Read write separation high availability

    The document structure is as follows : 1.ProxySQL explain ProxySQL yes mysql A middleware product of , It's flexible mysql Broker layer , Can achieve read-write separation , Support query The function of the router , Support dynamic specification sql Cache , Support dynamic plus ...

  5. MySQL for OPS 09:MHA + Atlas Achieve read-write separation and high availability

    Let me write it out front The front did MHA High availability , But there is a problem , We spent it. 4 Taiwan machine , But in the end, it's just one , Main library . In this way, the utilization rate of hardware is 25%, This means that unless something goes wrong , Otherwise, the other machines are all furnishings . The obvious ...

  6. MySQL Middleware ProxySQL(10): The methodology of separation of reading and writing

    return ProxySQL Series articles :http://www.cnblogs.com/f-ck-need-u/p/7586194.html 1. Different types of read write separation The most basic function of database middleware is to realize the separation of reading and writing ,Pr ...

  7. MySQL Read write separation, high availability cluster and read load balancing (Centos7)

    Catalog summary keepalived and heartbeat contrast One . Environmental Science Two . Deploy Deploy lvs Agents and keepalived MySQL+heartbeat+drbd Deployment of MySQL Master slave copy web Server and a ...

  8. mysql Read / write separation [ High availability ]

    seeing the name of a thing one thinks of its function , stay mysql There are many ways in load balancing , I am dull. , Only know about driver middleware and mysql_proxy Two ways , For the drive , Use of is ReplicationDriver, For details, please see this article of yuange : MySQL ...

  9. Atlas Read / write separation [ High availability ]

    Atlas Download address : https://github.com/Qihoo360/Atlas/releases Atlas It's out of 360 Of , Than mysql-proxy A more stable , It's easier to deploy . Environmental Science : pr ...

Random recommendation

  1. Java The way to learn note taking

    Overloaded methods must have different parameter lists . Methods cannot be overloaded based on different modifiers or return value types . package welcome; public class TestMethodOverloading { public ...

  2. Arc Engine Loading and processing of data

    1. load Shapefile data IWorkspaceFactory pWorkspaceFactory; IFeatureWorkspace pFeatureWorkspace; IFeatureLa ...

  3. CentOS yum install Apache + PHP + Tomcat7 + MySQL

    Linux Most used on the platform web The environment is php.java and MySQL 了 , Will build this environment , You can run a lot of open source programs . As a procedural ape , Although you don't need to be proficient in operation and maintenance , But basically Linux It's better to master the environment construction , ...

  4. 100722B

    stay stack Lining set, And then every time according to his operation , stay set In the operation , Put the brackets hash, Insert , Output set Of size-1 #include<iostream> #include<set> ...

  5. take Qt 5.6 Integrate into VS2015

    Abstract : because VS2015 No longer supported addin, So we have to use other means . Here is given 64 The installation steps under bit system ,32 Bit is similar to . One . install VS2015 The process is brief . It's worth noting that you have to choose what you want to install , Now that we need to use it Qt, that C++ phase ...

  6. unity3d Script life cycle (MonoBehaviour lifecycle)

    I'm doing a little example recently , The discovery class inherits from MonoBehaviour Class , There are many ways , So it's inevitable to ask a question : So many ways , What is the order of execution ? How is internal management conducted ? So I found a lot of information on the Internet , Found out Ri ...

  7. esp8266 Taste fresh

    Please add the current user to dialout Group , Otherwise, it will prompt to open /dev/ttyUSB0 Insufficient authority sudo usermod -a -G dialout `whoami` dmeg View driver installation information dmesg | g ...

  8. Java Connect postgreSQL database , Table not found .

    postgreSQL The database follows SQL standard , Table names and library names are not case sensitive . There is... In the database gongan_address_ALL The table of , But executing the following code will cause an error . stmt = c.createStatemen ...

  9. 【 Notes 】Q The lifting of the restrictions on the No

    I keep a lot of them on weekdays QQ number , There was something wrong with some numbers some time ago , At a time when I couldn't have logged in , And also abnormal operation . result , By QQ The security center is locked in a small dark room , Let me send a text message to get it back . This is the background of the matter , I won't go into details . It's a matter of ...

  10. python Learning notes glob modular

    python There are many class libraries , Now record your study , For future review : 1.glob modular For file name operations , Match the files in the specified directory , Return is the directory and file name , There are two commonly used functions : glob(pattern), Return matched ...