MySQL Replication Basics

Introduction

In this lab, you will learn the fundamentals of MySQL replication. Replication is a process that allows you to maintain copies of a MySQL database on multiple servers. This is commonly used for load balancing, data backup, and high availability.

You will use Docker to create a realistic master-slave replication setup with two separate MySQL containers. First, you will start and verify a master container, which is pre-configured with binary logging enabled. You will create a special replication user on the master. Then, you will configure the second container to act as a slave, connect it to the master, and verify that data is synchronized correctly. This hands-on approach will help you understand the key steps involved in managing a real-world MySQL replication environment.

Set Up the Docker Environment

In this first step, you will set up the necessary Docker containers for the master-slave replication environment. You will create a dedicated network and launch two MySQL containers.

Create a Docker Network:

A dedicated network allows containers to communicate with each other by name. Create a network called mysql-net.
```
docker network create mysql-net
```

Start the Master MySQL Container:

Next, start the master container. This command uses mysql:5.7 and configures it with a server ID of 1 and enables binary logging for the replication_db database.

docker run -d \
  --name mysql-master \
  --network mysql-net \
  -e MYSQL_ROOT_PASSWORD=lab_password \
  -e MYSQL_DATABASE=replication_db \
  mysql:5.7 \
  mysqld --server-id=1 --log-bin=mysql-bin --binlog-do-db=replication_db

Start the Slave MySQL Container:

Now, start the slave container. This container is also on the same network and is configured with a server ID of 2. We also create the replication_db database in advance, so that the slave can correctly apply changes from the master.
```
docker run -d \
  --name mysql-slave \
  --network mysql-net \
  -e MYSQL_ROOT_PASSWORD=lab_password \
  -e MYSQL_DATABASE=replication_db \
  mysql:5.7 \
  mysqld --server-id=2 --relay-log=mysql-relay-bin --log-slave-updates=1
```

Wait for Containers to Initialize:

The MySQL containers need some time to initialize. Wait for about 15 seconds to check if the containers are running.

docker ps

CONTAINER ID   IMAGE       COMMAND                  CREATED         STATUS         PORTS                 NAMES

cfe8c98d2ad0 mysql:5.7 "docker-entrypoint.s…" 2 minutes ago Up 2 minutes 3306/tcp, 33060/tcp mysql-slave
fc125bf293ea mysql:5.7 "docker-entrypoint.s…" 2 minutes ago Up 2 minutes 3306/tcp, 33060/tcp mysql-master

Now that your environment is set up, you can proceed to configure the master server.

Verify the Master Server Configuration

Now that the containers are running, the next step is to ensure the master server is configured correctly for replication. The master's main responsibility is to log all database changes into its binary log, which the slave will later read.

First, open a terminal from your desktop.

We will now connect to the master container and verify its configuration.

Connect to the Master Container:

Use docker exec to open a MySQL shell inside the mysql-master container. You will be prompted to enter the password, which is lab_password.
```
docker exec -it mysql-master mysql -uroot -plab_password
```

Verify Binary Logging and Server ID:

Once you see the mysql> prompt, run the following commands to verify that binary logging is enabled and the server ID is set correctly.

SHOW VARIABLES LIKE 'log_bin';

The output should show that log_bin is ON.

+---------------+----+
| Variable_name | Value |
+---------------+----+
| log_bin       | ON |
+---------------+----+
1 row in set (0.01 sec)

Next, check the server ID.

SHOW VARIABLES LIKE 'server_id';

The output should show the server_id is 1.

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

Now that you have verified the configuration, you can exit the MySQL shell.

exit;

You have successfully confirmed that the master server is ready for replication.

Create a Replication User and a Test Database

The slave server needs to connect to the master to read the binary log. For security, it is best practice to create a dedicated user account for this purpose with limited privileges. In this step, you will create a replication user and populate the test database to observe replication in action.

Connect to the Master Server:

Open a MySQL shell inside the mysql-master container.
```
docker exec -it mysql-master mysql -uroot -plab_password
```
Create a Replication User:

At the mysql> prompt, run the following command to create a user named repl_user with the password repl_password. This user will be used by the slave to connect. We use '@'%' to allow the user to connect from any host, which includes our slave container on the Docker network.
```
CREATE USER 'repl_user'@'%' IDENTIFIED BY 'repl_password';
```
Grant Replication Privileges:

The new user needs the REPLICATION SLAVE privilege, which allows it to read the binary logs from the master.
```
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
```
After granting privileges, reload the grant tables to apply the changes immediately.
```
FLUSH PRIVILEGES;
```

Check the Master's Binary Log Status:

Before configuring the slave, you need to know the exact coordinates in the master's binary log where the slave should start replicating. The SHOW MASTER STATUS command provides this information.

