How to Set Up MySQL Replication for Scaling and Redundancy

To scale your system and ensure redundancy, you can set up MySQL replication. MySQL replication allows one database server (primary/master) to replicate its data to one or more secondary/slave servers. Here’s a step-by-step guide:

1. Choose a Replication Mode

MySQL offers several replication modes:

  • Asynchronous Replication: Default; the master does not wait for acknowledgment from slaves.
  • Semi-Synchronous Replication: The master waits for at least one slave to confirm receipt of the transaction.
  • Group Replication: Multi-master synchronous replication.

For most scaling and redundancy needs, asynchronous or semi-synchronous replication is sufficient.

2. Prepare the Master Server

  1. Edit the MySQL configuration file (my.cnf or my.ini):

[mysqld]
server-id=1 # Unique server ID
log-bin=mysql-bin # Enable binary logging
binlog-format=ROW # Use row-based replication

2. Restart MySQL to apply changes:

systemctl restart mysql

3. Create a Replication User on the master:

CREATE USER ‘replica_user’@’%’ IDENTIFIED BY ‘replica_password’;
GRANT REPLICATION SLAVE ON . TO ‘replica_user’@’%’;
FLUSH PRIVILEGES;

4. Get Master Log File and Position:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Note the File and Position values. Keep this terminal open to retain the lock.

5. Backup the Master Data: Use a tool like mysqldump:

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

Unlock tables:

UNLOCK TABLES;

3. Prepare the Slave Server

  1. Edit the Slave’s MySQL configuration file (my.cnf or my.ini):

[mysqld]
server-id=2 # Unique server ID (different from master)
relay-log=relay-bin
log-bin=mysql-bin # Optional: Enable binary logging on the slave for chained replication

2. Restart MySQL on the slave:

systemctl restart mysql

3. Restore the Master Backup:

mysql -u root -p < master_backup.sql