Use Docker container to build MySQL master and slave copy _docker

1. Write the main MySQL docker file

Create a new folder named Mysql-master-v1. Create a new file Dockerfile and master.cnf in the folder Mysql-master-v1. The editor that I recommend is Visual Studio Code. Use the editor to set the encoding of these two files to UTF-8. Because the introduction of this article is installed in the Linux system, so the author recommends that the reader to set the line break to the "LF" to take into account the Linux operating system. Write the two documents as follows.

Dockerfile

From mysql:5.7
# set TimeZone as China/shanghai
RUN cp/usr/share/zoneinfo/prc/etc/localtime
# copy MySQL Co Nfig 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. Main MySQL configuration file parameter description

Explain the master.cnf.

Lower_case_table_names: Ignores the case of data structures such as table names, column names, and so on (note: Not the case for each row of records!) ）。

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

Log-bin: Open binary record. This is the setup for master and slave copying. This article uses RBR (row-based Replication) mode.

Replicate-wild-do-table: Specify which tables can be replicated from the primary server to the server. Db1.% refers to a database-db1 table that can be replicated to a server. You can set multiple settings to allow more than one database.

REPLICATE-DO-DB is not used because REPLICATE-DO-DB prohibits access across databases. For example, if the user has already used use DB2, and the database DB2 is selected, and the user performs the update db1.t_student set c_name= ' Stu_c ' where c_id= ' 1234c ', then replicate-do-db cannot be used. While programming is not used for use by programmers, it is possible for programmers to write URLs that are connected to MySQL as a specific database or not to specify a particular database. Even if you do not specify a database, using REPLICATE-DO-DB is not allowed.

Slow_query_log=1: Open the slow query log. If one of the SQL execution times exceeds the number of seconds long_query_time set, it is recorded. The record file path can use the show variables command to find a specific log file path under the variable name slow_query_log_file.

Long_query_time=1: The unit is seconds. Indicates that if an SQL statement executes longer than 1 seconds, it is recorded. This variable cannot be used until the slow query log has to be turned on.

Log_error: Open error log. Show variables like ' log_error '; You can query the path to the log file. MySQL Docker official mirror If you set another value, the container will not start properly.

3. Generate and use mirroring of master MySQL

Upload folder Mysql-master-v1 to Linux server. For ease of explanation, use Server A to refer to this server. The author's Linux version is Ubuntu14.04. Enter directory mysql-master-v1 via CD command. Then, enter the following command to compile the mirror:

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 a compiled mirror:

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

Using MySQL client to connect to the MySQL master server, the author is using MySQL Workbench. Execute the following command:

Show master status;

Record file and position. This assumes that file is Mysql-bin.000001,post is 154.

4. Write a docker file from MySQL

Create a new folder Mysql-slave-v1. Follow step 1 to create a new dockerfile and slave.cnf two files under folder Mysql-slave-v1. The following are the contents of these two files.

Dockerfile

From mysql:5.7
# set TimeZone as China/shanghai
RUN cp/usr/share/zoneinfo/prc/etc/localtime
# copy MySQL Co Nfig 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

The server-id=2 in the Slave.cnf file will set the MySQL server to be from the server.

5. Generate and use a mirror image from MySQL

Upload MYSQL-SLAVE-V1 to the second server. Use Server B to refer to this server.

Compile Mirror:

Docker build-t Zhangchao/mysql-slave5.7:v1.

To build a container with a 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 time MySQL master and slave server are running, need the reader to ensure that the data on both sides of the same. A master-slave replication connection can be established only when the data for both servers are the same.

6. Connect from MySQL to master MySQL

Using the client to connect to MySQL from the server, assume that server A's IP 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 command is executed normally, follow the following command:

Start slave;

This can be copied from the master. As long as you insert or modify data on the primary server's DB1 and DB2 databases, you can automatically copy it to the server.

The above is a small set to introduce the use of Docker containers to build MySQL master copy, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!