docker-compose 安装多个mysql并配置主从复制

docker-compose.yml

version: '3'
services:
  mysql-1:
    restart: always
    image: mysql:5.7.28
    container_name: mysql-1
    volumes:
      #      数据库存储，根据实际情况替换
      - /alidata/mysql-1/data:/var/lib/mysql:rw
      #      数据库配置目录，初始安装注释配置文件目录映射
      #- /alidata/mysql-1/conf/mysql.conf.d:/etc/mysql/mysql.conf.d:rw
      #      数据库还原目录 可将需要还原的sql文件放在这里
      - /alidata/mysql-1/source:/docker-entrypoint-initdb.d
    environment:
      #      数据库密码，根据实际情况替换
      - "MYSQL_ROOT_PASSWORD=123456" 
      - "TZ=Asia/Shanghai"
    # network_mode: "host"
    #      映射端口，根据实际情况替换
    ports:
      - 23306:3306
  mysql-2:
    restart: always
    image: mysql:5.7.28
    container_name: mysql-2
    volumes:
      #      数据库存储，根据实际情况替换
      - /alidata/mysql-2/data:/var/lib/mysql:rw
      #      数据库配置目录，初始安装注释配置文件目录映射
      # - /alidata/mysql-2/conf/mysql.conf.d:/etc/mysql/mysql.conf.d:rw
      #      数据库还原目录 可将需要还原的sql文件放在这里
      - /alidata/mysql-2/source:/docker-entrypoint-initdb.d
    environment:
      #      数据库密码，根据实际情况替换
      - "MYSQL_ROOT_PASSWORD=123456" 
      - "TZ=Asia/Shanghai"
    # network_mode: "host"
    #      映射端口，根据实际情况替换
    ports:
      - 23307:3306　

初始安装注释配置目录的映射,

docker-compose -p mysql up -d

　　拷贝mysql容器中的配置文件到主机目录中

docker cp mysql-1:/etc/mysql/mysql.conf.d /alidata/mysql-1/conf
docker cp mysql-2:/etc/mysql/mysql.conf.d /alidata/mysql-2/conf

　修改主机目录的配置文件，映射主机目录

version: '3'
services:
  mysql-1:
    restart: always
    image: mysql:5.7.28
    container_name: mysql-1
    volumes:
      #      数据库存储，根据实际情况替换
      - /alidata/mysql-1/data:/var/lib/mysql:rw
      #      修改配置文件后，初配置文件目录映射
      - /alidata/mysql-1/conf/mysql.conf.d:/etc/mysql/mysql.conf.d:rw
      #      数据库还原目录 可将需要还原的sql文件放在这里
      - /alidata/mysql-1/source:/docker-entrypoint-initdb.d
    environment:
      #      数据库密码，根据实际情况替换
      - "MYSQL_ROOT_PASSWORD=123456" 
      - "TZ=Asia/Shanghai"
    # network_mode: "host"
    #      映射端口，根据实际情况替换
    ports:
      - 23306:3306
  mysql-2:
    restart: always
    image: mysql:5.7.28
    container_name: mysql-2
    volumes:
      #      数据库存储，根据实际情况替换
      - /alidata/mysql-2/data:/var/lib/mysql:rw
      #      修改配置文件后，初配置文件目录映射
      - /alidata/mysql-2/conf/mysql.conf.d:/etc/mysql/mysql.conf.d:rw
      #      数据库还原目录 可将需要还原的sql文件放在这里
      - /alidata/mysql-2/source:/docker-entrypoint-initdb.d
    environment:
      #      数据库密码，根据实际情况替换
      - "MYSQL_ROOT_PASSWORD=123456" 
      - "TZ=Asia/Shanghai"
    # network_mode: "host"
    #      映射端口，根据实际情况替换
    ports:
      - 23307:3306

　　重新创建容器

docker-compose down
docker-compose -p mysql up -d

　　修改主数据库配置（mysql-1：mysqld.cnf）:

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# 主从复制配置如下

#主服务器唯一Id[必填]
server-id=1
#启用二进制日志[必填]
log-bin=/var/lib/mysql/mysqlbin 
#主机，读写都可以
read-only=0
#设置不要复制的数据库[可选]
#binlog-ignore-db=mysql
#设置需要复制的数据库[可选]（输数据库名字） 
binlog-do-db=hlyun_v5_iam
binlog-do-db=hlyun_v5_crm

　　修改从数据库配置（mysql-2：mysqld.cnf）:

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#主从复制配置
#从服务器唯一Id
server-id=2
#启用二进制日志
log-bin=/var/lib/mysql/mysqlbin 
#只读，对拥有super权限的账号是不生效的
read_only = 1

　　重启数据库：

　docker-compose restart

　　查看主数据库（mysql-1）:

show master status;

　　

 

 其他命令：

reset master
show master status;

　创建其他用户

CREATE USER 'slave' IDENTIFIED BY '123456';
grant replication slave on *.* to 'slave'@'192.168.1.130' identified by '123456';
flush privileges;

　　配置从数据库

change master to master_host='192.168.1.130',
master_user='root',
master_password='123456',
master_log_file='mysqlbin.000001',
master_log_pos=154;

　　启动复制服务

start slave

　　其他命令：

STOP SLAVE
reset slave 
show slave status

　　

 

 进入从库：

docker exec -it mysql-2 /bin/bash

　　进入数据库

 mysql -uroot -p123456

　　查看状态

show slave status\G

　　

 注意：需要手动创建初始数据库