Docker安装MySql8主从
摘要：主要介绍了基于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;查看状态
作者：昵称为空C
链接：https://juejin.cn/post/7169164947664928775