当前位置:网站首页>Database management and high availability

Database management and high availability

2020-11-10 14:28:29 osc_116hbwph

Database management and high availability ----MHA High availability configuration and fail over

 Experimental thinking :
1.MHA framework 
1) Database installation 
2) One master and two slaves 
3)MHA build 
 fault simulation 
1) The main library fails 
2) The alternate master database becomes the master database 
3) Slave Library 2 Point the alternate master library to the master library 
 Case environment 
1. The context of this case 
 The server CentOS7.4(64 position )MHA-manager/192.168.100.130
 The management node , install manager Components 
 The server CentOS7.4(64 position )Mysq11/192.168.100.150 Master  node , install node Components 
 The server CentOS7.4(64 position )Mysql2/192.168.100.160 Slave  node , install node Components 
 The server CentOS7.4(64 position )Mysql3/192.168.100.170 Slave node , install node Components 
 The operating system here is CentOS7 edition , So download it here MHA The version is 0.57 edition .
2. Case needs 
 This case requires that MHA monitor MySQL The database switches automatically in case of failure , It doesn't affect the business .
3. Case realization ideas 
1) install MySQL database 
2) To configure MySQL One master and two slaves 
3) install MHA Software 
4) Configure password free Authentication 
5) To configure  MySQL MHA High availability 
6) simulation master Fail over 
 At three MySQL  The database is installed on the node respectively ,MySQL Please use 5.6.36,cmake Version please make 
 use 2.8.6. It's just Mysql1 Here's a demonstration , The installation process is as follows .

1. Install and compile dependent environment 
[root@Mysql1~]# yum install ncurses-devel gcc-c++ perl-Module-Install -y

2. install gmake Compiling software 
[root@Mysql1~ ]# tar zxvf cmake-2.8.6.tar.gz
[root@Mysql1 ~]# cd cmake-2.8.6
[root@Mysql1 cmake-2.8.6]# ./configure
[root@Mysql1 cmake-2.8.6]# gmake && gmake install

3. install MySQL database 
[root@Mysql1~ ]# tar zxvf mysql-5.6.36.tar.gz
[root@Mysql1 ~]# cd mysql-5.6.36

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITII_EXTRA_CHARSETS=all \
-DSYSCONFDTR=/etc

[root@Mysql1 mysql-5.6.36]# make && make install
[root@Mysql1 mysql-5.6.36]# cp support-files/my-default.cnf /etc/my.cnf
[root@Mysql1 mysql-5.6.36]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
[root@Mysql1 ~]# chmod +x /etc/rc.d/init.d/mysqld
[root@MysqI1 ~]# chkconfig --add mysqld
[root@Mysql1 ~]# echo  "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@Mysql1 ~]# source /etc/profile
[root@Mysql1 ~]# groupadd mysql
[root@Mysql1 ~]# useradd -M -s /sbin/nologin mysql -g mysql
[root@Mysql1 ~]# chown -R mysql.mysql /usr/local/mysql

source /etc/profile
groupadd mysql
useradd -M -s /sbin/nologin mysql -g mysql
chown -R mysql.mysql /usr/local/mysql

[root@Mysql1 ~]# mkdir -p /data/mysql
/usr/local/mysql/scripts/mysql_install_db \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data \
--user=mysql
/usr/local/mysql/scripts/mysql_install_db \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data \
--user=mysql

4. modify Master Primary profile for /etc/my.cnf file , Three servers server-id It can't be the same 
[root@Mysql1 ~]# cat /etc/my.cnf
[mysqld]
server-id = 1
log_bin = master-bin
log-slave-updates = true
 Configure slave :
 stay /etc/my.cnf Modify or add the following content in .
[root@Mysql2 ~]# vim /etc/my.cnf
server-id = 2
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

5.Mysql1、Mysql2、Mysql3 Do two soft links respectively 
[root@Mysql1 ~]# In -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@Mysql1 ~]# In -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
6、Mysql1、Mysql2、Mysql3 start-up MySQL.

systemctl stop firewalld
setenforce 0
/usr/local/mysql/bin/mysqld_safe --user=mysql &
netstat -ntap | grep 3306

7. To configure MySQL One master and two slaves 

1).MySQL The master-slave configuration is relatively simple . It's important to note that authorization . Steps are as follows :
 Authorize two users on all database nodes , One is to use it synchronously from the library , The other is manager Use .
