docker-compose搭建mysql主從環境

原作者: 不愿署名的沈大佬(博主好友)

1. 目錄結構

mysql_sync/
   - docker-compose.yml
   - master/
       - Dockerfile
       - my.cnf
   - slave/
       - Dockerfile
       - my.cnf

2. 組態檔 my.cnf

• master/my.cnf

[mysqld]
# 主資料庫端ID號
server_id = 101
# 開啟二進制日志
log-bin = mysql-bin
# 不需要復制的資料庫名
binlog-ignore-db = mysql
binlog_cache_size=1M
# 二進制日志自動洗掉的天數，默認值為0,表示“沒有自動洗掉”，啟動時和二進制日志回圈時可能洗掉
expire_logs_days = 7
# 將函式復制到slave
log_bin_trust_function_creators = 1
binlog_format=mixed

• slave/my.cnf

[mysqld]
# 從資料庫端ID號
server_id = 102
log-bin = mysql-bin
relay_log=replicas-mysql-relay-bin
log-slave-updates = 1
# 指定slave要復制哪個庫
binlog-ignore-db=mysql
log_bin_trust_function_creators = 1
binlog_format=mixed
read_only=1

3. Dockerfile

• master/Dockerfile

FROM mysql:5.7.26
MAINTAINER klaus
ADD ./master/my.cnf /etc/mysql/my.cnf

• slave/Dockerfile

FROM mysql:5.7.26
MAINTAINER klaus
ADD ./slave/my.cnf /etc/mysql/my.cnf

4. docker-compose.yml

version: "3"
services:

  mysql-master:
    build:
      context: ./
      dockerfile: master/Dockerfile
    container_name: mysql_master
    ports:
      - 3306:3306
    volumes:
      - mysql-master-vol:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: asd123456
      MYSQL_DATABASE: rule_platform
      MYSQL_USER: ruler
      MYSQL_PASSWORD: asd123456
    restart: unless-stopped
    networks:
      mysql-ms-network:
        ipv4_address: 172.25.0.101

  mysql-slave:
    build:
      context: ./
      dockerfile: slave/Dockerfile
    container_name: mysql_slave
    ports:
      - 3307:3306
    volumes:
      - mysql-slave-vol:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: asd123456
      MYSQL_DATABASE: rule_platform
      MYSQL_USER: ruler
      MYSQL_PASSWORD: asd123456
    restart: unless-stopped
    networks:
      mysql-ms-network:
        ipv4_address: 172.25.0.102

volumes:
  mysql-master-vol:
  mysql-slave-vol:

networks:
  mysql-ms-network:
    driver: bridge
    ipam:
      config:
      - subnet: 172.25.0.0/24

5. 主從復制mysql鏡像構建

• docker-compose.yml所在目錄下

# 構建鏡像和容器并啟動容器
docker-compose up -d
# 查看構建的容器  state up則構建成功
docker-compose ps

• docker-compose進入容器中設定主從配置

# ------ 主mysql
[klaus@messi mysql_sync]\$ docker-compose exec mysql-master bash
root@dd8d062e1e72:/# mysql -uroot -p

mysql> grant replication slave on *.* to 'root'@'172.25.0.102' identified by 'asd123456';
Query OK, 0 rows affected, 1 warning (0.14 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      600 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# ------ 從mysql （根據master的status設定 File 和 Postion）
[klaus@messi mysql_sync]\$ docker-compose exec mysql-slave bash
root@1b90daad057a:/# mysql -uroot -p

mysql> reset slave;
Query OK, 0 rows affected (0.07 sec)

# 設定 HOST, USER, PASSWORD, (LOG_FILE, LOG_POS)---master中對應的File和Position
mysql> CHANGE MASTER TO MASTER_HOST='172.25.0.101', MASTER_USER='root', MASTER_PASSWORD='asd123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=600;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

# 查看從mysql是否配置成功
mysql> show slave status\\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.0.101
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 600
               Relay_Log_File: replicas-mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes							# Yes is Success --------
            Slave_SQL_Running: Yes							# Yes is Success --------
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
	  ......