How to Keep MySQL Slave Nodes Alive with Keepalived and Read/Write Splitting

Background

Our project adopts a read/write‑separation architecture where write and read traffic are directed to the primary (master) and secondary (slave) databases respectively, reducing load on the master. Diagrams illustrate the data flow and the fallback behavior when the slave becomes unavailable.

Investigation Records

1.1 Check Docker container logs

docker logs 043 --tail 200

The logs show a shutdown at 06:27:30 on 2023‑02‑08 without any error message.

1.2 View MySQL error log

cat /var/log/mysql/error.log

The error log also contains no abnormal entries at the shutdown time, leaving the cause unclear.

Understanding Read/Write Splitting

Read/write splitting imposes two constraints: the master handles both reads and writes, while the slave continuously replicates the master and is read‑only.

Full separation : master only writes, slave only reads.

Partial separation : master handles both reads and writes; the slave serves read‑intensive queries as a read‑only replica.

Our project uses the partial separation model, delegating I/O‑intensive queries to the MySQL slave.

Ensuring Slave High Availability

3.1 Keepalived for health monitoring

We deploy Keepalived to continuously check MySQL service health; if the service is down, Keepalived restarts the MySQL container.

3.2 Handling a non‑restartable slave

If the sole slave crashes, read traffic must fall back to the master, which can increase load. Two mitigation strategies are presented:

Redirect reads to the master (simple but may overload the master).

Deploy two slaves with mutual replication; one acts as a hot standby. This adds complexity and potential replication lag.

We currently use the first strategy, emphasizing the importance of keeping the slave stable.

Practical Steps to Enable Keepalived on the Slave

4.1 Install and start Keepalived

After installing Keepalived, start the service: systemctl start keepalived Enable it to start on boot by adding the command to /etc/rc.local:

sudo vim /etc/rc.local
# add:
systemctl start keepalived

4.2 Configure health checks

Edit /etc/keepalived/keepalived.conf to add a periodic MySQL health check.

sudo vim /etc/keepalived/keepalived.conf

4.3 Automatic MySQL restart script

The previously shared restart script is reused: when Keepalived detects a failed MySQL connection, it triggers the script to restart the container.

4.4 Preventing unwanted VIP failover

Because the master also runs Keepalived, we assign different virtual IPs to master (e.g., 192.168.56.88) and slave (192.168.56.89) to avoid automatic promotion of the slave when the master fails.

# Master VIP
192.168.56.88
# Slave VIP
192.168.56.89

Conclusion

Our project employs a read/write‑separation database architecture but initially lacked high availability for the slave, leading to service interruptions when the slave crashed. By documenting the failure and implementing Keepalived‑based monitoring and automatic restart, we achieved a practical HA solution for the MySQL slave.