Set up the MySql master slave copy using the Docker container

1. Compile the Docker file of master MySQL

Create a new folder and name it MySQL-master-v1. Create new files Dockerfile and master.cnf in the folder mysql-master-v1. The editor I recommend is Visual Studio Code. Use the editor to set the encoding of the two files to UTF-8. Since this article describes installing on an Linux system, we recommend that you set the newline to LF to accommodate the Linux operating system. Write the two files as follows.

Dockerfile

FROM mysql:5.7
# set timezone as china/shanghai
RUN cp /usr/share/zoneinfo/PRC /etc/localtime
# copy mysql config file
COPY master.cnf /etc/mysql/conf.d/
ENTRYPOINT ["docker-entrypoint.sh"]
EXPOSE 3306
CMD ["mysqld"]

master.cnf

user=root
password='123456'
[mysqld]
max_allowed_packet=8M
lower_case_table_names=1
character_set_server=utf8
max_connections=900
max_connect_errors=600
server-id=1
log-bin=mysql-bin
replicate-wild-do-table=db1.%
replicate-wild-do-table=db2.%
slow_query_log=1
long_query_time=1
log_error

2. Configuration file parameters for master MySQL

Explanation 1: master.cnf.

lower_case_table_names: ignore the case of data structures such as table names, column names, etc. (note: not case per row!) .

server-id =1: indicates that this MySQL server is the primary server.

log-bin: opens a base 2 record. This is for master and slave replication. This article USES the RBR (Row-Based Replication) pattern.

replicate-wild-do-table: specifies which tables can be copied from the master server to the slave server. db1.% means that any table in the database db1 can be copied to the slave server. You can have multiple Settings to allow multiple databases.

replicate-do-db is not used because replicate-do-db prohibits access across databases. For example, the user already USES use db2; Select database db2 and the user will also perform update db1.t_student c_name='stu_c' where c_id='1234c', then replicate-do-db cannot be used. While programmers do not use use when programming, it is possible for programmers to write url connected to mysql as a specific database or without specifying a specific database. The use of replicate-do-db is not allowed even if the database is not specified.

slow_query_log=1: start the slow query log. If the execution time of an SQL exceeds the number of seconds set by long_query_time, it is recorded. Record file paths using show variables; Command to find the specific log file path under the variable name slow_query_log_file.

long_query_time=1: in seconds. If an SQL statement takes longer than one second to execute, it is recorded. This variable can only be used when the slow query log must be turned on.

log_error: start error logging. show variables like 'log_error'; You can query the path to the log file. If the Docker official image of mysql is set to other values, the container will not start properly.

3. Generate and use the mirror image of master MySQL

Upload folder mysql-master-v1 to Linux server. For illustration purposes, server A is used to refer to this server. The author's version of Linux is Ubuntu14.04. Enter the directory mysql-master-v1 with the cd command. Then type the following command to compile the image:

docker build -t zhangchao/mysql-master5.7:v1 .

Be careful not to drop the last point at the end of the line.

Generate a new container based on the compiled image:

docker run --name mysql-master \
-p 3306:3306 \
-v /zc/mysql-master/datadir:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD='123456' -d zhangchao/mysql-master5.7:v1

The client of MySQL is connected to the MySQL primary server. The author is using MySQL Workbench. Execute the following command:

show master status;

Record file and position. So let's say that file is mysql-bin.000001, and post is 154.

4. Prepare Docker files from MySQL

New folder mysql-slave-v1. Create Dockerfile and slave.cnf under the folder mysql-slave-v1 as in step 1. The following are the contents of the two files.

Dockerfile

FROM mysql:5.7
# set timezone as china/shanghai
RUN cp /usr/share/zoneinfo/PRC /etc/localtime
# copy mysql config file
COPY slave.cnf /etc/mysql/conf.d/
ENTRYPOINT ["docker-entrypoint.sh"]
EXPOSE 3306
CMD ["mysqld"]

slave.cnf

[mysqldump]
user=root
password='123456'
[mysqld]
max_allowed_packet=8M
lower_case_table_names=1
character_set_server=utf8
max_connections=900
max_connect_errors=600
slow_query_log=1
long_query_time=1
log_error
# Regard this db as a slave
server-id=2

server-id =2 in the slave.cnf file sets the MySQL server to the slave server.

5. Generate and use mirrors from MySQL

Upload mysql-slave-v1 to the second server. Use server B to refer to this server.

Compile image:

docker build -t zhangchao/mysql-slave5.7:v1 .

Create the container with the new image:

docker run --name mysql-slave \
-p 3306:3306 \
-v /zc/mysql-slave/datadir:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD='123456' -d zhangchao/mysql-slave5.7:v1

At this point, the master and slave servers of MySQL are all running, and the reader needs to ensure that the data on both sides are the same. A master-slave replication connection can only be established if both servers have the same data.

6. Connect from MySQL to main MySQL

Use the client to connect to the MySQL slave server, assuming that the IP of server A is 192.168.10.1.

Execute the following command:

change master to 
master_host='192.168.10.1',
master_user='root',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=154;

After the normal execution of the command, execute the following command:

user=root
password='123456'
[mysqld]
max_allowed_packet=8M
lower_case_table_names=1
character_set_server=utf8
max_connections=900
max_connect_errors=600
server-id=1
log-bin=mysql-bin
replicate-wild-do-table=db1.%
replicate-wild-do-table=db2.%
slow_query_log=1
long_query_time=1
log_error

0

So you can copy master and slave. By inserting or modifying data into the db1 and db2 databases on the primary server, you can automatically copy to the slave server.