Implementation steps for building a MySQL master-slave replication environment based on Docker

1. Introduction

The previous program architecture may be in the following form:

When the program size increases, we may expand it to multiple background service instances, but there is still only one database, so the bottleneck of the system is still on the database. Therefore, the main task this time is to expand the database. The main form is: expand multiple database instances, realize read-write separation, assign some write tasks to the main database, and use the sub-database for reading tasks. Thereby improving system performance.

The modified architecture is as follows:

2. Environment Pre-construction

This time, Docker is used to build this environment, and the MySQL version used is 5.7.13.

docker pull mysql:5.7.13

The overall structure is:

• 1 master node, which serves as the write node.
• Two slave nodes serve as read nodes.

First start these nodes separately and map them to different ports. Use the database connection tool on this machine to connect and test whether it is started and connected normally.

docker run -p 3307:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.13
docker run -p 3308:3306 --name mysql-slave1 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.13
docker run -p 3309:3306 --name mysql-slave2 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.13

Here I map the master node (mysql-master) to port 3307 , and the two slave nodes (mysql-slave1, 2) to ports 3308 and 3309 respectively. Then set the MySQL root password to 123456 .

Then you can use tools such as navicat to connect to test MySQL.

Enter these nodes respectively and edit the configuration files.

docker exec -it mysql-master /bin/bash

I use name to enter the container, you can also select according to the id, that is, docker exec -it 對應容器的id /bin/bash .

Since the vi and vim programs are not pre-installed, you need to execute the apt update command when downloading, and then download from foreign sources. For well-known reasons, the speed is very slow. I changed the download source to a domestic source.

Enter the /etc/apt folder and first back up the original files:

mv sources.list sources.list.bak

Then use the following command to create a new file and enter the content:

echo deb http://mirrors.aliyun.com/ubuntu/ xenial main restricted universe multiverse > sources.list

Then we execute apt update and other operations, and finally install vim .

3. Perform master-slave configuration

Master node configuration

After entering the master node container, enter the /etc/mysql folder, there will be a my.cnf file, which is mainly modified.

Edit this file, find [mysqld] , and add the following command below it:

[mysqld]
...
...
## Unique number server-id=101
## This is the key configuration item log-bin=mysql-bin

After the configuration is complete, you need to restart the MySQL service for the configuration to take effect. Use service mysql restart command to restart. After the restart is complete, the MySQL container will be closed. We also need to restart the container docker restart mysql-master .

Slave configuration

Same as the master node, edit the /etc/mysql/my.cnf file

[mysqld]
...
...
## Unique number server-id=103
## Select, if you need to use this node as the master node of other nodes, you need to add # log-bin=mysql-bin

Linking master and slave nodes

Master Node

Enter MySQL mysql -u root -p in the master node container. The password is 123456 set when starting the container.

After entering MySQL, execute show master status; :

From here we get the values ​​of two pieces of information, File and Position , which are mysql-bin.000001 and 154 respectively.

Slave Node

Enter MySQL and execute the following command:

change master to master_host='***', master_port=3306, master_user='root', master_password='123456', master_log_file='****', master_log_pos= ***;

Explain what these parameters mean respectively:

master_host: The IP address of the master node. You can use the following command on this machine to view the IP address of the container.

docker inspect --format='{{.NetworkSettings.IPAddress}}' container name | container id

master_port: the port number of mysql, not the port number mapped externally

master_user: User in mysql, must have permissions, I directly used root, you can also create a new user to use

master_password: MySQL account password used for synchronization

master_log_file: The file used for synchronization, that is, the file queried from the master node. Here I am mysql-bin.000001

master_log_pos: The position where the binlog file starts to be synchronized, which is the position queried from the master node. In my case, it is 154

After executing the command just now, execute show slave status \G; in the MySQL terminal to view the master-slave synchronization status.

We can check the configuration information here, and then we can see that the two attributes slave_io_running and slave_sql_running are both no, which means they are closed.

We can execute start slave to start master-slave replication. After execution, execute show slave status \G; command again to see that both attributes have become yes , indicating that master-slave replication has been enabled.

If the startup fails, we can check whether the network is connected, whether the mysql password used for synchronization is correct, and whether the synchronization file name and location are correct!

test

We can create a new database in the master database. If we see the existence of this database in the slave database, it means that the master-slave synchronization is complete.

4. Cascade configuration

I want to add another backup node, and this node is backed up from the slave1 node, that is, the slave1 node serves as the master node of the backup node. This forms a cascade relationship of master->slave->backup.

I originally followed the above steps and added it to the slave's my.cnf

log-bin=mysql-slave-bin #To distinguish, I changed the file name

Then execute on the backup node

change master to master_host='***', master_user='root', master_password='123456', master_port=3306, master_log_file='****', master_log_pos= ***;

The command is replaced with the IP and other attributes of the corresponding slave node. It turns out that it doesn’t work. After the primary node is changed, the backup node is not changed!

So I started to investigate and found that there was no record of changed information in the binlog file in the slave node, and the backup node was equivalent to monitoring the changes in this file. If this file did not change, there would be no changes in the backup node. Let me extend this a little bit. MySQL's binlog records all our changes, so in theory we can use binlog to restore the database content at any time.

So the question becomes how to record the binlog log of the slave node after the master node changes.

We can add another line when editing the my.cnf file: log_slave_updates=1 , so that the slave will also write the binary log to its own binlog after receiving synchronization from the master.

This can be done. After the master node is changed, the slave node and the backup node will also be changed. The data of the backup node is backed up from the slave node.

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.