文章目录
一、安装Mysql
1.1 https://hub.docker.com/ 查询镜像
1.2 查找对应版本
1.3 查看已安装的镜像,我使用的是5.7版本
二、复制配置文件
2.1 启动mysql容器
docker run -d -p 3309:3306 \
--name master \
-e MYSQL_ROOT_PASSWORD=root \
mysql:5.7
2.2. 复制配置文件到本地
# 容器交互,只能和运行中的容器交互
docker exec -it word-mysql mysql -uroot -proot
docker exec -it word-mysql /bin/bash
# 复制master、slave配置文件
docker cp 36b57f024852:/etc/mysql/mysql.conf.d /opt/word-mysql-master/conf/
docker cp 36b57f024852:/etc/mysql/mysql.conf.d /opt/word-mysql-slave/conf/
docker cp 36b57f024852:/var/log /opt/word-mysql-master/
docker cp 36b57f024852:/var/log /opt/word-mysql-slave/
三、Mysql master、slave节点部署
3.1 挂载启动容器
docker run -d -p 3306:3306 \
--name word-mysql-master \
-v /Users/zoo/Documents/JavaEnvironment/Docker/Mysql/word-master/conf/mysql.conf.d:/etc/mysql/conf.d \
-v /Users/zoo/Documents/JavaEnvironment/Docker/Mysql/word-master/log:/var/log/mysql \
-v /Users/zoo/Documents/JavaEnvironment/Docker/Mysql/word-master/data:/var/lib/mysql \
--net mysql-network \
--ip 10.10.10.9 \
-e MYSQL_ROOT_PASSWORD=root \
mysql
3.2 extnetwork 报错需自定义网卡
# 安装network
docker network create --driver bridge --subnet 10.10.10.0/24 --gateway 10.10.10.1 mysql-network
# 删除网卡
docker network rm 网卡ID
3.3 删除容器,先停止后删除,然后在执行第一步
docker stop word-mysql-master
docker rm word-mysql-master
3.4 启动slava节点部署
docker run -d -p 3307:3306 \
--name word-mysql-slave \
-v /Users/zoo/Documents/JavaEnvironment/Docker/Mysql/word-slave/conf/mysql.conf.d:/etc/mysql/conf.d \
-v /Users/zoo/Documents/JavaEnvironment/Docker/Mysql/word-slave/log:/var/log/mysql \
-v /Users/zoo/Documents/JavaEnvironment/Docker/Mysql/word-slave/data:/var/lib/mysql \
--net mysql-network \
--ip 10.10.10.10 \
-e MYSQL_ROOT_PASSWORD=root \
mysql
四、主从文件配置
4.1 /opt/mysql-master/conf/mysql.conf.d/mysqld.cnf
[mysqld]
# 主服务器id,必须唯一
server-id=1
# 开启二进制日志文件名称
log-bin= mysql-bin
default-time_zone= '+8:00'
bind-address= 0.0.0.0
4.2 /opt/mysql-slave/conf/mysql.conf.d/mysqld.cnf
[mysqld]
# 主服务器id,必须唯一
server-id=2
log-bin=mysql-bin
default-time_zone= '+8:00'
五、数据库配置关系
5.1 master
-- 创建账户
create user 'slave1'@'%' IDENTIFIED BY '12345';
grant replication slave on *.* to 'slave1'@'%';
--刷新权限
flush privileges;
-- 指定IP
-- create user 'slave1'@'172.20.0.4' IDENTIFIED BY 'root';
-- grant replication slave on *.* to 'slave1'@'172.20.0.4';
-- flush privileges;
-- 查看状态
show master status;
-- 查看是否开启
show variables like '%log_bin%';
-- 重置关系(慎重)
RESET MASTER
-- 查看下进程是否Sleep太多。
show processlist;
5.2 slave
5.2.1 查看master库ip
docker inspect mysql-master
5.2.2 从库执行sql
change master to master_host='172.20.0.3', master_user='slave1',master_password='12345',master_log_file='mysql-binlog.000001',master_log_pos=154;
start slave;
5.2.3 查看同步状态
show slave status;
通过状态信息中的 Slave_IO_running 和 Slave_SQL_running 可以看出主从同步是否就绪,如果这两个参数全为Yes,表示主从同步已经配置完成。
-- 数据不同步,处理方法1
stop slave;
set global sql_slave_skip_counter=1;
-- 处理方法2,重置关系(重置关系后需重启容器)
RESET SLAVE
-- 查看下进程是否Sleep太多。
show processlist;
--mysql8
START REPLICA
STOP REPLICA
RESET REPLICA
SHOW REPLICA STATUS
5.3 Slave_IO_running 为 No/Connection
5.3.1 配置文件问题
虚拟机排查主从库 server_id 是否一致
如果一致:查看auto.cnf文件【cat /data/mysqldata/auto.cnf】,重命名【auto.cnf.bk】重启容器
-- 主从库执行,查看是否一致如果一致 修改
show variables like 'server_id';
修改配置文件不生效情况
mysql> stop slave;
Query OK, 0 rows affected (0.09 sec)
mysql> set global server_id=238475;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.13 sec)
5.3.2 用户权限问题
主库查看用户权限
正常权限 - GRANT REPLICATION SLAVE ON . TOslave1
@%
show grants for 'slave1'@'%';
--非正常权限执行
grant replication slave on *.* to 'slave1'@'%';
5.3.3 日志同步问题
查看log_bin
是否开启
show variables like '%log_bin%';
如下命令所示,则为未开启
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
修改/etc/my.cnf,在[mysqld]下添加log-bin=mysql-bin
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
主库操作
show master status
flush logs;
从库操作
STOP REPLICA
CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000003【主库File】',MASTER_LOG_POS=388【主库Position】;
start slave;