[大数据]docker——mysq5.7的docker构建主从、一主多从、高可用集群环境

摘要

在大型的系统中数据库为整个系统的基础设施。在系统的设计中尤为重要，一般在大型的项目的中数据路的配置都不是单点的。一般设计为主从架构或者一主多从架构或者高可用集群架构等。如果在生产环境中构建一个的这样的架构采用原生的安装配置方式比较复杂，本博文使用docker的方式来是生产环境下的mysql主从、一主多从、高可用集群环境。帮助大家在生产环境中能够快速的获取mysql集群的一种方式。

一、docker构建主从环境构建

MySQL 主从同步分为 5 个步骤：

1. master 节点将数据的更新记录写到 binary log 中。
2. slave 节点开启 IO 线程连接 master 节点，请求获取指定 binary log 文件的指定位置之后的日志。
3. master 节点的 binary log dump 线程将指定的 binary log 信息推送给 slave 节点。
4. slave 节点的 IO 线程接收到消息后，将日志内容写入 relay log 文件。
5. slave 节点的 SQL 线程检测到 relay log 新增了内容，马上解析 relay log 文件生成相应的 SQL 语句，并将这些 SQL 语句重放到数据库，保证主从数据一致性。

1.1 环境配置准备

• 1 安装docker(版本2.0 或者3.0)
• 2 下载好mysql5.7的镜像imaeg mysql:5.7（版本） 其他版本有差别。
• 3 配置好docker-compose
• 4 配置构建的相关msyql文件目录

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

1.2 mysql的主从配置文件

1.2.1 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

# MySQL 8.x，需要如下配置
default_authentication_plugin=mysql_native_password
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

1.2.2?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

# MySQL 8.x，需要如下配置
default_authentication_plugin=mysql_native_password
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

1.2.3 master/Dockerfile

FROM mysql:5.7
MAINTAINER zhuangxiaoyan
ADD ./master/my.cnf /etc/mysql/my.cnf

1.2.4?slave/Dockerfile

FROM mysql:5.7
MAINTAINER zhuangxiaoyan
ADD ./slave/my.cnf /etc/mysql/my.cnf

1.2.5 docker-compose.yml

配置的使用主机网络：

version: "2.0"                         #如果你是2.0 就选择2.0 如果是docekr 
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: root
      MYSQL_DATABASE: rule_platform
      MYSQL_USER: ruler
      MYSQL_PASSWORD: root
    restart: unless-stopped
 
  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: root
      MYSQL_DATABASE: rule_platform
      MYSQL_USER: ruler
      MYSQL_PASSWORD: root
    restart: unless-stopped
    networks:
      mysql-ms-network:
        ipv4_address: 172.25.0.102
 
volumes:
  mysql-master-vol:
  mysql-slave-vol:

使用自定义的自网络：?

version: "3"                         #如果你是2.0 就选择2.0 如果是docekr 
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: root
      MYSQL_DATABASE: rule_platform
      MYSQL_USER: ruler
      MYSQL_PASSWORD: root
    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: root
      MYSQL_DATABASE: rule_platform
      MYSQL_USER: ruler
      MYSQL_PASSWORD: root
    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

1.3?主从复制mysql镜像构建

• docker-compose.yml所在目录下

# 构建镜像和容器并启动容器
docker-compose up -d

# 查看构建的容器  state up则构建成功
docker-compose ps

1.4?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'@'mysql——slave的ip' identified by 'root';

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='docekr master的ip', MASTER_USER='root', MASTER_PASSWORD='root', 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: 
	  ......

1.5 docker5.7的主从测试

# master的节点创建新的库

> docker exec -it mysql-master bash

> mysql -uroot -p

> create database cluster_test;

# slave 查看是否同步

> docker exec -it mysql-slave bash

> mysql -uroot -p

> show databases;

二、docker构建一主多从环境构建

2.1 环境配置准备

• 1 安装docker(版本2.0 或者3.0)
• 2 下载好mysql5.7的镜像imaeg mysql:5.7（版本） 其他版本有差别。
• 3 配置好docker-compose
• 4 配置构建的相关msyql文件目录

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

2.2 mysql的一主多从配置文件

2.2.1 master/my.cnf

[mysqld]
server_id =  1

# 开启GTID模式
gtid-mode  =  ON
enforce-gtid-consistency    =   1

# 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db    =   mysql

# 开启二进制日志功能，可以随便取，最好有含义（关键就是这里了）
log-bin             =   mysql-bin

# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size   =   1M

# 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format       =   mixed

# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days    =   7

# 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors   =   1062

# log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates   =   ON

2.2.2? slave/my.cnf，修改server_id

[mysqld]
server_id   = 2

gtid-mode                =  ON
enforce-gtid-consistency =  1

binlog-ignore-db    =   mysql
log-bin             =   mysql-slave1-bin
binlog_cache_size   =   1M
binlog_format       =   mixed
expire_logs_days    =   7
slave_skip_errors   =   1062
relay_log           =   mysql-relay-bin
log_slave_updates   =   ON
read_only           =   1

2.2.3?docker-compose.yaml

