Using Docker for MySQL master-slave replication offers several key advantages:

• Efficient resource utilization, ideal for environments with limited hardware resources
• Avoids the high configuration requirements and tedious manual installation steps associated with virtual machines
• Enables running multiple isolated MySQL instences on a single physical host
• Each container maintains an independent network stack and IP address, eliminating port conflicts between instances
• Streamlined workflow with container startup times measured in seconds

Pull the MySQL Docker Image

Begin by fetching the MySQL 5.7 image, which we'll use for both master and slave instances:

docker pull mysql:5.7

Launch Master and Slave Containers

Start two separate containers for the master and slave nodes, using unique names to avoid conflicts and mapping distinct host ports to the container's default 3306 port:

Master Container:

docker run -p 3339:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

Slave Container:

docker run -p 3340:3306 --name mysql-slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

Verify the containers are running with:

docker ps

You can test connectivity using tools like Navicat, connecting to localhost:3339 (master) and localhost:3340 (slave) with the root password 123456.

Configure the Master Instance

Acess the master container's interactive shell:

docker exec -it mysql-master /bin/bash

Install Vim to edit configuration files (not included by default):

apt-get update && apt-get install -y vim

Navgiate to the MySQL configuration directory and edit my.cnf:

cd /etc/mysql
vim my.cnf

Add the following lines under the [mysqld] section to enable binary logging and set a unique server ID:

[mysqld]
# Unique server ID within the network (must differ from slave)
server-id=100
# Enable binary logging for replication events
log-bin=mysql-master-bin

Restart the MySQL service to apply changes (this will stop the container):

service mysql restart

Restart the master container:

docker start mysql-master

Create a dedicated replication user on the master. First, log into MySQL:

mysql -uroot -p123456

Flush privileges and create the user with replication permissions:

FLUSH PRIVILEGES;
CREATE USER 'repl_user'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl_user'@'%';

Exit the MySQL shell with exit;.

Configure the Slave Instance

Access the slave container's shell:

docker exec -it mysql-slave /bin/bash

Install Vim using the same steps as the master:

apt-get update && apt-get install -y vim

Edit the MySQL configuration file (use /etc/mysql/mysql.conf.d/mysqld.cnf if my.cnf is not present):

vim /etc/mysql/my.cnf

Add the following lines under [mysqld]:

[mysqld]
# Unique server ID, different from the master
server-id=101
# Enable binary logging (required if this slave will act as a master for other nodes)
log-bin=mysql-slave-bin
# Configure relay log for forwarding replication events
relay_log=edu-mysql-relay-bin

Restart MySQL and the slave container:

service mysql restart
docker start mysql-slave

Establish Master-Slave Replication Link

First, retrieve the master container's internal IP address:

docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-master

Log into the master MySQL instance and check the replication status:

mysql -uroot -p123456 -h localhost -P3339

SHOW MASTER STATUS;

Note the values of File and Position (e.g., mysql-master-bin.000001 and 2830). Avoid write operations on the master until the slave is configured, as this will alter these values.

Log into the slave MySQL instance:

mysql -uroot -p123456 -h localhost -P3340

Run the replication setup command, replacing placeholders with actual values:

CHANGE MASTER TO 
MASTER_HOST='<master_internal_ip>', 
MASTER_USER='repl_user', 
MASTER_PASSWORD='123456', 
MASTER_PORT=3306, 
MASTER_LOG_FILE='<master_log_file>', 
MASTER_LOG_POS=<master_log_position>, 
MASTER_CONNECT_RETRY=30;

Start the slave replication process:

START SLAVE;

Verify the replication status:

SHOW SLAVE STATUS \G;

Confirm that Slave_IO_Running and Slave_SQL_Running both display Yes to indicate successful setup.

Troubleshooting Replication Issues

If Slave_IO_Running shows Connecting, common causes include:

1. Network connectivity issues between master and slave containers
2. Incorrect replication username or password
3. Mismatched MASTER_LOG_FILE or MASTER_LOG_POS values

Testing Replication

Create a test database on the master:

CREATE DATABASE test_replication;

Log into the slave instance and check if the database exists:

SHOW DATABASES;

The test_replication database should be present on the slave, confirming successful replication.