grant replication slave on*.* to 'myslave'@'192.168.100.%' identified by '123';
grant all privileges on *.* to 'mha'@'192.168.100.%' identified by 'manager';
flush privileges;
grant replication slave on *.* to 'myslave'@'192.168.100.%' identified by '123';
grant all privileges on *.* to 'mha'@'192.168.100.%' identified by 'manager';
flush privileges;

2). In theory, the following three authorizations need not be added , But when you do a case study environment through MHA Check MySQL The master and subordinate have reported errors ,
 Two slave databases cannot connect to the master database through the host name , So all databases plus the authorization below .
grant all privileges on *.* to 'mha'@'Mysql1' identified by 'manager';
grant all privileges on *.* to 'mha'@'Mysql2' identified by 'manager';
grant all privileges on *.* to 'mha'@'Mysql3' identified by 'manager';
flush privileges;
3). stay Mysq1 View binaries and syncpoints on the host 
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |     1294 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

4. The next in Mysql2 and Mysql3 Perform synchronization separately .
mysql> change master to master_host='192.168.100.150',master_user='myslave',master_password='123',master_log_file='master-bin.000010',master_log_pos=1294;
flush privileges;
start slave;
stop slave;
start slave;
5. see IO and SQL Threads are yes Represents whether the synchronization is normal .
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show master status;
" View binary log path "
mysql> show variables like 'datadir';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| datadir       | /home/mysql/ |
+---------------+--------------+
 Both slave libraries must be set to read-only mode :
set global read_only=1;
flush privileges;
 Be careful : Set to complete the direct verification of master-slave copy function 

-------------------------- install MHA Software ----------------------------
1. All servers have MHA Dependent environment , First installation epel Source .
[root@MHA-manager ~]# yum install epel-release --nogpgcheck -y

yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN

2.MHA  Software packages are different for each operating system version , here CentOS7.4 Must choose 0.57 edition ,
 stay < Be careful : All servers > You have to install node Components , Last in MHA-manager Install on node manager Components ,
 because manager rely on node Components , It's all down here Mysql1 Operation demonstration installation on node Components .
// Upload mha4mysql-node-0.57.tar.gz
[root@Mysql1~]# tar zxvf mha4mysql-node-0.57.tar.gz
[root@Mysql1 ~]# cd mha4mysql-node-0.57
[root@Mysql1 mha4mysql-node-o.57]# perl Makefile.PL 
[root@Mysql1 mha4mysql-node-o.57]# make
[root@Mysql1 mha4mysql-node-0.57]# make && make install

3. Only in MHA-manager Installation on manager Components (! Be careful : Be sure to install first node Components to install manager Components )
[root@Mysql1 mha4mysql-node-0.57]# cd ~
[root@MHA-manager ~]# tar zxvf mha4mysql-manager-0.57.tar.gz
[root@MHA-manager~]# cd mha4mysql-manager-0.57
[root@MHA-manager mha4mysql-manager-0.57]# perl Makefile.PL
[root@MHA-manager mha4mysql-manager-0.57]# make
[root@MHA-manager mha4mysql-manager-o.57]# make && make install

manager After installation /usr/local/bin The following tools will be generated , It mainly includes the following :
masterha_check_ssh Check MHA Of SSH Configuration status 
masterha_check_repl Check MySQL Copy status 
masterha_manger start-up manager Script for 
masterha_check_status Detect current MHA Running state 
masterha_master_monitor testing master Is it down? 
masterha_master_switch Control failover ( Automatic or manual )
masterha_conf_host Add or remove configured server Information 
masterha_stop close manager

4.node After installation, it will also be in /usr/local/bin Several scripts will be generated below ( These tools are usually made of MHA
Manager The script triggers , There is no need for human operation ) Mainly as follows :
save_binary_logs Save and copy master Binary log 
apply_diff_relay_logs  Identify differentiated relay log events and apply their differentiated events to other slave
filter_mysqlbinlog Remove unnecessary ROLLBACK event (MHA This tool is no longer used )
purge_relay_logs Clear relay logs ( It won't block SQL Threads )

5. Configure password free Authentication 
1). stay manager No password authentication configured to all database nodes 
[root@MHA-manager ~]# ssh-keygen -t rsa    // Press enter all the way 
[root@MHA-manager ~]# ssh-copy-id 192.168.100.150   // Input yes  And 150 Of root password 
[root@MHA-manager ~]# ssh-copy-id 192.168.100.160
[root@MHA-manager ~]# ssh-copy-id 192.168.100.170
2). stay Mysql1 Configuration to the database node Mysql2 and Mysql3 No password authentication 
[root@Mysql1 ~]# ssh-keygen -t rsa
[root@MysqI1 ~]# ssh-copy-id 192.168.100.160
[root@Mysql1 ~]# ssh-copy-id 192.168.100.170
3). stay Mysql2 Configuration to the database node MysqI1 and Mysql3 No password authentication 
[root@Mysql2 ~]# ssh-keygen -t rsa
[root@Mysql2 ~]# ssh-copy-id 192.168.100.150
[root@Mysql2 ~]# ssh-copy-id 192.168.100.170
4). stay Mysql3 Configuration to the database node Mysql1 and Mysql2 No password authentication 
[root@Mysql3 ~]# ssh-keygen -t rsa
[root@Mysql3 ~]# ssh-copy-id 192.168.100.150
[root@Mysql3 ~]# ssh-copy-id 192.168.100.160