SHOW MASTER STATUS;

The output will look similar to this. Note down the File and Position values, as you will need them in the next step.

+------------------+----------+------------------+--------------------+-------------------+
| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB   | Executed_Gtid_Set |
+------------------+----------+------------------+--------------------+-------------------+
| mysql-bin.000001 |      337 | replication_db   |                    |                   |
+------------------+----------+------------------+--------------------+-------------------+
1 row in set (0.00 sec)

Create a Test Table and Data:

The replication_db database was created when the container started. Let's create a table and insert some data to test replication.

USE replication_db;
CREATE TABLE messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(255)
);
INSERT INTO messages (content) VALUES ('Hello from the master!');

You can verify the data was inserted correctly.

SELECT * FROM messages;

You should see the row you just inserted.

+----+------------------------+
| id | content                |
+----+------------------------+
|  1 | Hello from the master! |
+----+------------------------+
1 row in set (0.00 sec)

Now, exit the MySQL shell.

exit;

You have now prepared the master server by creating a replication user and some test data.

Configure and Start the Slave Server

Now that the master is ready, you will configure the slave container. In this step, you will use the binary log coordinates you recorded from the master in Step 3 to tell the slave where to begin replicating.

Connect to the Slave Container:

First, open a MySQL shell inside the mysql-slave container.
```
docker exec -it mysql-slave mysql -uroot -plab_password
```
Configure the Slave to Connect to the Master:

Now, you will use the CHANGE MASTER TO command to configure the replication settings. Use the File and Position values that you noted from SHOW MASTER STATUS in Step 3. The MASTER_HOST is 'mysql-master', which works because both containers are on the same Docker network.

Note: Replace mysql-bin.000001 and the position number with the actual values you recorded in Step 3. If you don't have the values, you can run docker exec mysql-master mysql -uroot -plab_password -e "SHOW MASTER STATUS;" in a new terminal to get them.
```
CHANGE MASTER TO
    MASTER_HOST='mysql-master',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='repl_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=337;
```
This command configures the slave with all the necessary information to connect to the master.
Start the Slave Threads:

Finally, start the replication process by running the START SLAVE command. This will start two threads: the I/O thread, which fetches binary logs from the master, and the SQL thread, which executes the events from the logs.
```
START SLAVE;
```
You can now exit the MySQL shell.
```
exit;
```

The slave is now configured and should be attempting to connect to the master and replicate data.

Verify Replication

After starting the slave, the final step is to verify that replication is working correctly. You will check the slave's status and confirm that the data from the master has been copied.

Connect to the Slave Server:

Open a MySQL shell on the mysql-slave container.
```
docker exec -it mysql-slave mysql -uroot -plab_password
```
Check the Slave Status:

The most important command for monitoring replication is SHOW SLAVE STATUS. The \G modifier formats the output vertically, which makes it much easier to read.
```
SHOW SLAVE STATUS\G
```
Look for the following two lines in the output. Both should have a value of Yes.
- Slave_IO_Running: Yes: This confirms the slave is successfully connected to the master and receiving binary log data.
- Slave_SQL_Running: Yes: This confirms the slave is successfully executing the events from the binary log.
Another important field is Seconds_Behind_Master. A value of 0 indicates that the slave is fully caught up with the master.

A healthy status will look similar to this (some values may differ):
```
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql-master
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 529
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 699
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
         Seconds_Behind_Master: 0
...
```

Verify the Replicated Data:

Now, check if the replication_db database and its data have been copied from the master.

SHOW DATABASES;

You should see replication_db in the list.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| replication_db     |
| sys                |
+--------------------+

Switch to the database and query the messages table.

USE replication_db;
SELECT * FROM messages;

The data you inserted on the master should now be present on the slave.

+----+------------------------+
| id | content                |
+----+------------------------+
|  1 | Hello from the master! |
+----+------------------------+
1 row in set (0.00 sec)

This confirms that replication is working correctly. You can now exit the MySQL shell.

exit;

You have successfully set up and verified a basic MySQL master-slave replication environment using Docker.

Summary

In this lab, you have learned the fundamental steps for setting up MySQL master-slave replication using Docker. You started by creating two separate MySQL containers, one for the master and one for the slave, connected via a Docker network. You verified the master's configuration, which included an enabled binary log and a unique server ID. You then created a dedicated replication user on the master for a secure connection. After preparing the master, you configured the slave container using the CHANGE MASTER TO command to establish the connection with the master's binary log coordinates. Finally, you verified the success of the replication by checking the slave's status with SHOW SLAVE STATUS and confirming that the test data was correctly synchronized. This lab has provided you with the essential skills to configure, manage, and verify a realistic MySQL replication setup.