MySQL的主从复制 && SpringBoot整合Sharding-JDBC解决读写分离

问题

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

MySQL的主从复制解决读写分离

介绍

​ mysql主从复制是一个异步复制的过程，底层是基于mysql数据自带的二进制日志功能。就是一台或者多台从库从另一台mysql数据库就是主库进行日志复制进行日志解析，并将解析出的sql在从库中页执行一遍。mysql主从复制是mysql数据库自带的功能，无需借助第三方工具。主库写，从库读。

MySQL复制过程分为三步

• master将改变记录到二进制日志
• slave将master的binary log拷贝到他的中继日志（relay log）
• slave重做中继日志中的事件，将改变应用到自己的数据库中

配置主从复制(Centos7.6)

前置条件

两台服务器，分别安装mysql5.6

master：192.168.100.10

slave： 192.168.100.101

配置主库Master

[root@localhost src]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin  #启用二进制日志
server-id=10       #服务器唯一ID

[root@localhost src]# systemctl restart mysqld
[root@localhost src]# mysql -uroot -proot
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.51-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


//密码可以随意设置，为xiaoming这个用户授予root权限
mysql> grant replication slave on *.* to 'xiaoming'@'%' identified by 'root@123456';
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      322 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从库Slave

[root@localhost ~]# vi /etc/my.cnf
[mysqld]
server-id=101   #服务器ID必须唯一

[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -proot
mysql> change master to master_host='192.168.100.10',master_user='xiaoming',master_password='root@123456',master_log_file='mysql-bin.000001',master_log_pos=322;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
注意：
master_host主库的ip 
master_password  主库设置的用户的密码
master_log_file  master_log_pos 主库show master status;查询出的值

#开启
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

检查（slave执行）

测试

注意

主库一开始就要和从库数据保持一致，不然删除了主库的数据从库同步日志的时候就会报没有该数据库，就会断开主从连接。

读写分离案例

Sharding-JDBC

​ 轻量级java框架，可以理解为增强版的JDBC驱动，完全兼容JDBC和割绒ORM框架。

前提：两台服务器mysql主从配置成功

实现

第一步导入Maven坐标

        <!--加入Spring sharding JDBC-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

第二步配置yml文件

将

  datasource:
    druid:
      #      driver-class-name: com.mysql.cj.jdbc.Driver
      #      url: jdbc:mysql://localhost:3306/reggie?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true
      #      username: root
      #      password: 796321@zy
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://192.168.100.10:3306/reggie?characterEncoding=utf-8
      username: root
      password: root

替换为sharding jdbc来代理

spring:
  application:
    name: reggie_take_out
  shardingsphere:
    datasource:
      names:
        master,slave
      #主数据源
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.100.10:3306/reggie?characterEncoding=utf-8
        username: root
        password: root
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.100.101:3306/reggie?characterEncoding=utf-8
        username: root
        password: root
    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

第三步测试

SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4791d6fc] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.MasterSlaveConnection@325065f3] will not be managed by Spring
==>  Preparing: SELECT id,type,name,sort,create_time,update_time,create_user,update_user FROM category WHERE id=?
2023-03-21 16:24:33.152  INFO 14928 --- [nio-8080-exec-6] ShardingSphere-SQL                       : Rule Type: master-slave
2023-03-21 16:24:33.152  INFO 14928 --- [nio-8080-exec-6] ShardingSphere-SQL                       : SQL: SELECT id,type,name,sort,create_time,update_time,create_user,update_user FROM category WHERE id=?  ::: DataSources: slave
==> Parameters: 1397844263642378242(Long)