Mysql master/slave replication cluster setup – based on DockerCompose

Software installation

  1. Software list
The name of the version
Docker 1.14.0 – rc2
Mysql 5.7.17
Docker compose 19.03.1
  1. The directory structure
./
  - docker-compose.yml
  - master/
    - my.cnf
    - Dockerfile
  - slave/
    - my.cnf
    - Dockerfile
Copy the code

Configuration docker – compose. Yml

docker-compose.yml

version: '2'
services:
  mysql-master:
    build:
      context: ./
      dockerfile: master/Dockerfile
    environment:
      - "MYSQL_ROOT_PASSWORD=root"
      - "MYSQL_DATABASE=replicas_db"
    links:
      - mysql-slave
    ports:
      - "33065:3306"
    restart: always
    hostname: mysql-master
  mysql-slave:
    build:
      context: ./
      dockerfile: slave/Dockerfile
    environment:
      - "MYSQL_ROOT_PASSWORD=root"
      - "MYSQL_DATABASE=replicas_db"
    ports:
      - "33066:3306"
    restart: always
    hostname: mysql-slave
Copy the code

Master mysql container configuration

  1. Configuration Dockerfile
FROM mysql:5.7.17 MAINTAINER Harrison add. /master/my.cnf /etc/ mysql.my.cnfCopy the code
  1. Configure my CNF
[mysqld]
Set server_id to IP
server_id=100  
SQL > select * from database where mysql database is not synchronized
binlog-ignore-db=mysql  
## Enable binary log function, can be arbitrary, the best meaning (key is here)
log-bin=replicas-mysql-bin  
The memory allocated for each session, used to store the binary log cache during the transaction
binlog_cache_size=1M  
## Mixed,statement,row, default statement
binlog_format=mixed  
## Number of days for automatic deletion/expiration of binary logs. The default value is 0, indicating that the data will not be automatically deleted.
expire_logs_days=7  
Skip all errors or specified types of errors encountered in master/slave replication to avoid replication interruption on slave.
Error 1032 is caused by data inconsistency between primary and secondary databases
slave_skip_errors=1062
Copy the code

Slave Configures the mysql container

  1. Configuration Dockerfile
FROM mysql:5.7.17 MAINTAINER Harrison ADD./slave/my.cnf /etc/mysql.my.cnfCopy the code
  1. Configure my CNF
[mysqld]
Set server_id to IP
server_id=101  
SQL > select * from database where mysql database is not synchronized
binlog-ignore-db=mysql  
The binary log function is enabled for the Slave to function as the Master of other slaves
log-bin=replicas-mysql-slave1-bin  
The memory allocated for each session, used to store the binary log cache during the transaction
binlog_cache_size=1M  
## Mixed,statement,row, default statement
binlog_format=mixed  
## Number of days for automatic deletion/expiration of binary logs. The default value is 0, indicating that the data will not be automatically deleted.
expire_logs_days=7  
Skip all errors or specified types of errors encountered in master/slave replication to avoid replication interruption on slave.
Error 1032 is caused by data inconsistency between primary and secondary databases
slave_skip_errors=1062  
## relay_log Configures relay logs
relay_log=replicas-mysql-relay-bin  
## log_slave_updates indicates that slave writes replication events to its binary log
log_slave_updates=1  
Prevent data from changing (except for special threads)
read_only=1  
Copy the code

Start the container

In the current directory, run the docker-compose startup command.

docker-compose up -d
Copy the code

If the following output is displayed, the container has been started successfully

[root@localhost my-mysql]# docker-compose up -dBuilding mysql-slave Step 1/3: FROM mysql:5.7.17 --> 9546CA122D3a Step 2/3: MAINTAINER Harrison --> Runningin df2ad9059e34
Removing intermediate container df2ad9059e34
 ---> eb0aacfa9ec1
Step 3/3 : ADD ./slave/my.cnf /etc/mysql/my.cnf
 ---> c6b14fcc1fcd
Successfully built c6b14fcc1fcd
Successfully tagged mymysql_mysql-slave:latest
WARNING: Image forservice mysql-slave was built because it did not already exist. To rebuild this image you must use `docker-compose Build 'or' docker-compose up --build '. Building mysql-master Step 3: FROM mysql:5.7.17 --> 9546ca122d3a Step 3: 'docker-compose' --build '. MAINTAINER harrison ---> Using cache ---> eb0aacfa9ec1 Step 3/3 : ADD ./master/my.cnf /etc/mysql/my.cnf ---> 193dc5745984 Successfully built 193dc5745984 Successfully tagged mymysql_mysql-master:latest WARNING: Imagefor service mysql-master was built because it did not already exist. To rebuild this image you must use `docker-compose build` or `docker-compose up --build`.
Creating mymysql_mysql-slave_1 ... 
Creating mymysql_mysql-slave_1 ... done
Creating mymysql_mysql-master_1 ... 
Creating mymysql_mysql-master_1 ... done
Copy the code

Using the database management tool, you can see that the connection between the master database and slave database can be successfully connected.

Check whether the configuration file is correct. If this step succeeds, subsequent operations can be recorded.

Secondary database Configuration

Mysql > install slave database (master); mysql > install slave database (master); mysql > install slave database (master); mysql > install slave database (master) So how do we associate with the master? Binary-log: binary log: binary log: binary log: binary log

  1. The state of the master library is first queried in the master database

    show master status;

    You can see that the direct result is:

    File: replicas-mysql-bin.000003

    Position: 154

  2. Run the related configuration SQL of the master database on the slave database for master/slave association

CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='replicas-mysql-bin.000003'.-- File name
MASTER_LOG_POS=154; -- binlog Records the location
Copy the code
  1. Restart theslaveservice
Docker restart Specifies the container name/id of the secondary databaseCopy the code

test

Create a test data table test in the primary database

SQL is as follows:

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

Then refresh the slave database to see if any tests have been synchronized from the primary database. Docker compose: docker compose: docker compose: docker compose: Docker compose: Docker compose