Mysql 5.7 docker master-slave replication architecture tutorial_MySQL

Share mysql 5.7 docker master-slave replication architecture tutorial for your reference, the specific content is as follows

Environment version:
MySQL: 5.7.13
Docker: 1.11.2
CentOS: 7.1

1. First install two MySQLs on two physical machines. The commands are as follows

The code is as follows:

docker pull mysql:5.7.13
docker run --name anuo-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=qaz.00JK -d mysql:5.7.13

2. Create a copy account on the main library

The code is as follows:

GRANT REPLICATION SLAVE ON *.* TO 'rep1'@'192.168.2.103' IDENTIFIED BY 'qaz.00JK';

The copied account is: rep1
The IP of the specified slave library must be: 192.168.2.103
Copy password: qaz.00JK

3. Modify the configuration file of the main library (Troublesome, there should be a more convenient way to modify it)

3.1 First copy the configuration file from docker to the host/root directory:

docker cp anuo-mysql:/etc/mysql/my.cnf /root

3.2 Open my.cnf on the host and add

at the end of the [mysqld] node

log-bin=mysql-bin
server-id=1

3.3 Then upload this file to docker mysql and overwrite it

docker cp /root/my.cnf anuo-mysql:/etc/mysql/my.cnf

3.4 Restart the docker of mysql to make the configuration take effect
docker restart anuo-mysql

4. Modify the configuration file of the slave library

Same as step 3, the only difference is
server-id=2

5. Start the backup, Execute the following command in the main database to put all tables in the main database into a read-only and non-writable state, so as to achieve data consistency between the master and slave databases

FLUSH TABLES WITH READ LOCK;

6. Back up the database of the main database and restore it from the slave database

It is very convenient to use navicat for mysql

7. After restoring from the slave library, release the read lock of the main library, so that the write permission of the main library is restored

unlock tables;

8. Configure the slave library to connect to the main library and execute it on the slave library

CHANGE MASTER TO 
MASTER_HOST='192.168.2.108', 
MASTER_PORT=3306, 
MASTER_USER='rep1', 
MASTER_PASSWORD='qaz.00JK', 
MASTER_LOG_FILE='mysql-bin.000002', 
MASTER_LOG_POS=898;

The last two items
MASTER_LOG_FILE and MASTER_LOG_POS
Execute: SHOW MASTER STATUS; command in the main library to obtain
The corresponding fields are File and Position

9. Start the slave thread in the slave library to start synchronization
START SLAVE;

10. Check the synchronization status in the slave library
show slave status;
If you see the Slave_Io_State field:

Waiting for master to send event...
Then it was a success!!! !

The above is the entire content of this article. I hope it will be helpful to everyone’s study and I hope you will support me a lot.