使用 Docker Compose 搭建 MySQL 資料庫主從複製例項

TomCzHen發表於2017-12-11
DockerMySql資料庫

說明

由於是單機同時執行主從例項，僅用於開發環境或學習主從複製配置。

專案地址 github.com/TomCzHen/my…

檔案結構

├── docker-compose.yaml
├── env
│   ├── base.env
│   ├── master.env
│   └── node.env
├── .env
├── init-db-sql
│   ├── init-master.sh
│   ├── init-node.sh
│   ├── sakila-data.sql
│   └── sakila-schema.sql
└── README.md
複製程式碼

env

根目錄下的 .env 檔案作用域是在 docker-compose.yaml 中，而 env 路徑下的檔案作用與容器內部環境變數，兩者作用不同。

  • .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
複製程式碼

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:
複製程式碼

由於使用了 YAML 的引用語法，可以通過 docker-compose config 檢視完整的內容。這裡配置了一個主庫，兩個從庫，可以根據需求改變從庫數量。

初始化例項

    ...
    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
    ...
複製程式碼

基礎映象會在初始化(僅首次執行)時按檔名順序執行 /docker-entrypoint-initdb.d 下的 .sql .sh 等檔案，詳細資訊可以檢視映象說明頁面：

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.

注：sakila 是 MySQL 的官方示例資料庫 dev.mysql.com/doc/sakila/…

配置例項引數

    ...
    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"
      ]
    ...
複製程式碼

使用執行引數可以不依靠 my.cnf 對例項進行配置，詳細資料可以檢視映象說明頁面：

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
複製程式碼

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

$ docker run -it --rm mysql:tag --verbose --help
複製程式碼

主從配置

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}';"
複製程式碼

在主庫中新增用於複製的帳號，由於映象預設開啟 --skip-name-resolve 引數，因此只能通過 IP 配置許可權。

注：指令碼中是獲取容器預設閘道器網段後新增使用者，不適用於生產環境。

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
  >&2 echo "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 & Position

master_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 status

MYSQL_PWD=${MYSQL_ROOT_PASSWORD} mysql -u root -e "START SLAVE;show slave status\G"
複製程式碼

配置從庫時會先等待主庫就緒，並在從庫連線到主庫獲取必要的引數值進行從庫初始化並開啟從庫複製狀態。

使用說明

根據需要調整變數值與指令碼，修改 docker-compose.yaml 中各個例項暴露的埠，在專案目錄下執行 docker-compose up -d 部署編排。

使用 docker-compose logs mysql-master 可以檢視對應容器輸出日誌。

使用 docker exec -ti mysql-master bash 可進入對應容器控制檯環境。

其他

GTIDs 模式需要調整指令碼與編排檔案中的執行引數。如果想使用容器來為已有例項新增從庫，需要修改 init-node.sh 將 File 與 Position 作為環境變數傳入應該更合適。

相關文章