1 Experimental introduction
In this experiment, we use docker images to open three containers to achieve one master and two slaves.
| Container Host | IP address | Map host ports |
|---|---|---|
| mysql-master | 192.168.1.2 | 3306 |
| mysql-slave1 | 192.168.1.3 | 3307 |
| mysql-slave2 | 192.168.1.4 | 3308 |
2 Experimental preparation
2.1 Create a directory for MySQL mount
Using docker to do stateful MySQL requires good data reliability, so you have to use the host to mount it into the container
[root@chucong mysql_zhucong]# tree /data/ -L 3
/data/
├── master # master's directory │ ├── conf # Configuration file storage directory │ │ └── # Configuration File │ └── master-data # Where to store data ├── slave1
│ ├── conf
│ │ └──
│ └── slave1-data
└── slave2
├── conf
│ └──
└── slave2-data
# MySQL configuration file [root@chucong mysql_zhucong]# cat /data/master/conf/
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/
symbolic-links=0
log_bin=mysql-bin # Enable bin-log log, note that only the main configuration file needs to be enabled server_id=10
#gtid_mode=ON # Enable GTID #enforce-gtid-consistency=ON # Ensure strong consistency of GTID
[root@chucong mysql_zhucong]# cat /data/slave1/conf/
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/
symbolic-links=0
server_id=20
#super_read_only=on #Read only #gtid_mode=ON # Enable GTID #enforce-gtid-consistency=ON # Ensure strong consistency of GTID
[root@chucong mysql_zhucong]# cat /data/slave2/conf/
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/
symbolic-links=0
server_id=30
#super_read_only=on #Read only #gtid_mode=ON # Enable GTID #enforce-gtid-consistency=ON # ensureGTIDStrong consistency2.2 Writing a file
[root@chucong mysql_zhucong]# docker pull mysql:9.1.0
[root@chucong mysql_zhucong]# vim
version: '2.27.1'
services:
mysql-master:
image: mysql:9.1.0
container_name: mysql-master
environment: # Define system values MYSQL_ROOT_PASSWORD: root # root password MYSQL_USER: repl # Create a copy user MYSQL_PASSWORD: root # Set password ports:
- 3306:3306
volumes:
- /data/master/master-data:/var/lib/mysql # Mount the host directory to the container - /data/master/conf/:/etc/
restart: always
networks:
mynet1:
ipv4_address: 192.168.1.2
mysql-slave1:
image: mysql:9.1.0
container_name: mysql-slave1
environment:
MYSQL_ROOT_PASSWORD: root
MYSQL_USER: repl
MYSQL_PASSWORD: root
ports:
- 3307:3306
volumes:
- /data/slave1/slave1-data:/var/lib/mysql # Mount the host directory to the container - /data/slave1/conf/:/etc/
restart: always
networks:
mynet1:
ipv4_address: 192.168.1.3
mysql-slave2:
image: mysql:9.1.0
container_name: mysql-slave2
environment:
MYSQL_ROOT_PASSWORD: root
MYSQL_USER: repl
MYSQL_PASSWORD: root
ports:
- 3308:3306
volumes:
- /data/slave2/slave2-data:/var/lib/mysql # Mount the host directory to the container - /data/slave2/conf/:/etc/
restart: always
networks:
mynet1:
ipv4_address: 192.168.1.4
networks:
mynet1: # Use custom bridge mode driver: bridge
ipam:
config:
- subnet: 192.168.1.0/24 # Define network segments gateway: 192.168.1.1 # Define gateway2.3 Start the container
[root@chucong zhucong]# docker compose up -d # Check whether the container is running [root@chucong zhucong]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES e5776a980697 mysql:9.1.0 "…" 59 minutes ago Up 43 minutes 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql-slave1 0bb3a700077c mysql:9.1.0 "…" 59 minutes ago Up 43 minutes 33060/tcp, 0.0.0.0:3308->3306/tcp, :::3308->3306/tcp mysql-slave2 af2f63aa00e8 mysql:9.1.0 "…" 59 minutes ago Up 4 seconds 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp mysql-master
3 Master-slave copy operation
3.1 MASTER operation command
[root@chucong mysql_zhucong]# docker exec -it mysql-master bash
bash-5.1# mysql -uroot -proot
# If you don't set the variable to create automatically, you can create it manually. If you set it, you can omit the following commands mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'root';
# Grant all databases all tables repl user REPLICATION SLAVE permissions mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;
mysql> SHOW BINARY LOG STATUS\G # It's different from the previous version. It used to be SHOW MASTER status\G *************************** 1. row ***************************
File: mysql-bin.000034 # Note here Position: 198 # There is also this source, these two lines play a crucial role in operating from the library Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: d83de5a7-b723-11ef-a65b-0242c0a80104:1-2
1 row in set (0.00 sec)3.2 SLAVE1 operation command
[root@chucong mysql_zhucong]# docker exec -it mysql-slave1 bash
bash-5.1# mysql -uroot -proot
# If you don't set the variable to create automatically, you can create it manually. If you set it, you can omit the following commands mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'root';
# The following command has also changed drastically, and there is no need to specify a port mysql> CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.1.2', # MASTER's IP SOURCE_USER='repl', # Created user SOURCE_PASSWORD='root', # password SOURCE_LOG_FILE='mysql-bin.000034', # Use the File value in SHOW BINARY LOG STATUS\G for the main library GET_SOURCE_PUBLIC_KEY=1, # Note here Automatically obtain the public key of the main server (applicable to caching_sha2_password) SOURCE_LOG_POS=198; # Use the Position value in SHOW BINARY LOG STATUS\G for the main library
# The old version of the command is as follows, you can specify the port # mysql> CHANGE MASTER TO
# -> MASTER_HOST='192.168.1.2',
# -> MASTER_PORT=3306,
# -> MASTER_USER='repl',
# -> MASTER_PASSWORD='root',
# -> MASTER_LOG_FILE='mysql-bin.000034',
# -> MASTER_LOG_POS=198;
mysql> START REPLICA; # Start the command to replicate 9.1.0 changes. Previous version was START SLAVE; mysql> SHOW REPLICA STATUS; # Start the command to replicate 9.1.0 changes Previous version was SHOW SLAVE STATUS;
mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.1.2
Source_User: repl
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.000034
Read_Source_Log_Pos: 7061047
Relay_Log_File: e5776a980697-relay-bin.000002
Relay_Log_Pos: 7061177
Relay_Source_Log_File: mysql-bin.000034
Replica_IO_Running: Yes # IO thread YES indicates that the connection has been successful. If not, no or connect will be displayed. Replica_SQL_Running: Yes 3.3 SLAVE2 operation commands
[root@chucong mysql_zhucong]# docker exec -it mysql-slave2 bash
bash-5.1# mysql -uroot -proot
# If you don't set the variable to create automatically, you can create it manually. If you set it, you can omit the following commands mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'root';
# The following command has also changed drastically, and there is no need to specify a port mysql> CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.1.2', # MASTER's IP SOURCE_USER='repl', # Created user SOURCE_PASSWORD='root', # password SOURCE_LOG_FILE='mysql-bin.000034', # Use the File value in SHOW BINARY LOG STATUS\G for the main library GET_SOURCE_PUBLIC_KEY=1, # Note here Automatically obtain the public key of the main server (applicable to caching_sha2_password) SOURCE_LOG_POS=198; # Use the Position value in SHOW BINARY LOG STATUS\G for the main library
# The old version of the command is as follows, you can specify the port # mysql> CHANGE MASTER TO
# -> MASTER_HOST='192.168.1.2',
# -> MASTER_PORT=3306,
# -> MASTER_USER='repl',
# -> MASTER_PASSWORD='root',
# -> MASTER_LOG_FILE='mysql-bin.000034',
# -> MASTER_LOG_POS=198;
mysql> START REPLICA; # Start the command to replicate 9.1.0 changes. Previous version was START SLAVE; mysql> SHOW REPLICA STATUS; # Start the command to replicate 9.1.0 changes Previous version was SHOW SLAVE STATUS;
mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 192.168.1.2
Source_User: repl
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.000034
Read_Source_Log_Pos: 7061047
Relay_Log_File: 0bb3a700077c-relay-bin.000002
Relay_Log_Pos: 7061177
Relay_Source_Log_File: mysql-bin.000034
Replica_IO_Running: Yes
Replica_SQL_Running: Yes4 Verify whether the master and slave are implemented
4.1 Import SQL script to check whether the master-slave copy is normal
# MASTER [root@chucong ~]# docker cp smart_work_v3.sql mysql-master:/ Successfully copied 6.53MB to mysql-master:/ [root@chucong ~]# docker exec -it mysql-master bash bash-5.1# ls afs boot home lib64 mnt proc run smart_work_v3.sql sys usr bin dev etc lib media opt root sbin srv tmp var bash-5.1# mysql -uroot -proot mysql> CREATE DATABASE shuyan; # Create a database mysql> USE shuyan # Switch to database Database changed mysql> SOURCE smart_work_v3.sql # Import sql scripts mysql> SHOW TABLES; +----------------------------------------+ | Tables_in_shuyan | +----------------------------------------+ | aaa | | abi_http_log | | act_app_appdef | | act_app_databasechangelog | | act_app_databasechangeloglock | | act_app_deployment | | act_app_deployment_resource | | act_cmmn_casedef | +----------------------------------------+
4.2 Check whether the copy is successful from the library
mysql> USE shuyan; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW TABLES; +----------------------------------------+ | Tables_in_shuyan | +----------------------------------------+ | aaa | | abi_http_log | | act_app_appdef | | act_app_databasechangelog | | act_app_databasechangeloglock | | act_app_deployment | | act_app_deployment_resource | | act_cmmn_casedef | | act_cmmn_databasechangelog | | act_cmmn_databasechangeloglock | | act_cmmn_deployment | +----------------------------------------+
This is the article about the most basic master-slave replication steps of MySQL9.1.0. This is the end of this article. For more related content related to MySQL9.1.0 master-slave replication, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!