6. To configure MHA

1). stay manager Copy related scripts on node to /usr/local/bin Catalog .
[root@mha-manager~]# cp -ra /root/mha4mysql-manager-0.57/samples/scripts /usr/local/bin
// After copying, there are four executable files 
[root@MHA-manager ~]#ll/usr/local/bin/scripts/
 Total usage 32
-rwxr-xr-x 1 mysql mysql 36485 month 312015 master_ip_failover# When switching automatically VIP Managed scripts 
-rwxr-xr-x 1 mysql mysql 98725 month 2509:07 master_ip_online_change # When switching online vip Management of 
-rwxr-xr-x 1 mysql mysql118675 month 312015 power_manager # Script to shut down the host after the failure 
-rwxr-Xr-x 1 mysql mysql 13605 month 312015 send_report # Because the script that sends the alarm after the failover 

2). When copying the above automatic switching VIP Manage scripts to /usr/local/bin Catalog , Script management is used here VIP,
[root@mha-manager~]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin

3). The modification is as follows :( Delete the original content , Direct copy )

[root@MHA-manager ~]# vim /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';

use Getopt::Long;

my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
############################# Add content section #########################################
my $vip = '192.168.100.200';
my $brdc = '192.168.100.255';
my $ifdev = 'ens33';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
my $exit_code = 0;
#my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
#my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";
##################################################################################
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 \"`;
}
# 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_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";
}

4). establish MHA Software directory and copy configuration files .
[root@MHA-manager ~]# cd /usr/local/bin/scripts/
[root@MHA-manager scripts]# cp master_ip_online_change /usr/local/bin/
[root@MHA-manager scripts]# cp send_report /usr/local/
[root@MHA-manager ~]# mkdir /etc/masterha
[root@MHA-manager ~]# cp /root/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha
[root@MHA-manager ~]# vim /etc/masterha/app1.cnf

[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1
master_binlog_dir=/usr/local/mysql/data
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=manager
ping_interval=1
remote_workdir=/tmp
repl_password=123
repl_user=myslave
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.100.160 -s 192.168.100.170
shutdown_script=""
ssh_user=root
user=mha

[server1]    #master node 
hostname=192.168.100.150
port=3306

[server2]    #salve1
hostname=192.168.100.160
port=3306
candidate_master=1
check_repl_delay=0

[server3]    #slave2
hostname=192.168.100.170
port=3306

################ Configuration item explanation 
[server default]
manager_log=/var/log/masterha/app1/manager.log  'manager journal '
manager_workdir=/var/log/masterha/app1    'manager working directory '
master_binlog_dir=/usr/local/mysql/data    '#master preservation binlog The location of , The path here has to do with master Internally configured binlog The path has always been , In order to mha Can find , This also corresponds to the data directory of the compilation and installation database '
master_ip_failover_script=/usr/local/bin/master_ip_failover  ' Set auto failover When the switch script , That's the script above '
master_ip_online_change_script=/usr/local/bin/master_ip_online_change ' Set the switch script for manual switching '
password=manager  '# Set up mysql in root User's password , This password is the one that created the monitoring user in the previous article '
ping_interval=1  ' Set up the main monitoring library , send out ping Time interval between packages , The default is 3 second , Try three times when there is no response rail'
remote_workdir=/tmp  ' Set the remote end mysql When a switch occurs binlog Where to save '
repl_password=123  ' Set the password of the copy user '
repl_user=myslave   ' Set the user of the copy user '
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.179.124 -s 192.168.179.125 ' Set the address of the slave server '
shutdown_script="" ' Set to close the failed host script after the failure occurs ( The main role of the script is to shut down the host in the occurrence of cerebral fissure , It's not used here )'
ssh_user=root  ' Set up ssh Login user name of '
user=mha

