MHA overview

Developed by yoshimaton (now working for Facebook), a Japanese DeNA company
 A set of excellent high availability software for failover and master-slave promotion in MySQL high availability environment

Composition of MHA

MHA Manager (management node)
MHA Node

MHA features

In the process of automatic failover, MHA tries to save binary logs from the down master server to ensure the data is not lost to the greatest extent
 Using MySQL 5.5 semi synchronous replication can greatly reduce the risk of data loss

Experimental thinking

1. MHA architecture

(1) Database installation
 (2) One master and two slaves
 (3) MHA construction

2. Fault simulation

(1) Main library failure
 (2) Alternative master database becomes master database
 (3) Point the alternative master from library 2 to the master

Deployment environment

2. Experimental requirements

In this case, it is required to monitor MySQL database through MHA to switch automatically in case of failure, without affecting the business.

3. Realization ideas

(1) Install MySQL database
 (2) Configure MySQL one master and two slaves
 (3) Install MHA software
 (4) Configure password free authentication
 (5) Configure MySQL MHA high availability
 (6) Simulate master failover

Step 1: install the database on three MySQL nodes respectively

(use 5.6.36 for MySOL and 2.8.6 for cmake)

1. Install build dependent environment

yum install -y install ncurses-devel gcc gcc-c++ perl-Module-Install

2. Remote mount

mkdir /abc
mount.cifs //192.168.1421/mha /abc/

3. Install gmake compiler

cd /abc/mha/
tar zxvf cmake-2.8.6.tar.gz -C /opt/

cd /opt/cmake-2.8.6/
./configure
gmake && gmake install

4. Install MySQL database

cd /abc/mha/
tar zxvf mysql-5.6.36.tar.gz -C /opt/

cd /opt/mysql-5.6.36/
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DSYSCONFDIR=/etc

make && make install

cp support-files/my-default.cnf /etc/my.cnf
cp support-files/mysql.server /etc/rc.d/init.d/mysqld

chmod +x /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile

useradd -M -s /sbin/nologin mysql
chown -R mysql.mysql /usr/local/mysql

/usr/local/mysql/scripts/mysql_install_db \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data \
--user=mysql

5. Modify the main configuration file / etc/my.cnf of the master. The server IDs of the three servers cannot be the same

vim /etc/my.cnf
[mysqld]
server-id = 1
log_bin = master-bin
log-slave-updates = true

Step 2: configure slave server 1

Modify the main configuration file of mysql

#Modify or add the following in / etc/my.cnf.
[mysqld]
server-id = 2
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

Step 3: configure slave server 2

1. Modify mysql's main configuration file / etc/my.cnf

vim /etc/my.cnf
[mysql]
server-id = 3
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index

2. Make two soft connections on the master, slave1 and slave2

ln -s /usr/local/mysql/bin/mysql /usr/sbin/
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/

3. Start mysql on master, slave1 and slave2, and check the opening status

#mysql start 
/usr/local/mysql/bin/mysqld_safe --user=mysql &

#View service port status
netstat -ntap | grep 3306

#Turn off firewall and security features
systemctl stop firewalld.service 
setenforce 0

Step 4: configure MySQL one master and two slaves

1. The master-slave configuration of mysq is relatively simple. We need to pay attention to the authorization. Two users are authorized on all database nodes. One is that the slave database uses the user myslave synchronously, and the other is that the manager uses the monitoring user mha

grant replication slave on *.* to 'myslave'@'192.168.142.%' identified by '123';
grant all privileges on *.* to 'mha'@'192.168.142.%' identified by 'manager';
flush privileges;

2. The following three authorizations do not need to be added according to the theory, but the master and slave of mysql report an error through MHA during the case experiment environment,
It is reported that two slave databases cannot connect to the master database through the host name, so all databases are authorized as follows

grant all privileges on *.* to 'mha'@'master' identified by 'manager';
grant all privileges on *.* to 'mha'@'slave1' identified by 'manager';
grant all privileges on *.* to 'mha'@'slave2' identified by 'manager';

#Refresh database
flush privileges;

3. View binaries and synchronization points on the master

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |     1292 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

4. Perform synchronization on slave1 and slave2 respectively

change master to master_host='192.168.142.130',master_user='myslave',master_password='123',master_log_file='masterbin.000001',master_log_pos=1292;
start slave;
#Turn on slave

5. Check whether IO and SQL threads are yes, which means synchronization is normal

show slave status\G;

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
#Two slave libraries must be set to read-only mode

#Set two slave libraries to read-only mode
set global read_only=1;

#Refresh database
flush privileges;

Step 5: install the MHA software (install the environment that MHA depends on on on all servers, first install the epel source)

#Turn off firewall and security features
systemctl stop firewalld.service 
setenforce 0

#Install MHA dependent environment
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

#Install node (install node on all servers)
tar zxvf /abc/rpm/MHA/mha4mysql-node-0.57.tar.gz
cd mha4mysql-node-0.57/
perl Makefile.PL
make && make install

Step 6: install the manager component on MHA Manager (Note: install the node component before installing the manager component)

tar zxvf /abc/rpm/MHA/mha4mysql-manager-0.57.tar.gz
cd mha4mysql-manager-0.57/
perl Makefile.PL 
make
make install

manager After installation, the/usr/local/bin Here are a few tools:

masterha_conf_host           
#Add or remove configured server information

masterha_stop                   
#Close manager

masterha_manager            
#Start manager script

masterha_check_repl         
#Check mysql replication

masterha_master_monitor 
#Check whether the master is down

masterha_check_ssh          
#Check SSH configuration of MHA

masterha_master_switch    
#Control failover (automatic or manual)

masterha_check_status      
#Detect current MHA operation status

