使用Docker自動化部署MySQL集群

作者：微技術(shù) 2024-09-13 15:32:18

本文主要是簡化mysql主從搭建，同時(shí)給需要學(xué)習(xí)mysql主從的同學(xué)一個(gè)現(xiàn)成的環(huán)境。

1. 編寫master.sh腳本

此腳本主要是根據(jù)環(huán)境變量，創(chuàng)建出用于主從同步的用戶。關(guān)于為什么要創(chuàng)建出master,slave腳本，是為了區(qū)分master和slave環(huán)境，因?yàn)橹灰裺ql和sh文件放到master目錄下面，master在第一次初始化的時(shí)候，會自動執(zhí)行里面的文件內(nèi)容

mkdir -p init init/master init/slave
cat > init/master/master.sh <<EOF
#!/bin/bash
#定義用于同步的用戶名
MASTER_SYNC_USER=\${MASTER_SYNC_USER:-sync_admin}
#定義用于同步的用戶密碼
MASTER_SYNC_PASSWORD=\${MASTER_SYNC_PASSWORD:-123456}
#定義用于登錄mysql的用戶名
ADMIN_USER=\${ADMIN_USER:-root}
#定義用于登錄mysql的用戶密碼
ADMIN_PASSWORD=\${ADMIN_PASSWORD:-123456}
#定義運(yùn)行登錄的host地址
ALLOW_HOST=\${ALLOW_HOST:-%}
#定義創(chuàng)建賬號的sql語句
CREATE_USER_SQL="CREATE USER '\$MASTER_SYNC_USER'@'\$ALLOW_HOST' IDENTIFIED BY '\$MASTER_SYNC_PASSWORD';"
#定義賦予同步賬號權(quán)限的sql,這里設(shè)置兩個(gè)權(quán)限，REPLICATION SLAVE，屬于從節(jié)點(diǎn)副本的權(quán)限，REPLICATION CLIENT是副本客戶端的權(quán)限，可以執(zhí)行show master status語句
GRANT_PRIVILEGES_SQL="GRANT SELECT,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO '\$MASTER_SYNC_USER'@'\$ALLOW_HOST';"
#定義刷新權(quán)限的sql
FLUSH_PRIVILEGES_SQL="FLUSH PRIVILEGES;"
#執(zhí)行sql
mysql -u"\$ADMIN_USER" -p"\$ADMIN_PASSWORD" -e "\$CREATE_USER_SQL \$GRANT_PRIVILEGES_SQL \$FLUSH_PRIVILEGES_SQL"
EOF

2. 編寫slave.sh腳本

slave主要是去master查詢最新的pos位置和binlog文件名稱，然后創(chuàng)建同步需要的相關(guān)信息，然后執(zhí)行start slave;

