当前位置： 首页 > news >正文

MySQL主从同步读写分离

news 2025/10/23 20:35:28

[!NOTE]

准备三台机器：

两台用于主从同步，一台读写分离。

环境初始化：关防火墙

一、主从同步实验

  1. 主机从机安装数据库

    # 安装 wget下载工具yum -y install wget
# 下载 mysql 官方 yum 源安装包wget https://repo.mysql.com/mysql57-community-release-el7-11.noarch.rpm 
# 安装 mysql 官方 yum 源yum -y localinstall mysql57-community-release-el7-11.noarch.rpm
# 配置yum源秘钥rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
# 列出所有版本yum repolist all | grep mysql#mysql默认用5.7版本，一下可不做# 安装yum配置工具#	yum -y install yum-utils# 禁用8.0的版本#	yum-config-manager --disable mysql80-community# 启动5.7的版本#	yum-config-manager --enable mysql57-community# 检查启动版本yum repolist enabled | grep mysql
# 安装服务端、客户端yum -y install mysql-community-server mysql
# 设置开机启动机启动mysql服务端systemctl enable mysqld --now
# 检查mysql的安装ls /var/lib/mysql
# 获取首次登录密码grep 'password' /var/log/mysqld.log
# 登录mysql数据库mysql -uroot -p'y;3YqgyA4P>'
# 修改 mysql 数据库密码（密码必须符合复杂性要求，包含字母大小写，数字，特殊符号，长度不少于8位）alter user 'root'@'localhost' identified by 'Aa123456=';
# 用新密码登录数据库mysql -uroot -p'Aa123456='

  2. 导入测试数据库(自己随便用一个)

    将数据库拖进主机

    #登录数据库
mysql -uroot -p'Aa123456='#创建数据库
create database washer;#使用数据库
use washer;#导入数据库
source /root/washer.sql;#检查
show tables;
#退出
exit

  3. 主机配置

    1. 修改配置文件，启动binlog日志就相关的配置，修改后重启MySQL服务

      vim /etc/my.cnf添加：

      #mysql服务ID，保证整个集群环境中唯一，取值范围：1 – 2的32次方-1，默认为1
server-id=1
#是否只读,1 代表当前服务器只读(不能做增删改操作), 0代表读写
read-only=0
#开启binlog日志，名字随机
log_bin=mysql-bin
#日志格式
binlog_format=row                
#下面两个选项可以不设置，但可以了解下。我们这里不指定这两个参数，也就是说我们现在创建的所有数据库都需要同步。
#忽略的数据, 指不需要同步的数据库
#binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01

      systemctl restart mysqld

    2. 添加并授权slave复制用户的复制权限，刷新权限

      登录mysql

      mysql -uroot -p'Aa123456='

      创建用于同步数据的用户，设置密码，添加权限

      CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'Aa123456=';

      GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';

      flush privileges;

    3. 查看并确认binlog日志开启

      登录mysql后

      show master status;

    4. 备份主库数据（如果是从0开始搭建主从同步，可以不用进行该操作/如果是主库之前就有了数据，则需要将数据导出来，现在从库中导入数据）

      在命令行中运行(不用登录mysql)

      mysqldump -uroot -p'Aa123456=' washer > /root/washer_20251022.sql

    5. 将备份文件发送到从机（如果不是从0搭建的话，需要执行该步骤）

      scp /root/washer_20251022.sql root@192.168.233.134:~

  4. 从机配置

    [!CAUTION]

    如果不是从0开始，主机配置时有备份数据库并发送数据库到从机。那么第四步‘从库导入数据’要最先做。

    1. 修改配置文件(修改server_id，添加"read_only＝on")

      vim /etc/my.cnf

      server-id=2
read-only=1

    2. 修改数据库UUID的值（如果机器是从主机克隆出来的，数据库的UUID是一样的，会导致主从失败）

      这边是另外创建而不是克隆主机，就不改了。

      修改方式：

      vim /var/lib/mysql/auto.cnf

      [auto]
server-uuid=3790f9b4-672f-11ef-bebe-000c291aea97

    3. 重启MySQL服务

      systemctl restart mysqld

    4. 从库导入数据(导入master发送过来的备份数据)

      如果有这一步，因为前面设置了只读，所以这一步要最先做。

      参考前面2.导入测试数据库

    5. 配置同步master（在从库中配置主从同步，就是从master上复制数据过来）

      # 登录数据库
