MySQL

MySQL Replication Setup: Master-Slave Configuration

Professional guide to mysql replication setup: master-slave configuration with real-world examples and optimization techniques.

Tue Oct 07 2025 08:09:01 GMT+0000 (Coordinated Universal Time) Jamaurice Holt 5 min read
mysql database optimization

Discover expert insights on MySQL Replication Setup: Master-Slave Configuration for MySQL database administrators and developers.

Overview

MySQL Master-Slave Replication enables data from one MySQL server (master) to be copied automatically to one or more MySQL servers (slaves). This setup provides high availability, load balancing, and disaster recovery capabilities.

Replication is essential for scaling read operations, creating backups without impacting production, and ensuring business continuity.

💡 Key Concepts

How MySQL Replication Works

  1. Binary Log (Binlog) - Master records all changes
  2. Relay Log - Slave receives and stores changes
  3. SQL Thread - Slave applies changes to its database
-- Replication process flow
Master: Write operation → Binary log
  ↓
Slave: I/O thread → Relay log → SQL thread → Database

Replication Types

  • Asynchronous Replication (default) - Master doesn't wait for slave confirmation
  • Semi-Synchronous Replication - Master waits for at least one slave to acknowledge
  • Synchronous Replication - All slaves must acknowledge (Group Replication)

Prerequisites

Before setting up replication, ensure you have:

✅ MySQL 5.7+ or MySQL 8.0 installed on both servers ✅ Network connectivity between master and slave ✅ Unique server IDs for each MySQL instance ✅ Binary logging enabled on master ✅ Replication user account created

Step-by-Step Master-Slave Setup

Step 1: Configure the Master Server

Edit MySQL configuration file (my.cnf or my.ini):

[mysqld]
# Unique server ID (must be different from slaves)
server-id = 1

# Enable binary logging
log-bin = mysql-bin

# Binary log format (ROW recommended for accuracy)
binlog-format = ROW

# Databases to replicate (optional - omit to replicate all)
binlog-do-db = production_db

# Databases to ignore (optional)
# binlog-ignore-db = test_db
# binlog-ignore-db = information_schema

Restart MySQL:

# Linux
sudo systemctl restart mysql

# Or
sudo service mysql restart

Step 2: Create Replication User on Master

-- Connect to master MySQL
mysql -u root -p

-- Create dedicated replication user
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'StrongPassword123!';

-- Grant replication privileges
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

-- Apply changes
FLUSH PRIVILEGES;

🔒 Security Tip: Use a strong password and limit access by IP address instead of %:

CREATE USER 'replication_user'@'192.168.1.20' IDENTIFIED BY 'StrongPassword123!';

Step 3: Get Master Binary Log Position

-- Lock tables to get consistent snapshot
FLUSH TABLES WITH READ LOCK;

-- Record the File and Position values
SHOW MASTER STATUS;

Output example:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      157 | production_db|                  |
+------------------+----------+--------------+------------------+

⚠️ Important: Keep this terminal open! Don't unlock tables until slave is configured.

Step 4: Create Database Backup (Optional but Recommended)

In a new terminal:

# Create database dump while tables are locked
mysqldump -u root -p --all-databases --master-data=2 > master_backup.sql

# Or for specific database
mysqldump -u root -p --master-data=2 production_db > production_backup.sql

💡 Tip: The --master-data=2 option includes the binary log position in the dump file.

Step 5: Unlock Master Tables

Back in the original terminal:

-- Unlock tables
UNLOCK TABLES;

Step 6: Configure the Slave Server

Edit MySQL configuration file:

[mysqld]
# Unique server ID (must be different from master)
server-id = 2

# Enable relay log
relay-log = mysql-relay-bin

# Read-only mode (recommended for slaves)
read-only = 1

# Skip errors (use cautiously - only for specific error codes)
# slave-skip-errors = 1062,1032

# Binary logging on slave (for chained replication)
log-bin = mysql-bin
binlog-format = ROW

Restart slave MySQL:

sudo systemctl restart mysql

Step 7: Restore Backup on Slave (if taken)

# Import master backup to slave
mysql -u root -p < master_backup.sql

Step 8: Configure Replication on Slave

-- Connect to slave MySQL
mysql -u root -p

-- Stop slave if already running
STOP SLAVE;

-- Configure master connection
CHANGE MASTER TO
    MASTER_HOST='192.168.1.10',        -- Master IP
    MASTER_USER='replication_user',
    MASTER_PASSWORD='StrongPassword123!',
    MASTER_LOG_FILE='mysql-bin.000001', -- From SHOW MASTER STATUS
    MASTER_LOG_POS=157;                 -- From SHOW MASTER STATUS

-- Start replication
START SLAVE;

Step 9: Verify Replication Status

-- Check slave status
SHOW SLAVE STATUS\G

Look for these key values:

✅ Slave_IO_Running: Yes ✅ Slave_SQL_Running: Yes ✅ Seconds_Behind_Master: 0 (or low number) ✅ Last_Error: (should be empty)

-- Key output to verify
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.10
                  Master_User: replication_user
             Slave_IO_Running: Yes    -- Must be Yes
            Slave_SQL_Running: Yes    -- Must be Yes
        Seconds_Behind_Master: 0      -- Should be 0 or low
                 Last_IO_Error:       -- Should be empty
                Last_SQL_Error:       -- Should be empty

Testing Replication

Test 1: Create Test Data on Master

-- On master
CREATE DATABASE replication_test;
USE replication_test;

CREATE TABLE test_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    message VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO test_table (message) VALUES ('Replication test 1');
INSERT INTO test_table (message) VALUES ('Replication test 2');

Test 2: Verify Data on Slave

