Sharding-Sphere:Sharding-JDBC读写分离,Docker搭建MySQL主从

读写分离可以提高系统吞吐量,在网上看了很多SpringBoot实现读写分离的文章,大部分是基于Spring AOP 硬编码Java代码去实现的,代码侵入性较大,原理是:如果调用select、get开头的方法,就调用从库,否则调用主库,本篇将基于当当开源的sharding-jdbc实现读写分离,看名字就知道它是在jdbc上,代码零入侵,配置文件配置主从库地址。

本篇博客将分成两大块:1.Docker环境搭建MySQL读写分离,2.SpringBoot基于Sharding-JDBC应用读写分离

Docker环境搭建Mysql读写分离

搭建Docker环境下MySQL的读写分离时,遇到一个坑,最新的MySQL镜像是8.0版本的,无法成功搭建,后面用的5.7.26就好了

镜像地址:https://hub.docker.com/_/mysql

1
docker pull mysql:5.7.26

下面简述Docker配置MySQL主从过程

(1)在本机上创建2个.cnf的配置文件,2个数据存放目录,用来保存MySQL数据

1
2
3
4
master_data/
master.cnf
slave_data/
slave.cnf

(2)mysql.cnf配置文件内容如下,只有server-id不能一样

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links=0
character-set-server = utf8
#skip-networking
innodb_print_all_deadlocks = 1
max_connections = 2000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
binlog_cache_size = 1M
max_heap_table_size = 8M
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4

#从库需要修改端口后再用docker启动
port=3306
#从库的server-id不能和主库一致
server-id = 1
log-bin = master-bin.log
sync_binlog=1
#binlog_format = mixed
binlog_format = row

performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
interactive_timeout = 2880000
wait_timeout = 2880000
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet = 64M
[myisamchk]
tmp_table_size = 16M
join_buffer_size = 128M
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
#MySQL读入缓冲区的大小
read_buffer_size = 16M
#MySQL的随机读缓冲区大小
read_rnd_buffer_size = 8M

#线程缓存大小
thread_cache_size=16
thread_concurrency=32

(3)Docker运行2个mysql镜像,使用下面的命令,改一下主从库的配置文件地址与数据存放目录,并设置root的登陆密码123456,从库修改配置文件中的port=3307,对应docker启动命令-p 3307:3307

1
docker run -itd --name mysql_master -v /data/www/database/mysql/master.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf -v /data/www/database/mysql/master_data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 mysql:5.7.26

(4)找到主库容器ID,进入主库容器控制台docker exec -it container id /bin/bash

(5)进入mysql命令行,mysql -uroot -p123456,输入如下命令:

1
2
3
4
5
6
创建一个用户,让从库通过该用户进行复制日志
创建一个用户:create user 'yezhou'@'%' identified by '123456';
用户赋予权限:grant all privileges on *.* to 'yezhou'@'%' with grant option;
赋予复制权限:grant replication slave on *.* to 'yezhou'@'%' with grant option;
使刚刚的命令生效:flush privileges;
查看主库日志文件:show master status; //记住File与Position的值,从库要用

(6)进入从库容器mysql命令行,和上面一样的命令,连接主库

1
2
3
4
连接主库:change master to master_host='192.168.0.115',master_user='root',master_password='123456',master_port=3306,master_log_file='master-bin.000004',master_log_pos=1118;  //主库记录的Position值
开启从库:start slave;
查看从库状态:show slave status\G
如果连接配置错误要重新更改,可以使用 stop slave; reset slave;

看到如下两个 Yes,则说明主从库搭建成功

1
2
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

(7)使用 mysql 客户端连接主库,创建一个数据库和表,再连接从库查看该数据库是否成功同步过来

SpringBoot基于Sharding-JDBC应用读写分离

Sharding-JDBC是当当开源的分库分表,读写分离的利器,基于JDBC,已经进入apache孵化器项目
GitHub的地址:https://github.com/apache/incubator-shardingsphere
shardingsphere:https://github.com/apache/incubator-shardingsphere-example
中文文档地址:https://shardingsphere.apache.org/document/current/cn/overview/

先看看Sharding-JDBC的架构图,可以有个直观的了解

Sharding-JDBC架构图

