docker compse创建mysql主从数据库

！！以下内容在mysql8.0+测试未通过
一、docker创建mysql主从数据库
1.进入docker compose目录，创建docker compose文件

mysql_master.yml

version: '3.7'
services:
  mysql_master:
    image: mysql:5.7.26
    hostname: mysql_master
    environment:
      - TZ=Asia/Shanghai
      - MYSQL_ROOT_PASSWORD=Mysql123
    configs:
      - source: mysql_master
        target: /etc/mysql/conf.d/mysql.cnf
    volumes:
      - /docker/data/mysql_master:/var/lib/mysql
    networks:
      - cloud
    ports:
      - 3306:3306
configs:
  mysql_master:
    file: ./profiles/mysql_master/mysql.cnf
networks:
  cloud:
    external:
      name: cloud

mysql_slave.yml

version: '3.7'
services:
  mysql_slave:
    image: mysql:5.7.26
    hostname: mysql_slave
    environment:
      - TZ=Asia/Shanghai
      - MYSQL_ROOT_PASSWORD=Mysql123
    configs:
      - source: mysql_slave
        target: /etc/mysql/conf.d/mysql.cnf
    volumes:
      - /docker/data/mysql_slave:/var/lib/mysql
    networks:
      - cloud
    ports:
      - 13306:3306
configs:
  mysql_slave:
    file: ./profiles/mysql_slave/mysql.cnf
networks:
  cloud:
    external:
      name: cloud

2.创建本地挂载数据目录
3.创建mysql.cnf文件

mysql master配置文件

[mysqld]
server-id=100    # server-id必须唯一  
log-bin=mysql-bin  
log-slave-updates=1    # 重要参数！
binlog-do-db={dbname}    # 需要备份的数据库名，如果有多个，重复设置这个选项即可

主数据库可选配置项

binlog-ignore-db={dbname}    # 不需要备份的数据库名，如果有多个，重复设置这个选项即可
slave-skip-errors=1    # 跳过错误，继续执行复制操作
expire_logs_days=30    # 二进制日志过期时间
max_binlog_size=500M    # 二进制日志文件大小上限

mysql slave配置文件

[mysqld]
server-id=101    # server-id必须唯一  
log-bin=mysql-slave-bin   
relay_log=edu-mysql-relay-bin

4.发布

docker stack deploy -c mysql_master.yml --with-registry-auth cloud
docker stack deploy -c mysql_slave.yml --with-registry-auth cloud

二、建立主从复制
1.连接mysql主数据库，新建一个测试DB
2.mysql主数据库操作
1）进入mysql主数据库容器

docker exec -it {容器ID：mysql-master} /bin/bash    # 进入master数据库容器

2）登录mysql主数据库，创建一个复制用户，具有replication slave 权限

CREATE USER 'slave'@'%' IDENTIFIED WITH ‘mysql_native_password’ BY 'Slave123';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
FLUSH PRIVILEGES;

3）设置读锁

FLUSH TABLES WITH READ LOCK;

4）查看binlog日志文件名和偏移量

SHOW MASTER STATUS;

QQ截图20200806142850.png

6）备份需要同步的数据库

mysqldump -uroot -pMysql123 {dbname}>{dbname}.sql    # 备份需要同步的数据库

7）解锁

UNLOCK TABLES;

3.mysql从数据库操作
1）将备份的数据库数据恢复到从数据库

cd /docker/data/mysql-slave/
cp ../mysql-master/{dbname}.sql .    
docker exec -it {容器ID：mysql-slave} /bin/bash    #进入slave数据库容器

CREATE DATABASE `{dbname}` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
USE {dbname}
SOURCE /var/lib/mysql/{dbname}.sql

2）设置从数据库

STOP SLAVE;

change master to 
master_host='mysql-master',
master_user='slave',
master_password='Slave123',
master_port=3306, 
master_log_file='mysql-bin.000003', 
master_log_pos=76324, 
master_connect_retry=30;

START SLAVE;

/** 查看状态，如果Slave_IO_Running和Slave_SQL_Running均为Yes则成功  **/
SHOW SLAVE STATUS;

QQ截图20200806144000.png

参考资料：
https://www.cnblogs.com/rwxwsblog/p/4542417.html

最后编辑于：2020.08.10 11:31:57