Sharding-JDBC分库分表从入门到熟练(6)

9 读写分离

9.1 读写分离的概念

​ 面对日益增加的系统访问量，数据库的吞吐量面临巨大瓶颈，对于同一时刻有大量并发读操作和较少写操作类型的系统来说，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能避免由数据更新导致的行锁，使得整个系统的查询性能得到极大提升。

​ 一主多从方式：可以将查询请求均匀的分配到多个数据副本中，进一步提升系统的处理能力。

​ 多主多从方式：能提升系统的吞吐量，提升系统可用性，达到任何一个数据库宕机，甚至磁盘损毁的情况下系统仍正常运行的效果。

​ Sharding-JDBC读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库，它提供透明化读写分离，让使用方尽量像使用一个数据库一样使用主从数据库集群。

9.2 MySQL主从复制(Windows环境)

1、新增MySQL实例

复制原有MySQL实例并修改my.ini文件

[mysqld]
#设置端口
port = 3307
#设置mysql的安装目录
basedir=D:\mysql2
#设置数据库的数据存放目录
datadir=D:\mysql2\data

让后我们将新的实例安装成windows服务

D:\mysql2\bin>mysqld install mysql2 --defaults-file="D:\mysql2\my.ini"

由于从库是从主库复制过来的，因此里面的数据完全一致，可使用原来的账号、密码登录。

2、修改主从数据库的配置文件my.ini

主库：

[mysqld]
#开启日志
log-bin = mysql-bin
#设置服务ID
server-id = 1
#设置需要同步的数据库
binlog-do-db=user_db
#屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

从库：

[mysqld]
#开启日志
log-bin = mysql-bin
#设置服务ID
server-id = 2
#设置需要同步的数据库表
replicate_wild_do_table=user_db.%
#屏蔽系统库同步
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%

重启主库和从库：

net start [数据库服务名]

注意：主从MySQL下的数据目录下有个文件auto.cnf,文件中定义了UUID，要保证主从数据库实例的UUID不一样，建议直接删除，重启服务将会重新生成。

3、授权主从复制专用账号

#切换至主库bin目录，登录主库
mysql -h localhost -uroot -p;
#授权主备复制专用账号
GRANT REPLICATION SLAVE ON *.* TO 'user_db_sync'@'%' IDENTIFIED BY '123456';
#刷新权限
FLUSH PRIVILEGES;
#确认位点，记录下文件名及位点
show master status;
#记录下查询到的File\Position

4、设置从库向主库同步数据，并检查链路

#切换至从库bin目录，登录从库
mysql -h localhost -P3307 -uroot -p
#先停止同步
STOP SLAVE
#修改从库指向到主库，使用上一步查询到的file\position
CHANGE MASTER TO 
	master_host = 'localhost',
	master_user = 'user_db_sync',
	master_password = '123456',
	master_log_file = 'mysql-bin.000002',
	master_log_pos = 154;
#启动同步
START SLAVE;
#查看从库状态，Slave_IO_Runing和Slave_SQL_Runing都为yes说明同步成功
#否则请检查error_log
show slave status \G

9.3 Sharding-JDBC实现读写分离

规则配置

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      #新增从库userDbS
      names: order1,order2,userDb,userDbS
      order1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:33068/order_db_1
        username: root
        password: 123456
      order2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:33068/order_db_2
        username: root
        password: 123456
      #主数据库
      userDb:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:33068/user_db
        username: root
        password: 123456
      #从数据库
      userDbS:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://localhost:33078/user_db
        username: root
        password: 123456
    sharding:
      #配置主从
      master-slave-rules:
        userDBMS:
          masterDataSourceName: userDB
          slaveDataSourceNames: userDBS
      broadcast-tables: t_dict
      tables:
        t_order:
          databaseStrategy:
            inline:
              shardingColumn: user_id
              algorithmExpression: order$->{user_id % 2 + 1}
          actualDataNodes: order$->{1..2}.t_order_$->{1..2} #配置节点
          keyGenerator: #主键策略
            column: order_id
            type: SNOWFLAKE
          tableStrategy:
            inline:
              shardingColumn: order_id
              algorithmExpression: t_order_$->{order_id % 2 + 1}
         #修改节点，由原来的userDB改为主从节点的userDBMS
         t_user:
          actualDataNodes: userDBMS.t_user #配置节点
          tableStrategy:
            inline:
              shardingColumn: user_id
              algorithmExpression: t_user