How to Implement Replication in MySQL for High Availability

Database MySQL
Team CNC
6 December 2024
0
6 minutes read

MySQL replication is a technique used to copy data from one database server (master) to one or more database servers (slaves). It enhances high availability, ensures redundancy, and enables load balancing. This guide covers the different types of replication, setup steps, monitoring, and best practices.

1. Types of MySQL Replication

MySQL supports multiple replication types, each serving different use cases:

Asynchronous Replication: The master server does not wait for the slave to confirm receiving updates, leading to potential lag but improved performance.
Semi-Synchronous Replication: At least one slave acknowledges receipt before the master commits the transaction, reducing data loss risk.
Group Replication: A multi-master approach where multiple servers work together as a cluster for fault tolerance and redundancy.

2. Setting Up MySQL Replication

2.1 Prerequisites

Install MySQL on both master and slave servers.
Ensure network connectivity between the master and slave.
Use the same MySQL version on both servers.

2.2 Configure the Master Server

Edit MySQL Configuration Modify the MySQL configuration file (/etc/mysql/my.cnf or /etc/my.cnf):
```
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=mydatabase
```
Restart MySQL to apply changes:
```
systemctl restart mysql
```

Create a Replication User

CREATE USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;

Get Master Status
```
SHOW MASTER STATUS;
```
Note the File and Position values; they are required for the slave setup.

2.3 Configure the Slave Server

Edit MySQL Configuration

[mysqld]
server-id=2
relay-log=relay-bin

Restart MySQL:

systemctl restart mysql

Set Up Replication Replace <master_ip>, <file>, and <position> with values from the master:

CHANGE MASTER TO MASTER_HOST='master_ip',
MASTER_USER='replica',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

Start the Slave
```
START SLAVE;
SHOW SLAVE STATUS \G;
```
Ensure Slave_IO_Running and Slave_SQL_Running both show Yes.

3. Monitoring MySQL Replication

To check replication status:

SHOW SLAVE STATUS \G;

Key fields to monitor:

Slave_IO_Running and Slave_SQL_Running should be Yes.
Seconds_Behind_Master should be as low as possible.

4. Best Practices for MySQL Replication

Use GTID-Based Replication: Ensures transactional consistency and simplifies failover.
Monitor Replication Lag: Set up alerts to detect delays.
Use Separate Networks for Replication Traffic: Avoid bandwidth contention.
Perform Regular Backups: Protect against data corruption or accidental deletions.
Enable SSL for Secure Replication: Encrypts replication traffic.

5. Conclusion

Replication in MySQL enhances high availability, disaster recovery, and load balancing. By setting up replication correctly and following best practices, you can ensure a resilient database infrastructure.