mysql5.7 主从配置_mysql5.7主从搭建

一、原理

简单来讲，就是主库操作写入binlog,从库通过配置，复写binlog，并执行对应操作。

可以做到读写分离，减少服务器压力。

对于请求过多的情况，比如写请求，仅写入主库也会造成压力，但是可以根据原理多建几个可写的库，确保读的库能做到数据统一就可。

参考：

【精选】MySQL主从复制原理_牛奔的博客-CSDN博客

如何评价 mysql 主从复制原理？ - 知乎

二、搭建

这次试验使用本地phpstudy和docker搭建的mysql，使用相同host不同端口，主库3306从库3307。

都是本地的环境，docker的mysql配置还是挂载的本地文件，参考性不大，但是能体验一些bug。

phpstudy不用讲，讲下docker的mysql搭建。

现在docker hub必须让登录，没有vpn又不能翻墙。之前不用登陆的时候还能看看tags，现在全靠百度。

一键部署MySQL，轻松搞定Docker安装和配置！ - 知乎

基本上就是拉取镜像然后建立容器。

本地使用xml文件建立内容如下：

#docker-compose.yml
version: '3.1'
services:
  mysql:
    image: mysql:5.7.28
    container_name: mysql57
    privileged: true
    restart: always
    ports:
      - "3307:3306"
    environment:
      MYSQL_ROOT_PASSWORD: mima
      MYSQL_CHARACTER_SET: utf8mb4
      TZ: Asia/Shanghai
    command:
      --wait_timeout=31536000
      --interactive_timeout=31536000
      --max_connections=1000
      --default-authentication-plugin=mysql_native_password
      --lower-case-table-names=1
      --default-time_zone='+8:00'
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_unicode_ci
    volumes:
      - "D:/workspace/docker/mysql/data:/var/lib/mysql"
      - "D:/workspace/docker/mysql/conf:/etc/mysql"
      - "D:/workspace/docker/mysql/logs:/var/log/mysql"

 在对应文件位置执行 docker-compose up -d,好处就是比直接跑docker run方便点，也能设置更多参数。

搭建好后可以本地直连。正式线上应该再操作可远程访问的账户，参考：ubuntu搭建php环境记录二_php ubuntu-CSDN博客

三、配置

 mysql配置：MySQL :: MySQL 5.7 Reference Manual :: 16.1.6.4 Binary Logging Options and Variables

说实话，之前测试跑master的命令时报错，查了半天没改好。重启几次之后，错误日志有了，它自己就好了……我都不知道为啥报错。

主库配置

[mysqld]
#设置mysqlbinlog配置 开启binlog
log_bin=D:/software/cood_tools/mysql/log/mysql_master.log
 
#设置默认字符 不是必须设置
default-character-set=utf8
 
#主库id 区分主从库
server-id=1
 
#需使用主从模式的库 多个库写多个设置
binlog-do-db=test
 
#主从模式需忽略的表
binlog-ignore-db=mysql

 从库配置

[mysqld]
## 开启二进制日志功能，并指定日志文件的前缀为mysql-bin
log-bin=/var/log/mysql/slave1_bin_log.log
 
## 设置二进制日志的格式.
## 为行级格式，可以记录具体的修改内容
binlog-format=ROW
## 设置使用的二进制日志格式
##binlog_format=mixed
 
## 设置二进制日志的名称前缀为slave1-bin,实际日志文件将以该前缀加上序列号来命名
#binlog-name=slave1-bin
 
## 设置server_id，同一局域网中需要唯一
server-id=2
 
## 指定不需要同步的数据库 
binlog-ignore-db=mysql
 
## 指定需要同步的数据库，如果不配置，表示同步所有的库
binlog-do-db=test
 
# 设置二进制日志使用内存大小（事务）
binlog_cache_size=2M
 
## 二进制日志过期清理时间，默认为0，代表不自动清理
expire_logs_days=7
## 忽略主从复制中遇到的所有错误活指定的错误类型，避免slave端复制中断
## 如：1062错误码代表主键重复；1032错误码代表主从数据库数据不一致
slave_skip_errors=1062
 
## relay_log配置中继日志
relay_log=mysql-relay-bin
 
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
 
## slave设置为只读权限（具有super权限的用户除外） 0读写
read_only=1
##超级管理员仅读
#super_read_only=1
 
log-error=/var/log/mysql/error.log

主库配置成功证明

SHOW MASTER STATUS

 

 从库配置成功证明

show SLAVE STATUS

 

此处的端口名是指docker中的对应的端口，并非向主机暴露的接口。

在从库中设置主库信息

change master to master_host='宿主机ip',master_post=宿主机mysql端口号,master_user='用户名',master_password='密码';

start slave 从库启动主从服务。启动两个复制线程。复制 I/O 线程从源服务器读取事件，并将事件存储在中继日志中。

详见：MySql 中文文档 - 13.4.2.5 START SLAVE 语句 | Docs4dev

stop slave 停止复制线程

详见：MySql 中文文档 - 13.4.2.6 STOP SLAVE 语句 | Docs4dev

reset slave 使得副本忘记了其在源二进制日志中的复制位置。该语句旨在用于全新启动：清除复制元数据存储库，删除所有中继日志文件，并启动新的中继日志文件。

详见：MySql 中文文档 - 13.4.2.4 RESET SLAVE 语句 | Docs4dev

更多内容详见文档：MySql 中文文档 - MySql 5.7 | Docs4dev

四、测试

主库

update userinfo set name="123" where id=1

select * from userinfo where id=1

从库

select * from userinfo where id=1

 注：一开始设置docker的从库master_host为127.0.0.1，导致主库和从库uuid相同，报错“Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids”。

相关解决方法：

主从复制报错Fatal error:The slave I/O thread stops because master and slave have equal MySQL server UUIDs；_fatal error: the slave i/o thread stops because ma-CSDN博客

 按照方法做了不起作用，还是报同样的错，而且当时主库和从库uuid不相同，才发现是master_host错误。

五 参考

mysql文档：MySql 中文文档 - MySql 5.7 | Docs4dev

主从配置：MySQL主从数据库配置（详细介绍）_mysql主从配置-CSDN博客

bug解决：主从复制报错Fatal error:The slave I/O thread stops because master and slave have equal MySQL server UUIDs；_fatal error: the slave i/o thread stops because ma-CSDN博客