(1)使用HikariCP与mybatis进行读写分离的验证,创建了3个表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- ----------------------------
-- Table structure for t_member
-- ----------------------------
DROP TABLE IF EXISTS `t_member`;
CREATE TABLE `t_member` (
`member_id` bigint(20) NOT NULL,
`member_name` varchar(36) DEFAULT NULL,
`nick_name` varchar(36) DEFAULT NULL,
`account_no` varchar(20) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`birth_date` datetime DEFAULT NULL,
`ebl_flag` varchar(1) DEFAULT '1',
`del_flag` varchar(1) DEFAULT '0',
`description` varchar(255) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会员表';

-- ----------------------------
-- Table structure for t_order
-- ----------------------------
DROP TABLE IF EXISTS `t_order`;
CREATE TABLE `t_order` (
`order_id` bigint(20) NOT NULL,
`member_id` varchar(36) DEFAULT NULL,
`order_code` varchar(36) DEFAULT NULL,
`order_amount` varchar(20) DEFAULT NULL,
`status` varchar(1) DEFAULT '1',
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

-- ----------------------------
-- Table structure for t_order_item
-- ----------------------------
DROP TABLE IF EXISTS `t_order_item`;
CREATE TABLE `t_order_item` (
`item_id` bigint(20) NOT NULL,
`order_id` varchar(36) DEFAULT NULL,
`product_name` varchar(128) DEFAULT NULL,
`item_account` double DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单详情表';

(2)新建SpringBoot项目,添加引用:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
</dependency>

(3)配置文件,请不要用ds_masterds_slave等,有下划线会报错

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
server.port=8070

# jdbc_config datasource
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#spring.datasource.url=jdbc:mysql://192.168.0.115:3306/appblog_business1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
#spring.datasource.username=root
#spring.datasource.password=123456

# Hikari will use the above plus the following to setup connection pooling
#spring.datasource.type=com.zaxxer.hikari.HikariDataSource
#spring.datasource.hikari.minimum-idle=10
#spring.datasource.hikari.maximum-pool-size=25
#spring.datasource.hikari.auto-commit=true
#spring.datasource.hikari.idle-timeout=30000
#spring.datasource.hikari.pool-name=HikariCP
#spring.datasource.hikari.max-lifetime=1800000
#spring.datasource.hikari.connection-timeout=30000
#spring.datasource.hikari.connection-test-query=SELECT 1


spring.shardingsphere.datasource.names=master,slave0

# 主库
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://192.168.0.115:3306/appblog_business1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

# 从库
spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://192.168.0.115:3307/appblog_business1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&allowMultiQueries=true&useSSL=false
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=123456

spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0

spring.shardingsphere.props.sql.show=true

# mybatis 配置
mybatis.mapper-locations=classpath:mapping/*.xml
mybatis.type-aliases-package=cn.appblog.sharding.business.entity

(4)MyBatis配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<!-- 数据库驱动:选择你的本地硬盘上面的数据库驱动包-->
<classPathEntry location="D:\software\database\mybatis-generator-core-1.3.2\lib\mysql-connector-java-5.1.25-bin.jar"/>
<context id="DB2Tables" targetRuntime="MyBatis3" defaultModelType="flat">
<commentGenerator>
<property name="suppressDate" value="true"/>
<!-- 是否去除自动生成的注释 true:是 : false:否 -->
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!--数据库链接URL,用户名、密码 -->
<jdbcConnection driverClass="com.mysql.jdbc.Driver" userId="xnxx" password="Xnxx85781245!" connectionURL="jdbc:mysql://47.107.177.150:3306/zy_sharding_business1?serverTimezone=UTC&amp;useUnicode=true&amp;characterEncoding=utf8&amp;maxReconnects=15000&amp;allowMultiQueries=true">
</jdbcConnection>
<javaTypeResolver>
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!-- 生成模型的包名和位置-->
<javaModelGenerator targetPackage="com.zypcy.sharding.business.entity" targetProject="src/main/java">
<property name="enableSubPackages" value="true"/>
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!-- 生成映射文件的包名和位置-->
<sqlMapGenerator targetPackage="mapping" targetProject="src/main/resources">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!-- 生成DAO的包名和位置-->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.zypcy.sharding.business.repository" targetProject="src/main/java">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<!-- 要生成的表 tableName是数据库中的表名或视图名 domainObjectName是实体类名-->
<!-- 注意:已生成的不要再生成,不然会覆盖已生成的文件 -->
<!--
<table tableName="t_member" domainObjectName="Member" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
<table tableName="t_order" domainObjectName="Order" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
<table tableName="t_order_item" domainObjectName="OrderItem" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
-->

</context>
</generatorConfiguration>

(5)添加控制器、服务、mapper接口等

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@Service
public class MemberServiceImpl implements IMemberService {

@Autowired private MemberMapper memberMapper;

@Override
public int insert(Member record) {
return memberMapper.insert(record);
}

@Override
public Member selectByPrimaryKey(String memberId) {
return memberMapper.selectByPrimaryKey(memberId);
}

@Override
public int deleteByPrimaryKey(String memberId) {
return memberMapper.deleteByPrimaryKey(memberId);
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
@RequestMapping("/member")
@RestController
public class MemberController {

@Autowired private IMemberService memberService;

@RequestMapping("/add")
public String add() {
addMember();
return "success";
}

@RequestMapping("/getMemberById")
public Member getMemberById(String memberId) {
return memberService.selectByPrimaryKey(memberId);
}

@RequestMapping("/delete")
public String delete(String memberId) {
memberService.deleteByPrimaryKey(memberId);
return "success";
}

private void addMember() {
Member member = new Member();
member.setMemberId("201904280001");
member.setMemberName("张三");
member.setNickName("闪耀的瞬间");
member.setAccountNo("zhangsan");
member.setPassword("123465");
member.setAge(27);
member.setBirthDate(new Date());
member.setEblFlag("1");
member.setDelFlag("0");
member.setDescription("屌丝一个");
member.setCreateTime(new Date());
member.setUpdateTime(new Date());
memberService.insert(member);
}
}

(6)运行项目,访问localhost:8070/member/add接口,添加一条数据,查看数据库,可以看到主从库都已经添加了数据

(7)修改从库的数据,把member_name字段值手动改为Joe.Ye,然后访问localhost:8070/member/getMemberById?memberId=201908270001,可以看到查询接口返回的是从库修改后的值

到此,读写分离的搭建与应用已经完成

Powered by AppBlog.CN     浙ICP备14037229号

Copyright © 2012 - 2021 APP开发技术博客 All Rights Reserved.

访客数 : | 访问量 :