I use Docker for database master-slave configuration, because I have this demand, but after searching on the Internet, I find that there are no relevant practical documents that meet my demand, some are some fraggly documents, and I also stepped in many pits when referring to these documents for deployment.

So BASED on my own successful deployment experience, I wrote this document.

The use of Docker naturally requires a Docker environment. Of course, docker images are slow to be accessed in China, so it is recommended to use domestic sources.

Build DockerFile

Our work is based on the Mysql image.

Docker pull mysql:5.7.20 this command will download the latest mysql image or specify the version.

Create a new DockerFile:

FROM mysql:5.7.20 EXPOSE 3306 COPY my."mysqld"]Copy the code

CNF = my.cnf = my.cnf = my.cnf = my.cnf = my.cnf = my.cnf = my.cnf

! includedir /etc/mysql/conf.d/ [mysqld] pid-file=/var/run/mysqld/mysqld.pid socket=/var/run/mysqld/mysqld.sock datadir=/var/lib/mysql#log-error=/var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address    = 127.0.0.1
log-bin=/var/log/mysql/mysql-bin.index
server-id=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0Copy the code

The above is a my.conf file that I modified. We mainly modified the following options:

This option can be accessed by a remote machine, or it can be changed to 0.0.0.0
log-bin=/var/log/mysql/mysql-bin.index # Enable log-bin log path
server-id=1 Select * from IP address where ID = 1Copy the code

This is my my.cnf for my main library, basically the same for my slave library, except that the servier-id for my slave library is 2, put this file in the folder as well.

The required file structure for the build looks like this:

Heavy Exercises ── heavy exercises ── heavy exercisesCopy the code

Docker build -t master/mysql:5.7.20 Docker build -t slave/mysql:5.7.20. At the end of the command is a., which, don’t forget, represents the current directory. -t stands for tag, which is short for –tag, which is to name the image. If docker does not add the image, it will randomly give the image a name. The recommended format is name:tag

Configure the Docker container

When the build is complete we start the master and slave libraries with the following command:

docker run -p 3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=root -dMaster /mysql:5.7.20 docker run -p 3306 --name mysql-slave --link mysql-master:master-e MYSQL_ROOT_PASSWORD=root -dSlave/mysql: 5.7.20Copy the code

Then run the docker exec-it mysql-master bash and docker exec-it slave-master bash commands to access the container. Run mysql-uroot-proot to enter the mysql environment, then we have completed the environment setup, the following formal configuration master/slave connection.

Of course, docker will assign a unique port to the container, which can also be configured with mysql client connection. Use Docker PS to view the port number, you can use the client connection for management.

Netmysql-master-slave docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 6C7648e829e0 slave/mysql:5.7.20 "docker-entrypoint..." 4 minutes ago Up 4 minutes 0.0.0.0:32769->3306/ TCP mysql-slave 483842C63235 master/mysql:5.7.20 "docker-entrypoint..." 5 minutes ago Up 5 minutes 0.0.0.0:32768->3306/ TCP mysql-masterCopy the code

GRANT REPLICATION SLAVE ON *.* to ‘user’@’%’ identified by ‘mysql’; GRANT REPLICATION SLAVE ON *.* TO ‘user’@’192.168.1.200’ IDENTIFIED BY ‘mysql’; Create a user whose previous IP address is accessible to all users and whose next IP address is accessible to all users.

GRANT SELECT,REPLICATION SLAVE ON *.* TO ‘user’@’%’; Give the user read permission.

Run the show master status command to check the status of the mysql master container. The following information is displayed:

+------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB |  Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+Copy the code

Use hostname to check the hostname of the primary container, for example, my primary container is 483842C63235.

Mysql > select * from mysql;

change master to
master_host='master'.The IP address of the primary server to connect to
master_user='user'.# select root as user name
master_log_file='mysql-bin.000003'.The value recorded by the primary library
master_log_pos=154,The pos value of the main library
master_port=3306,The port mapped from primary library 3306
master_password='mysql';The password of the user to connect to the main databaseCopy the code

Run the start slave command to start the master/slave synchronization

Use the show slave status\G command

The following information is displayed:

*************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: Master // IP address of the primary server Master_User: user // Name of the authorized account. Avoid using root Master_Port: 3306 // Database port. Connect_Retry: 60 Master_Log_File: mysql-bin.000003 // Synchronize the log file name of the primary library Read_Master_Log_Pos: 154 // Synchronize the position for reading binary logs. The value is greater than or equal to Relay_Log_File: 6c7648e829e0-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes // This status must be Yes Slave_SQL_Running: Yes // This status must be Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 154 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave hasread all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)Copy the code

This completes a set of master/slave mysql configurations.

A shell script

Here is a corresponding shell script that can start a set of mysql master/slave containers in 1 minute. Running this script requires minor modifications, mainly to the mysql configuration file.

#! /bin/bash

MASTER_DIR=/var/lib/mysql/mysql-master
SLAVE_DIR=/var/lib/mysql/mysql-slave

## First we could rm the existed container
docker rm -f mysql-master
docker rm -f mysql-slave

## Rm the existed directory
rm -rf $MASTER_DIR
rm -rf $SLAVE_DIR

## Start instancedocker run -p 3306 --name mysql-master -v /etc/master.cnf:/etc/mysql/my.cnf -v $MASTER_DIR:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -d master/mysql:5.7.20 docker run -p 3306 --name mysql-slave -v /etc/slave.cnf:/etc/mysql/my.cnf -v $MASTER_DIR:/var/lib/mysql --link mysql-master:master -e MYSQL_ROOT_PASSWORD=root -d Slave/mysql: 5.7.20
## Creating a User for Replicationdocker stop mysql-master mysql-slave docker start mysql-master mysql-slave sleep 3 docker exec -it mysql-master mysql -S The/var/lib/mysql/mysql. The sock - e "CREATE USER 'users' @' 127.0.0.1 IDENTIFIED BY 'mysql'; GRANT REPLICATION SLAVE ON *.* TO 'users'@'127.0.0.1';"
## Obtaining the Replication Master Binary Log Coordinatesmaster_status=`docker exec -it master mysql -S /var/lib/mysql/mysql.sock -e "show master status\G"` Master_log_file = ` echo "$master_status" | awk 'NR 2 = = {print substr ($2, 1, length ($2) - 1)}' ` master_log_pos = ` echo "$master_status" | awk 'NR==3{print $2}'` master_log_file="'""$master_log_file""'"
## Setting Up Replication Slaves docker exec -it mysql-slave mysql -S /var/lib/mysql/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='master',MASTER_PORT=3306,MASTER_USER='users',MASTER_PASSWORD='mysql',MASTER_LOG_FILE=$master_log_file,MASTE R_LOG_POS=$master_log_pos;" docker exec -it mysql-slave mysql -S /var/lib/mysql/mysql.sock -e "start slave;" docker exec -it mysql-slave mysql -S /var/lib/mysql/mysql.sock -e "show slave status\G"
## Creates shortcutsgrep "alias mysql-master" /etc/profile if [ $? -eq 1 ]; then echo 'alias mysql="docker exec -it mysql-master mysql"' >> /etc/profile echo 'alias master="docker exec -it Mysql -master mysql -h 127.0.0.1 -p3306 "' >> /etc/profile echo 'alias slave="docker exec it mysql-master mysql -h 127.0.0.1 -p3307 "' >> /etc/profile source /etc/profile fiCopy the code