version: '3'   #如果你是2.0 就选择2.0 如果是docekr 
services:
    mysql-master:
        container_name: mysql-master
        hostname: mysql-master
        image: "mysql:5.7"
        ports:
            - "33060:3306"
        volumes:
            - ./mysql/master/etc:/etc/mysql
            - ./mysql/master/var/lib:/var/lib/mysql
            - ./mysql/master/my.cnf:/etc/my.cnf
            - ./master/init_db/:/docker-entrypoint-initdb.d/
        environment:
            - TZ=Asia/Shanghai
            - MYSQL_ROOT_PASSWORD=123456
        command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
        restart: always

    mysql-slave-1:
        container_name: mysql-slave-1
        hostname: mysql-slave-1
        image: "mysql:5.7"
        ports:
            - "33070:3306"
        volumes:
            - ./mysql/slave1/etc:/etc/mysql
            - ./mysql/slave1/var/lib:/var/lib/mysql
            - ./mysql/slave1/my.cnf:/etc/my.cnf
            - ./slave-1/init_db/:/docker-entrypoint-initdb.d/
        environment:
            - TZ=Asia/Shanghai
            - MYSQL_ROOT_PASSWORD=123456
        command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
        restart: always
    mysql-slave-2:
        container_name: mysql-slave-2
        hostname: mysql-slave-2
        image: "mysql:5.7"
        ports:
            - "33080:3306"
        volumes:
            - ./mysql/slave2/etc:/etc/mysql
            - ./mysql/slave2/var/lib:/var/lib/mysql
            - ./mysql/slave2/my.cnf:/etc/my.cnf
            - ./slave-2/init_db/:/docker-entrypoint-initdb.d/
        environment:
            - TZ=Asia/Shanghai
            - MYSQL_ROOT_PASSWORD=123456
        command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
        restart: always

version: '3'   #如果你是2.0 就选择2.0 如果是docekr 
services:
    mysql-master:
        container_name: mysql-master
        hostname: mysql-master
        image: "mysql:5.7"
        ports:
            - "33060:3306"
        networks: ## 引入外部预先定义的网段
            srm-mysql:
                ipv4_address: 173.200.6.110   #设置ip地址
        volumes:
            - ./mysql/master/etc:/etc/mysql
            - ./mysql/master/var/lib:/var/lib/mysql
            - ./mysql/master/my.cnf:/etc/my.cnf
            - ./master/init_db/:/docker-entrypoint-initdb.d/
        environment:
            - TZ=Asia/Shanghai
            - MYSQL_ROOT_PASSWORD=123456
        command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
        restart: always

    mysql-slave-1:
        container_name: mysql-slave-1
        hostname: mysql-slave-1
        image: "mysql:5.7"
        ports:
            - "33070:3306"
        networks: ## 引入外部预先定义的网段
            srm-mysql:
                ipv4_address: 173.200.6.120   #设置ip地址
        volumes:
            - ./mysql/slave1/etc:/etc/mysql
            - ./mysql/slave1/var/lib:/var/lib/mysql
            - ./mysql/slave1/my.cnf:/etc/my.cnf
            - ./slave-1/init_db/:/docker-entrypoint-initdb.d/
        environment:
            - TZ=Asia/Shanghai
            - MYSQL_ROOT_PASSWORD=123456
        command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
        restart: always
    mysql-slave-2:
        container_name: mysql-slave-2
        hostname: mysql-slave-2
        image: "mysql:5.7"
        ports:
            - "33080:3306"
        networks: ## 引入外部预先定义的网段
            srm-mysql:
                ipv4_address: 173.200.6.130   #设置ip地址
        volumes:
            - ./mysql/slave2/etc:/etc/mysql
            - ./mysql/slave2/var/lib:/var/lib/mysql
            - ./mysql/slave2/my.cnf:/etc/my.cnf
            - ./slave-2/init_db/:/docker-entrypoint-initdb.d/
        environment:
            - TZ=Asia/Shanghai
            - MYSQL_ROOT_PASSWORD=123456
        command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
        restart: always
# 设置网络模块
networks:
    # 自定义网络
    srm-mysql:
        driver: bridge
        ipam: #定义网段
            config:
                - subnet: "173.200.6.0/24"

2.3 mysql一主多从节点配置

连接主节点创建复制账号

> docker exec -it mysql-master bash

> mysql -uroot -p

> create user 'repl'@'%' identified by 'repl';

> grant replication client,replication slave on *.* to 'repl'@'%';

连接从节点重置主

> docker exec -it mysql-slave-1 bash

> mysql -uroot -p

> reset master;

> CHANGE MASTER TO MASTER_HOST='mysql-master',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;

> start slave;

三、docker构建高可用环境构建

version: "2"

services:
    mha_share:
        image: debian:jessie
        volumes:
            - "./employees_share/:/mha_share/"
    mha_node:
        build: ./mha_node
        image: prontera/mha_node
        read_only: true
    mha_manager:
        build: ./mha_manager
        image: prontera/mha_manager
        read_only: true
    master:
        image: prontera/mha_node
        depends_on:
            - mha_node
        ports:
            - "3406:3306"
        volumes:
            - "./employees_db/:/docker-entrypoint-initdb.d/:ro"
            - "./employees_master/lib/:/var/lib/mysql/"
            - "./employees_master/log/:/var/log/mysql/"
            - "./employees_master/conf.d/:/etc/mysql/mysql.conf.d/"
        volumes_from:
            - mha_share
        env_file:
            - ./account.env
        environment:
            - MYSQL_USER=prontera
            - MYSQL_PASSWORD=123123
    slave_1:
        image: prontera/mha_node
        depends_on:
            - master
        ports:
            - "3407:3306"
        volumes:
            - "./employees_slave_1/lib/:/var/lib/mysql/"
            - "./employees_slave_1/log/:/var/log/mysql/"
            - "./employees_slave_1/conf.d/:/etc/mysql/mysql.conf.d/"
        volumes_from:
            - mha_share
        env_file:
            - ./account.env
    manager:
        image: prontera/mha_manager
        depends_on:
            - mha_manager
            - slave_1
        volumes_from:
            - mha_share
        entrypoint:
            tailf /dev/null
        env_file:
            - ./account.env

博文参考

[MySQL] 用Docker搭建MySQL主从多节点集群 - piaohua's blog