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
- Edit the MySQL configuration file (
my.cnf
ormy.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
- Edit the Slave’s MySQL configuration file (
my.cnf
ormy.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