In today's data management environment, ensuring database availability and scalability is critical. MySQL master-slave replication is a popular technique to achieve this, helping to distribute the workload and enhance system reliability. Combined with Docker, the process of deploying and managing this system becomes easier and more flexible. In this article, we will explore how to set up MySQL master-slave replication using Docker.
What is MySQL Master-Slave Replication?
MySQL master-slave replication is a mechanism in which data from a "master" server is automatically replicated to one or more "slave" servers. This helps distribute read load, enhance resiliency, and provide easier backups.
Setup steps
1. We need to set up the directory structure as follows.
2. Edit my.cnf file information for master and slave.
master/my.cnf
|
[mysqld] [mysql] [client] |
slave/my.cnf
|
[mysqld] [mysql] [client] |
3. Edit docker-compose.yml file information.
|
version: '3'
services:
mysql-master:
image: percona:ps-8.0
volumes:
- ./master/data:/var/lib/mysql
- ./master/my.cnf:/etc/my.cnf
environment:
TZ: Asia/Tokyo
MYSQL_USER: master
MYSQL_PASSWORD: Master123@
MYSQL_ROOT_PASSWORD: Mastermaster123
mysql-slave:
image: percona:ps-8.0
container_name: mysql-slave
volumes:
- ./slave/data:/var/lib/mysql
- ./slave/my.cnf:/etc/my.cnf
environment:
TZ: Asia/Tokyo
MYSQL_USER: slave
MYSQL_PASSWORD: slave123@
MYSQL_ROOT_PASSWORD: slaveslave123
|
4. Start 2 servers:
| docker compose up -d |
5. Wait for the process of building the container to be done successfully.
After that, check the process with the command:
| docker-compose ps |
6. Enter the "mysql-master" container to set the replication DB on the master server:
| docker-compose exec mysql-master bash |
7. Login to mySQL by the root account:
| mysql -u root -pMastermaster123 |
8. Check the binary log whether enabled successfully or not:
| SHOW VARIABLES LIKE 'log_bin'; |
You must make sure the status of the binary is ON before processing the next step.
9. Create a new user in order to the slave server can access to the master server and check the binary log.
Use the below command to create the 'replication' user.
| CREATE USER 'replication'@'%' IDENTIFIED BY 'Slaverepl123'; |
10. Grant user replication access to allow creation for MySQL replications:
| GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%'; |
11. Confirm whether the grants of the user were successful or not:
| SHOW GRANTS FOR replication@'%'; |
12. Confirm the status of the binary log on the master server:
| SHOW MASTER STATUS\G |
You need to save the File and Position.
We will use these values to set on the slave server.
13. Enter the "mysql-slave" container to set the replication DB on the slave server:
| docker-compose exec mysql-slave bash |
14. Login to mySQL by the root account:
| mysql -u root -pslaveslave123 |
15. Execute the SQL command so it can read the binary log for any changes on the master server and make it the same on the slave server.
We will set the value of the MASTER_LOG_FILE and MASTER_LOG_POS by the value obtained from step 12.
| CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_USER='replication', MASTER_PASSWORD='Slaverepl123', MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=1161; |
16. Let’s start the slave on mysql:
| START SLAVE; |
17. Confirm the status of the replication on the slave server:
| SHOW SLAVE STATUS\G |
If the Slave_IO_State is "Waiting for source to send event" then we have set up successfully.
From now on, if the master server changes anything, it also changes on the slave server.
18. Confirm synchronization between the two databases.
Check all databases on the master/slave server.
| SHOW DATABASES; |
Create a new database on the master server and re-check all databases on the master server.
| CREATE DATABASE replicate_db; |
A new database has been created on the master server and it has also been created on the slave server.
Conclusion
With the use of Docker, setting up MySQL master-slave replication becomes much simpler and faster. This not only improves data management but also provides an easy environment for testing and development. Try implementing this system for your project to see its obvious benefits.
Using Docker to manage MySQL replication helps you take advantage of the benefits of containerization, such as easily deploying, scaling, and managing complex database systems efficiently.
References:
