小公司里用SpringBoot做MySQL分库分表，踩了一些坑！

怼烎@ 2023-09-27 09:30 57阅读 0赞

> **1、MySQL主从复制**

我们仔细观察我们会发现，当我们的项目都是用的单体数据库时，那么就可能会存在如下问题：

*  读和写所有压力都由一台数据库承担， **压力大**
 *  数据库服务器磁盘损坏则 **数据丢失** ，单点故障

为了解决上述提到的两个问题，我们可以准备两 (多) 台MySQL，一台主( **Master** )服务器，一台从( **Slave** )服务器，主库的 **数据变更** (写、更新、删除这些操作) ，需要 **同步** 到从库中 (主从复制) 。

而用户在访问我们项目时，如果是 **写操作** (insert、update、delete)，则直接操作 **主库** ；如果是 **读操作** (select) ，则直接操作从库，这种结构就是 **读写分离** 啦。

![6ac5e98237ee4189bc036caea3d1bd12.jpeg][]

在这种读写分离的结构中，从库是可以有多个的

####  ####

1.1、介绍

MySQL主从复制是一个 **异步** 的复制过程，底层是基于Mysql数据库自带的 **二进制日志** 功能。就是一台或多台MySQL数据库（slave，即 **从库** ）从另一台MySQL数据库（master，即 **主库** ）进行日志的复制，然后再解析日志并应用到自身，最终实现 **从库** 的数据和 **主库** 的数据保持一致。

MySQL主从复制是 MySQL数据库自带功能，无需借助第三方工具。

**二进制日志：**

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。默认MySQL是未开启该日志的。

**MySQL复制过程分成三步：**

1.  MySQL master 将数据变更写入二进制日志( **binary log** )
2.  slave将master的binary log拷贝到它的中继日志（ **relay log** ）
3.  slave重做中继日志中的事件，将数据变更反映它自己的数据

![66b617b6eed543eb8460560c7171a315.jpeg][]

1.2、主从库搭建

在环境搭建之前，我们需要准备好两台服务器，如果生活富裕使用的是两台云服务器的时候记得要开放安全组，即防火墙；如果是比狗子我生活好点但也是用的虚拟机的话，记得别分这么多内存启动蓝屏了（别问怎么知道的）

这里就不给大家展示数据库的安装和防火墙的操作了，这个我感觉网上好多资源都能够满足遇到的问题，在搭建主从库的时候有在网上见到过说MySQL版本要一致的，我也没太留意直接就在之前的MySQL上操作了，大家可以自己去验证一下。

**1.2.1、主库配置**

服务器：192.168.150.100（别试了黑不了的，这是虚拟机的ip）

**1、修改Mysql数据库的配置文件** `vim /etc/my.cnf`

在打开的文件中加入下面两行，其中的server-id不一定是100，确保唯一即可
    log-bin=mysql-bin #[必须]启用二进制日志
    server-id=100       #[必须]服务器唯一ID

![b15a2caed6f8490c8173573904884b1a.jpeg][]

**2、重启Mysql服务**

这里有三个方法都能重启MySQL，最简单的无疑就是一关一开：

net stop mysql;net start mysql;
    systemctl restart mysqld
    service mysqld restart

**3、创建数据同步的用户并授权**

登录进去MySQL之后才能够执行下面的命令，因为这是SQL命令，Linux不认识这玩意是啥。

GRANT REPLICATION SLAVE ON *.* to '用户名'@'开放的地址' identified by '密码';
    eg: GRANT REPLICATION SLAVE ON *.* to 'masterDb'@'%' identified by 'Master@123456';
    记得刷一下权限
    FLUSH PRIVILEGES;

**4、查看master同步状态**

这个时候还 **不用退出MySQL** ，因为下面的命令还是SQL命令，执行下面的SQL，可以拿到我们后面需要的两个重要参数。

show master status;

![a8db38d805b5407e96444808fc753599.jpeg][]

执行完这一句SQL之后，==不要再操作主库！不要再操作主库！不要再操作主库！==重要的事情说三遍，因为再操作主库之后可能会导致红框中的 **两个属性值会发生变化** ，后面如果发生了错误可能就和这里有那么两毛钱关系了。

**1.2.2、从库配置**

服务器：192.168.150.101（别试了黑不了的，这也是虚拟机的ip）

**1、 修改Mysql数据库的配置文件** `vim /etc/my.cnf`

![e27f1fa72f7e449e8e8cd2a2f9579b2d.jpeg][]

这里要注意server-id和主库以及其他从库都不能相同，否则后面将会配置不成功。

**2、重启Mysql服务**

这里有三个方法都能重启MySQL，最简单的无疑就是一关一开：

net stop mysql;net start mysql;
    systemctl restart mysqld
    service mysqld restart

**3、设置主库地址及同步位置**

登录进去MySQL之后才能够执行下面的命令，因为这是SQL命令

设置主库地址和同步位置
    change master to master_host='192.168.150.100',master_user='masterDb',master_password='Master@123456',master_log_file='mysql-bin.000010',master_log_pos=68479;
    记得记得开启从库配置
    start slave;

**参数说明：**

*  master\_host: 主库的 **IP地址**
 *  master\_user: 访问主库进行主从复制的 **用户名** ( 上面在主库创建的 )
 *  master\_password: 访问主库进行主从复制的用户名对应的 **密码**
 *  master\_log\_file: 从哪个 **日志文件** 开始同步 ( 即1.2.1中第4步获取的 **File** )
 *  master\_log\_pos: 从指定日志文件的哪个 **位置** 开始同步 ( 即1.2.1中第4步获取的 **Position** )

**4、查看从数据库的状态**

