问题
读和写所有压力都由一台数据库承担，压力大，数据库服务器磁盘损坏则数据丢失，容易造成单点故障。
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)
