How MySQL Master-Slave Replication Works

When deploying MySQL in production environments, a single database instance often falls short in terms of reliability, availability, and scalability. The standard approach involves using master-slave replication to synchronize data across multiple servers, combined with read-write separation through a proxy layer to handle high concurrency loads.

Replication Mechanism

The replication process operates through a coordinated thread architecture on the slave server:

1. The slave instance spawns two background threads: an I/O thread and an SQL thread
2. The I/O thread connects to the master and requests binary log events, writing received data to a relay log file
3. The master maintains a log dump thread that transmits binary log contents to connected slaves
4. The SQL thread reads events from the relay log, parses them, and executes the corresponding operations on the local database

This design ensures that data modifications on the master are captured and replayed on the slave, maintaining consistency between the two instances.

Configurasion Workflow

The following example demonstrates a basic master-slave setup with two MySQL instances:

Master Server (10.0.0.1:3000) Slave Server (10.0.0.2:3003)

Step 1: Configure the Master

Edit the MySQL configuration file (/etc/my.cnf):

[mysqld]
log-bin = 3306-bin
server-id = 1

Enable binary logging and assign a unique server identifier. The binary log records all data changes and is essential for replication.

Step 2: Create a Replication User

Connect to the master database and create an account for replication:

mysql> CREATE USER 'repl_user'@'10.0.0.2' IDENTIFIED BY 'secure_password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'10.0.0.2';
mysql> FLUSH PRIVILEGES;

If encountering permission errors, ensure the skip-grant-tables option is not active, then reload privileges before proceeding.

Step 3: Export Data from Master

Backup the existing data to transfer to the slave:

mysqldump -h127.0.0.1 -uroot -p -P3000 -A -B --master-data=2 --events > /tmp/master_backup.sql

Step 4: Import Data to Slave

Restore the backup on the slave instance:

mysql -h127.0.0.1 -uroot -p -P3003 < /tmp/master_backup.sql

Step 5: Configure the Slave

Update the slave configuration file:

[mysqld]
log-bin = 3306-bin
server-id = 2

The server-id must differ from the master to avoid conflicts.

Step 6: Point Slave to Master

Execute the following commands while connected to the slave:

CHANGE MASTER TO
    MASTER_HOST='10.0.0.1',
    MASTER_PORT=3000,
    MASTER_USER='repl_user',
    MASTER_PASSWORD='secure_password',
    MASTER_LOG_FILE='3306-bin.000001',
    MASTER_LOG_POS=333;

Step 7: Start Replication

mysql> START SLAVE;

Verification

Monitor the replication status with:

mysql> SHOW SLAVE STATUS\G

Key metrics to verify:

Any values other than "Yes" for the running threads indicate a problem requiring investigation.

Test the replication by creating a database on the master:

mysql> CREATE DATABASE validation_test;

Confirm the new database appears on the slave server to verify end-to-end synchronization.