Docker uses 2 servers to build mysql master-slave replication

Docker uses 2 servers to build mysql master-slave replication

The author here prepares 2 centos7 servers, their IP is 192.168.0.113, 192.168.0.111, 113 and 111 for short hereafter

113 server is used as the master server, 111 server is used as the slave server

The mysql version uses 8.0.25

The server installation docker will not go into details here

1. The master and slave servers pull mysql mirrors separately

docker pull mysql:8.0.25

After the pull is complete, the master and slave servers run mysql containers separately

The main mysql container alias is mysql8-master

docker run --privileged=true --name=mysql8-master -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /docker_data/mysql8-master:/var/lib/mysql -d mysql:8.0.25

The alias from the mysql container is mysql8-slave

docker run --privileged=true --name=mysql8-slave -it -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /docker_data/mysql8-slave:/var/lib/mysql -d mysql:8.0.25

Main server

Slave server

2. Modify the configuration file of the main server

Copy the mysql.cnf file of the main mysql container to the root directory

docker cp mysql8-master:/etc/mysql/conf.d/mysql.cnf /root 

Edit the mysql.cnf file

vi mysql.cnf

Copy the following content into the mysql.cnf file

[mysqld]log-bin=mysql-bin    server-id=1

As shown below

Save after copy

Copy the modified mysql.cnf file from the root directory back to the main mysql container

docker cp /root/mysql.cnf mysql8-master:/etc/mysql/conf.d/

Restart the main mysql container for the new configuration file to take effect

docker restart mysql8-master

3. Create an account for the main mysql container

After restarting the master mysql, enter the container to create an account for master-slave replication

docker exec -it mysql8-master bash

Log in to mysql

mysql -u root -p123456

Create a user for copying from the library in the main library, account slavecopyuser password copy123456 , and then grant permissions

The ip that is restricted here can only be the ip address of the slave server 192.168.0.111

create user 'slavecopyuser'@'192.168.0.111' identified with mysql_native_password by 'copy123456';
grant replication client,replication slave on *.* to 'slavecopyuser'@'192.168.0.111';

If you don’t want to restrict access to the ip address, use the following command

create user 'slavecopyuser'@'%' identified with mysql_native_password by 'copy123456';
grant replication client,replication slave on *.* to 'slavecopyuser'@'%';

After creating a user and granting permissions, refresh permissions

flush privileges;

The command is as shown below

After the user is created, check the master_log_file and master_log_pos parameters of the master server

show master status;

Write down mysql-bin.000001 and 890 here

4. Modify the configuration file from the mysql container

The steps are the same as the main mysql container, first copy the configuration file from the container, modify it and then copy it back

docker cp mysql8-slave:/etc/mysql/conf.d/mysql.cnf /root
vi mysql.cnf

Copy the following content to the slave mysql configuration file

[mysqld]log-bin=mysql-bin    server-id=2

After saving, copy the mysql.cnf file back to the mysql container

docker cp /root/mysql.cnf mysql8-slave:/etc/mysql/conf.d/

Restart the slave mysql container

docker restart mysql8-slave

5. Turn on master-slave replication in the slave mysql container

After restarting the slave mysql container, today from the mysql container

docker exec -it mysql8-slave bash

log in

mysql -u root -p123456

Configure the information to connect to the main server

Here you need to write down mysql-bin.000001 and 890 in the main mysql container

master_host is the ip address of the main mysql container

master_user is the account used for replication

master_password is the password of the account used for replication

change master to master_host='192.168.0.113',master_user='slavecopyuser',master_password='copy123456',master_log_file='mysql-bin.000001',master_log_pos=890;

After configuration, enable master-slave replication

start slave;

Check master-slave connection status

show slave status\G

Slave_IO_Running: Yes and Slave_SQL_Running: Yes indicate no problem

If it is No or connect, it is not connected

6. Test master-slave replication

Master server creates database

create database user_test;

View from the server

show databases;

The main server creates the database user_test, and the user_test can be seen from the service

It shows that the master-slave replication is successfully built

So far