[server1]
hostname=192.168.179.123
port=3306

[server2]
candidate_master=1  ' Set as candidate master'
hostname=192.168.179.124
check_repl_delay=0  ' By default, if one slave backward master 100M Of relay logs  Words ,mha Will not choose the slave As a new master'
port=3306

[server3]
hostname=192.168.179.125
port=3306

5). test ssh No password authentication , If it's normal, it will output successfully, As shown below .
[root@mha-manager~]# masterha_check_ssh -conf=/etc/masterha/app1.cnf
Fri Aug 28 12:42:32 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Aug 28 12:42:32 2020 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Fri Aug 28 12:42:32 2020 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Fri Aug 28 12:42:32 2020 - [info] Starting SSH connection tests..
Fri Aug 28 12:42:38 2020 - [debug] 
Fri Aug 28 12:42:32 2020 - [debug]  Connecting via SSH from root@192.168.100.150(192.168.100.150:22) to root@192.168.100.160(192.168.100.160:22)..
Fri Aug 28 12:42:33 2020 - [debug]   ok.
Fri Aug 28 12:42:33 2020 - [debug]  Connecting via SSH from root@192.168.100.150(192.168.100.150:22) to root@192.168.100.170(192.168.100.170:22)..
Fri Aug 28 12:42:38 2020 - [debug]   ok.
Fri Aug 28 12:42:39 2020 - [debug] 
Fri Aug 28 12:42:32 2020 - [debug]  Connecting via SSH from root@192.168.100.160(192.168.100.160:22) to root@192.168.100.150(192.168.100.150:22)..
Fri Aug 28 12:42:33 2020 - [debug]   ok.
Fri Aug 28 12:42:33 2020 - [debug]  Connecting via SSH from root@192.168.100.160(192.168.100.160:22) to root@192.168.100.170(192.168.100.170:22)..
Fri Aug 28 12:42:39 2020 - [debug]   ok.
Fri Aug 28 12:42:39 2020 - [debug] 
Fri Aug 28 12:42:33 2020 - [debug]  Connecting via SSH from root@192.168.100.170(192.168.100.170:22) to root@192.168.100.150(192.168.100.150:22)..
Fri Aug 28 12:42:34 2020 - [debug]   ok.
Fri Aug 28 12:42:34 2020 - [debug]  Connecting via SSH from root@192.168.100.170(192.168.100.170:22) to root@192.168.100.160(192.168.100.160:22)..
Fri Aug 28 12:42:39 2020 - [debug]   ok.
Fri Aug 28 12:42:39 2020 - [info] All SSH connection tests passed successfully.

// There is a problem in this step, which shows the key distribution problem 

[root@mha-manager ~]# masterha_check_repl -conf=/etc/masterha/app1.cnf

Fri Aug 28 16:20:51 2020 - [info] Checking replication health on 192.168.100.160..
Fri Aug 28 16:20:51 2020 - [info]  ok.
Fri Aug 28 16:20:51 2020 - [info] Checking replication health on 192.168.100.170..
Fri Aug 28 16:20:51 2020 - [info]  ok.
Fri Aug 28 16:20:51 2020 - [info] Checking master_ip_failover_script status:
Fri Aug 28 16:20:51 2020 - [info]   /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.100.150 --orig_master_ip=192.168.100.150 --orig_master_port=3306 

IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.100.200===

Checking the Status of the script.. OK 
Fri Aug 28 16:20:51 2020 - [info]  OK.
Fri Aug 28 16:20:51 2020 - [warning] shutdown_script is not defined.
Fri Aug 28 16:20:51 2020 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

// Be careful : The first configuration needs to go to master Turn on Virtual IP
[root@Mysql1 ~]# /sbin/ifconfig ens33:1 192.168.100.200/24

7. start-up MHA

[root@mha-manager ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &

--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, the directory will be recorded in the log , That is, the log set above 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.
 Insufficient septum 8 In an hour , It won't go on Failover, The reason for this restriction is to avoid ping-pong effect . The 
 The parameter represents that the last time is ignored MHA Trigger the file generated by switching , By default ,MHA  When the switch occurs, it will be recorded in the log 
 Catalog , That is, the log set above app1.failover.complete file , Next time you switch again, if you find 
 If the file exists in this directory, it 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.

8. see MHA state , You can see the current master yes Mysql1 node .
[root@mha-manager~]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:70465) is running(0:PING_OK), master:192.168.100.150

