MySQL high availability cluster construction (GTID mode + automatic failover)
1. Environmental Planning
|
Role |
IP address |
illustrate |
|
Master library |
192.168.1.100 |
Run MySQL + Keepalived/MHA |
|
From Library 1 (Slave1) |
192.168.1.101 |
Run MySQL + Keepalived/MHA |
|
From Library 2 (Slave2) |
192.168.1.102 |
Run MySQL |
|
Virtual IP (VIP) |
192.168.1.200 |
Client access portal |
2. Configure GTID master-slave synchronization
1. All nodes configure MySQL
# Edit configuration file (MySQL 8.0)
sudo vim /etc/mysql//
[mysqld]
# General configuration
server-id = 1 # Set the master library to 1, increment the slave library in turn (2, 3...)
log_bin = /var/log/mysql/
binlog_format = ROW
expire_logs_days = 7
# GTID Configuration
gtid_mode = ON # Enable GTID
enforce_gtid_consistency = ON # Force GTID consistency
log_slave_updates = ON # Record binlog from library (required for cascading copying)
# Restart MySQL
sudo systemctl restart mysql
2. Main library operation
-- Create a copy dedicated user
CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'SecurePass123!';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';
FLUSH PRIVILEGES;
-- Check GTID status
SHOW MASTER STATUS\G
-- Output example:
-- File: mysql-bin.000001
-- Position: 154
-- Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5
3. Operation from the library
-- Configure the master library information (all slave library executions)
CHANGE MASTER TO
MASTER_HOST = '192.168.1.100',
MASTER_USER = 'repl',
MASTER_PASSWORD = 'SecurePass123!',
MASTER_AUTO_POSITION = 1; -- GTID Automatic synchronization position of mode
-- Start copy
START SLAVE;
-- Check the replication status
SHOW SLAVE STATUS\G
-- Key indicators:
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Retrieved_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5
-- Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5
3. Automatic failover solution
Solution 1: Keepalived + VIP (fast switch)
1. Install Keepalived (master and slave library 1)
# Ubuntu/Debian
sudo apt install keepalived
# CentOS/RHEL
sudo yum install keepalived
2. Main library Keepalived configuration
sudo vim /etc/keepalived/
vrrp_script chk_mysql {
script "/usr/bin/pgrep mysqld" # Check MySQL Process
interval 2
weight 2
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100 # The main library has higher priority
advert_int 1
authentication {
auth_type PASS
auth_pass 1234
}
virtual_ipaddress {
192.168.1.200/24 # Virtual IP
}
track_script {
chk_mysql
}
}
3. Configure from library Keepalived
sudo vim /etc/keepalived/
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 50 # Priority is lower than the main library
advert_int 1
authentication {
auth_type PASS
auth_pass 1234
}
virtual_ipaddress {
192.168.1.200/24
}
track_script {
chk_mysql
}
}
4. Start and verify
sudo systemctl restart keepalived
sudo systemctl enable keepalived
# Check VIP bindings
ip addr show eth0 | grep 192.168.1.200
Solution 2: MHA (intelligent failover)
1. All nodes are configured with SSH password-free login
# Execute on the MHA Manager node
ssh-keygen -t rsa
ssh-copy-id [email protected]
ssh-copy-id [email protected]
ssh-copy-id [email protected]
2. Install MHA Node (all MySQL nodes)
# Ubuntu/Debian
sudo apt install mha4mysql-node
# CentOS/RHEL
sudo yum install /yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.
3. Install MHA Manager (Management Node)
# Ubuntu/Debian
sudo apt install mha4mysql-manager
# CentOS/RHEL
sudo yum install /yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.
4. Configure MHA
sudo mkdir -p /etc/mha
sudo vim /etc/mha/
[server default]
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/
ssh_user=root
repl_user=repl
repl_password=SecurePass123!
ping_interval=3
[server1]
hostname=192.168.1.100
candidate_master=1
[server2]
hostname=192.168.1.101
candidate_master=1
[server3]
hostname=192.168.1.102
5. Start MHA
masterha_manager --conf=/etc/mha/ --remove_dead_master_conf --ignore_last_failover
# Check status
masterha_check_status --conf=/etc/mha/
4. Data backup and recovery (GTID mode)
1. Full backup
# Use mysqldump (main library execution)
mysqldump -u root -p --all-databases --single-transaction --set-gtid-purged=ON > full_backup_$(date +%Y%m%d).sql
2. Incremental backup
-- Regular record of GTID collections
SHOW GLOBAL VARIABLES LIKE 'gtid_executed';
-- Example output: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-1000
-- Export new GTID intervals during incremental backup
mysqlbinlog --include-gtids='3E11FA47-71CA-11E1-9E33-C80AA9429562:1001-2000' /var/log/mysql/mysql-bin.00000* > incremental_backup.sql
3. Data recovery
# Full recovery
mysql -u root -p < full_backup_20231001.sql
# Incremental recovery
mysql -u root -p < incremental_backup.sql
5. Master-slave switching operation
1. Manual switch (GTID mode)
-- Execute in the target from the library
STOP SLAVE;
RESET SLAVE ALL;
-- Promoted to a new main library
SET GLOBAL read_only = OFF;
-- Other slave libraries point to the new master library
CHANGE MASTER TO
MASTER_HOST='192.168.1.101',
MASTER_USER='repl',
MASTER_PASSWORD='SecurePass123!',
MASTER_AUTO_POSITION=1;
START SLAVE;
VI. Verification and Monitoring
1. Check GTID consistency
-- All nodes execute
SHOW GLOBAL VARIABLES LIKE 'gtid_executed';
-- Make sure that all nodes GTID collections are the same
2. Monitor replication delay
SHOW SLAVE STATUS\G
-- Follow Seconds_Behind_Master
3. Log check
# Keepalived Log
journalctl -u keepalived
# MHA Log
tail -f /var/log/mha/app1/
7. Troubleshooting command
1. Master-slave copy interrupt
-- Check the cause of the error
SHOW SLAVE STATUS\G
-- Skip the error (make careful operation)
STOP SLAVE;
SET GTID_NEXT='3E11FA47-71CA-11E1-9E33-C80AA9429562:1001';
BEGIN; COMMIT;
SET GTID_NEXT='AUTOMATIC';
START SLAVE;
2. VIP not drifted
# Check the Keepalived process
ps aux | grep keepalived
# Manually release VIP
systemctl restart keepalived
8. Key points to note
- GTID Limitations：
- It is prohibited to directly modify gtid_purged, which may lead to data conflicts.
- All transactions must be copied through GTID, and non-GTID operations (such as CHANGE MASTER TO MASTER_LOG_FILE) are disabled.
- Full backup once a day, incremental backup once an hour.
- usePercona XtraBackupAlternative to mysqldump supports hot backup:
xtrabackup --backup --target-dir=/backup/xtrabackup/full
- Safety reinforcement：
- Restrict replication user IP range (such as repl'@'192.168.1.%').
- Encrypt backup files and store them to a remote server.
Through the above steps, you will get a based onGTID + Keepalived/MHAHigh availability MySQL cluster supports automatic failover, data consistency assurance and rapid recovery.