MySQL replication is a foundational technology for scaling read-heavy database workloads and providing disaster recovery capabilities. Master-slave replication copies data from a primary server to one or more replicas, allowing read queries to be distributed across multiple servers while writes go to the master.
Configuring and Managing Replication
On the master, enable the binary log by setting log-bin in my.cnf and assign a unique server-id. Create a dedicated replication user with the REPLICATION SLAVE privilege. Take a consistent snapshot of the master database using mysqldump with the --master-data flag, which records the binary log position needed to initialize the slave.
On each slave, restore the snapshot, configure the server-id, and execute CHANGE MASTER TO with the master's hostname, replication user credentials, and the binary log coordinates from the snapshot. Start the slave with START SLAVE and verify replication status with SHOW SLAVE STATUS, checking that Slave_IO_Running and Slave_SQL_Running both show Yes.
Monitor replication lag by tracking the Seconds_Behind_Master metric. Persistent replication lag indicates that the slave cannot keep up with the master's write volume, which may require optimizing the slave's I/O subsystem or implementing parallel replication. For failover, promote a slave to master by stopping the slave process, resetting the slave configuration, and redirecting application connections to the new master.