node After installation, the/usr/local/bin Here are a few scripts generated (these tools are usually MHA Manager The script starts without any manual operation)

apply_diff_relay_logs             
#Identify different relay log events and apply them to other slave s

filter_mysqlbinlog             
#Remove unnecessary ROLLBACK events (MHA no longer uses this tool)

purge_relay_logs            
#Clear relay logs (does not block SQL threads)

save_binary_logs            
#Save and copy the binary log of the master

Step 7: configure password free authentication

(1) Configure password free authentication to all database nodes on manager

#Because there is no password verification, press enter all the way
ssh-keygen -t rsa         
ssh-copy-id 192.168.142.130
ssh-copy-id 192.168.142.131
ssh-copy-id 192.168.142.132

(2) Password free authentication to database nodes slave1 and slave2 configured on the master

ssh-keygen -t rsa
ssh-copy-id 192.168.142.131
ssh-copy-id 192.168.142.132

(3) Password free authentication configured to database nodes master and slave 2 on slave 1

ssh-keygen -t rsa
ssh-copy-id 192.168.142.130
ssh-copy-id 192.168.142.132

(4) Password free authentication configured to database nodes master and slave1 on slave2

ssh-keygen -t rsa
ssh-copy-id 192.168.142.130
ssh-copy-id 192.168.142.131

Step 8: configure MHA

1. Copy the relevant script on the manager node to the directory / usr/local/bin

cp -ra /root/mha4mysql-manager-0.57/samples/scripts /usr/local/bin
#There will be four executables after copying

#View directory permissions
ll /usr/local/bin/scripts/

-rwxr-xr-x. 1 1001 1001  3648 May 31  2015 master_ip_failover            #Script of VIP management during automatic switch
-rwxr-xr-x. 1 1001 1001  9870 May 31  2015 master_ip_online_change      #Management of VIP during online switching
-rwxr-xr-x. 1 1001 1001 11867 May 31  2015 power_manager           #Script to shut down a host after a failure
-rwxr-xr-x. 1 1001 1001  1360 May 31  2015 send_report             #Script to send alert after failover

2. Copy the above script of VIP management during automatic switch to the directory / usr/local/bin, where the script is used to manage VIP

cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin

3. Rewrite the master IP failover script: (delete the original content and write the following content directly)

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.142.200';
my $brdc = '192.168.142.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. Create MHA software directory and copy configuration files

mkdir /etc/masterha
cp /root/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha
vim /etc/masterha/app1.cnf 

[server default]
#manager Profile
manager_log=/var/log/masterha/app1/manager.log     

#manager log
manager_workdir=/var/log/masterha/app1

#The location where the master saves the binlog. The path here should be the same as that of the bilog configured in the master
master_binlog_dir=/usr/local/mysql/data

#Set the switch script for automatic failover. That's the script above
master_ip_failover_script=/usr/local/bin/master_ip_failover

#Set the switch script for manual switch
master_ip_online_change_script=/usr/local/bin/master_ip_online_change

#This password is the one used to create the monitoring user
password=manager
remote_workdir=/tmp

#Set copy user password
repl_password=123

#Set users for replication users
repl_user=myslave

#Set the script of alarm after switching
reporl_script=/usr/local/send_report
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.45.130 -s 192.168.45.134

#Set failure shutdown failure script host
shutdown_script=""

#Set the login user name of ssh
ssh_user=root

#Set monitoring user
user=mha

[server1]
hostname=192.168.142.
port=3306

[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.142.
port=3306

[server3]
hostname=192.168.142.
port=3306

5. Test ssh without password authentication

masterha_check_ssh -conf=/etc/masterha/app1.cnf
masterha_check_repl -conf=/etc/masterha/app1.cnf

#Note: for the first configuration, you need to go to the master and start the virtual IP manually
/sbin/ifconfig ens33:1 192.168.142.200/24

6. Start MHA

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 &

7. View the MHA status, and you can see that the current master is mysql1 node

masterha_check_status --conf=/etc/masterha/app1.cnf

8. Check the MHA log, and you can see that the current master is 192.168.142.130

cat /var/log/masterha/app1/manager.log 

Step 9: fault simulation

1. Start monitoring observation logging

tailf /var/log/masterha/app1/manager.log

2. View address changes

pkill -9 mysql        
#Shut down mysql service      

VIP The address won't be manager Node stop MHA Services disappear,VIP Address will be transferred to slave1 upper

#View vip address transfer from server
ifconfig

ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.142.131  netmask 255.255.255.0  broadcast 192.168.142.255
        inet6 fe80::b81a:9df:a960:45ac  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:97:8e:66  txqueuelen 1000  (Ethernet)
        RX packets 1687418  bytes 1157627305 (1.0 GiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 1376468  bytes 170996461 (163.0 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.142.200  netmask 255.255.255.0  broadcast 192.168.142.255
        ether 00:0c:29:97:8e:66  txqueuelen 1000  (Ethernet)

3. Open another new terminal on MHA manager and install mysql directly

 yum install mysql -y

#Give permissions to slave1, otherwise MHA manager cannot access the database:
grant all on *.* to 'root'@'%' identified by 'abc123';

#Log in on MH Manager:
 mysql -h 192.168.142.200 -uroot -p
Enter password:             
#Input password

(1) Create a database school, create a table info, and write a simple content

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MySQL [(none)]> create database school;
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> use school;
Database changed
MySQL [school]> create table info (id int);
Query OK, 0 rows affected (0.01 sec)

(2) After creation, view in the database on slave1, and the data will be synchronized

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+

(3) Because slave1 and slave2 are synchronized with each other, data should also be synchronized on slave2

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+

Thank you for reading!!!