mysql -uroot -p'Aa123456='# 停止从库复制
stop slave;# 配置从库复制(这里的数据ip要写主机ip，user和password写前面主机配置中添加的slave用户信息，log_file和log_pos要在主机登录数据库用show master status指令查看)
change master to master_host='192.168.233.133', master_user='slave', master_password='Aa123456=', master_log_file='mysql-bin.000002', master_log_pos=749;
# 开启从库复制
start slave;

      SOURCE_HOST：原主机地址；
      SOURCE_USER 原主机上对应的MySQL的用户名是什么；
      SOURCE_PASSWORD：原主机密码是什么
      SOURCE_LOG_FILE：我从哪个二进制文件开始读；
      SOURCE_LOG_POS：代表position，我要从这份日志中的哪个位置开始读

    6. 查看同步状态

      # 查看从库复制的状态
show slave status\G;

image-20251022182208023

image-20251022182217263

  master主机中查看状态`show master status;``show slave hosts;`

  1. 测试验证

    在主机数据库中创建测试数据库

    create database test1;                # 创建完成之后去从库查询新创建的库是否同步过去
use test1;
create table student(id int, name varchar(20));
insert into student values(1, 'zhangsan'), (2, 'lisi');            # 添加成功之后去从库中查询数据

    在从机中查看数据库，有test1数据库就成功了。

二、读写分离实验

将jdk8和mycat1.6.7.5的压缩包拖进MobarX中。

  1. 安装jdk和mycat

    #解压jdk压缩包
tar -xf jdk-8u471-linux-x64.tar.gz -C /usr/local
#配置java环境变量
vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.8.0_471
export PATH=$PATH:$JAVA_HOME/binsource /etc/profile#解压mycat压缩包
# 下载地址
https://github.com/MyCATApache/Mycat-Server/releases
tar -xf Mycat-server-1.6.7.5-release-20200422133810-linux.tar.gz -C /usr/local
#mycat目录说明
bin	相关软件管理命令等
conf	配置文件目录
logs	日志目录

  2. 尝试启动mycat

    #创建日志
mkdir /usr/local/mycat/logs
# 添加hosts解析
vim /etc/hosts
192.168.233.135 mycat#尝试启动
cd /usr/local/mycat/bin
./mycat console

    [!NOTE]

    1. 注意如果启动不成功，可能是由于没有解析主机名称。需要在hosts文件中进行解析
    2. jvm内存设置：vim //usr/local/mycat/conf/wrapper.conf (大约在35行)

    wrapper.java.additional.9=-Xmx512M
    wrapper.java.additional.10=-Xms256M

    wrapper.startup.timeout=300

    再不行就是版本问题，jdk要是1.8的

  3. 配置读写分离

    [!IMPORTANT]

    server.xml 配置mycat的对外的用户、密码、映射数据库名称等信息
    schema.xml 配置后端真实数据库的用户、密码、真实数据库名称等信息

    1. 配置server.xml

      这个在server.xml里就有的，可以自己修改，这里不做修改

      <user name="root" defaultAccount="true"><property name="password">123456</property><property name="schemas">TESTDB</property><property name="defaultSchema">TESTDB</property>
</user>
#mycat对外提供的账号：
#账号：root
#密码：123456
#数据库名称：TESTDD

    2. schema.xml

      cd /usr/local/mycat/conf

      vim schema.xml

      200dd清除内容

      需要把 的database改成要读写分离的数据库

      需要把ip改成主机和从机的 
      <?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/"><!-- 1、TESTDB和server.xml配置文件中的映射的数据库名称要一致   dataNone填写下面的dataNode名称 --><schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn3"></schema><!-- 2、dataNode name和上面的一致  dataHost填写下面的dataHost的name名称  database填写后端真实数据库名称--><dataNode name="dn3" dataHost="localhost1" database="washer" /> <!-- 3、可以配置负载均衡、读写分离算法   暂时可以不用动--><dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100"><!-- 对后端数据库服务器 发送的心跳检测 --><heartbeat>select user()</heartbeat><!-- can have multi write hosts --><!-- 4、配置后端真实数据库的相关登录信息 --><writeHost host="hostM1" url="192.168.233.133:3306" user="root" password="Aa123456="><!-- can have multi read hosts --><readHost host="hostS2" url="192.168.233.134:3306" user="root" password="Aa123456=" /></writeHost></dataHost>
