MySQL replication: master-slave synchronization

MySQL replication automatically copies data from a master database to one or more slave databases. This provides redundancy, enables load balancing for reads, and supports high availability setups.

Master-slave architecture

• Master: Receives writes, logs changes to binary log
• Slave: Reads binary log from master, applies changes to stay synchronized
• Applications: Write to master, read from slaves for load distribution

Basic setup steps

1. Configure Master (my.cnf)

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydatabase

2. Configure Slave (my.cnf)

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

3. Backup master and restore on slave

mysqldump --master-data -u user -p mydb > backup.sql
# Copy to slave and import
mysql -u user -p mydb < backup.sql

4. Start replication on slave

CHANGE MASTER TO
  MASTER_HOST='master-ip',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=123;
START SLAVE;

Verify replication

SHOW SLAVE STATUS\G
# Check: Slave_IO_Running = Yes, Slave_SQL_Running = Yes

Monitor replication

Watch for replication lag:

SHOW SLAVE STATUS\G | grep Seconds_Behind_Master;

Related: Database monitoring | High availability