Environment Preparation

This procedure outlines the configuration of a MySQL 5.7 primary-replica architecture utilizing Docker on a Linux host. Ensure the Docker daemon is active. The host system in this demonstration operates on the internal subnet 10.0.4.50.

Fetching the Database Image

Retrieve the official MySQL 5.7 image from the container registry:

docker pull mysql:5.7

Validate the image availability locally:

docker images | grep mysql

Instantiating Primary and Replica Containers

Prepare persistent storage directories on the host filesystem to isolate configuration files and data volumes:

mkdir -p /opt/containers/db-primary/{conf,data}
mkdir -p /opt/containers/db-replica/{conf,data}

Deploy the primary database instance:

docker run -d \
  --name db-primary \
  -p 4306:3306 \
  -v /opt/containers/db-primary/conf:/etc/mysql/conf.d \
  -v /opt/containers/db-primary/data:/var/lib/mysql \
  -e MYSQL_ROOT_PASSWORD=SecureP@ss2024 \
  mysql:5.7

Deploy the replica database instance:

docker run -d \
  --name db-replica \
  -p 4307:3306 \
  -v /opt/containers/db-replica/conf:/etc/mysql/conf.d \
  -v /opt/containers/db-replica/data:/var/lib/mysql \
  -e MYSQL_ROOT_PASSWORD=SecureP@ss2024 \
  mysql:5.7

Verify that both processes are running and port mappings are active:

docker ps --format "table {{.Names}}\t{{.Ports}}"

Network Connectivity Verification

Docker's default bridge network dynamically assigns IP addresses. Extract the internal addresses for both containers:

docker inspect -f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' db-primary db-replica

Validate routing between the two instances by executing a ping command from the primary container:

docker exec db-primary ping -c 3 <REPLICA_IP>

Zero packet loss confirms successful inter-container communication across the Docker bridge.

Configuring Replication Parameters

Edit the mounted configuration files to activate binary logging and relay logging mechanisms.

Primary Node Configuration (/opt/containers/db-primary/conf/my.cnf):

[mysqld]
server-id = 100
log-bin = primary-bin
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-do-db = app_production
binlog_format = ROW

Replica Node Configuration (/opt/containers/db-replica/conf/my.cnf):

[mysqld]
server-id = 200
relay-log = replica-relay-bin
read_only = 1

Restart both containers to apply the new MySQL directives:

docker restart db-primary db-replica

Establishing Replication Links

Enter the primary container and authenticate with the root credentials:

docker exec -it db-primary mysql -uroot -pSecureP@ss2024

Create a dedicated replication account and assign the required privileges:

CREATE USER 'sync_agent'@'%' IDENTIFIED BY 'ReplicaSync!99';
GRANT REPLICATION SLAVE ON *.* TO 'sync_agent'@'%';
FLUSH PRIVILEGES;

Query the primary server to obtain the current binary log coordinates:

SHOW MASTER STATUS;

Record the values displayed under the File and Position columns. Avoid executing additional data manipulation language commands on the primary after this step to prevent coordinate drift.

Access the replica container:

docker exec -it db-replica mysql -uroot -pSecureP@ss2024

Bind the replica to the primary by specifying the host IP, mapped port, and authentication details:

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO
  MASTER_HOST='10.0.4.50',
  MASTER_USER='sync_agent',
  MASTER_PASSWORD='ReplicaSync!99',
  MASTER_PORT=4306,
  MASTER_LOG_FILE='primary-bin.000001',
  MASTER_LOG_POS=154;
START SLAVE;

Inspect the replication thread status:

SHOW SLAVE STATUS\G

Confirm that both Slave_IO_Running and Slave_SQL_Running display Yes. If the IO thread reports Connecting, verify that MASTER_HOST references the reachable host IP rather than the internal Docker IP. If the SQL thread fails, inspect error logs for schema mismatches or adjust MASTER_LOG_POS to align with the primary's current state.

Synchronization Validation

Initialize the designated database and populate a sample table on the primary node:

CREATE DATABASE app_production;
USE app_production;
CREATE TABLE system_metrics (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cpu_usage DECIMAL(5,2),
  log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO system_metrics (cpu_usage) VALUES (12.8), (45.3);

Query the replica node immediately to verify data propagation:

USE app_production;
SELECT * FROM system_metrics;

Identical result sets across both nodes indicate successful binary log transmission and execution on the replica.