1主2从 读写分离
1、拉取镜像
docker pull mysql:8.0.26
2、找个目录创建对应的文件夹
data,log,conf,my.cnf
my.cnf 我放在最后
3、创建主库容器
docker run -p 3388:3306 --name master --restart=always --privileged=true --restart unless-stopped -v /usr/local/src/mysql-readWrite/write/log:/var/log/mysql -v /usr/local/src/mysql-readWrite/write/data:/var/lib/mysql -v /usr/local/src/mysql-readWrite/write/my.cnf:/etc/mysql/my.cnf -v /usr/local/src/mysql-readWrite/write/conf/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.26 --server-id=1 --log-bin=bin-log --binlog-do-db=znzm-dlaq
- docker run 表示创建并运行容器
- --restart=always docker 开机启动,失败也会一直重启;
- --privileged=true 让容器可以访问宿主机的所有设备
-
--restart unless-stopped
这个选项意味着除非你手动停止了容器,否则Docker会尝试重启它。这个选项在你需要一个始终运行的后台服务时非常有用。 - -p 3388:3306 把宿主机的3388端口映射到容器的3306端口
- --name master 表示给这个容器取个名字叫 master
- -e MYSQL_ROOT_PASSWORD=123456 设置 root 用户的密码
- -d mysql 字母d是detach的首字母,表示后台静默运行
- --server-id=1 设置唯一id
- --log-bin=bin-log 设置binlog日志文件名,因为主从复制是通过这个日志来传输
- --binlog-do-db=znzm-dlaq 对应要复制的数据名(!主从读写分离主要使用该数据库)
创建从库容器1
docker run -p 3389:3306 --name slave --restart=always --privileged=true --restart unless-stopped -v /usr/local/src/mysql-readWrite/read/log:/var/log/mysql -v /usr/local/src/mysql-readWrite/read/data:/var/lib/mysql -v /usr/local/src/mysql-readWrite/read/my.cnf:/etc/mysql/my.cnf -v /usr/local/src/mysql-readWrite/read/conf/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.26 --server-id=2 --relay-log=mysql-relay --read-only=1
创建从库容器2
docker run -p 3390:3306 --name slave2 --restart=always --privileged=true --restart unless-stopped -v /usr/local/src/mysql-readWrite/read2/log:/var/log/mysql -v /usr/local/src/mysql-readWrite/read2/data:/var/lib/mysql -v /usr/local/src/mysql-readWrite/read2/my.cnf:/etc/mysql/my.cnf -v /usr/local/src/mysql-readWrite/read2/conf/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.26 --server-id=3 --relay-log=mysql-relay --read-only=1
--server-id=2
设置唯一id--relay-log=mysql-relay
relay-log日志文件名,因为主从复制时去主库复制过来后,会先写入这个本机日志,然后再写入sql库里--read-only=1
表示从库,只读不能写
4、在master主库中创建主从读写分离账户
进入主库,密码是123456
docker exec -it master /bin/bash
mysql -u root -p
下面是创建主从读写分离账户,可自定义:
# 创建一个用户名slave1,密码123456
CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';
# 授权
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';
#此语句必须执行。否则报错
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
# 使权限生效
flush privileges;
查询主库的状态,并记录下File和Position的值,下面从库连接主库时要用。
show master status;
Crtl+D退出容器,查看主库容器本地ip。
docker inspect --format={{.NetworkSettings.IPAddress}} master
5、进入slave从库中,配置主从连接
进入从库,密码是123456
docker exec -it slave /bin/bash
mysql -u root -p
配置主从连接
CHANGE MASTER TO MASTER_HOST='172.17.0.5', MASTER_PORT=3306,MASTER_USER='slave1', MASTER_PASSWORD='123456', MASTER_LOG_FILE='bin-log.000003', MASTER_LOG_POS=2763;
说明:
- MASTER_HOST='172.17.0.5' 本机容器的ip
- MASTER_PORT=3306 容器的ip
- MASTER_LOG_FILE='bin-log.000003' 主库File
- MASTER_LOG_POS=2763; 主库Position
启动主从同步:
START SLAVE;
执行sql语句,检查主从同步是否搭建成功
SHOW SLAVE STATUS\G;
如果有异常,请停止主从同步后,重新配置
stop slave;
reset slave;
2个从库都是同步操作即可。
my.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Custom config should go here
!includedir /etc/mysql/conf.d/
[mysqld]
slow_query_log = on
slow_query_log_file = /var/lib/mysql/slow-query.log
long_query_time = 2
user=mysql
#服务端口号 默认3306
port=3306
#忽略表名大小写
lower_case_table_names=1
character-set-server=utf8
#密码加密
default_authentication_plugin=mysql_native_password
secure_file_priv=/var/lib/mysql
expire_logs_days=7
#mysql8会有group by 报错这样可以避免
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections=1000
[client]
default-character-set=utf8
[mysql]