Prerequisites: Three servers, MySQL 5.7-glibc installation, master-slave configuration using GTID mode.
MySQL Installation
- Prepare installation package
Download MySQL from: https://pan.baidu.com/s/14w-oNT-oeTUujhFAnqjkSAExtract code: g8tk
[root@db-node1 install]# ll mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
-rw-r--r-- 1 root root 661214270 Jan 20 19:59 mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
- Extract, set environment variables, and add to PATH
tar zxvf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.32-linux-glibc2.12-x86_64 /usr/local/mysql
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile
Create user and grant permissions
useradd -s /sbin/nologin -M mysqluser
mkdir -p /data/mysql/
mkdir -p /data/mysql/instance_3306/
Grant permissions chown -R mysqluser.mysqluser /data/mysql/ chown -R mysqluser.mysqluser /data/mysql/instance_3306/
Create symlink due to different extraction directory
ln -s /usr/local/mysql /opt/mysql chown -R mysqluser.mysqluser /opt/mysql*
- Initialize database
mysqld --initialize-insecure --user=mysqluser --basedir=/opt/mysql --datadir=/data/mysql/instance_3306/
- Modify configuration file
cat >/etc/my.cnf <<'EOF'
[mysqld]
port=3306
user=mysqluser
basedir=/opt/mysql
datadir=/data/mysql/instance_3306/
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
EOF
- Modify startup script
cp /opt/mysql/support-files/mysql.server /etc/init.d/mysqld
systemctl daemon-reload
systemctl status mysqld
systemctl start mysqld
- Verify login
[root@db-node1 install]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.32 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
# Installation complete
Install MySQL on all three servers. This is the basic installation without master-slave setup or optimizations. First, ensure MySQL starts normally.
Master-Slave Configuration:
If the master has data, perform a backup first and import it to the slave.
mysqldump -A > full_backup.sql
Copy to slave nodes
for i in 52 53 ;do scp /data/full_backup.sql 192.168.1.$i:/usr/local/;done
mysql < /usr/local/full_backup.sql
Configure master node:
[root@db-master install]# cat /etc/my.cnf
[mysqld]
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
port=3306
user=mysqluser
basedir=/opt/mysql
datadir=/data/mysql/instance_3306/
socket=/tmp/mysql.sock
log_error=/var/log/mysql/error.log
server_id=1
log_bin=/binlog/mysql-bin
[mysql]
socket=/tmp/mysql.sock
[client]
socket=/tmp/mysql.sock
Slave nodes configuration is identical to master except for server_id.
Create binlog directory and set permissions
mkdir /binlog/
chown mysqluser.mysqluser /binlog/
Log directory
mkdir /var/log/mysql/
chown mysqluser.mysqluser /var/log/mysql/
Establish master-slave relationships. Execute the following on all three servers for MHA setup.
grant replication slave on *.* to 'replicator'@'%' identified by 'replica_pass';
Then execute the following on the master to check binlog status
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| mysql-bin.000003 | 652 | | | 03a2b688-ddc5-11ee-90df-000c293d0361:1-2,
1b604040-ddc4-11ee-929d-000c291478ee:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
On both slave nodes, establish replication
Execute on each slave
Stop slave
stop slave;
change master to master_host='192.168.1.51',master_user='replicator',master_password='replica_pass',MASTER_AUTO_POSITION=1;
Start slave
start slave;
MHA Setup Process
Create a read-only user on the master node (will be automatically synchronized)
grant select on *.* to readonly_user@'192.168.1.%' identified by 'readonly_pass';
# Set global read-only lock with root
set global read_only=1;
Difference:
1. grant select is an active permission grant limiting a specific account to query only
2. Setting --read-only applies to the entire database instance for all users
MHA tools require mysql command, so create symlinks
ln -s /opt/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /opt/mysql/bin/mysql /usr/bin/mysql
- All nodes execute - ansure mutual communication
yum install sshpass -y
ssh-keygen
sshpass -p 'securepass' ssh-copy-id 192.168.1.51 -o StrictHostKeyChecking=no
sshpass -p 'securepass' ssh-copy-id 192.168.1.52 -o StrictHostKeyChecking=no
sshpass -p 'securepass' ssh-copy-id 192.168.1.53 -o StrictHostKeyChecking=no
Install MHA-node on all node
Download package from:
https://pan.baidu.com/s/1aYC-EnDKc6N016DnbQawrQExtract code: 06g2
mha-node software -- for --mha-manage communication
# MHA is developed in Perl
# 1. Install dependencies first
yum install -y perl-DBD-MySQL
# 2. Install software
yum localinstall mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
Install MHA-Manager on db-node3 (manager node cannot be master)
Since the Manager node monitors the MySQL cluster via SSH, if the master node server crashes or has network issues, MHA cannot perform failover.
Therefore, mha-manager should not be installed on the master node
yum install epel-release -y
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN perl-Time-HiRes
yum localinstall -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
Create mha user on all nodes
MHA commands, scripts use this user to check master-slave replication status on all machines
# Execute on db-master
mysql>
mysql> grant all privileges on *.* to mha_user@'%' identified by 'mha_secure_pass';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Create manager configuration file on db-node3
mkdir -p /etc/mha #<== Create mha directory under /etc.
mkdir -p /var/log/mha/app1 #<== Create log directory.
Use this configuration
Execute the following command on all nodes in MySQL command line:
grant replication slave on . to repl_user@'%' identified by 'repl_password';
Execute the following on node3
cat > /etc/mha/app1.cnf << 'EOF'
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1.log
master_binlog_dir=/mysql_binlog/
# Comment this script for now
master_ip_failover_script=/usr/local/bin/master_ip_failover
user=mha_user
password=mha_secure_pass
ping_interval=2
# Enter your current master-slave replication account password
repl_user=repl_user
repl_password=repl_password
# MHA uses this account for passwordless login to all three nodes
ssh_user=root
[server1]
hostname=192.168.1.51
port=3306
[server2]
hostname=192.168.1.52
port=3306
[server3]
hostname=192.168.1.53
port=3306
EOF
Create MHA high availability database based on VIP
Modify this script witth your network interface configuration
Create the following script, which will be automatically called by the MHA configuration file, written in Perl
/usr/local/bin/master_ip_failover
# Enter the following content
#!/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.1.55/24';
# Network interface alias
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
my $ssh_Bcast_arp="/sbin/arping -I eth0 -c 3 -A 192.168.1.55";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
return 0 unless ($ssh_user);
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
Add VIP to the current master node (on node1 in this example)
# Create
ifconfig eth0:1 192.168.1.55/24
The following lines are not needed for creation
# Delete
ifconfig eth0:1 del 192.168.1.55
# Stop
ifconfig eth0:1 down
Prepare to start MHA, first check status
Check MHA operating conditions
# MHA provides convenient scripts to check your MHA environment setup
- SSH passwordless login
- MySQL master-slave replication
[root@db-node3 ~]#masterha_check_ssh --conf=/etc/mha/app1.cnf
Tue Aug 2 00:36:08 2022 - [info] All SSH connection tests passed successfully.
# Master-slave detection script uses the mha account to log in to all three machines
# Check master-slave replication status
masterha_check_repl --conf=/etc/mha/app1.cnf
All results must be successful
Start MHA and perform failover test
1. Start the MHA management node in background mode -- using db-node3
[root@db-node3 ~]#
[root@db-node3 ~]#nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover /var/log/mha/app1/manager.log 2>&1 &
[1] 3647
[root@db-node3 ~]#nohup: ignoring input and appending output to 'nohup.out'
[root@db-node3 ~]#
[root@db-node3 ~]#
[root@db-node3 ~]#jobs
[1]+ Running nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover /var/log/mha/app1/manager.log 2>&1 &
[root@db-node3 ~]#
[root@db-node3 ~]#
[root@db-node3 ~]#ps -ef|grep mha
root 3647 1634 1 20:37 pts/0 00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover /var/log/mha/app1/manager.log
root 3664 1634 0 20:37 pts/0 00:00:00 grep --color=auto mha
2 .Check if MHA is running
[root@db-node3 ~]#masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:3647) is running(0:PING_OK), master:192.168.1.51
# MHA confirms master is still on node51
Failover Test
Expected results:
1. Current environment
db-node51 master
db-node52 slave
db-node53 slave
2. Take down master
pkill mysql
2.1 Check VIP status, MHA script should move VIP to new master, VIP should disappear from node51
Confirmed VIP is gone from node51
3. Check master-slave replication relationship
[root@db-node53 ~]#mysql -uroot -psecurepass
mysql> show slave status\G
Conclusion: MHA automatically elects db-node52 as the new master
Verify data by comparing binlog from old master (node51) with new master (node52)
[root@db-node51 /mysql_binlog]#mysqlbinlog -vv mysql-bin.000002 |grep -i 'gtid'
Last commit was transaction ID 8
Next transaction starts with 9
---Check node52
----Node52
4. MHA will terminate after failover
Confirmed
Test new master-slave relationship
Write data on node52
Confirmed transaction updates are written to new master node52 with GTID version
6a952706-1a30-11ed-882e-000c294c7d18:2
Check data on new slave and compare GTID numbers
GTID numbers
Based on server_id in configuration file
SHOW GLOBAL VARIABLES LIKE 'server_uuid';
At this point, node52 has been promoted to master.
Restore MHA. First, add node51 back to the MySQL cluster as a slave of node52.
1. On node51, start MySQL and establish replication with new master
# 1. Start
systemctl start mysqld
# 2. Add master-slave relationship
mysql -uroot -psecurepass
change master to master_host='192.168.1.52', master_user='replicator', master_password='replica_pass' , MASTER_AUTO_POSITION=1;
start slave;
show slave status;
Add node51 back to the MHA cluster
[root@db-node3 ~]#cat /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1.log
master_binlog_dir=/mysql_binlog/
master_ip_failover_script=/usr/local/bin/master_ip_failover
password=mha_secure_pass
ping_interval=2
repl_password=repl_password
repl_user=repl_user
ssh_user=root
user=mha_user
[server2]
hostname=192.168.1.52
port=3306
[server3]
hostname=192.168.1.53
port=3306
1. Add node51 configuration to the cluster
masterha_conf_host --command=add --conf=/etc/mha/app1.cnf --hostname=192.168.1.51 --block=server1 --params="port=3306"
[root@db-node3 ~]#cat /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1.log
master_binlog_dir=/mysql_binlog/
master_ip_failover_script=/usr/local/bin/master_ip_failover
password=mha_secure_pass
ping_interval=2
repl_password=repl_password
repl_user=repl_user
ssh_user=root
user=mha_user
[server1]
hostname=192.168.1.51
port=3306
[server2]
hostname=192.168.1.52
port=3306
[server3]
hostname=192.168.1.53
port=3306
[root@db-node3 ~]#
2. Re-check SSH and replication
[root@db-node3 ~]#masterha_check_repl --conf=/etc/mha/app1.cnf
masterha_check_ssh --conf=/etc/mha/app1.cnf
3. Start MHA
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover /var/log/mha/app1/manager.log 2>&1 &
Now node51 is added back to the cluster, but the master is still on node52. You need to manually stop node52, and node51 will be automatically promoted to master. Then execute similar commands to add node52 back. This completes the entire test scenario.