Building Production-Ready MySQL Master-Slave Replication with Docker and ProxySQL: A Complete Guide

MEDIUM ARTICLE: Link

Introduction

Database replication is a critical component of modern applications that demand high availability, load distribution, and fault tolerance. In this comprehensive guide, I'll walk you through building a complete MySQL master-slave replication setup using Docker, featuring one primary server, three replica servers, and ProxySQL for intelligent query routing.

This isn't just another "hello world" database setup. By the end of this article, you'll have a production-ready database architecture that can handle read-heavy workloads, provide automatic failover capabilities, and scale horizontally as your application grows.

View Installation Guide

For detailed installation instructions, please refer to the INSTALLATION.md file.

What We're Building

Our architecture consists of:

1 Primary MySQL Server (user-primary): Handles all write operations
3 Replica MySQL Servers (user-replica1, user-replica2, user-replica3): Handle read operations
ProxySQL: Intelligent proxy for query routing and load balancing
Automated Scripts: Complete automation for setup, monitoring, and testing

Project Structure Overview

D:\KYOKU\KYOKU-DOCKER
|   .env
|   .gitignore
|   docker-compose.yml
|   readme.md
|   run.bat
|
\---mysql
    \---user
        +---proxy
        |       proxysql.conf.template
        |
        +---scripts
        |       check-replication.bat
        |       generate-configs.bat
        |       setup-replication.bat
        |       start-replication.bat
        |       test-replication.bat
        |
        \---sql
                01_user_db.sql
                insert_user_db.sql
                setup-replication-master.sql.template

This structure separates concerns beautifully - configuration templates, automation scripts, and SQL initialization files are all organized logically.

Docker Compose Configuration: The Foundation

Let's start with the heart of our setup - the docker-compose.yml file:

services:
  user-primary:
    image: mysql:8.4.0
    container_name: user-primary
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_USER_PASSWORD}
      MYSQL_REPLICATION_USER: ${MYSQL_USER_REPLICATION_USER}
      MYSQL_REPLICATION_PASSWORD: ${MYSQL_USER_REPLICATION_PASSWORD}
    ports:
      - "1000:3306"
    volumes:
      - ./mysql/user/sql/01_user_db.sql:/docker-entrypoint-initdb.d/01_user_db.sql
      - ./mysql/user/sql/insert_user_db.sql:/docker-entrypoint-initdb.d/insert_user_db.sql
      - ./mysql/user/sql/setup-replication-master.sql:/docker-entrypoint-initdb.d/setup-replication-master.sql
      - ./data/mysql/data/user-primary/data:/var/lib/mysql
    networks:
      - db-network
    command: >
      --server-id=1
      --log-bin=mysql-bin
      --binlog-do-db=USER
      --gtid-mode=ON
      --enforce-gtid-consistency=ON
      --log-replica-updates=ON
      --binlog-expire-logs-seconds=604800

Key Configuration Points

Server ID: Each MySQL server needs a unique server-id. Our primary uses ID 1, and replicas use 2, 3, and 4.

Binary Logging: --log-bin=mysql-bin enables binary logging, which is essential for replication as it records all changes to the database.

GTID Mode: Global Transaction Identifiers (GTIDs) make replication more reliable by uniquely identifying each transaction. This eliminates the need to track log file positions manually.

Database-Specific Replication: --binlog-do-db=USER ensures only changes to the USER database are replicated, improving performance and security.

Log Expiration: --binlog-expire-logs-seconds=604800 (7 days) automatically cleans up old binary logs to prevent disk space issues.

Replica Configuration

Each replica server follows a similar pattern:

user-replica1:
  image: mysql:8.4.0
  container_name: user-replica1
  environment:
    MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_USER_PASSWORD}
  ports:
    - "1001:3306"
  volumes:
    - ./data/mysql/data/user-replica1/data:/var/lib/mysql
  networks:
    - db-network
  depends_on:
    - user-primary
  command: >
    --server-id=2
    --relay-log=relay-bin
    --read-only=1
    --gtid-mode=ON
    --enforce-gtid-consistency=ON
    --log-replica-updates=ON
    --skip-replica-start=1

Read-Only Mode: --read-only=1 prevents accidental writes to replica servers, maintaining data integrity.

Relay Logs: --relay-log=relay-bin configures the relay log, which temporarily stores replication events before applying them.

Skip Auto Start: --skip-replica-start=1 prevents replication from starting automatically, giving us control over the initialization process.

ProxySQL: The Intelligent Router

ProxySQL acts as a middleware layer that intelligently routes queries based on their type:

