1, MHA concept
MHA (MasterHigh Availability) is a set of excellent software for failover and master-slave replication in MySQL high availability environment.
The emergence of MHA is to solve the problem of MySQL single point.
During MySQL failover, MHA can automatically complete the failover operation within 0-30 seconds.
MHA can ensure the consistency of data to the greatest extent in the process of failover, so as to achieve high availability in the real sense.
1. Composition of MHA
● MHA Node
The MHA Node runs on each MySQL server.
● MHA Manager (management node)
MHA Manager can be deployed on an independent machine to manage multiple master slave clusters; It can also be deployed on a slave node.
The MHA Manager will periodically probe the master node in the cluster. When the master fails, it can automatically promote the slave of the latest data to the new master, and then point all other slave to the new master again. The entire failover process is completely transparent to the application.
2. Characteristics of MHA
● during automatic failover, MHA tries to save binary logs from the down master server to ensure that data is not lost to the greatest extent
● using semi synchronous replication can greatly reduce the risk of data loss. If only one slave has received the latest binary log, MHA can apply the latest binary log to all other slave servers, so it can ensure the data consistency of all nodes
● at present, MHA supports a master-slave architecture with at least three services, i.e. one master and two slaves
2, Build MySQL+MHA
Experimental ideas
1. MHA architecture
Database installation
One master and two slaves
MHA construction
2. Fault simulation
Simulated main library failure
The alternative master library becomes the master library
The original fault master database recovers and rejoins the MHA as a slave database
1. All servers, turn off the system firewall and security mechanism
systemctl stop firewalld
systemctl disable firewalld
setenforce 0
2. Modify the main configuration file / etc/my.cnf of the three MySQL servers
vim /etc/my.cnf [mysqld] server-id = 20 log_bin = master-bin log-slave-updates = true systemctl restart mysqld ln -s /usr/local/mysql/bin/mysql /usr/sbin/ ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
3. Configure MySQL one master and two slave
mysql -e "grant replication slave on *.* to 'myslave'@'192.168.220.%' identified by '123123';" mysql -e "grant all privileges on *.* to 'mha'@'192.168.220.%' identified by 'manager';" mysql -e "grant all privileges on *.* to 'mha'@'192.168.220.4' identified by 'manager';" mysql -e "grant all privileges on *.* to 'mha'@'192.168.220.5' identified by 'manager';"
mysql -e "change master to master_host='192.168.220.4',master_user='myslave',master_password='123123',master_log_file='master-bin.000001',master_log_pos=2205;"
mysql -e "start slave;"
mysql -e "show slave status\G" | awk '/Running:/{print}'
4. Master slave replication verification
5. Install MHA software
MHA dependent environments are installed on all servers. First install the epel source, which needs to be installed online
mv /etc/yum.repos.d/repos.bak/CentOS-* /etc/yum.repos.d/ 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 Package mha4mysql-node-0.57.tar.gz Put/opt Directory cd /opt tar zxvf mha4mysql-node-0.57.tar.gz cd mha4mysql-node-0.57 perl Makefile.PL make && make install
Install the manager component on the MHA manager node
Package mha4mysql-manager-0.57.tar.gz Put/opt Directory cd /opt tar zxvf mha4mysql-manager-0.57.tar.gz cd mha4mysql-manager-0.57 perl Makefile.PL make && make install
6. Configure password less authentication on all servers
SSH keygen - t RSA # all the way press enter
ssh-copy-id 192.168.220.4
ssh-copy-id 192.168.220.5
ssh-copy-id 192.168.220.6
Configure password less authentication from the master node to the slave node
ssh-keygen -t rsa
ssh-copy-id 192.168.220.5
ssh-copy-id 192.168.220.6
Configure password less authentication from one slave node to the master node and another slave node
ssh-keygen -t rsa
ssh-copy-id 192.168.220.4
ssh-copy-id 192.168.220.6
Configure password less authentication from one slave node to the master node and another slave node
ssh-keygen -t rsa
ssh-copy-id 192.168.220.4
7. Configure MHA on the manager node
cp -rp /opt/mha4mysql-manager-0.57/samples/scripts /usr/local/bin
#After copying, there will be four executables
ll /usr/local/bin/scripts/
cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin
The amendments are as follows:
echo '' > /usr/local/bin/master_ip_failover
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.220.200'; #Specify the address of the vip
my $brdc = '192.168.220.255'; #Specify the broadcast address of the vip
my $ifdev = 'ens33'; #Specify the network card bound by vip
my $key = '1'; #Specifies the serial number of the virtual network card bound by the vip
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip"; #Represents that the value of this variable is ifconfig ens33:1 192.168.220.200
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down"; #Represents that the value of this variable is ifconfig ens33:1 192.168.220.200 down
my $exit_code = 0; #Specifies that the exit status code is 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";
}
Create the MHA software directory and copy the configuration file. Use the app1.cnf configuration file to manage the mysql node server. The configuration file is generally placed in the / etc / directory
mkdir /etc/masterha
cp /opt/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha
#Delete the original content, directly copy and modify the IP address of the node server
echo '' > /etc/masterha/app1.cnf 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=123123 repl_user=myslave secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.220.5 -s 192.168.220.6 shutdown_script="" ssh_user=root user=mha [server1] hostname=192.168.220.4 port=3306 [server2] candidate_master=1 check_repl_delay=0 hostname=192.168.220.5 port=3306 [server3] hostname=192.168.220.6 port=3306
[server default] manager_log=/var/log/masterha/app1/manager.log #manager log manager_workdir=/var/log/masterha/app1.log #manager working directory master_binlog_dir=/usr/local/mysql/data/ #The location where the master saves binlog. The path here should be consistent with the path of binlog configured in the master so that MHA can find it master_ip_failover_script=/usr/local/bin/master_ip_failover #Set the switching script for automatic failover, that is, the script above master_ip_online_change_script=/usr/local/bin/master_ip_online_change #Set the switching script for manual switching password=manager #Set the password of the root user in mysql, which is the password of the monitoring user created in the previous article ping_interval=1 #Set the monitoring master database. The interval between sending ping packets is 1 second, which is 3 seconds by default. When there is no response after three attempts, the failover will be performed automatically remote_workdir=/tmp #Set the save location of binlog when remote mysql switches repl_password=123123 #Set the password of the replication user repl_user=myslave #Set the user of the replication user report_script=/usr/local/send_report #Set the script of the alarm sent after switching secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.220.5 -s 192.168.220.6 #Specifies the IP address of the slave server to check shutdown_script="" #Set the script to close the fault host after the fault occurs (the main function of this script is to close the host to prevent brain crack, which is not used here) ssh_user=root #Set ssh login user name user=mha #Set monitoring user root [server1] hostname=192.168.220.4 port=3306 [server2] hostname=192.168.220.5 port=3306 candidate_master=1 #Set as the candidate master. After setting this parameter, the slave database will be promoted to the master database after the master-slave switch occurs, even if the master database is not the latest slave in the cluster check_repl_delay=0 #By default, if a slave lags behind the master by more than 100M of relay logs, MHA will not select the slave as a new master, because the recovery of the slave takes a long time; By setting check_repl_delay=0, MHA triggers the switch. When selecting a new master, the replication delay will be ignored. This parameter is set to candidate_ The host with master = 1 is very useful because the candidate master must be a new master in the process of switching [server3] hostname=192.168.220.6 port=3306
Open VIP on MHA server
ifconfig ens33:1 192.168.220.200/24
In the MHA test, ssh has no password authentication. If it is normal, it will finally output successfully.
masterha_check_repl -conf=/etc/masterha/app1.cnf
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 &
View MHA status
masterha_check_status --conf=/etc/masterha/app1.cnf
View MHA log
cat /var/log/masterha/app1/manager.log | grep "current master"
Check whether the VIP address 192.168.220.200 exists
ifconfig
Close the manager service
masterha_stop --conf=/etc/masterha/app1.cnf
Or it can be closed directly by using the kill process ID
8. Fault simulation
Monitor observation logging on the manager node
tail -f /var/log/masterha/app1/manager.log
Stop the mysql service on the primary node
systemctl stop mysqld
1
Check whether the slave node takes over the VIP
ifconfig
1
Algorithm of failover alternative master library:
1. Generally, the slave library is judged from (position/GTID). There are differences in data. The slave closest to the master becomes the alternative master.
2. If the data is consistent, select an alternative master database according to the order of the configuration file.
3. Set a weight (candidate_master=1), and force the alternative master to be specified according to the weight.
By default, if a slave lags behind the master's 100m relay logs, even if it has a weight, it will become invalid.
If check_ repl_ If delay = 0, even if it lags behind many logs, it is forced to select it as the alternative primary.