Network Topology and Port Allocasion

Node Role	Host IP	Mapped Port
Primary-A	192.168.153.19	3321
Replica-A	192.168.153.19	3322
Primary-B	192.168.153.19	3323
Replica-B	192.168.153.19	3324

Directory Initialization and Image Retrieval

Execute the following shell commands to fetch the database image and prepare persistent storage paths:

docker pull mysql:5.7

BASE_DIR="/opt/mysql-cluster"
for node in pri-a rep-a pri-b rep-b; do
  mkdir -p ${BASE_DIR}/${node}/{cfg,dat,log}
done

Container Provisioning

Launch each database instance, binding the host directories and exposing the designated ports:

docker run -d --name pri-a \
  -p 3321:3306 \
  --privileged=true \
  -e MYSQL_ROOT_PASSWORD=SecureRt!2024 \
  -v ${BASE_DIR}/pri-a/log:/var/log/mysql \
  -v ${BASE_DIR}/pri-a/dat:/var/lib/mysql \
  -v ${BASE_DIR}/pri-a/cfg:/etc/mysql/conf.d \
  mysql:5.7

docker run -d --name rep-a \
  -p 3322:3306 \
  --privileged=true \
  -e MYSQL_ROOT_PASSWORD=SecureRt!2024 \
  -v ${BASE_DIR}/rep-a/log:/var/log/mysql \
  -v ${BASE_DIR}/rep-a/dat:/var/lib/mysql \
  -v ${BASE_DIR}/rep-a/cfg:/etc/mysql/conf.d \
  mysql:5.7

docker run -d --name pri-b \
  -p 3323:3306 \
  --privileged=true \
  -e MYSQL_ROOT_PASSWORD=SecureRt!2024 \
  -v ${BASE_DIR}/pri-b/log:/var/log/mysql \
  -v ${BASE_DIR}/pri-b/dat:/var/lib/mysql \
  -v ${BASE_DIR}/pri-b/cfg:/etc/mysql/conf.d \
  mysql:5.7

docker run -d --name rep-b \
  -p 3324:3306 \
  --privileged=true \
  -e MYSQL_ROOT_PASSWORD=SecureRt!2024 \
  -v ${BASE_DIR}/rep-b/log:/var/log/mysql \
  -v ${BASE_DIR}/rep-b/dat:/var/lib/mysql \
  -v ${BASE_DIR}/rep-b/cfg:/etc/mysql/conf.d \
  mysql:5.7

Configuration File Adjustment

Modify the custom configuration directory for each node to define server identifiers, binary logging behavior, and replication filters.

Primary-A (/opt/mysql-cluster/pri-a/cfg/replication.cnf)

[mysqld]
server-id=101
log-bin=pri-a-binlog
binlog-ignore-db=information_schema
binlog-do-db=app_data
binlog_cache_size=2M
binlog_format=statement
expire_logs_days=10
log-slave-updates
slave-skip-errors=1062,1032
auto-increment-increment=2
auto-increment-offset=1

Primary-B (/opt/mysql-cluster/pri-b/cfg/replication.cnf)

[mysqld]
server-id=201
log-bin=pri-b-binlog
binlog-ignore-db=information_schema
binlog-do-db=app_data
binlog_cache_size=2M
binlog_format=statement
expire_logs_days=10
log-slave-updates
slave-skip-errors=1062,1032
auto-increment-increment=2
auto-increment-offset=2

Replica-A (/opt/mysql-cluster/rep-a/cfg/relay.cnf)

[mysqld]
server-id=301
relay-log=rep-a-relay-bin

Replica-B (/opt/mysql-cluster/rep-b/cfg/relay.cnf)

[mysqld]
server-id=401
relay-log=rep-b-relay-bin

After updating the configuration files, restart the container group to apply the changes:

docker restart pri-a rep-a pri-b rep-b

Replication Account Creation

Access each primary instance to provision a dedicated account for synchronization tasks.

Connect to Primary-A:

docker exec -it pri-a bash -c "mysql -uroot -pSecureRt!2024"

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

Connect to Primary-B:

docker exec -it pri-b bash -c "mysql -uroot -pSecureRt!2024"

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

Establishing Primary-to-Replica Synchronization

On the replica nodes, point them to their respective upstream primaries. Retrieve the current binary log coordinates from the primary using SHOW MASTER STATUS; before executing the connection command.

Configure Replica-A to track Primary-A (Port 3321):

docker exec -it rep-a bash -c "mysql -uroot -pSecureRt!2024"

CHANGE MASTER TO
  MASTER_HOST='192.168.153.19',
  MASTER_PORT=3321,
  MASTER_USER='sync_worker_a',
  MASTER_PASSWORD='ReplicaSync#99',
  MASTER_LOG_FILE='pri-a-binlog.000001',
  MASTER_LOG_POS=547;

START SLAVE;
SHOW SLAVE STATUS\G

Configure Replica-B to track Primary-B (Port 3323):

docker exec -it rep-b bash -c "mysql -uroot -pSecureRt!2024"

CHANGE MASTER TO
  MASTER_HOST='192.168.153.19',
  MASTER_PORT=3323,
  MASTER_USER='sync_worker_b',
  MASTER_PASSWORD='ReplicaSync#99',
  MASTER_LOG_FILE='pri-b-binlog.000001',
  MASTER_LOG_POS=547;

START SLAVE;
SHOW SLAVE STATUS\G

Verify that Slave_IO_Running and Slave_SQL_Running both report Yes in the output.

Bidirectional Primary Synchronization Setup

To achieve an active-active topology where both primary nodes replicate each other, execute cross-connection commands.

On Primary-B, establish a replication channel pointing to Primary-A:

CHANGE MASTER TO
  MASTER_HOST='192.168.153.19',
  MASTER_PORT=3321,
  MASTER_USER='sync_worker_a',
  MASTER_PASSWORD='ReplicaSync#99',
  MASTER_LOG_FILE='pri-a-binlog.000001',
  MASTER_LOG_POS=547;
START SLAVE;
SHOW SLAVE STATUS\G

On Primary-A, establish a replication channel pointing to Primary-B:

CHANGE MASTER TO
  MASTER_HOST='192.168.153.19',
  MASTER_PORT=3323,
  MASTER_USER='sync_worker_b',
  MASTER_PASSWORD='ReplicaSync#99',
  MASTER_LOG_FILE='pri-b-binlog.000001',
  MASTER_LOG_POS=547;
START SLAVE;
SHOW SLAVE STATUS\G

Validation and Binary Log Format Considerations

Since the configuration restricts replication to the app_data database, insure all test queries execute against that specific schema. When inserting data, be aware that the statement binlog format logs exact SQL statements. System-level functions such as @@hostname will record the literal hostname of the originating server, which may cause inconsistencies across nodes during failover. Switching binlog_format=statement to binlog_format=row in the configuraton files guarantees that actual data changes are replicated rather than raw SQL statements, preventing environment-specific variable conflicts in production deployments.