9. see MHA journal , Also to see the current master yes 192.168.100.150, As shown below .
[root@mha-manager ~]# cat /var/log/masterha/app1/manager.log
.......
IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.100.200===
.........

10. see  Mysql1 Of VIP Address 192.168.100.200 Whether there is ?, This VIР The address is not because 
manager  Nodes stop MHA  Service and disappear .
[root@mysql1 mha4mysql-node-0.57]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.100.150  netmask 255.255.255.0  broadcast 192.168.100.255
        inet6 fe80::7264:7578:de4e:190  prefixlen 64  scopeid 0x20<link>
        inet6 fe80::9433:6cdb:ee1d:7b3d  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:17:9c:75  txqueuelen 1000  (Ethernet)
        RX packets 101492  bytes 130137031 (124.1 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 43150  bytes 7281255 (6.9 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.100.200  netmask 255.255.255.0  broadcast 192.168.100.255
        ether 00:0c:29:17:9c:75  txqueuelen 1000  (Ethernet)

------------------------------------ verification ------------------------------

[root@mha-manager ~]# tailf /var/log/masterha/app1/manager.log
// Enable monitoring observation logging 
[root@MysqI1~]# pkill -9 mysql / see master change 
[root@mha-manager ~]# tailf /var/log/masterha/app1/manager.log
Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.100.150(192.168.100.150:3306)
The latest slave 192.168.100.160(192.168.100.160:3306) has all relay logs for recovery.
Selected 192.168.100.160(192.168.100.160:3306) as a new master.
192.168.100.160(192.168.100.160:3306): OK: Applying all logs succeeded.
192.168.100.160(192.168.100.160:3306): OK: Activated master IP address.
192.168.100.170(192.168.100.170:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.100.170(192.168.100.170:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.100.160(192.168.100.160:3306)
192.168.100.160(192.168.100.160:3306): Resetting slave info succeeded.
Master failover to 192.168.100.160(192.168.100.160:3306) completed successfully.  'master Successfully switched to standby 160 above '
[root@mha-manager bin]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:72487) is running(0:PING_OK), master:192.168.100.160
[root@mha-manager bin]# 

[root@mysql2 mha4mysql-node-0.57]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.100.160  netmask 255.255.255.0  broadcast 192.168.100.255
        inet6 fe80::1bbb:ba05:a579:42dd  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:9f:ee:79  txqueuelen 1000  (Ethernet)
        RX packets 100963  bytes 130156228 (124.1 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 40046  bytes 6979445 (6.6 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.100.200  netmask 255.255.255.0  broadcast 192.168.100.255
        ether 00:0c:29:9f:ee:79  txqueuelen 1000  (Ethernet)
 The virtual address is also switched to mysql2
 fault simulation :
 On the main warehouse :
pkill mysqld
 You can see the status from the library , One of them must have switched to the main database 
 The algorithm of switching the alternate master database :
1. In general, the judgment from the database is from (position/GTID) Judge the pros and cons , The data are different , Closest to master Of slave, Become a candidate .
2. When the data is consistent , In the order of configuration files , Select an alternate master library .
3. Set weights (candidate_master=1), Mandatory assignment of alternate masters by weight .
1) By default, if one slave backward master 100M Of relay logs Words , Even with weight , It's going to fail .
2) If check_repl_delay=0 Words , Even behind a lot of logs , It is also mandatory to choose it as the alternative host .

 Troubleshooting steps :
1. Repair db
/etc/init.d/mysqld start
2 Fix the master-slave 
>change master to master_host='192.168.100.150',master_port=3306,master_auto_position=1,master_user='mha',master_passwd='manager',master_log_file='master-bin.000001'
change master to master_host='192.168.100.150',master_user='myslave',master_password='123',master_log_file='master-bin.000001',master_log_pos=1294;
>start slave;
3. Modify the configuration file ( Add this record to it , Because it will automatically disappear when it fails to detect )
vi /etc/masterha/app1.cnf
[server1]
hostname=192.168.195.129
port=3306
4. start-up manager( stay manager On that machine )
nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --gnore _last failover < /dev/null

dos2unix /usr/local/bin/master_ip_failover To solve the problem of Chinese and English word incompatibility 

版权声明
本文为[osc_116hbwph]所创,转载请带上原文链接,感谢