This guide demonstrates how to configure MySQL master-slave replication using Docker containers in a WSL2 Ubuntu environment. The setup involves two independent MySQL instances where one acts as the primary (master) and the other as a read-only replica (slave).

Environment Setup

The environment uses WSL2 with Ubuntu 20.04 LTS. Docker is installed and running within the Linux distribution. Verify your setup with:

$ wsl --list --all
Ubuntu-20.04 (default)

$ uname -a
Linux HOSTNAME 4.19.128-microsoft-standard #1 SMP Tue Jun 23 12:58:10 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

$ lsb_release -a
Distributor ID: Ubuntu
Description:    Ubuntu 20.04.1 LTS
Release:        20.04
Codename:       focal

MySQL Image Preparation

First, search for available MySQL images in Docker Hub:

$ docker search mysql
NAME                              DESCRIPTION                                     STARS     OFFICIAL
mysql                             MySQL is a widely used, open-source relation…   10072     [OK]
mariadb                           MariaDB is a community-developed fork of MyS…   3692      [OK]
mysql/mysql-server                Optimized MySQL Server Docker images. Create…   736

Pull the MySQL 8.0.21 image:

$ docker image ls -a
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
hello-world         latest              bf756fb1ae65        9 months ago        13.3kB

$ docker pull mysql:8.0.21
8.0.21: Pulling from library/mysql
...
Status: Downloaded newer image for mysql:8.0.21

$ docker image ls -a
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
mysql               8.0.21              8e85dd5c3255        4 days ago          544MB
hello-world         latest              bf756fb1ae65        9 months ago        13.3kB

Container Deployment

Launch two MySQL containers with different port mappings:

Primary (Master) Container:

$ docker run -p 3310:3306 --name mysql-primary -e MYSQL_ROOT_PASSWORD=SecureRoot123 -d mysql:8.0.21
a12eb8cd4c19467d6daee6d899b61df3d4c19b0aabde526153de8651676fb505

Replica (Slave) Container:

$ docker run -p 3311:3306 --name mysql-replica -e MYSQL_ROOT_PASSWORD=SecureRoot123 -d mysql:8.0.21
6d999ccc3c9f360e35131030a849efbde3f2f3b1f535b4ee7231aff017dd77c4

Verify both containers are running:

$ docker container ls -a
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
6d999ccc3c9f        mysql:8.0.21        "docker-entrypoint.s…"   16 seconds ago      Up 15 seconds       33060/tcp, 0.0.0.0:3311->3306/tcp   mysql-replica
a12eb8cd4c19        mysql:8.0.21        "docker-entrypoint.s…"   27 seconds ago      Up 27 seconds       33060/tcp, 0.0.0.0:3310->3306/tcp   mysql-primary

MySQL Client Installation

Install MySQL client on the host to connect to the containers:

$ sudo apt install mysql-client-core-8.0

Test connectivity to both instances:

$ mysql -uroot -h127.0.0.1 -P3310 -p
Enter password: SecureRoot123
Welcome to the MySQL monitor...
mysql> exit

$ mysql -uroot -h127.0.0.1 -P3311 -p
Enter password: SecureRoot123
Welcome to the MySQL monitor...
mysql>

Primary Server Configuration

Extract the configuration file from the primary container:

$ docker cp mysql-primary:/etc/mysql/my.cnf /tmp/my-primary.cnf

Edit the configuration file to add replication settings:

$ cat /tmp/my-primary.cnf
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
server-id=100
log-bin=mysql-bin
binlog-format=ROW
!includedir /etc/mysql/conf.d/

Copy the modified configuration back to the container:

$ docker cp /tmp/my-primary.cnf mysql-primary:/etc/mysql/my.cnf
$ docker restart mysql-primary

Replication User Creation

Create a dedicated user for replication on the primary server:

$ docker exec -it mysql-primary bash
root@a12eb8cd4c19:/# mysql -uroot -p
Enter password: SecureRoot123

mysql> CREATE USER 'repl_user'@'%' IDENTIFIED BY 'ReplPass456';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl_user'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT host, user FROM mysql.user WHERE user='repl_user';
+------+-----------+
| host | user      |
+------+-----------+
| %    | repl_user |
+------+-----------+
1 row in set (0.00 sec)

Replica Server Configuration

Configure the replica server with a different server ID and relay logs:

$ cat /tmp/my-replica.cnf
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
server-id=200
log-bin=mysql-replica-bin
relay_log=mysql-relay-bin
read_only=1
!includedir /etc/mysql/conf.d/

Apply the configuration:

$ docker cp /tmp/my-replica.cnf mysql-replica:/etc/mysql/my.cnf
$ docker restart mysql-replica

Establishing Replication Connection

First, retrieve the primary container's IP address:

$ docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' mysql-primary
172.17.0.2

Check the binary log position on the primary:

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      724 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

Configure the replica to connect to the primary:

$ docker exec -it mysql-replica bash
root@6d999ccc3c9f:/# mysql -uroot -p
Enter password: SecureRoot123

mysql> CHANGE MASTER TO
    -> MASTER_HOST='172.17.0.2',
    -> MASTER_USER='repl_user',
    -> MASTER_PASSWORD='ReplPass456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=724,
    -> MASTER_CONNECT_RETRY=30;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

Start the replication threads:

mysql> START SLAVE;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

mysql> RESET SLAVE;
Query OK, 0 rows affected (0.04 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.03 sec)

Check the replication status:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 172.17.0.2
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
                Last_IO_Errno: 2061
                Last_IO_Error: error connecting to master 'repl_user@172.17.0.2:3306' - retry-time: 30 retries: 2 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

To resolve the authentication issue, retrieve the public key from the primary:

root@6d999ccc3c9f:/# mysql --ssl-mode=DISABLED -h172.17.0.2 -urepl_user -p --get-server-public-key
Enter password: ReplPass456
...
mysql> exit

After this step, replication should work correctly:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 724
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 939
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0

Verification

Create a test database and table on the primary:

mysql> CREATE DATABASE test_repl CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE USER 'app_user'@'%' IDENTIFIED BY 'AppPass789';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL ON test_repl.* TO 'app_user'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> USE test_repl;
mysql> CREATE TABLE sample_data(id INT PRIMARY KEY, value VARCHAR(128));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO sample_data VALUES (1, 'Data1'), (2, 'Data2'), (3, 'Data3');
Query OK, 3 rows affected (0.03 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Verify data replication on the replica:

$ docker exec -it mysql-replica bash
root@6d999ccc3c9f:/# mysql -uroot -p
Enter password: SecureRoot123

mysql> SHOW DATABASES LIKE 'test_repl';
+--------------------+
| Database (test_repl) |
+--------------------+
| test_repl          |
+--------------------+
1 row in set (0.00 sec)

mysql> USE test_repl;
mysql> SELECT * FROM sample_data;
+----+-------+
| id | value |
+----+-------+
|  1 | Data1 |
|  2 | Data2 |
|  3 | Data3 |
+----+-------+
3 rows in set (0.00 sec)