instructions

It is only used in the development environment or to learn the master/slave replication configuration because the master/slave instances are running on a single machine.

Project address github.com/TomCzHen/my…

File structure

├ ─ ─ docker - compose. Yaml ├ ─ ─ env │ ├ ─ ─ base. The env │ ├ ─ ─ master. The env │ └ ─ ─ node. The env ├ ─ ─ the env ├ ─ ─ init - db - SQL │ ├ ─ ─ Init - master. Sh │ ├ ─ ─ init - node. Sh │ ├ ─ ─ sakila - data. SQL │ └ ─ ─ sakila - schema. SQL └ ─ ─ the README, mdCopy the code

env

The.env file in the root directory is scoped in docker-comemage. yaml, while the env file has a different role from the container’s internal environment variables.

• .env

TAG=5.7.20

MASTER_SERVER_ID=1
NODE_1_SERVER_ID=10
NODE_2_SERVER_ID=20

MASTER_MYSQL_ROOT_PASSWORD=master_root_pwd
NODE_MYSQL_ROOT_PASSWORD=node_root_pwd
Copy the code

docker-compose.yaml

version: "3.3"

services:
  
  mysql-master: &mysql
    image: mysql:${TAG}
    container_name: mysql-master
    restart: unless-stopped
    env_file:
      - env/base.env
      - env/master.env
    environment:
      - MYSQL_ROOT_PASSWORD=${MASTER_MYSQL_ROOT_PASSWORD}
    ports:
      - "3306:3306"
    expose:
      - "3306"
    volumes:
      - mysql-master-data:/var/lib/mysql
      - ./init-db-sql/sakila-schema.sql:/docker-entrypoint-initdb.d/1-schema.sql
      - ./init-db-sql/sakila-data.sql:/docker-entrypoint-initdb.d/2-data.sql
      - ./init-db-sql/init-master.sh:/docker-entrypoint-initdb.d/3-init-master.sh
    command: [
      "--log-bin=mysql-bin".
      "--server-id=${MASTER_SERVER_ID}".
      "--character-set-server=utf8mb4".
      "--collation-server=utf8mb4_unicode_ci".
      "--innodb_flush_log_at_trx_commit=1".
      "--sync_binlog=1"
      ]

  mysql-node-1: &mysql-node
    < < : *mysql
    container_name: mysql-node-1
    env_file:
      - env/base.env
      - env/node.env
    environment:
      - MYSQL_ROOT_PASSWORD=${NODE_MYSQL_ROOT_PASSWORD}
      - MASTER_MYSQL_ROOT_PASSWORD=${MASTER_MYSQL_ROOT_PASSWORD}
    ports:
      - "3307:3306"
    depends_on:
      - mysql-master
    volumes:
      - mysql-node-1-data:/var/lib/mysql
      - ./init-db-sql/sakila-schema.sql:/docker-entrypoint-initdb.d/1-schema.sql
      - ./init-db-sql/sakila-data.sql:/docker-entrypoint-initdb.d/2-data.sql
      - ./init-db-sql/init-node.sh:/docker-entrypoint-initdb.d/3-init-node.sh
    command: [
      "--server-id=${NODE_1_SERVER_ID}".
      "--character-set-server=utf8mb4".
      "--collation-server=utf8mb4_unicode_ci".
      ]
  
  mysql-node-2:
    < < : *mysql-node
    container_name: mysql-node-2
    ports:
      - "3308:3306"
    volumes: 
      - mysql-node-2-data:/var/lib/mysql
      - ./init-db-sql/sakila-schema.sql:/docker-entrypoint-initdb.d/1-schema.sql
      - ./init-db-sql/sakila-data.sql:/docker-entrypoint-initdb.d/2-data.sql
      - ./init-db-sql/init-node.sh:/docker-entrypoint-initdb.d/3-init-node.sh
    command: [
      "--server-id=${NODE_2_SERVER_ID}".
      "--character-set-server=utf8mb4".
      "--collation-server=utf8mb4_unicode_ci".
      ]
    

volumes:
  mysql-master-data:
  mysql-node-1-data:
  mysql-node-2-data:
Copy the code

Because YAML’s reference syntax is used, you can see the full content in the Docker-compose config. There is one master library and two slave libraries, and the number of slave libraries can be changed as required.

Initialize instance

    .
    volumes:
      - mysql-master-data:/var/lib/mysql
      - ./init-db-sql/sakila-schema.sql:/docker-entrypoint-initdb.d/1-schema.sql
      - ./init-db-sql/sakila-data.sql:/docker-entrypoint-initdb.d/2-data.sql
      - ./init-db-sql/init-master.sh:/docker-entrypoint-initdb.d/3-init-master.sh
    .
