摘要：主要介绍了基于docker mysql:8.0.31的主从模式搭建和使用

基础准备

• docker

参考 juejin.cn/post/696827…

• docker-compose

参考 juejin.cn/post/696827…

• 创建docker network

docker network create --driver=bridge --subnet=192.168.0.1/16 huzhihui 

master节点

docker-compose.yml

version: '3'
services:
  mysql:
    image: mysql:8.0.31
    networks:
      - default
    container_name: mysql8-master
    volumes:
      - ./data:/var/lib/mysql
      - ./conf/my.cnf:/etc/my.cnf
    environment:
      - "MYSQL_ROOT_PASSWORD=root"
      - "TZ=Asia/Shanghai"
    ports:
      - 3316:3306
networks:
  default:
    external:
      name: huzhihui

my.cnf配置文件

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
default-storage-engine=INNODB
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
max_connections=2000

server-id=1
innodb_flush_log_at_trx_commit=2
sync_binlog=1
log-bin=binlog
replicate-ignore-db=mysql,information_schema,performance_schema,sys

运行并查看状态

• 启动主节点

docker-compose up -d

• 状态查看 docker ps

[root@localhost ~]# docker ps
CONTAINER ID   IMAGE          COMMAND                  CREATED       STATUS       PORTS                                                  NAMES
c5f7e9bb25b9   mysql:8.0.31   "docker-entrypoint.s…"   2 hours ago   Up 2 hours   33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp   mysql8-master

创建同步用户和查看binlog节点

登陆master节点

docker exec -it mysql8-master mysql -uroot -proot

创建同步用户slave

CREATE USER `slave`@`%` IDENTIFIED WITH mysql_native_password BY 'slave@123';
GRANT File, Replication Slave ON *.* TO `slave`@`%`;

查看master binlog节点

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |     2164 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

如果只想创建单机版，则到这里就可以结束教程了！

slave节点

docker-compose.yml

version: '3'
services:
  mysql:
    image: mysql:8.0.31
    networks:
      - default
    container_name: mysql8-slave
    volumes:
      - ./data:/var/lib/mysql
      - ./conf/my.cnf:/etc/my.cnf
    environment:
      - "MYSQL_ROOT_PASSWORD=root"
      - "TZ=Asia/Shanghai"
    ports:
      - 3317:3306
networks:
  default:
    external:
      name: huzhihui

my.cnf配置文件

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
default-storage-engine=INNODB
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
max_connections=2000

server-id=2
innodb_flush_log_at_trx_commit=2
sync_binlog=1
log-bin=binlog
replicate-ignore-db=mysql,information_schema,performance_schema,sys
read_only=1

运行并查看状态

• 启动从节点

docker-compose up -d

• 状态查看 docker ps

[root@localhost ~]# docker ps
CONTAINER ID   IMAGE          COMMAND                  CREATED       STATUS       PORTS                                                  NAMES
0e7861b70939   mysql:8.0.31   "docker-entrypoint.s…"   2 hours ago   Up 2 hours   33060/tcp, 0.0.0.0:3317->3306/tcp, :::3317->3306/tcp   mysql8-slave
c5f7e9bb25b9   mysql:8.0.31   "docker-entrypoint.s…"   2 hours ago   Up 2 hours   33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp   mysql8-master

设置同步

登陆slave节点

docker exec -it mysql8-master mysql -uroot -proot

设置同步参数

mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST = '192.168.137.110',
MASTER_PORT = 3316,
MASTER_USER = 'slave',
MASTER_PASSWORD = 'slave@123',
MASTER_LOG_FILE = 'binlog.000003',
MASTER_LOG_POS = 2164;
mysql> start slave;

查看从节点状态

mysql> show slave status;
| Slave_IO_State                   | Slave_IO_Running | Slave_SQL_Running 
| Waiting for source to send event | Yes              | Yes   

如果显示如上的状态则表示设置成功了，就能同步了

常见参数配置说明如下

跳转到 juejin.cn/post/704995… 该文章是5.7版本的主从配置，流程基本一致

常见问题

Slave_SQL_Running 显示NO

先手动同步所有数据到从库，然后再重新设置同步节点

• 备份主库的数据
• 从库数据清空并重新导入主库的数据
• 重新设置同步节点

# 停止
stop slave;
# 重置
reset slave;
# 重新配置节点
CHANGE MASTER TO MASTER_HOST = '192.168.137.110',
MASTER_PORT = 3316,
MASTER_USER = 'slave',
MASTER_PASSWORD = 'slave@123',
MASTER_LOG_FILE = 'binlog.000003',
MASTER_LOG_POS = 2164;
# 开启
start slave;

再次show slave status;查看状态