这个时候还 **不用退出MySQL** ，因为下面的命令还是SQL命令，执行下面的SQL，可以看到从库的状态信息。通过状态信息中的 **Slave\_IO\_running** 和 **Slave\_SQL\_running** 可以看出主从同步是否就绪，如果这两个参数全为 **Yes** ，表示主从同步已经配置完成。

show slave status\G;

![55f02c12ebad417a9fd6c6723c3ed2de.jpeg][]

####  ####

1.3、坑位介绍

**1.3.1、UUID报错**

这可能是由于linux 是复制出来的，MySQL中还有一个 **server\_uuid** 是一样的，我们也需要修改。 `vim /var/lib/mysql/auto.cnf`

![3ab4910357c54c89b22b4ca7fe6ea7c7.jpeg][]

**1.3.2、server\_id报错**

这应该就是各位大牛设置server\_id的时候不小心设置相同的id了，修改过来就行，步骤在上面的配置中。

**1.3.3、同步异常解决**

这是狗子在操作过程中搞出来的一个错误……

出错的原因是在主库中删除了用户信息，但是在从库中同步的时候失败导致同步停止，下面记录自己的操作（是在进入MySQL的操作且是从库）。

MASTER_LOG_POS

STOP SLAVE;
    SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
    START SLAVE;
    SHOW SLAVE STATUS\G;

在数据库中操作时，一定要注意当前所在的数据库是哪个，作为一个良好的实践：在SQL语句前加 `USE dbname` 。

**操作不规范，亲人两行泪……**

> **2、项目中实现**

2.1、ShardingJDBC

Sharding-JDBC定位为 **轻量级Java框架** ，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以 **jar包** 形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动， **完全兼容JDBC和各种ORM框架** 。

使用Sharding-JDBC可以在程序中轻松的实现数据库 **读写分离** 。

**Sharding-JDBC具有以下几个特点：**

*  适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
 *  支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
 *  支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。

下面我们将用ShardingJDBC在项目中实现MySQL的读写分离。

####  ####

2.2、依赖导入

在pom.xml文件中导入ShardingJDBC的依赖坐标

<dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.0.0-RC1</version>
    </dependency>

####  ####

2.3、配置文件

在application.yml中增加数据源的配置

spring:
      shardingsphere:
        datasource:
          names:
            master,slave
          # 主数据源
          master:
            type: com.alibaba.druid.pool.DruidDataSource
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://192.168.150.100:3306/db_test?useUnicode=true&characterEncoding=utf-8&useSSL=false
            username: root
            password: 123456
          # 从数据源
          slave:
            type: com.alibaba.druid.pool.DruidDataSource
            driver-class-name: com.mysql.cj.jdbc.Driver
            url: jdbc:mysql://192.168.150.101:3306/db_test?useUnicode=true&characterEncoding=utf-8&useSSL=false
            username: root
            password: 123456
        masterslave:
          # 读写分离配置，设置负载均衡的模式为轮询
          load-balance-algorithm-type: round_robin
          # 最终的数据源名称
          name: dataSource
          # 主库数据源名称
          master-data-source-name: master
          # 从库数据源名称列表，多个逗号分隔
          slave-data-source-names: slave
        props:
          sql:
            show: true #开启SQL显示，默认false
      # 覆盖注册bean，后面创建数据源会覆盖前面创建的数据源
      main:
        allow-bean-definition-overriding: true

2.4、测试跑路

这时我们就可以对我们项目中的配置进行一个测试，下面分别调用一个更新接口和一个查询接口，通过查看日志中记录的数据源来判断是否能够按照我们预料中的跑。

*  更新操作（写操作）

![ebf7dfd6ce8a4cb9b5ae64b8df8b449a.jpeg][]

*  查询操作（读操作）

![d8c9b7061390466c9753ce6d30730c1b.jpeg][]

搞定！！！程序正常按照我们预期的成功跑起来了，成功借助ShardingJDBC在我们项目中实现了数据库的读写分离。

[6ac5e98237ee4189bc036caea3d1bd12.jpeg]: https://img-blog.csdnimg.cn/6ac5e98237ee4189bc036caea3d1bd12.jpeg
[66b617b6eed543eb8460560c7171a315.jpeg]: https://img-blog.csdnimg.cn/66b617b6eed543eb8460560c7171a315.jpeg
[b15a2caed6f8490c8173573904884b1a.jpeg]: https://img-blog.csdnimg.cn/b15a2caed6f8490c8173573904884b1a.jpeg
[a8db38d805b5407e96444808fc753599.jpeg]: https://img-blog.csdnimg.cn/a8db38d805b5407e96444808fc753599.jpeg
[e27f1fa72f7e449e8e8cd2a2f9579b2d.jpeg]: https://img-blog.csdnimg.cn/e27f1fa72f7e449e8e8cd2a2f9579b2d.jpeg
[55f02c12ebad417a9fd6c6723c3ed2de.jpeg]: https://img-blog.csdnimg.cn/55f02c12ebad417a9fd6c6723c3ed2de.jpeg
[3ab4910357c54c89b22b4ca7fe6ea7c7.jpeg]: https://img-blog.csdnimg.cn/3ab4910357c54c89b22b4ca7fe6ea7c7.jpeg
[ebf7dfd6ce8a4cb9b5ae64b8df8b449a.jpeg]: https://img-blog.csdnimg.cn/ebf7dfd6ce8a4cb9b5ae64b8df8b449a.jpeg
[d8c9b7061390466c9753ce6d30730c1b.jpeg]: https://img-blog.csdnimg.cn/d8c9b7061390466c9753ce6d30730c1b.jpeg