cat >init/slave/slave.sh<<EOF
#定義連接master進(jìn)行同步的賬號
SLAVE_SYNC_USER="\${SLAVE_SYNC_USER:-sync_admin}"
#定義連接master進(jìn)行同步的賬號密碼
SLAVE_SYNC_PASSWORD="\${SLAVE_SYNC_PASSWORD:-123456}"
#定義slave數(shù)據(jù)庫賬號
ADMIN_USER="\${ADMIN_USER:-root}"
#定義slave數(shù)據(jù)庫密碼
ADMIN_PASSWORD="\${ADMIN_PASSWORD:-123456}"
#定義連接master數(shù)據(jù)庫host地址
MASTER_HOST="\${MASTER_HOST:-%}"
#連接master數(shù)據(jù)庫，查詢二進(jìn)制數(shù)據(jù)，并解析出logfile和pos，這里同步用戶要開啟 REPLICATION CLIENT權(quán)限，才能使用SHOW MASTER STATUS;
RESULT=\`mysql -u"\$SLAVE_SYNC_USER" -h\$MASTER_HOST -p"\$SLAVE_SYNC_PASSWORD" -e "SHOW MASTER STATUS;" | grep -v grep |tail -n +2| awk '{print \$1,\$2}'\`
#解析出logfile
LOG_FILE_NAME=\`echo \$RESULT | grep -v grep | awk '{print \$1}'\`
#解析出pos
LOG_FILE_POS=\`echo \$RESULT | grep -v grep | awk '{print \$2}'\`
#設(shè)置連接master的同步相關(guān)信息
SYNC_SQL="change master to master_host='\$MASTER_HOST',master_user='\$SLAVE_SYNC_USER',master_password='\$SLAVE_SYNC_PASSWORD',master_log_file='\$LOG_FILE_NAME',master_log_pos=\$LOG_FILE_POS,get_master_public_key=1;"
#開啟同步
START_SYNC_SQL="start slave;"
#查看同步狀態(tài)
STATUS_SQL="show slave status\G;"
mysql -u"\$ADMIN_USER" -p"\$ADMIN_PASSWORD" -e "\$SYNC_SQL \$START_SYNC_SQL \$STATUS_SQL"
EOF

3. 編寫docker-compose.yml腳本

version: '3'
services:
  master:
    image: mysql:8
    container_name: mysql-master
    ports:
    - '3306:3306'
    restart: always
    hostname: mysql-master
    environment:
      MYSQL_ROOT_PASSWORD: "123456"
      MASTER_SYNC_USER: "sync"
      MASTER_SYNC_PASSWORD: "123456"
      ADMIN_USER: "root"
      ADMIN_PASSWORD: "123456"
      TZ: "Asia/Shanghai"
    deploy:
      resources:
        limits:
          memory: 512M
          cpus: 50m
    healthcheck:
      test: ["CMD","mysqladmin","-uroot","-p$${MYSQL_ROOT_PASSWORD}","ping","-h","localhost"]
      timeout: 2s
      interval: 10s
      retries: 5
      start_period: 5s
    logging:
      options:
        max-file: '1'
        max-size: '128k'
    command:
    -  "--server-id=1"
    -  "--character-set-server=utf8mb4"
    -  "--collation-server=utf8mb4_unicode_ci"
    -  "--log-bin=mysql-bin"
    -  "--sync_binlog=1"
    -  "--binlog-ignore-db=mysql"
    -  "--binlog-ignore-db=sys"
    -  "--binlog-ignore-db=performance_schema"
    -  "--binlog-ignore-db=information_schema"
    -  "--sql_mode=NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES"
    volumes:
    - ./init/master:/docker-entrypoint-initdb.d
    - ./data/master:/var/lib/mysql
  slave:
    image: mysql:8
    container_name: mysql-slave
    ports:
    - '3307:3306'
    restart: always
    hostname: mysql-slave
    environment:
      MYSQL_ROOT_PASSWORD: "123456"
      SLAVE_SYNC_USER: "sync"
      SLAVE_SYNC_PASSWORD: "123456"
      ADMIN_USER: "root"
      ADMIN_PASSWORD: "123456"
      MASTER_HOST: "mysql-master"
      TZ: "Asia/Shanghai"
    healthcheck: 
      test: ["CMD","mysqladmin","-uroot","-p$${MYSQL_ROOT_PASSWORD}","ping","-h","localhost"]
      timeout: 2s
      interval: 10s
      retries: 5
      start_period: 5s
    deploy:
      resources:
        limits:
          memory: 512M
          cpus: 50m
    logging:
      options:
        max-file: '1'
        max-size: '128k'
    depends_on:
      master:
        condition: service_healthy
    command:
    -  "--server-id=2"
    -  "--character-set-server=utf8mb4"
    -  "--collation-server=utf8mb4_unicode_ci"
    -  "--sql_mode=NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES"
    volumes:
    - ./init/slave:/docker-entrypoint-initdb.d
    - ./data/slave:/var/lib/mysql

啟動腳本

docker-compose up -d

后期維護(hù)過程中可能用到的腳本

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO master_log_pos=0;
START SLAVE;
SHOW SLAVE STATUS;

寫在后面，基于docker-compose搭建mysql主從已經(jīng)結(jié)束，這里主要演示了如何一鍵搭建mysql主從，同時(shí)測試在各種環(huán)境下，還能保證主從同步。但是單主從同步肯定也無法滿足企業(yè)的需求，有興趣的同學(xué)可以研究一些主主雙寫同步，然后通過nginx搭建主備模式，達(dá)到高可用。

這里提供一個(gè)判斷的方法，編寫#boot.sh腳本文件，這時(shí)候需要重寫dockerfile文件，使用myslq debian的鏡像文件。并通過apt添加netcat工具。具體不再詳述了，以下為判斷腳本。

#!/bin/bash
wait_for() {
    echo Waiting for $1 to listen on $2...
    sleep 1
    while ! nc -vz $1 $2
    do
       echo waiting...;
       sleep 1s;
    done
}
if [ -e "/slave" ]; then
   echo "slave..."
   cp /slave.cnf /etc/mysql/conf.d/
else
   echo "master..."
   cp /master.cnf /etc/mysql/conf.d/
fi


echo "master config ... "
/master.sh
echo "complete. "
if [ -n "$MASTER_HOST" ]; then
   echo "replicaof $MASTER_HOST"
   wait_for $MASTER_HOST $MASTER_PORT
   /slave.sh
        echo "Slave config complete."
fi

責(zé)任編輯：華軒來源：微技術(shù)之家