user-proxysql:
  image: proxysql/proxysql:2.5.5
  container_name: user-proxysql
  ports:
    - "1004:6033" # MySQL protocol
    - "1005:6032" # Admin interface
  volumes:
    - ./mysql/user/proxy/proxysql.conf:/etc/proxysql.conf:ro
  networks:
    - db-network
  depends_on:
    - user-primary
    - user-replica1
    - user-replica2
    - user-replica3

ProxySQL Configuration Deep Dive

The proxysql.conf.template file contains sophisticated routing rules:

mysql_query_rules =
(
    {
        rule_id=1
        active=1
        match_pattern="^SELECT.*"
        destination_hostgroup=1
        apply=1
        comment="Route SELECT to read replicas"
    },
    {
        rule_id=2
        active=1
        match_pattern="^INSERT|UPDATE|DELETE.*"
        destination_hostgroup=0
        apply=1
        comment="Route writes to primary"
    }
)

This configuration automatically:

Routes all SELECT queries to replica servers (hostgroup 1)
Routes all INSERT, UPDATE, and DELETE queries to the primary server (hostgroup 0)

Database Schema: Real-World User Management

Our database schema represents a realistic user management system:

CREATE DATABASE IF NOT EXISTS USER;

USE USER;

CREATE TABLE IF NOT EXISTS UserType (
    id INT PRIMARY KEY AUTO_INCREMENT,
    `type` VARCHAR(8) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS Country (
    id INT PRIMARY KEY AUTO_INCREMENT,
    country VARCHAR(40) NOT NULL,
    code VARCHAR(4) NOT NULL,
    UNIQUE KEY uq_country (country),
    UNIQUE KEY uq_code (code)
);

CREATE TABLE IF NOT EXISTS `User` (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_type_id INT NOT NULL,
    email VARCHAR(320) NOT NULL,
    username VARCHAR(320) NOT NULL,
    display_name VARCHAR(320) NOT NULL,
    password_hash VARCHAR(700) NOT NULL,
    profile_pic VARCHAR(700) DEFAULT NULL,
    birth_date DATE DEFAULT NULL,
    country_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    --
    UNIQUE KEY uq_user_type_email (user_type_id, email),
    INDEX idx_email (email),
    INDEX idx_user_type_id (user_type_id),
    CONSTRAINT fk_user_user_type FOREIGN KEY (user_type_id) REFERENCES UserType (id) ON DELETE CASCADE,
    CONSTRAINT fk_user_country FOREIGN KEY (country_id) REFERENCES Country (id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS UserJWTToken (
    user_id BIGINT PRIMARY KEY,
    refresh_token VARCHAR(1000) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_token FOREIGN KEY (user_id) REFERENCES `User` (id) ON DELETE CASCADE
);

This schema demonstrates several best practices:

Proper Indexing: Critical columns like email and user_type_id are indexed for query performance.

Foreign Key Constraints: Maintains referential integrity with cascading deletes.

Flexible User Types: Supports different authentication methods (Google OAuth, email registration).

JWT Token Management: Secure token storage with proper relationships.

Automation Scripts: The Magic Behind the Scenes

Master Orchestration Script (run.bat)

The run.bat script orchestrates the entire setup process:

@echo off
setlocal enabledelayedexpansion

echo ================================================================================================
echo       Development Environment Setup Script Running......
echo ================================================================================================

REM Check if Docker is running
docker --version >nul 2>&1
if !errorlevel! neq 0 (
    echo [ERROR] Docker is not running or not installed!
    echo [INFO] Please start Docker Desktop and try again.
    pause
    exit /b 1
)

echo [INFO] Docker is running!

REM Generate configuration files from templates
echo ================================================================================================
echo    Step 1: Generate configuration files
echo ================================================================================================
call mysql\user\scripts\generate-configs.bat

REM Start Docker services
echo ================================================================================================
echo   Step 2: Starting Docker services
echo ================================================================================================
docker-compose up -d

REM Wait for services to initialize
echo [INFO] Waiting 30 seconds for all services to initialize...
timeout /t 30 /nobreak >nul

echo ================================================================================================
echo                                Setup Replication
echo ================================================================================================
call mysql\user\scripts\start-replication.bat

This script demonstrates professional automation practices:

Error Handling: Checks for Docker availability before proceeding
Clear Progress Indication: Users always know what's happening
Proper Timing: Waits for services to initialize before continuing
Modular Design: Delegates specific tasks to specialized scripts

Configuration Generation (generate-configs.bat)

One of the most elegant aspects of this setup is the template-based configuration system:

echo [INFO] Generating proxysql.conf from template...
powershell -Command "(Get-Content 'mysql\user\proxy\proxysql.conf.template') -replace '{{MYSQL_USER_PROXY_ADMIN}}', '%MYSQL_USER_PROXY_ADMIN%' -replace '{{MYSQL_USER_PROXY_PASSWORD}}', '%MYSQL_USER_PROXY_PASSWORD%' -replace '{{MYSQL_USER_USER}}', '%MYSQL_USER_USER%' -replace '{{MYSQL_USER_PASSWORD}}', '%MYSQL_USER_PASSWORD%' -replace '{{MYSQL_MONITOR_USER}}', '%MYSQL_MONITOR_USER%' -replace '{{MYSQL_MONITOR_PASSWORD}}', '%MYSQL_MONITOR_PASSWORD%' | Set-Content 'mysql\user\proxy\proxysql.conf'"

This approach provides several benefits:

Security: Sensitive information is kept in environment variables
Flexibility: Easy to deploy across different environments
Maintainability: Template changes automatically apply everywhere

Replication Setup (setup-replication.bat)

The replication setup script handles the complex process of configuring MySQL 8.x replication:

:configure_replication
echo [INFO] Checking GTID mode on %replica_name%...
docker exec %replica_name% mysql -uroot -p%MYSQL_ROOT_USER_PASSWORD% -e "SHOW VARIABLES LIKE 'gtid_mode';"

echo [INFO] Setting up replication for %replica_name%...
docker exec %replica_name% mysql -uroot -p%MYSQL_ROOT_USER_PASSWORD% -e "STOP REPLICA; RESET REPLICA ALL; CHANGE REPLICATION SOURCE TO SOURCE_HOST='user-primary', SOURCE_USER='%MYSQL_USER_REPLICATION_USER%', SOURCE_PASSWORD='%MYSQL_USER_REPLICATION_PASSWORD%', SOURCE_AUTO_POSITION=1, GET_SOURCE_PUBLIC_KEY=1; START REPLICA;"

Key MySQL 8.x Changes: The script uses modern MySQL 8.x syntax (CHANGE REPLICATION SOURCE instead of CHANGE MASTER, SHOW REPLICA STATUS instead of SHOW SLAVE STATUS).

GTID Auto-positioning: SOURCE_AUTO_POSITION=1 eliminates the need to manually specify binary log positions.

Security: GET_SOURCE_PUBLIC_KEY=1 handles MySQL 8.x's enhanced security requirements.

Monitoring and Testing

The setup includes comprehensive monitoring capabilities:

# Check replication status
docker exec user-replica1 mysql -uroot -p%MYSQL_ROOT_USER_PASSWORD% -e "SHOW REPLICA STATUS\G" | findstr /C:"Replica_IO_Running" /C:"Replica_SQL_Running" /C:"Seconds_Behind_Source" /C:"Last_Error"

# Test replication functionality
docker exec user-primary mysql -uroot -p%MYSQL_ROOT_USER_PASSWORD% USER -e "INSERT INTO UserType (type) VALUES ('%test_value%');"

# Verify data propagation
docker exec user-replica1 mysql -uroot -p%MYSQL_ROOT_USER_PASSWORD% USER -e "SELECT * FROM UserType WHERE type = '%test_value%';"

Security Considerations

This setup implements several security best practices:

User Management

-- Create replication user with minimal privileges
CREATE USER IF NOT EXISTS 'replication_user'@'%' IDENTIFIED WITH caching_sha2_password BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

-- Create monitor user for ProxySQL with limited access
CREATE USER IF NOT EXISTS 'monitor_user'@'%' IDENTIFIED WITH caching_sha2_password BY 'monitor_password';
GRANT REPLICATION CLIENT ON *.* TO 'monitor_user'@'%';
GRANT PROCESS ON *.* TO 'monitor_user'@'%';
GRANT SELECT ON performance_schema.* TO 'monitor_user'@'%';

Network Security

All services communicate through a dedicated Docker network (db-network)
External access is limited to specific ports with defined purposes
ProxySQL acts as a security layer, preventing direct access to database servers

Authentication

Uses MySQL 8.x's caching_sha2_password authentication plugin
Environment variables keep sensitive information out of configuration files
Template-based configuration prevents credential exposure in version control

Performance Optimization

Connection Pooling

ProxySQL provides sophisticated connection pooling:

mysql_variables= {
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    poll_timeout=2000
    connect_timeout_server=3000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    ping_interval_server_msec=120000
}

Load Balancing

The replica servers are configured with weights:

mysql_servers = (
    {
        address="user-primary"
        port=3306
        hostgroup=0
        weight=1000
        comment="Primary Server"
    },
    {
        address="user-replica1"
        port=3306
        hostgroup=1
        weight=900
        comment="Read Replica 1"
    }
)

Higher weights mean more connections, allowing fine-tuned load distribution.

Binary Log Management

--binlog-expire-logs-seconds=604800

Automatically purges binary logs older than 7 days, preventing disk space issues while maintaining adequate recovery windows.

Troubleshooting Common Issues

Replication Lag

Monitor replication lag with:

docker exec user-replica1 mysql -uroot -ppassword -e "SHOW REPLICA STATUS\G" | grep "Seconds_Behind_Source"

Common Causes:

Network latency between primary and replica
Heavy write load on primary
Insufficient replica server resources

Solutions:

Increase replica server resources
Implement parallel replication
Optimize slow queries

Connection Issues

If replicas can't connect to the primary:

Check Network Connectivity:

docker exec user-replica1 ping user-primary

Verify User Privileges:

SELECT User, Host FROM mysql.user WHERE User = 'replication_user';

Check GTID Status:

SHOW VARIABLES LIKE 'gtid_mode';

ProxySQL Routing Issues

Debug query routing:

-- Connect to ProxySQL admin interface
mysql -h127.0.0.1 -P6032 -uadmin -padmin

-- Check query routing statistics
SELECT rule_id, match_digest, match_pattern, destination_hostgroup, apply FROM mysql_query_rules;

Production Deployment Considerations

High Availability

For production environments, consider:

Automatic Failover: Implement tools like MHA (Master High Availability) or Orchestrator
Load Balancer Redundancy: Deploy multiple ProxySQL instances behind a load balancer
Cross-Region Replication: Set up replicas in different geographic regions

Monitoring

Implement comprehensive monitoring:

Replication Lag Alerts: Alert when lag exceeds acceptable thresholds
Connection Pool Monitoring: Track ProxySQL connection statistics
Performance Metrics: Monitor query response times and throughput

Backup Strategy

# Consistent backup across all servers
docker exec user-primary mysqldump --single-transaction --routines --triggers --all-databases > backup.sql

Scaling Considerations

As your application grows:

Read Replica Scaling: Add more replica servers to handle increased read load
Sharding: Implement horizontal partitioning for write-heavy workloads
Connection Pooling: Fine-tune ProxySQL connection pool settings

Running the Complete Setup

Getting everything running is straightforward:

Clone and Configure:

git clone your-repository
cd KYOKU-DOCKER

Set Environment Variables: Create a .env file with:

MYSQL_ROOT_USER_PASSWORD=your_root_password
MYSQL_USER_REPLICATION_USER=replication_user
MYSQL_USER_REPLICATION_PASSWORD=replication_password
MYSQL_USER_USER=app_user
MYSQL_USER_PASSWORD=app_password
MYSQL_USER_MONITOR_USER=monitor_user
MYSQL_USER_MONITOR_PASSWORD=monitor_password

Execute Setup:

./run.bat

Verify Installation:

# Check replication status
./mysql/user/scripts/check-replication.bat

# Test functionality
./mysql/user/scripts/test-replication.bat

Connection Details

After successful setup, your services are available at:

Primary Server: localhost:1000
Replica 1: localhost:1001
Replica 2: localhost:1002
Replica 3: localhost:1003
ProxySQL (MySQL Protocol): localhost:1004
ProxySQL (Admin Interface): localhost:1005

Conclusion

This MySQL replication setup provides a solid foundation for scalable, high-performance database architectures. The combination of Docker containerization, automated configuration management, and intelligent query routing creates a system that's both developer-friendly and production-ready.

Key takeaways:

Automation is King: The script-based approach eliminates manual configuration errors and speeds deployment
Security First: Proper user management and network isolation protect your data
Monitoring Matters: Comprehensive status checking helps identify issues early
Template-Based Configuration: Separating configuration from secrets improves security and flexibility

Whether you're building a new application or upgrading an existing system, this architecture provides the scalability and reliability your users expect. The modular design makes it easy to adapt for different requirements, and the comprehensive automation ensures consistent deployments across environments.

Remember: this is just the beginning. As your application scales, you'll want to add features like automatic failover, cross-region replication, and advanced monitoring. But with this foundation, you're well-prepared for those next steps.

Happy coding! 🚀

If you found this article helpful, please give it a clap and follow me for more deep-dive technical content on database architecture, containerization, and scalable system design.

Name		Name	Last commit message	Last commit date
Latest commit History 3 Commits
mysql/user		mysql/user
.env.example		.env.example
.gitignore		.gitignore
INSTALLATION.md		INSTALLATION.md
LICENSE		LICENSE
README.md		README.md
docker-compose.yml		docker-compose.yml
run.bat		run.bat
tree.txt		tree.txt

License

POULASTAAdAS/mysql-master-slave-architecture

Folders and files

Latest commit

History

Repository files navigation