MySQL读写分离方案之 MySQL 一主多从

关注

原创最新推荐文章于 2026-04-17 09:57:42 发布 1.6k 阅读 AI 写同款· GEO 优化 ›

【Java】 Java MySQL 实现读写分离方案 - 配置连接池 -->-- 数据库驱动 -->-- 相应驱动的jdbcUrl -->-- 数据库的用户名 -->-- 数据库的密码 -->-- 检查数据库连接池中空闲连接的间隔时间，单位是分，默认值：240，如果要取消则设置为0 -->-- 连接池中未使用的链接最大存活时间，单位是分，默认值：60，如果要永远存活设置为0 -->-- 每个分区最大的连接数 -->-- 每个分区最小的连接数 --></bean>-- 配置连接池 -->-- 数据库驱动 --> 阅读详情

MySQL 一主多从实现读写分离

本节主要基于Docker 实现mysql主从复制达到读写分离的效果

主从架构原理

第一步就是master记录二进制日志（这些记录叫做二进制日志事件，binary log events）。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

第二步就是slave将master的binary log拷贝到它自己的中继日志(relay log)。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

第三步SQL slave thread处理该过程的最后一步，slave重复中继日志中的事件，将改变执行到自己的数据库。SQL线程从中继日志读取事件，更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。

环境配置

在主节点和从节点都做如下配置

1. 创建目录

mkdir -p /usr/local/docker/mysql
cd /usr/local/docker/mysql

mkdir conf data

2. 配置docker-compose 环境

下载docker-compose 二进制文件,并配置docker-compose

curl -L https://github.com/docker/compose/releases/download/1.24.1/docker-compose-`uname -s`-`uname -m` -o /usr/local/bin/docker-compose

chmod +x /usr/local/bin/docker-compose

3. yaml 文件

在 /usr/local/docker/mysql 目录下，创建docker-compose.yml文件

version: '3.1'
services:
  mysql:
    restart: always
    image: mysql:5.7
    container_name: mysql
    ports:
      - 3306:3306
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 123456
    command:
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
      --max_allowed_packet=128M
      --sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
    volumes:
      - ./data:/var/lib/mysql
      - ./conf:/etc/mysql/mysql.conf.d

配置Mysql 主从模式

1. 主节点

编写 mysqld.cnf 文件

配置 /usr/local/docker/mysql/conf/mysqld.cnf 文件

#  mysqld.cnf 文件内容如下
[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir    = /var/lib/mysql

symbolic-links=0

server-id = 1
log-bin = mysql-bin

启动mysql

cd /usr/local/docker/mysql

docker-compose up -d

查看master 节点状态

[外链图片转存失败(img-G2jY6pG6-1569051350666)(C:\Users\jiangkuan\Desktop\容器技术\mysql集群搭建\asserts\20190911212823.png)]

记住2个参数 File,Position的值一般都是以下2个值，不是的在Master节点上 reset master
- File: mysql-bin.000001
- Positon: 154

2. 从节点

编写 mysqld.cnf 文件

配置 /usr/local/docker/mysql/conf/mysqld.cnf 文件

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir    = /var/lib/mysql

symbolic-links=0

server-id=2

启动mysql

cd /usr/local/docker/mysql

docker-compose up -d

关闭slave服务（若没有开启，这步跳过）

stop slave

动态设置主节点信息

# 主节点ip 主节点MySQLServer的用户名密码  二级制文件名称和位置
change master to master_host='192.168.141.55',master_user='root',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=888;

[外链图片转存失败(img-VCbLLFlB-1569051350668)(C:\Users\jiangkuan\Desktop\容器技术\mysql集群搭建\asserts\20190911214255.png)]

开启Slave 服务

start slave

至此基于Docker的MySQL的读写分离，到此搭建完毕。

搭建过程中遇到的问题

Slave_IO_Running、Slave_SQL_Running不全为YES 情况

mysql replication 中slave机器上有两个关键的进程，死一个都不行，一个是slave_sql_running，一个是Slave_IO_Running，一个负责与主机的io通信，一个负责自己的slave mysql进程。

如果是slave_io_running no了，那么就我个人看有三种情况，一个是网络有问题，连接不上，像有一次我用虚拟机搭建replication，使用了nat的网络结构，就是死都连不上，第二个是有可能my.cnf有问题，配置文件怎么写就不说了，网上太多了，最后一个是授权的问题，replication slave和file权限是必须的。
一旦io为no了先看err日志，看看爆什么错，很可能是网络，也有可能是包太大收不了，这个时候主备上改max_allowed_packet这个参数。 

如果是slave_sql_running no了，那么也有两种可能，一种是slave机器上这个表中出现了其他的写操作，就是程序写了，这个是会有问题的，今天我想重现，但是有时候会有问题，有时候就没有问题，现在还不是太明了，后面再更新，还有一种占绝大多数可能的是slave进程重启，事务回滚造成的，这也是mysql的一种自我保护的措施，像关键时候只读一样。 

# 问题复现
这个时候想恢复的话，只要停掉slave，set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;再开一下slave就可以了，这个全局变量赋值为N的意思是： 
This statement skips the next N events from the master. This is useful for recovering from replication stops caused by a statement. 
This statement is valid only when the slave thread is not running. Otherwise, it produces an error.

错误实例：
show slave status\G 
Slave_IO_Running: Yes 
Slave_SQL_Running: No 

有问题了，Slave_SQL_Running应该是Yes才对。 

再往下看，有错误的提示： 

 Last_Errno: 1062
 Last_Error: Error 'Duplicate entry 'b847a74ad1e35badd71d0171bd703360' for key 'PRIMARY'' on query. Default database: 'fangh6fu'. Query: 'INSERT INTO `fangh6fu`.`ecs_sessions` (sesskey, expiry, ip, data) VALUES ('b847a74ad1e35badd71d0171bd703360', '1447919111', '192.168.1.214', 'a:0:{}')'
 
# 解决方案：
先stop slave，然后执行了一下提示的语句，就是把重复的主键记录删除，
再SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 
最后 START SLAVE; 

# 查看Slave 状态
show slave status\G 

Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 

OK了，从服务器也在几分钟内把堆积的log处理完了，两边又同步了：

收录于