</mycat:schema>

    3. 重新启动mycat

      cd /usr/local/mycat/bin

      ``./mycat start`

    4. 查看mycat监听的端口

      ss -tnlp | grep java

      8066：mycat的客户端端口

      9066：mycat的服务端端口

  4. master与slave需要添加root账号的远程访问权限

    grant all on *.* to 'root'@'%' identified by "Aa123456=";
flush privileges;

  5. 验证

    找一台客户机安装MySQL的客户端，连接mycat，操作数据库，实际上操作的则是后端真实的数据库服务器。这里以slave为例

    mysql -h 192.168.233.135 -P8066 -uroot -p123456

image-20251022194046394

三、高可用读写分离

四、补充

1. 关系与非关系型数据库

关系型数据库：存储在磁盘中，mysql

非关系型数据库：存储在内存或磁盘中，redis

mysql数据存储位置：

# 查询数据库配置文件存储的路径
find / -name "my.cnf"
# 查看MySQL配置文件，查询数据存储的位置
vim /etc/my.cnf

2. 数据同步延迟

走内网快

3. 数据同步失败

做数据校验

4. 数据库为什么慢

数据存储在磁盘(限制软件工程的最大瓶颈)

5. mysql如何实现主从同步

  1. 在主机上开启binlog日志，配置binlog日志存储的类型
  2. 如果是首次同步，需要提前把主机数据同步到从机上
  3. 在从机上修改配置文件，设置只读
  4. 在从机上配置已同步的主机信息(停止从机、修改主机信息、开启从机)

6.主从同步原理

主要是基于binlog日志。对于master，将对数据的修改保存到binlog日志文件中。slave中的IO线程不断地去读取binlog日志文件中的增量信息。读取之后将读取到的数据发送到中继日志中。SQL线程去读取中继日志中的信息，再进行重放，把数据同步到从机中。
image-20251022194811199

7. mysql客户端和服务端区别

客户端：是操作数据库实例的工具，包括mysql命令行客户端、mysqladmin、mysqldump等，这些工具使得用户能够连接到MySQL服务器，执行SQL查询，管理数据库，备份和恢复数据

​ yum install mysql-community-client

服务端：负责数据的存储和管理，提供数据库实例任务调度线程之类，并提供相关接口供不同客户端调用。

​ yum install mysql-community-server

8. 主从复制的优点

数据冗余和备份

通过主从复制，可以将主服务器上的数据复制到从服务器上，实现数据的冗余和备份。当主服务器发生硬件故障、数据损坏或人为错误时，可以通过从服务器恢复数据，确保数据的安全性和可用性

分摊读取负载

主服务器负责处理写入操作，而从服务器可以用于处理读取操作，从而分摊读取负载。通过在从服务器上执行读取操作，可以提高整个系统的读取性能和吞吐量，减轻主服务器的压力

提供高可用性

通过设置主从复制，可以实现主服务器的冗余，从而提供高可用性。当主服务器发生故障或维护时，可以快速将从服务器提升为新的主服务器，保持系统的连续性和可用性，减少服务中断时间

灾难恢复和故障转移

在主从复制架构下，可以使用从服务器恢复数据，从而实现快速的灾难恢复。当主服务器不可用时，可以切换到从服务器继续提供服务，实现故障转移

数据分析和报表生成

通过从服务器进行数据分析、查询和报表生成等操作，可以减少对主服务器的影响，保持主服务器的高性能。从服务器可以用于处理复杂的查询和数据分析任务，从而提供更好的用户体验

9. 延迟问题

Mysql默认采用的异步操作，因为它的效率明显是最高的。因为只要写入bin log后事务就结束返回成功了。但由于从库从主库异步拷贝日志 以及串行执行 SQL 的特点，所以从库的数据一定会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。这就是主从同步延时问题。

1）影响的因素

  • 主节点如果执行一个很大的事务，那么就会对主从延迟产生较大的影响
  • 网络延迟，日志较大，slave数量过多
  • 主上多线程写入，从节点只有单线程同步
  • 机器性能问题，从节点是否使用了“烂机器”
  • 锁冲突问题也可能导致从机的SQL线程执行慢

2）延迟优化

这个没有说去完全解决，要想解决那么就只能采用同步复制策略。不过，一般不建议使用这种同步模式。显而易见，如果写操作必须等待更新同步完成，肯定会极大地影响性能，除非你不在乎性能

  • 大事务：将大事务分为小事务，分批更新数据
  • 减少Slave的数量，不要超过5个，减少单次事务的大小
  • MySQL 5.7之后，可以使用多线程复制，使用MGR复制架构（提升MySQL版本）
  • 提升硬件

10. binlog格式

binlog 日志有三种格式，分别是 statement，row 和 mixed

如果是 statement 格式，binlog 记录的是 SQL的原文，如果主库和从库选的索引不一致，可能会导致主库不一致。我们来分析一下。假设主库执行删除这个SQL(其中，a 和 create_time 都会有索引)如下：

delete from t where a > '666' and create_time < '2022-03-02' limit 1;
我们知道，数据库选择了 a 索引和选择 create_time 索引，最后 limit 1 出来的数据一般是不一样的。所以就会存在这种情况：在 binlog = statement 格式时，主库在执行这条SQL时，使用的是索引a，而从库在执行这条SQL时，使用了索引 create_time。最后主从数据不一致了。

如何解决这个问题呢？

可以把binlog格式修改为 row。row 格式的 binlog 日志，记录的不是 SQL原文，而是两个 event: Table_map 和 Delete_rows。Table_map event 说明要操作的表，Delete_rows event 用于定义要删除的行为，记录删除的具体行数。row 格式的binlog记录的就是要删除的主键ID信息，因此不会出现主从不一致的问题。

但是如果SQL删除10万行数据，使用row格式就会很占空间的，10万条数据都在 binlog 里面，写 binlog 的时候也很耗IO。但是 statement 格式的binlog可能会导致数据不一致，因此设计MySQL的大叔想了一个折中的方案，mixed 格式的 binlog。所谓的 mixed格式其实就是 row 和 statement 格式混合使用，当 MySQL 判断可能数据不一致时，就用 row 格式，否则使用 statement 格式。

11. 发现主从不同步

  • 如果能够定位到不同步的数据，直接修改即可
  • 不然直接将主机数据导出，直接导入到从库中

出现的原因：

  • 网络问题
  • 大事务
  • mysql宕机

还是定位不到：

  • 加监控

12. 复制方式

binlog日志有 statement,row,mixed

IO线程在读取数据，同步，异步，半异步

13. Mysql一主多从出故障切换以什么标准

数据完整性，position的位置越大的机器数据越完整。

http://www.hskmm.com/?act=detail&tid=37608

相关文章：

  • ollama v0.12.2 版本更新详解：Qwen3 架构协助、Multi-Regex 分词器、新引擎前后缀匹配等功能升级
  • AI股票预测分析报告 - 2025年10月23日 20:26
  • 软件包管理
  • nginx反向代理测试搭建
  • 基础概念
  • .NET Core报错克服【无废话上操作】
  • 题解：P11831 [省选联考 2025] 追忆
  • 2025-10-23 MX-S 模拟赛 赛后总结【MX】
  • PCL1.12 解决memory.h中EIGEN处中断问题
  • 完整教程：状态管理库 Zustand 的接入流程与注意点
  • 20251023
  • Java常用机制 - SPI机制详解
  • 塔吊施工环境与附属设施监测！思通数科 AI 卫士筑牢全场景安全防线
  • 采用opencv来识别信用卡的号码
  • 网络设备
  • 第二十二篇
  • 《程序员修炼之道:从小工到专家》阅读笔记1
  • 负载均衡及三种软件负载
  • 在 GEO / AIO 角度：如何优化 SEO 内容？
  • 多级多卡训练模型时有些参数没有参与loss计算和梯度更新的解决办法
  • 无题
  • Idea提高制作效率的快捷键最佳学习方式
  • rocky10自己手动换源
  • ski 和 db 模块的通信
  • 完整教程：ImmuCellAI 免疫浸润分析
  • 4.6.2版本来了！快来看看新版本有哪些改动
  • 2025-10-22 ZR-J 模拟赛 赛后总结【ZR】
  • P5285 [十二省联考 2019] 骗分过样例
  • Liferay Portal与DXP集合提供程序存在授权缺失漏洞分析
  • MapGIS Objects Java计算一条三维线段与一个三角形所在的平面的交点 - 教程