Spring Boot + Sharding-JDBC 读写分离
本文使用 Sharding-JDBC 实现读写分离，基于 CentOS 7 + MySQL 5.7
一、MySQL 安装及配置
1.1 安装
依次执行命令：
sudo wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
sudo yum -y install mysql57-community-release-el7-10.noarch.rpm
sudo yum -y install mysql-community-server
sudo yum -y remove mysql57-community-release-el7-10.noarch
启动：
sudo systemctl start mysqld
1.2 修改密码
查看默认密码：
grep "password" /var/log/mysqld.log
进入数据库：
mysql -uroot -p
修改密码：
alter user 'root'@'localhost' identified by 'NEW PASSWORD';
远程访问：
use mysql;
grant all privileges on *.* TO 'root'@'%' identified by 'PASSWORD';
flush privileges;
1.3 主从配置
本文一主 (192.168.30.101) 两从 (192.168.30.102, 192.168.30.103)
1.3.1 主库
sudo vim /etc/my.cnf
# server-id 给数据库服务的唯一标识
server-id=101
# log-bin 设置此参数表示启用 binlog 功能，并指定路径名称
log-bin=/var/lib/mysql/mysql-bin
sync_binlog=0
# 设置日志过期天数
# binlog-ignore-db 表示同步时忽略的数据库
# binlog-do-db 表示需要同步的数据库
expire_logs_days=7
binlog-do-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
重启数据库，执行 SQL:
grant replication slave on *.* to 'root'@'192.168.30.102' identified by 'YOUR PASSWORD';
flush privileges;
grant replication slave on *.* to 'root'@'192.168.30.103' identified by 'YOUR PASSWORD';
flush privileges;
重启数据库，执行 SQL:
show master status;
记录下 File 和 Position
1.3.2 从库配置
以 192.168.30.102 为例：
log-bin=mysql-bin
server-id=102
binlog-ignore-db=information_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
重启数据库，执行 SQL:
stop slave;
change master to master_host='192.168.30.101',master_user='root',master_password='YOUR PASSWORD',master_log_file='mysql-bin.000002', master_log_pos=154;
start slave;
其中 master_log_file 和 master_log_pos 分别为上步记录主库的 File 和 Position
二、使用
2.1 pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.18</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
2.2. application.yml
spring:
main:
allow-bean-definition-overriding: true
mybatis:
configuration:
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper-locations: classpath:mapping/*.xml
sharding:
jdbc:
datasource:
names: db-master-1,db-slave-1,db-slave-2
db-master-1:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.30.101:3306/test
username: root
password: root
maxPoolSize: 20
db-slave-1:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.30.102:3306/test
username: root
password: root
maxPoolSize: 20
db-slave-2:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.30.103:3306/test
username: root
password: root
maxPoolSize: 20
config:
masterslave:
load-balance-algorithm-type: round_robin # random 随机, round_robin 轮询
name: db1s2
master-data-source-name: db-master-1
slave-data-source-names: db-slave-1,db-slave-2
props:
sql:
show: true
完整代码：GitHub
参考：
