MySQL主从同步读写分离

[!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;
      

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

5. 测试验证

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

   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

三、高可用读写分离

四、补充

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线程去读取中继日志中的信息，再进行重放，把数据同步到从机中。

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的位置越大的机器数据越完整。