Setting Up MySQL Replication for High Availability

Ensuring data availability and redundancy is a critical aspect of database management. Setting up MySQL replication allows businesses to achieve high availability, disaster recovery, and improved performance. MySQL replication enables automatic data synchronization between a master server and one or more slave servers, reducing downtime and ensuring uninterrupted access to critical information.

By implementing MySQL replication, you can distribute database queries, enhance fault tolerance, and prepare for potential failures without disrupting operations.

What is MySQL Replication?

MySQL replication is a process that enables automatic data synchronization between two or more MySQL servers. This is essential for achieving redundancy, load balancing, and disaster recovery.

Types of MySQL Replication

1. Master-Slave Replication – One master server handles writes, and one or more slaves replicate the data for read queries.
2. Master-Master Replication – Two or more servers function as both master and slave, allowing read and write operations.
3. Group Replication – A fault-tolerant cluster-based solution with built-in automatic failover.
4. Semi-Synchronous Replication – Ensures at least one slave has received the transaction before committing it.

For this guide, we’ll focus on Master-Slave Replication, which is the most commonly used replication method.

Prerequisites

Before we start, make sure you have:

• Two or more MySQL servers (Master and Slave) installed.
• Root access to both servers.
• A stable network connection between the servers.
• MySQL version 5.7 or later (recommended).

For more details, check the official MySQL documentation: MySQL Replication Guide

Step-by-Step Guide to Setting Up MySQL Master-Slave Replication

Configure the Master Server

Step 1: Edit the MySQL Configuration File

Open the my.cnf or mysqld.cnf file (usually located in /etc/mysql/) and modify the following settings:

[mysqld]
bind-address = 0.0.0.0
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydatabase   # Specify the database to replicate

[mysqld]
bind-address = 0.0.0.0
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydatabase   # Specify the database to replicate

Restart MySQL for changes to take effect:

sudo systemctl restart mysql

sudo systemctl restart mysql

Step 2: Create a Replication User

Log in to MySQL and create a dedicated user for replication:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'StrongPassword';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

CREATE USER 'replicator'@'%' IDENTIFIED BY 'StrongPassword';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;

Step 3: Get the Binary Log Position

Run the following command to get the log file name and position:

SHOW MASTER STATUS;

SHOW MASTER STATUS;

Take note of File and Position values, as they will be needed on the slave server.

Configure the Slave Server

Step 1: Edit the MySQL Configuration File

Modify the my.cnf or mysqld.cnf file on the slave server:

[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log

[mysqld]
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log

Restart MySQL:

sudo systemctl restart mysql

sudo systemctl restart mysql

Step 2: Connect to the Master Server

Run the following SQL commands to connect the slave to the master:

CHANGE MASTER TO 
    MASTER_HOST='Master_IP_Address',
    MASTER_USER='replicator',
    MASTER_PASSWORD='StrongPassword',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=123456;

CHANGE MASTER TO 
    MASTER_HOST='Master_IP_Address',
    MASTER_USER='replicator',
    MASTER_PASSWORD='StrongPassword',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=123456;

(Replace Master_IP_Address, mysql-bin.000001, and 123456 with the actual values from the master server.)

Step 3: Start Replication

START SLAVE;

START SLAVE;

Verify that replication is running:

SHOW SLAVE STATUS \G;

SHOW SLAVE STATUS \G;

If Slave_IO_Running and Slave_SQL_Running are both ‘Yes’, replication is working correctly!

Verifying Replication

To test the setup, insert data into the master server:

USE mydatabase;
INSERT INTO users (id, name) VALUES (1, 'John Doe');

USE mydatabase;
INSERT INTO users (id, name) VALUES (1, 'John Doe');

Now, check the slave server:

SELECT * FROM users;

SELECT * FROM users;

If the record appears, your replication is successfully set up!

Troubleshooting Common Issues

Replication stopped unexpectedly? Check the error log:

tail -f /var/log/mysql/error.log

tail -f /var/log/mysql/error.log

Slave SQL or IO thread not running? Restart replication:

STOP SLAVE;
START SLAVE;
SHOW SLAVE STATUS \G;

STOP SLAVE;
START SLAVE;
SHOW SLAVE STATUS \G;

Master and slave desynchronized? Resync by taking a fresh dump from the master:

mysqldump -u root -p --all-databases --master-data > backup.sql

mysqldump -u root -p --all-databases --master-data > backup.sql

Copy the dump to the slave and restore it:

mysql -u root -p < backup.sql

mysql -u root -p < backup.sql

Restart replication after restoring.

For additional troubleshooting steps, refer to this MySQL troubleshooting guide: MySQL Troubleshooting

Conclusion

Setting up MySQL replication is a game-changer for high availability, load balancing, and disaster recovery. Whether you’re running a small website or an enterprise-level application, replication ensures your data remains available even when failures occur.

🔹 Next Steps: Consider implementing failover mechanisms like MySQL Group Replication or using a load balancer such as HAProxy to improve performance even further!

For more advanced replication setups, check this guide on Group Replication.