Spring Boot+ShardingSphere4.0实现读写分离

实现读写分离，首先需要配置数据库的主从同步。本文以MySQL5.7作为数据库，Spring Boot采用2.1.5版本，ShardingSphere版本为4.0.0-RC1。

一、MySQL主从同步配置

1、配置主数据库

（1）编辑配置文件（其中部分已存在配置，则不用重复添加）：

vi /etc/my.cnf

[mysqld]
master-id=1   #配置id，需要唯一
log-bin=mysql-bin   #指定日志路径
binlog-do-db=lovestudy  #指定需要同步（记录日志）的数据库
#binlog-ignore-db=hatestudy  #指定忽略同步（记录日志）的数据库
binlog_format=mixed  #指定同步方式为mixed（默认）

（2）进入MySQL，查看日志名称及定位

[root@idev ~]# mysql -u root -p ‘password’
mysql->show master status;

记录下mysql-bin.000018 position:15210，此为从库开始同步的起始点。
（3）若主库已有数据，则临时锁库并备份

mysql->flush tables with read lock;     //锁定库
mysql->exit;

[root@idev ~]# mysqldump -h 127.0.0.1 -p 3306 -uroot -p’password’ --databases idev >     /data/backup/idev.sql                //备份数据
[root@idev ~]# scp /data/backup/idev.sql [email protected]:/data/  //将备份传到从库服务器上
[root@idev ~]# mysql -u root -p password;

mysql->unlock tables;         //解锁库

（4）授权从数据库服务器

mysql>GRANT REPLICATION SLAVE ON *.* to 'slave'@'slave.idev.top' identified by 'slavePassword';
mysql>FLUSH PRIVILEGES;

2、配置从数据库

（1）进入mysql，配置主库信息

mysql> CHANGE MASTER TO MASTER_HOST='idev.top',
　　MASTER_PORT=3306,
　　MASTER_USER='slave',
　　MASTER_PASSWORD='slavePasword',
　　MASTER_LOG_FILE='mysql-bin.000018',
　　MASTER_LOG_POS=15210; #后面两个参数的值与主库保持一致

（2）导入主库备份的数据

[[email protected] ~]# mysql -uroot -ppassword idev<idev.sql

（3）启动slave进程

mysql-> start slave; 或者 slave start
mysql-> show slave status\G;

二、Shardingsphere实现读写分离

1、新建Spring Boot项目

pom文件如下：

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.12.2</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-configuration-processor</artifactId>
        <version>2.1.2.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.0.1</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>io.shardingsphere</groupId>
        <artifactId>sharding-jdbc-core</artifactId>
        <version>3.1.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        <version>4.0.0-RC1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>sharding-jdbc-spring-namespace</artifactId>
        <version>4.0.0-RC1</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-dbcp2</artifactId>
        <version>2.6.0</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.1.7.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.16</version>
    </dependency>
</dependencies>

2、配置shardingsphere数据源

在application.properties中加入以下配置：

spring.shardingsphere.datasource.names=master,slave
spring.shardingsphere.datasource.master.type=org.apache.commons.dbcp2.BasicDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://idev.top:3306/idev
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=idev.top

spring.shardingsphere.datasource.slave0.type=org.apache.commons.dbcp2.BasicDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://slave.idev.top:3306/idev
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=idev.top

spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave

#是否开启SQL显示，默认值: false
spring.shardingsphere.props.sql.show=true

3、书写其他内容
4、测试

程序启动后，访问数据库时，控制台就会输入shardingsphere的info信息，如上图所示。