Introduction
MySQL master‑slave replication remains a foundational technology for database scaling, high availability, and disaster recovery. As a Senior Database Administrator, I routinely design and maintain replication topologies that support mission‑critical workloads. In this post we dive into the practical steps required to set up a reliable master‑slave pair, address common pitfalls, and outline best practices that align with broader DBA responsibilities—performance tuning, backup automation, and cross‑platform awareness of Oracle, SQL Server, and PostgreSQL.
Understanding MySQL Master–Slave Replication
Key Concepts
- Binary Log (binlog) – The master records all data‑modifying statements in the binlog, which the slave reads.
- Read‑only Slave – By default, slaves are read‑only to prevent accidental writes that could break the replication stream.
- Replication Threads – One IO thread pulls the binlog from the master; one or more SQL threads apply the changes locally.
- GTID (Global Transaction Identifier) – A unique identifier for each transaction that simplifies failover and restores.
Master‑slave replication is essentially a “write‑through cache” model: all writes go to the master, and the slave mirrors those writes for read scaling or disaster recovery. Unlike PostgreSQL’s streaming replication or SQL Server’s AlwaysOn, MySQL offers a lightweight, file‑based approach that integrates well with existing backup tools such as RMAN for Oracle or native MySQL backup utilities.
Preparing the Environment
Server Requirements
- Hardware: Ensure both nodes have similar or better CPU, RAM, and disk I/O capabilities.
- OS: Prefer a stable Linux distribution (Ubuntu 22.04 LTS, CentOS 8, or RHEL 9).
- MySQL Version: Use the same major version on both master and slave (e.g., 8.0.x) to avoid compatibility issues.
Network and Security Considerations
- Open port 3306 for MySQL traffic.
- Restrict the replication user to the master’s IP or a narrow subnet.
- Use TLS/SSL for replication traffic if data sensitivity is high.
Storage and Disk I/O
Replication consumes disk space for the binlog on the master and the data directory on the slave. Allocate at least 10 % of the total data size for the binlog on the master, and ensure the slave’s disk can handle the write load without becoming a bottleneck.
Configuring the Master
Server Variables
Open my.cnf on the master and add the following under [mysqld]:
[mysqld]n
server-id=1n
log_bin=mysql-binn
binlog_format=rown
expire_logs_days=7n
gtid_mode=ONn
enforce_gtid_consistency=ONn
master_info_repository=TABLEn
relay_log_info_repository=TABLEn
relay_log_recovery=ONn
Key points:
- server-id – Must be unique across the cluster.
- binlog_format=row – Ensures binary logs capture the exact row changes, which is preferable for multi‑threaded replication.
- gtid_mode – Enables GTID‑based replication, simplifying failover.
Binary Log Setup
Restart MySQL:
sudo systemctl restart mysqld
Verify binlog generation:
SHOW VARIABLES LIKE 'log_bin%';
SHOW BINARY LOGS;
User and Permissions
Create a dedicated replication user:
CREATE USER 'replicator'@'%' IDENTIFIED BY 'StrongP@ssw0rd!';n
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';n
FLUSH PRIVILEGES;
Restrict the user’s host to the slave’s IP for tighter security:
REVOKE ALL PRIVILEGES ON *.* FROM 'replicator'@'%';n
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'slave-ip-address';n
FLUSH PRIVILEGES;
Configuring the Slave
Server Variables
On the slave, add these lines to my.cnf:
[mysqld]n
server-id=2n
read_only=ONn
relay_log=slave-relay-binn
relay_log_index=slave-relay-bin.indexn
gtid_mode=ONn
enforce_gtid_consistency=ONn
master_info_repository=TABLEn
relay_log_info_repository=TABLEn
relay_log_recovery=ONn
Connecting to the Master
Obtain the master’s current GTID set:
SELECT @@GLOBAL.GTID_PURGED;
On the slave, set the same GTID_PURGED value:
SET GTID_PURGED='master-gtid-set';
Now start replication:
CHANGE MASTER TOn
MASTER_HOST='master-ip-address',n
MASTER_USER='replicator',n
MASTER_PASSWORD='StrongP@ssw0rd!',n
MASTER_AUTO_POSITION=1;n
START SLAVE;
Initial Data Synchronization
Two approaches:
- Dump & Restore – Use
mysqldump --single-transaction --master-data=2on the master, transfer the dump to the slave, and restore. - Physical Backup – For large datasets, use Percona XtraBackup or MySQL Enterprise Backup to copy the data directory and then restore on the slave.
After the data is synced, the slave will read the binary log from the master and apply changes automatically.
Verifying and Monitoring Replication
Status Commands
- Master Status –
SHOW MASTER STATUS; - Slave Status –
SHOW SLAVE STATUSG;
Key fields to check:
- Seconds_Behind_Master – Should be close to zero.
- Slave_IO_Running and Slave_SQL_Running – Both must be Yes.
- Last_Errno – Should be 0.
Monitoring Tools
Consider the following open‑source options:
- Mysqldump – Built‑in
mysqldumpcan output replication progress. - Percona Monitoring and Management (PMM) – Provides dashboards for replication lag and server health.
- Prometheus + Grafana – Export MySQL metrics via node_exporter or mysqld_exporter, then visualize replication metrics.
Common Pitfalls and Troubleshooting
Typical Errors
- ER_FK_WRONG_CHECK – Foreign key checks fail due to missing tables or mismatched schemas.
- ER_NOT_SUPPORTED_YET – Using unsupported features (e.g.,
READ UNCOMMITTED) on the slave. - Slave_Skip_Error – The slave skips a statement, causing divergence.
Recovery Strategies
When the slave falls behind or stops:
- Check
SHOW SLAVE STATUSfor error details. - If the binary log position is corrupted, re‑run
CHANGE MASTER TOwith a freshMASTER_LOG_FILEandMASTER_LOG_POS. - Use
RESET SLAVE ALL;followed by a full data sync if necessary. - For GTID‑based replication, you can re‑apply missing transactions with
START SLAVE UNTIL MASTER_GTID_SET='gtid-set';.
Advanced Topics
Semi‑Synchronous Replication
MySQL 8.0 offers semi‑synchronous replication where the master waits for at least one slave to acknowledge receipt of a transaction before committing. Configure by adding:
[mysqld]n
master_info_repository=TABLEn
relay_log_info_repository=TABLEn
gtid_mode=ONn
enforce_gtid_consistency=ONn
rpl_semi_sync_master_enabled=ONn
rpl_semi_sync_slave_enabled=ONn
Multi‑Master (InnoDB Cluster)
For true multi‑master replication, consider InnoDB Cluster or Group Replication. These provide automatic conflict detection and resolution, but require additional licensing and a deeper understanding of MySQL Cluster internals.
Best Practices
Performance Tuning
- Enable
innodb_io_capacityon the slave to match the write load. - Use
read_buffer_sizeandsort_buffer_sizetuning on the master to optimize binlog generation. - Disable
innodb_flush_log_at_trx_commit=2on the slave to reduce latency (be aware of durability trade‑off).
Backup Automation Integration
Leverage MySQL Enterprise Backup or Percona XtraBackup for full physical backups. Automate backups on both master and slave using cron jobs or backup orchestration tools. Combine replication with point‑in‑time recovery: after a master failure, promote the slave, apply GTID logs, and roll back to the last consistent state.
Conclusion
Master‑slave replication in MySQL is a tried‑and‑true mechanism that, when configured correctly, offers high availability, read scaling, and a foundation for more advanced architectures like InnoDB Cluster. By carefully setting server variables, securing replication users, and monitoring the replication stream, DBAs can maintain robust, fault‑tolerant environments. The principles outlined here translate to other relational databases—Oracle DBA’s RMAN, SQL Server’s AlwaysOn, PostgreSQL’s streaming replication—emphasizing the universal need for reliable backup automation, performance tuning, and disciplined configuration management.
Ready to take your MySQL replication to the next level? Subscribe for more DBA insights, or follow us on LinkedIn for real‑world tips and best practices.
