Or through Https://download.csdn.net/download/xxxzhaobb/11151222 download.
1. Basic information of software and hardware
|
OS |
Hostname |
IP |
Master / slave |
Remarks |
|
Redhat 7.4 |
Mysql1 |
192.168.54.230 |
from |
Management node |
|
Redhat 7.4 |
Mysql2 |
192.168.54.231/233 |
main |
VIP address 233 |
|
Redhat 7.4 |
Mysql3 |
192.168.54.232 |
from |
|
2 extract and install mysql
MySQL version mysql-5.7.25-linux-glibc2.12-x86_.tar.gz
Create MySQL users and close the firewall before installing mysql. Because you need to install numactl after decompression.
2.1 create MySQL user
groupadd mysql useradd -d /home/mysql -g mysql -m mysql passwd mysql mkdir -p /mysql/mysql/data
2.2 turn off the firewall
systemctl status firewalld systemctl stop firewalld systemctl disable firewalld systemctl is-enabled firewalld.service
2.3 packages required to install mysql
yum install numactl
2.4 install mysql. Before installation, configure my.cnf file
[root@mysql2 bin]# ./mysqld --initialize-insecure --user=mysql --basedir=/mysql/mysql --datadir=/mysql/mysql/data
2.5 add mysql service
Remove the / etc/my.cnf file first, or rename it.
Add mysql service
cd /mysql/mysql/support-files/ cp mysql.server /etc/init.d/mysql
Edit the datadir, basedir in mysql
Vi /etc/init.d/mysql
The above operations are performed on all three nodes.
3. Build the mysql master-slave architecture.
3.1 create a replication account on all Mysql
create user 'rep1'@'192.168.54.%' identified by 'mysql'; grant replication slave on *.* to 'rep1'@'192.168.54.%'; flush privileges;
3.2 edit the my.cnf configuration file of each Mysql for testing. There are fewer parameters in it. The production environment adds parameters according to the situation.
[mysqld] server_id=3 relay-log-index=slave-relay-bin.index relay-log=slave-relay-bin port=3306 character_set_server=utf8 basedir=/mysql/mysql datadir=/mysql/mysql/data log-bin=mysql1-bin gtid_mode=on enforce-gtid-consistency=true log-slave-updates=true
[mysqld] server_id=1 log_bin=master-bin log_bin-index=master-bin.index port=3306 character_set_server=utf8 basedir=/mysql/mysql datadir=/mysql/mysql/data socket=/tmp/mysql.sock log-bin=mysql2-bin gtid_mode=on enforce-gtid-consistency=true log-slave-updates=true
[mysqld] server_id=2 relay-log-index=slave-relay-bin.index relay-log=slave-relay-bin port=3306 character_set_server=utf8 basedir=/mysql/mysql datadir=/mysql/mysql/data log-bin=mysql3-bin gtid_mode=on enforce-gtid-consistency=true log-slave-updates=true
3.3 enable semi synchronous replication of mysql
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; SET GLOBAL rpl_semi_sync_master_enabled=ON；
3.4 turn on master-slave synchronization and execute on each slave database
change master to master_host='192.168.54.231',master_user='rep1',master_password='mysql',master_auto_position=1;
3.5 start the slave database and check the master-slave status to make sure slave IO running and slave SQL running are Yes.
Start slave； Show master status ; Show slave status \G;
4 install MHA
4.1 configure SSH mutual trust
mkdir ~/.ssh chmod 700 ~/.ssh cd ~/.ssh ssh-keygen -t rsa #Keep returning. ls -al cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys ls -al chmod 600 ~/.ssh/authorized_keys
Then, the contents of the authorized keys file on the three nodes are combined into one file. When no password is needed, ssh mutual trust configuration is successful
Ssh mysql1 Ssh mysql2 Ssh mysql3
4.2 installation package: install the required package on three nodes. The software package OS comes with its own CD-ROM.
yum install perl-DBD-MySQL yum install perl-DBI yum install mysql-libs
4.3 install nodes on three nodes. If the package is missing, install it on the OS CD via yum.
tar xzvf mha4mysql-node-0.58.tar.gz cd mha4mysql-node-0.58 perl Makefile.PL make && make install
4.4 install the package on the management node
yum install perl-Config-Tiny-2.14-7.el7.noarch.rpm yum install perl-Email-Date-Format-1.002-15.el7.noarch.rpm yum install perl-File-Remove-1.52-6.el7.noarch.rpm yum install perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm yum install perl-Mail-Sender-0.8.23-1.el7.noarch.rpm yum install perl-Mail-Sendmail-0.79-21.el7.noarch.rpm yum install perl-MIME-Lite-3.030-1.el7.noarch.rpm yum install perl-MIME-Types-1.38-2.el7.noarch.rpm yum install perl-Module-Install-1.06-4.el7.noarch.rpm yum install perl-Module-ScanDeps-1.10-3.el7.noarch.rpm yum install perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm yum install perl-YAML-0.84-5.el7.noarch.rpm
4.5 install the management node. If you are prompted that the package is missing, install it via yum on the OS CD.
tar xzvf mha4mysql-manager-0.58.tar.gz cd mha4mysql-manager-0.58 perl Makefile.PL make && make install
4.6 configure the MHA script on the management node
Mkdir -p /usr/local/mha
Vi mha.conf
[server default] user = root password = mysql ssh_user = root repl_user = rep1 repl_password = mysql ping_interval = 1 ping_type = SELECT manager_workdir=/usr/local/mha manager_log=/usr/local/mha/manager.log remote_workdir=/usr/local/mha ##--master_binlog_dir="/u01/my3306/log/binlog" master_ip_failover_script="/usr/local/scripts/master_ip_failover" ##--master_ip_online_change_script="/mysql/mha/scripts/master_ip_failover" master_ip_online_change_script="/usr/local/scripts/master_ip_online_change" shutdown_script="" report_script="" #check_repl_delay=0 [server1] hostname=mysql1 port=3306 master_binlog_dir="/mysql/mysql/data" no_master=1 [server2] hostname=mysql2 port=3306 master_binlog_dir="/mysql/mysql/data" candidate_master=1 [server3] hostname=mysql3 port=3306 master_binlog_dir="/mysql/mysql/data" candidate_master=1
4.7 configure failover and onlinechange scripts
#!/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.54.233/24'; # Virtual IP
my $key = "1";
my $int = "ens192";
my $ssh_start_vip = "/sbin/ifconfig $int:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig $int:$key down";
my $arp_effect = "/sbin/arping -Uq -s192.168.54.233 -I $int 192.168.54.254 -c 3"; # Virtual IP and gatway
#my $test = "echo successfull >/tmp/test.txt";
$ssh_user = "root";
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\@cluster1 \" $ssh_start_vip \"`;
&status();
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 \"`;
`ssh $ssh_user\@$new_master_host \" $arp_effect \"`;
# `ssh $ssh_user\@$new_master_host \" $test \"`;
}
# 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 status() {
print `ssh $ssh_user\@$orig_master_host \" ip add show $int \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_maste
r_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
#!/usr/bin/env perl
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 $vip = "192.168.54.233";
my $if = "ens192";
my (
$command, $orig_master_is_new_slave, $orig_master_host,
$orig_master_ip, $orig_master_port, $orig_master_user,
$orig_master_password, $orig_master_ssh_user, $new_master_host,
$new_master_ip, $new_master_port, $new_master_user,
$new_master_password, $new_master_ssh_user,
);
GetOptions(
'command=s' => \$command,
'orig_master_is_new_slave' => \$orig_master_is_new_slave,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'orig_master_user=s' => \$orig_master_user,
'orig_master_password=s' => \$orig_master_password,
'orig_master_ssh_user=s' => \$orig_master_ssh_user,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
'new_master_user=s' => \$new_master_user,
'new_master_password=s' => \$new_master_password,
'new_master_ssh_user=s' => \$new_master_ssh_user,
);
exit &main();
sub drop_vip {
my $output = `ssh -o ConnectTimeout=15 -o ConnectionAttempts=3 $orig_master_host /sbin/ip addr del $vip/32 dev $if`;
}
sub add_vip {
my $output = `ssh -o ConnectTimeout=15 -o ConnectionAttempts=3 $new_master_host /sbin/ip addr add $vip/32 dev $if`;
}
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";
print current_time_us() . " drop vip $vip..\n";
#drop_app_user($orig_master_handler);
&drop_vip();
## Waiting for N * 100 milliseconds so that current connections can exit
my $time_until_read_only = 15;
$_tstart = [gettimeofday];
my @threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
while ( $time_until_read_only > 0 && $#threads >= 0 ) {
if ( $time_until_read_only % 5 == 0 ) {
printf
"%s Waiting all running %d threads are disconnected.. (max %d milliseconds)\n",
current_time_us(), $#threads + 1, $time_until_read_only * 100;
if ( $#threads < 5 ) {
print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"
foreach (@threads);
}
}
sleep_until();
$_tstart = [gettimeofday];
$time_until_read_only--;
@threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
}
## Setting read_only=1 on the current master so that nobody(except SUPER) can write
print current_time_us() . " Set read_only=1 on the orig master.. ";
$orig_master_handler->enable_read_only();
if ( $orig_master_handler->is_read_only() ) {
print "ok.\n";
}
else {
die "Failed!\n";
}
## Waiting for M * 100 milliseconds so that current update queries can complete
my $time_until_kill_threads = 5;
@threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
while ( $time_until_kill_threads > 0 && $#threads >= 0 ) {
if ( $time_until_kill_threads % 5 == 0 ) {
printf
"%s Waiting all running %d queries are disconnected.. (max %d milliseconds)\n",
current_time_us(), $#threads + 1, $time_until_kill_threads * 100;
if ( $#threads < 5 ) {
print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"
foreach (@threads);
}
}
sleep_until();
$_tstart = [gettimeofday];
$time_until_kill_threads--;
@threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
}
## Terminating all threads
print current_time_us() . " Killing all application threads..\n";
$orig_master_handler->kill_threads(@threads) if ( $#threads >= 0 );
print current_time_us() . " done.\n";
$orig_master_handler->enable_log_bin_local();
$orig_master_handler->disconnect();
## After finishing the script, MHA executes FLUSH TABLES WITH READ LOCK
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
## Activating master ip on the new master
# 1. Create app user with write privileges
# 2. Moving backup script if needed
# 3. Register new master's ip to the catalog database
# We don't return error even though activating updatable accounts/ip failed so that we don't interrupt slaves' recovery.
# If exit code is 0 or 10, MHA does not abort
my $exit_code = 10;
eval {
my $new_master_handler = new MHA::DBHelper();
# args: hostname, port, user, password, raise_error_or_not
$new_master_handler->connect( $new_master_ip, $new_master_port,
$new_master_user, $new_master_password, 1 );
## Set read_only=0 on the new master
$new_master_handler->disable_log_bin_local();
print current_time_us() . " Set read_only=0 on the new master.\n";
$new_master_handler->disable_read_only();
## Creating an app user on the new master
#print current_time_us() . " Creating app user on the new master..\n";
print current_time_us() . "Add vip $vip on $if..\n";
# create_app_user($new_master_handler);
&add_vip();
$new_master_handler->enable_log_bin_local();
$new_master_handler->disconnect();
## Update master ip on the catalog database, etc
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
# do nothing
exit 0;
}
else {
&usage();
exit 1;
}
}
sub usage {
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;
}
4.8 inspection status
masterha_check_ssh --conf=/usr/local/mha/mha.conf
masterha_check_repl --conf=/usr/local/mha/mha.conf
[root@mysql1 bin]# masterha_check_ssh --conf=/usr/local/mha/mha.conf Sun Apr 28 10:14:21 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sun Apr 28 10:14:21 2019 - [info] Reading application default configuration from /usr/local/mha/mha.conf.. Sun Apr 28 10:14:21 2019 - [info] Reading server configuration from /usr/local/mha/mha.conf.. Sun Apr 28 10:14:21 2019 - [info] Starting SSH connection tests.. Sun Apr 28 10:14:22 2019 - [debug] Sun Apr 28 10:14:21 2019 - [debug] Connecting via SSH from root@mysql1(192.168.54.230:22) to root@mysql2(192.168.54.231:22).. Sun Apr 28 10:14:21 2019 - [debug] ok. Sun Apr 28 10:14:21 2019 - [debug] Connecting via SSH from root@mysql1(192.168.54.230:22) to root@mysql3(192.168.54.232:22).. Sun Apr 28 10:14:22 2019 - [debug] ok. Sun Apr 28 10:14:23 2019 - [debug] Sun Apr 28 10:14:22 2019 - [debug] Connecting via SSH from root@mysql3(192.168.54.232:22) to root@mysql1(192.168.54.230:22).. Sun Apr 28 10:14:22 2019 - [debug] ok. Sun Apr 28 10:14:22 2019 - [debug] Connecting via SSH from root@mysql3(192.168.54.232:22) to root@mysql2(192.168.54.231:22).. Sun Apr 28 10:14:23 2019 - [debug] ok. Sun Apr 28 10:14:23 2019 - [debug] Sun Apr 28 10:14:21 2019 - [debug] Connecting via SSH from root@mysql2(192.168.54.231:22) to root@mysql1(192.168.54.230:22).. Sun Apr 28 10:14:22 2019 - [debug] ok. Sun Apr 28 10:14:22 2019 - [debug] Connecting via SSH from root@mysql2(192.168.54.231:22) to root@mysql3(192.168.54.232:22).. Sun Apr 28 10:14:23 2019 - [debug] ok. Sun Apr 28 10:14:23 2019 - [info] All SSH connection tests passed successfully.
[root@mysql1 mha]# masterha_check_repl --conf=/usr/local/mha/mha.conf
Sun Apr 28 14:45:49 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 28 14:45:49 2019 - [info] Reading application default configuration from /usr/local/mha/mha.conf..
Sun Apr 28 14:45:49 2019 - [info] Reading server configuration from /usr/local/mha/mha.conf..
Sun Apr 28 14:45:49 2019 - [info] MHA::MasterMonitor version 0.58.
Sun Apr 28 14:45:50 2019 - [info] GTID failover mode = 0
Sun Apr 28 14:45:50 2019 - [info] Dead Servers:
Sun Apr 28 14:45:50 2019 - [info] Alive Servers:
Sun Apr 28 14:45:50 2019 - [info] mysql1(192.168.54.230:3306)
Sun Apr 28 14:45:50 2019 - [info] mysql2(192.168.54.231:3306)
Sun Apr 28 14:45:50 2019 - [info] mysql3(192.168.54.232:3306)
Sun Apr 28 14:45:50 2019 - [info] Alive Slaves:
Sun Apr 28 14:45:50 2019 - [info] mysql1(192.168.54.230:3306) Version=5.7.25-log (oldest major version between slaves) log-bin:enabled
Sun Apr 28 14:45:50 2019 - [info] Replicating from 192.168.54.231(192.168.54.231:3306)
Sun Apr 28 14:45:50 2019 - [info] Not candidate for the new Master (no_master is set)
Sun Apr 28 14:45:50 2019 - [info] mysql3(192.168.54.232:3306) Version=5.7.25-log (oldest major version between slaves) log-bin:enabled
Sun Apr 28 14:45:50 2019 - [info] Replicating from 192.168.54.231(192.168.54.231:3306)
Sun Apr 28 14:45:50 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Sun Apr 28 14:45:50 2019 - [info] Current Alive Master: mysql2(192.168.54.231:3306)
Sun Apr 28 14:45:50 2019 - [info] Checking slave configurations..
Sun Apr 28 14:45:50 2019 - [info] read_only=1 is not set on slave mysql1(192.168.54.230:3306).
Sun Apr 28 14:45:50 2019 - [warning] relay_log_purge=0 is not set on slave mysql1(192.168.54.230:3306).
Sun Apr 28 14:45:50 2019 - [info] read_only=1 is not set on slave mysql3(192.168.54.232:3306).
Sun Apr 28 14:45:50 2019 - [warning] relay_log_purge=0 is not set on slave mysql3(192.168.54.232:3306).
Sun Apr 28 14:45:50 2019 - [info] Checking replication filtering settings..
Sun Apr 28 14:45:50 2019 - [info] binlog_do_db= , binlog_ignore_db=
Sun Apr 28 14:45:50 2019 - [info] Replication filtering check ok.
Sun Apr 28 14:45:50 2019 - [info] GTID (with auto-pos) is not supported
Sun Apr 28 14:45:50 2019 - [info] Starting SSH connection tests..
Sun Apr 28 14:45:52 2019 - [info] All SSH connection tests passed successfully.
Sun Apr 28 14:45:52 2019 - [info] Checking MHA Node version..
Sun Apr 28 14:45:53 2019 - [info] Version check ok.
Sun Apr 28 14:45:53 2019 - [info] Checking SSH publickey authentication settings on the current master..
Sun Apr 28 14:45:54 2019 - [info] HealthCheck: SSH to mysql2 is reachable.
Sun Apr 28 14:45:54 2019 - [info] Master MHA Node version is 0.58.
Sun Apr 28 14:45:54 2019 - [info] Checking recovery script configurations on mysql2(192.168.54.231:3306)..
Sun Apr 28 14:45:54 2019 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/mysql/mysql/data --output_file=/usr/local/mha/save_binary_logs_test --manager_version=0.58 --start_file=mysql2-bin.000001
Sun Apr 28 14:45:54 2019 - [info] Connecting to root@192.168.54.231(mysql2:22)..
Creating /usr/local/mha if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /mysql/mysql/data, up to mysql2-bin.000001
Sun Apr 28 14:45:54 2019 - [info] Binlog setting check done.
Sun Apr 28 14:45:54 2019 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Sun Apr 28 14:45:54 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=mysql1 --slave_ip=192.168.54.230 --slave_port=3306 --workdir=/usr/local/mha --target_version=5.7.25-log --manager_version=0.58 --relay_log_info=/mysql/mysql/data/relay-log.info --relay_dir=/mysql/mysql/data/ --slave_pass=xxx
Sun Apr 28 14:45:54 2019 - [info] Connecting to root@192.168.54.230(mysql1:22)..
Checking slave recovery environment settings..
Opening /mysql/mysql/data/relay-log.info ... ok.
Relay log found at /mysql/mysql/data, up to slave-relay-bin.000007
Temporary relay log file is /mysql/mysql/data/slave-relay-bin.000007
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.
Sun Apr 28 14:45:55 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=mysql3 --slave_ip=192.168.54.232 --slave_port=3306 --workdir=/usr/local/mha --target_version=5.7.25-log --manager_version=0.58 --relay_log_info=/mysql/mysql/data/relay-log.info --relay_dir=/mysql/mysql/data/ --slave_pass=xxx
Sun Apr 28 14:45:55 2019 - [info] Connecting to root@192.168.54.232(mysql3:22)..
Checking slave recovery environment settings..
Opening /mysql/mysql/data/relay-log.info ... ok.
Relay log found at /mysql/mysql/data, up to slave-relay-bin.000006
Temporary relay log file is /mysql/mysql/data/slave-relay-bin.000006
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.
Sun Apr 28 14:45:55 2019 - [info] Slaves settings check done.
Sun Apr 28 14:45:55 2019 - [info]
mysql2(192.168.54.231:3306) (current master)
+--mysql1(192.168.54.230:3306)
+--mysql3(192.168.54.232:3306)
Sun Apr 28 14:45:55 2019 - [info] Checking replication health on mysql1..
Sun Apr 28 14:45:55 2019 - [info] ok.
Sun Apr 28 14:45:55 2019 - [info] Checking replication health on mysql3..
Sun Apr 28 14:45:55 2019 - [info] ok.
Sun Apr 28 14:45:55 2019 - [info] Checking master_ip_failover_script status:
Sun Apr 28 14:45:55 2019 - [info] /usr/local/scripts/master_ip_failover --command=status --ssh_user=root --orig_master_host=mysql2 --orig_master_ip=192.168.54.231 --orig_master_port=3306
IN SCRIPT TEST====/sbin/ifconfig ens192:1 down==/sbin/ifconfig ens192:1 192.168.54.233/24===
Checking the Status of the script.. OK
2: ens192: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
link/ether 00:50:56:9e:56:39 brd ff:ff:ff:ff:ff:ff
inet 192.168.54.231/24 brd 192.168.54.255 scope global ens192
valid_lft forever preferred_lft forever
inet6 fe80::7657:5bdf:3e45:94ab/64 scope link
valid_lft forever preferred_lft forever
Sun Apr 28 14:45:56 2019 - [info] OK.
Sun Apr 28 14:45:56 2019 - [warning] shutdown_script is not defined.
Sun Apr 28 14:45:56 2019 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.4.9 add VIP
Add VIP address on Main Library
ip addr add 192.168.54.233/24 dev ens192
5 MHA start and test
5.1 start MHA service
masterha_manager --conf=/usr/local/mha/mha.conf > /tmp/mha_manager.log < /dev/null 2>&1 &
5.2 verify MHA status
masterha_check_status --conf=/usr/local/mha/mha.conf
5.3 fault simulation: close the master database, and the slave database will automatically convert to the master database and obtain the VIP address
-- Close the main library and check the status, mysql3 Become a new master library
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.54.231
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql2-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: slave-relay-bin.000007
Relay_Log_Pos: 369
Relay_Master_Log_File: mysql2-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 743
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 43314749-67f6-11e9-b1c7-0050569e5639
Master_Info_File: /mysql/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.54.232
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: mysql3-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 528
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: d45ab02b-67f8-11e9-8a14-0050569e8e90
Master_Info_File: /mysql/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
-- Then? VIP Drift to the mysql3 upper
[root@mysql3 network-scripts]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens192: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
link/ether 00:50:56:9e:8e:90 brd ff:ff:ff:ff:ff:ff
inet 192.168.54.232/24 brd 192.168.54.255 scope global ens192
valid_lft forever preferred_lft forever
inet 192.168.54.233/24 brd 192.168.54.255 scope global secondary ens192:1
valid_lft forever preferred_lft forever
inet6 fe80::f39e:715c:c308:83da/64 scope link
valid_lft forever preferred_lft forever
[root@mysql3 network-scripts]#
-- Then, view the status on the management node,MHA Process will automatically stop running
[root@mysql1 bin]# masterha_check_status --conf=/usr/local/mha/mha.conf
mha is stopped(2:NOT_RUNNING).
[root@mysql1 bin]#
--- Restore operation, start the closed node
[root@mysql2 mha]# service mysql start
Starting MySQL. SUCCESS!
[root@mysql2 mha]#
-- Point to the new master database again, configure the master and slave -- Operate on the original master database, that is, treat it as a slave database
change master to master_host='192.168.54.232',master_user='rep1',master_password='mysql',master_log_pos=154;
start slave 5.4 online switching test
[root@mysql1 bin]# masterha_master_switch --conf=/usr/local/mha/mha.conf --master_state=alive --new_master_host=mysql3 --orig_master_is_new_slave
Mon Apr 29 10:36:58 2019 - [info] MHA::MasterRotate version 0.58.
Mon Apr 29 10:36:58 2019 - [info] Starting online master switch..
Mon Apr 29 10:36:58 2019 - [info]
Mon Apr 29 10:36:58 2019 - [info] * Phase 1: Configuration Check Phase..
Mon Apr 29 10:36:58 2019 - [info]
Mon Apr 29 10:36:58 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Apr 29 10:36:58 2019 - [info] Reading application default configuration from /usr/local/mha/mha.conf..
Mon Apr 29 10:36:58 2019 - [info] Reading server configuration from /usr/local/mha/mha.conf..
Mon Apr 29 10:36:59 2019 - [info] GTID failover mode = 1
Mon Apr 29 10:36:59 2019 - [info] Current Alive Master: mysql2(192.168.54.231:3306)
Mon Apr 29 10:36:59 2019 - [info] Alive Slaves:
Mon Apr 29 10:36:59 2019 - [info] mysql1(192.168.54.230:3306) Version=5.7.25-log (oldest major version between slaves) log-bin:enabled
Mon Apr 29 10:36:59 2019 - [info] GTID ON
Mon Apr 29 10:36:59 2019 - [info] Replicating from 192.168.54.231(192.168.54.231:3306)
Mon Apr 29 10:36:59 2019 - [info] Not candidate for the new Master (no_master is set)
Mon Apr 29 10:36:59 2019 - [info] mysql3(192.168.54.232:3306) Version=5.7.25-log (oldest major version between slaves) log-bin:enabled
Mon Apr 29 10:36:59 2019 - [info] GTID ON
Mon Apr 29 10:36:59 2019 - [info] Replicating from 192.168.54.231(192.168.54.231:3306)
Mon Apr 29 10:36:59 2019 - [info] Primary candidate for the new Master (candidate_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 mysql2(192.168.54.231:3306)? (YES/no): yes
Mon Apr 29 10:37:01 2019 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Mon Apr 29 10:37:01 2019 - [info] ok.
Mon Apr 29 10:37:01 2019 - [info] Checking MHA is not monitoring or doing failover..
Mon Apr 29 10:37:01 2019 - [info] Checking replication health on mysql1..
Mon Apr 29 10:37:01 2019 - [info] ok.
Mon Apr 29 10:37:01 2019 - [info] Checking replication health on mysql3..
Mon Apr 29 10:37:01 2019 - [info] ok.
Mon Apr 29 10:37:01 2019 - [info] mysql3 can be new master.
Mon Apr 29 10:37:01 2019 - [info]
From:
mysql2(192.168.54.231:3306) (current master)
+--mysql1(192.168.54.230:3306)
+--mysql3(192.168.54.232:3306)
To:
mysql3(192.168.54.232:3306) (new master)
+--mysql1(192.168.54.230:3306)
+--mysql2(192.168.54.231:3306)
Starting master switch from mysql2(192.168.54.231:3306) to mysql3(192.168.54.232:3306)? (yes/NO): yes
Mon Apr 29 10:37:03 2019 - [info] Checking whether mysql3(192.168.54.232:3306) is ok for the new master..
Mon Apr 29 10:37:03 2019 - [info] ok.
Mon Apr 29 10:37:03 2019 - [info] ** Phase 1: Configuration Check Phase completed.
Mon Apr 29 10:37:03 2019 - [info]
Mon Apr 29 10:37:03 2019 - [info] * Phase 2: Rejecting updates Phase..
Mon Apr 29 10:37:03 2019 - [info]
Mon Apr 29 10:37:03 2019 - [info] Executing master ip online change script to disable write on the current master:
Mon Apr 29 10:37:03 2019 - [info] /usr/local/scripts/master_ip_online_change --command=stop --orig_master_host=mysql2 --orig_master_ip=192.168.54.231 --orig_master_port=3306 --orig_master_user='root' --new_master_host=mysql3 --new_master_ip=192.168.54.232 --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
Mon Apr 29 10:37:03 2019 200912 Set read_only on the new master.. ok.
Mon Apr 29 10:37:03 2019 202523 drop vip 192.168.54.233..
Mon Apr 29 10:37:03 2019 533798 Waiting all running 2 threads are disconnected.. (max 1500 milliseconds)
{'Time' => '3364','db' => undef,'Id' => '22','User' => 'rep1','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysql1:39526'}
{'Time' => '2161','db' => undef,'Id' => '48','User' => 'rep1','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysql3:36336'}
Mon Apr 29 10:37:04 2019 033354 Waiting all running 2 threads are disconnected.. (max 1000 milliseconds)
{'Time' => '3364','db' => undef,'Id' => '22','User' => 'rep1','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysql1:39526'}
{'Time' => '2161','db' => undef,'Id' => '48','User' => 'rep1','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysql3:36336'}
Mon Apr 29 10:37:04 2019 533708 Waiting all running 2 threads are disconnected.. (max 500 milliseconds)
{'Time' => '3365','db' => undef,'Id' => '22','User' => 'rep1','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysql1:39526'}
{'Time' => '2162','db' => undef,'Id' => '48','User' => 'rep1','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysql3:36336'}
Mon Apr 29 10:37:05 2019 034148 Set read_only=1 on the orig master.. ok.
Mon Apr 29 10:37:05 2019 034870 Waiting all running 2 queries are disconnected.. (max 500 milliseconds)
{'Time' => '3365','db' => undef,'Id' => '22','User' => 'rep1','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysql1:39526'}
{'Time' => '2162','db' => undef,'Id' => '48','User' => 'rep1','State' => 'Master has sent all binlog to slave; waiting for more updates','Command' => 'Binlog Dump GTID','Info' => undef,'Host' => 'mysql3:36336'}
Mon Apr 29 10:37:05 2019 534506 Killing all application threads..
Mon Apr 29 10:37:05 2019 535141 done.
Mon Apr 29 10:37:05 2019 - [info] ok.
Mon Apr 29 10:37:05 2019 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Mon Apr 29 10:37:05 2019 - [info] Executing FLUSH TABLES WITH READ LOCK..
Mon Apr 29 10:37:05 2019 - [info] ok.
Mon Apr 29 10:37:05 2019 - [info] Orig master binlog:pos is mysql2-bin.000004:9000459.
Mon Apr 29 10:37:05 2019 - [info] Waiting to execute all relay logs on mysql3(192.168.54.232:3306)..
Mon Apr 29 10:37:05 2019 - [info] master_pos_wait(mysql2-bin.000004:9000459) completed on mysql3(192.168.54.232:3306). Executed 0 events.
Mon Apr 29 10:37:05 2019 - [info] done.
Mon Apr 29 10:37:05 2019 - [info] Getting new master's binlog name and position..
Mon Apr 29 10:37:05 2019 - [info] mysql3-bin.000007:194
Mon Apr 29 10:37:05 2019 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='mysql3 or 192.168.54.232', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep1', MASTER_PASSWORD='xxx';
Mon Apr 29 10:37:05 2019 - [info] Executing master ip online change script to allow write on the new master:
Mon Apr 29 10:37:05 2019 - [info] /usr/local/scripts/master_ip_online_change --command=start --orig_master_host=mysql2 --orig_master_ip=192.168.54.231 --orig_master_port=3306 --orig_master_user='root' --new_master_host=mysql3 --new_master_ip=192.168.54.232 --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
Mon Apr 29 10:37:05 2019 633353 Set read_only=0 on the new master.
Mon Apr 29 10:37:05 2019 633728Add vip 192.168.54.233 on ens192..
Mon Apr 29 10:37:05 2019 - [info] ok.
Mon Apr 29 10:37:06 2019 - [info]
Mon Apr 29 10:37:06 2019 - [info] * Switching slaves in parallel..
Mon Apr 29 10:37:06 2019 - [info]
Mon Apr 29 10:37:06 2019 - [info] -- Slave switch on host mysql1(192.168.54.230:3306) started, pid: 25045
Mon Apr 29 10:37:06 2019 - [info]
Mon Apr 29 10:37:07 2019 - [info] Log messages from mysql1 ...
Mon Apr 29 10:37:07 2019 - [info]
Mon Apr 29 10:37:06 2019 - [info] Waiting to execute all relay logs on mysql1(192.168.54.230:3306)..
Mon Apr 29 10:37:06 2019 - [info] master_pos_wait(mysql2-bin.000004:9000459) completed on mysql1(192.168.54.230:3306). Executed 0 events.
Mon Apr 29 10:37:06 2019 - [info] done.
Mon Apr 29 10:37:06 2019 - [info] Resetting slave mysql1(192.168.54.230:3306) and starting replication from the new master mysql3(192.168.54.232:3306)..
Mon Apr 29 10:37:06 2019 - [info] Executed CHANGE MASTER.
Mon Apr 29 10:37:06 2019 - [info] Slave started.
Mon Apr 29 10:37:07 2019 - [info] End of log messages from mysql1 ...
Mon Apr 29 10:37:07 2019 - [info]
Mon Apr 29 10:37:07 2019 - [info] -- Slave switch on host mysql1(192.168.54.230:3306) succeeded.
Mon Apr 29 10:37:07 2019 - [info] Unlocking all tables on the orig master:
Mon Apr 29 10:37:07 2019 - [info] Executing UNLOCK TABLES..
Mon Apr 29 10:37:07 2019 - [info] ok.
Mon Apr 29 10:37:07 2019 - [info] Starting orig master as a new slave..
Mon Apr 29 10:37:07 2019 - [info] Resetting slave mysql2(192.168.54.231:3306) and starting replication from the new master mysql3(192.168.54.232:3306)..
Mon Apr 29 10:37:07 2019 - [info] Executed CHANGE MASTER.
Mon Apr 29 10:37:08 2019 - [info] Slave started.
Mon Apr 29 10:37:08 2019 - [info] All new slave servers switched successfully.
Mon Apr 29 10:37:08 2019 - [info]
Mon Apr 29 10:37:08 2019 - [info] * Phase 5: New master cleanup phase..
Mon Apr 29 10:37:08 2019 - [info]
Mon Apr 29 10:37:08 2019 - [info] mysql3: Resetting slave info succeeded.
Mon Apr 29 10:37:08 2019 - [info] Switching master to mysql3(192.168.54.232:3306) completed successfully.
[root@mysql1 bin]# END