How to deploy MySQL 5.7 & 8.0 master-slave cluster using Docker

> Deploy MySQL 5.7 cluster master & slave (for testing only)

Image version 5.7

1. Create an overlay network

docker network create --driver overlay common-network --attachable

2. Edit two configuration files master.cnf and slave.cnf

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
log-bin=mysql-bin
server-id=1
gtid-mode=ON
enforce-gtid-consistency=ON

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
server-id=2
gtid-mode=ON
enforce-gtid-consistency=ON

3. Start 2 MYSQL: mysql-master, mysql-slave

docker run -d \
--name mysql-master \
--network common-network \
-e MYSQL_ROOT_PASSWORD=Passw0rd \
-v `pwd`/master.cnf:/etc/mysql/my.cnf \
-p 3306:3306 \
-d mysql:5.7

docker run -d \
--name mysql-slave \
--network common-network \
-e MYSQL_ROOT_PASSWORD=Passw0rd \
-v `pwd`/slave.cnf:/etc/mysql/my.cnf \
-p 3307:3306 \
-d mysql:5.7

4. Add users from the library for replication

docker run -it --rm --network common-network mysql mysql -hmysql-master -uroot -pPassw0rd \
-e "CREATE USER 'repl'@'%' IDENTIFIED BY 'password' REQUIRE SSL; " \
-e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';"

5. Connect master & slave

docker run -it --rm --network common-network mysql mysql -hmysql-slave -uroot -pPassw0rd \
-e "CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_AUTO_POSITION=1, MASTER_SSL=1;" \
-e "START SLAVE;"

6. Verify slave status

docker run -it --rm --network common-network mysql mysql -hmysql-slave -uroot -pPassw0rd -e "show slave status\G"

The following status is normal:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

> Deploy MySQL 8.0 cluster master & slave (for testing only)

Mirror version mysql:8.0

1. Create an overlay network

docker network create --driver overlay common-network --attachable

2. Start 2 MYSQL: mysql-master, mysql-slave

docker run -d \
--name mysql-master \
--network common-network \
-e MYSQL_ROOT_PASSWORD=Passw0rd \
-p 3306:3306 \
-d mysql --default-authentication-plugin=mysql_native_password

docker run -d \
--name mysql-slave \
--network common-network \
-e MYSQL_ROOT_PASSWORD=Passw0rd \
-p 3307:3306 \
-d mysql --default-authentication-plugin=mysql_native_password

3. Configure master & slave

docker run -it --rm --network common-network mysql mysql -hmysql-master -uroot -pPassw0rd \
-e "SET PERSIST server_id=1;" \
-e "SET PERSIST_ONLY gtid_mode=ON;" \
-e "SET PERSIST_ONLY enforce_gtid_consistency=true; " \
-e "CREATE USER 'repl'@'%' IDENTIFIED BY 'password' REQUIRE SSL; " \
-e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';"

docker run -it --rm --network common-network mysql mysql -hmysql-slave -uroot -pPassw0rd \
-e "SET PERSIST server_id=2;" \
-e "SET PERSIST_ONLY gtid_mode=ON;" \
-e "SET PERSIST_ONLY enforce_gtid_consistency=true; "

4. Restart master & slave

docker restart mysql-master mysql-slave

5. Connect master & slave

docker run -it --rm --network common-network mysql mysql -hmysql-slave -uroot -pPassw0rd \
-e "CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_AUTO_POSITION=1, MASTER_SSL=1;" \
-e "START SLAVE;"

6. Verify slave status

docker run -it --rm --network common-network mysql mysql -hmysql-slave -uroot -pPassw0rd -e "show slave status\G"

The following status is normal:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

> Verify data synchronization

Create database anoyi on master

docker run -it --rm --network common-network mysql mysql -hmysql-master -uroot -pPassw0rd \
 -e "create database anoyi default character set utf8mb4 collate utf8mb4_general_ci;"

View the database list on the slave

docker run -it --rm --network common-network mysql mysql -hmysql-slave -uroot -pPassw0rd \
 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
|anoyi |
| information_schema |
|mysql |
| performance_schema |
|sys|
+--------------------+

Related information:

MySQL 5.7: https://dev.mysql.com/doc/refman/5.7/en/replication.html
MySQL 8.0: https://dev.mysql.com/doc/refman/8.0/en/replication.html

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:

How to build a MySQL PXC cluster
MySQL high availability cluster deployment and failover implementation
MySQL 5.7 cluster configuration steps
Implementation of Docker deployment of MySQL cluster
Detailed steps for installing MySQL using cluster rpm
Detailed explanation of MySQL cluster: one master and multiple slaves architecture implementation
Detailed explanation of galera-cluster deployment in cluster mode of MySQL
Example of how to build a Mysql cluster with docker
MySQL Cluster Basic Deployment Tutorial
How to build a MySQL high-availability and high-performance cluster

<<: Detailed explanation of JavaScript data types

>>: The simplest MySQL data backup and restore tutorial in history (Part 2) (Part 36)