Copy the code

The.sql. sh file under /docker-entrypoint-initdb.d will be executed in the order of file names during initialization (only for the first time). For details, you can view the image description page:

hub.docker.com/_/mysql/

Initializing a fresh instance

When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. You can easily populate your mysql services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.

Note:sakilaIs the official sample database for MySQLDev.mysql.com/doc/sakila/…

Configuring Instance Parameters

    .
    command: [
      "--log-bin=mysql-bin".
      "--server-id=${MASTER_SERVER_ID}".
      "--character-set-server=utf8mb4".
      "--collation-server=utf8mb4_unicode_ci".
      "--innodb_flush_log_at_trx_commit=1".
      "--sync_binlog=1"
      ]
    .
Copy the code

Use execution parameters to configure instances without relying on my.cnf. For details, see the image description page:

hub.docker.com/_/mysql/

Configuration without a cnf file Many configuration options can be passed as flags to mysqld. This will give you the flexibility to customize the container without needing a cnf file. For example, if you want to change the default encoding and collation for all tables to use UTF-8 (utf8mb4) just run the following:

$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
Copy the code

If you would like to see a complete list of available options, just run:

$ docker run -it --rm mysql:tag --verbose --help
Copy the code

A master-slave configuration

init-master.sh

#! /bin/bash

set -e

# create replication user

mysql_net=$(ip route | awk '$1=="default" {print $3}' | sed "s/\.[0-9]\+$/.%/g")

MYSQL_PWD=${MYSQL_ROOT_PASSWORD} mysql -u root \
-e "CREATE USER '${MYSQL_REPLICATION_USER}'@'${mysql_net}' IDENTIFIED BY '${MYSQL_REPLICATION_PASSWORD}'; \
GRANT REPLICATION SLAVE ON *.* TO '${MYSQL_REPLICATION_USER}'@'${mysql_net}';"
Copy the code

Add an account for replication to the master library. Since the –skip-name-resolve parameter is enabled by default for mirroring, permissions can only be configured by IP.

Note: The user is added after obtaining the default gateway network segment of the container in the script, which is not applicable to the production environment.

init-node.sh

#! /bin/bash

# check mysql master run status

set -e

until MYSQL_PWD=${MASTER_MYSQL_ROOT_PASSWORD} mysql -u root -h mysql-master ; do
  >& 2echo "MySQL master is unavailable - sleeping"
  sleep 3
done

# create replication user

mysql_net=$(ip route | awk '$1=="default" {print $3}' | sed "s/\.[0-9]\+$/.%/g")

MYSQL_PWD=${MYSQL_ROOT_PASSWORD} mysql -u root \
-e "CREATE USER '${MYSQL_REPLICATION_USER}'@'${mysql_net}' IDENTIFIED BY '${MYSQL_REPLICATION_PASSWORD}'; \
GRANT REPLICATION SLAVE ON *.* TO '${MYSQL_REPLICATION_USER}'@'${mysql_net}';"

# get master log File & Positionmaster_status_info=$(MYSQL_PWD=${MASTER_MYSQL_ROOT_PASSWORD} mysql -u root -h mysql-master -e "show master status\G") LOG_FILE=$(echo "${master_status_info}" | awk 'NR! =1 && $1=="File:" {print $2}') LOG_POS=$(echo "${master_status_info}" | awk 'NR! =1 && $1=="Position:" {print $2}')
# set node master

MYSQL_PWD=${MYSQL_ROOT_PASSWORD} mysql -u root \
-e "CHANGE MASTER TO MASTER_HOST='mysql-master', \
MASTER_USER='${MYSQL_REPLICATION_USER}', \
MASTER_PASSWORD='${MYSQL_REPLICATION_PASSWORD}', \
MASTER_LOG_FILE='${LOG_FILE}', \
MASTER_LOG_POS=${LOG_POS};"

# start slave and show slave statusMYSQL_PWD=${MYSQL_ROOT_PASSWORD} mysql -u root -e "START SLAVE; show slave status\G"Copy the code

When configuring the slave library, wait for the master library to be ready, and get the necessary parameter values to initialize the slave library and start the slave library replication state when connecting the slave library to the master library.

Directions for use

Adjust variable values and scripts as needed, modify ports exposed by each instance of docker-compose. Yaml, and perform docker-compose up -d deployment orcheography in the project directory.

Docker-compose logs mysql-master: docker-compose logs mysql-master

Docker exec-ti mysql-master bash docker exec-ti mysql-master bash

other

GTIDs mode requires tuning execution parameters in scripts and choreography files. If you want to use containers to add slave libraries to existing instances, you need to modify init-node.sh to pass File and Position as environment variables.