-- On slave
USE replication_test;
SELECT * FROM test_table;

✅ Expected Result: You should see the same data on the slave.

🚀 Advanced Configuration

Semi-Synchronous Replication

Advantages:

  • Higher data durability
  • Reduced risk of data loss
  • Master waits for slave acknowledgment

On Master:

-- Install plugin
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

-- Enable semi-sync
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- Set timeout (microseconds)
SET GLOBAL rpl_semi_sync_master_timeout = 1000;

On Slave:

-- Install plugin
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

-- Enable semi-sync
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- Restart slave I/O thread
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

Verify:

-- On master
SHOW STATUS LIKE 'Rpl_semi_sync%';

Multi-Source Replication (MySQL 5.7+)

Configure slave to replicate from multiple masters:

-- Configure first master
CHANGE MASTER TO
    MASTER_HOST='master1.example.com',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=157
FOR CHANNEL 'master1';

-- Configure second master
CHANGE MASTER TO
    MASTER_HOST='master2.example.com',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=250
FOR CHANNEL 'master2';

-- Start both channels
START SLAVE FOR CHANNEL 'master1';
START SLAVE FOR CHANNEL 'master2';

Monitoring and Maintenance

Monitor Replication Lag

-- Check lag on slave
SHOW SLAVE STATUS\G

-- Or use Performance Schema
SELECT * FROM performance_schema.replication_connection_status\G
SELECT * FROM performance_schema.replication_applier_status\G

Common Monitoring Queries

-- Replication lag in seconds
SELECT VARIABLE_VALUE as Seconds_Behind_Master
FROM performance_schema.global_status
WHERE VARIABLE_NAME='Slave_SQL_running_state';

-- Show relay log info
SHOW RELAYLOG EVENTS;

-- Check binary log size
SHOW BINARY LOGS;

Troubleshooting Common Issues

Issue 1: Slave_IO_Running: Connecting

⚠️ Problem: Slave can't connect to master

Solutions:

# Check network connectivity
ping master_ip_address
telnet master_ip_address 3306

# Verify firewall rules
sudo ufw allow 3306/tcp

# Check master MySQL is listening on network
# In my.cnf:
bind-address = 0.0.0.0  # Not just 127.0.0.1

Issue 2: Slave SQL Error 1062 (Duplicate Key)

⚠️ Problem: Row already exists on slave

Solution:

-- Skip one error (use cautiously!)
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;

-- Or reset slave and resync
STOP SLAVE;
RESET SLAVE;
-- Then reconfigure from Step 8

Issue 3: Replication Lag

🚀 Solutions:

-- Increase parallel replication workers (MySQL 5.6+)
STOP SLAVE SQL_THREAD;
SET GLOBAL slave_parallel_workers = 4;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
START SLAVE SQL_THREAD;

# In my.cnf
[mysqld]
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK

Issue 4: Binary Log Position Lost

Solution - Reset and resync:

-- On master: Get current position
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

-- On slave: Reset and reconfigure
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO
    MASTER_LOG_FILE='new_file',
    MASTER_LOG_POS=new_position;
START SLAVE;

-- On master: Unlock
UNLOCK TABLES;

Best Practices

✅ Configuration Best Practices

  1. Use unique server IDs

    -- Verify unique IDs
SELECT @@server_id;

  2. Enable GTID for easier failover (MySQL 5.6+)

    [mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON

  3. Set appropriate binary log retention

    -- Retain logs for 7 days
SET GLOBAL binlog_expire_logs_seconds = 604800;

  4. Monitor disk space for binary logs

    # Check binary log disk usage
du -sh /var/lib/mysql/mysql-bin.*

✅ Security Best Practices

  1. Use SSL/TLS for replication

    CHANGE MASTER TO
    MASTER_SSL=1,
    MASTER_SSL_CA='/path/to/ca.pem',
    MASTER_SSL_CERT='/path/to/client-cert.pem',
    MASTER_SSL_KEY='/path/to/client-key.pem';

  2. Limit replication user privileges

    -- Only grant necessary privileges
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave_ip';

✅ Operational Best Practices

  1. Regular replication health checks
  2. Automated monitoring and alerting
  3. Document failover procedures
  4. Test failover regularly
  5. Keep master and slave MySQL versions in sync

Conclusion

MySQL Master-Slave replication is a powerful tool for:

  • ✅ Scaling read operations across multiple slaves
  • ✅ Creating live backups without impacting production
  • ✅ Disaster recovery with minimal data loss
  • ✅ Geographic distribution of data

Remember: Replication is not a substitute for backups! Always maintain separate backup strategies.

Next Steps

  1. ✅ Set up monitoring for replication lag
  2. ✅ Implement automated failover procedures
  3. ✅ Consider upgrading to GTID-based replication
  4. ✅ Explore MySQL Group Replication for multi-master setups
  5. ✅ Test your disaster recovery plan regularly

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in MySQL, SQL optimization, and enterprise database solutions.

Related Articles

Need help with MySQL optimization? Contact us for expert database consulting.

Continue Reading

Leadership

Leadership in Motion: Technical Leadership Lessons from Database Operations

How a decade of database administration shaped my approach to technical leadership — lessons on incident response, mentoring engineers,...

July 10, 2024 · Read article → Performance

Database Performance Tuning: Lessons from the Trenches

Real-world strategies for optimizing database performance in high-traffic production environments — query plan analysis, indexing, caching,...

August 22, 2024 · Read article → AI

AI Governance and Hardware: Why Sovereign Refusal Belongs in Silicon

Software guardrails can be prompted around. The real frontier in AI safety is anchoring refusal below the model — at the hardware level. A...

September 15, 2024 · Read article →