官网地址:http://shardingsphere.io/document/current/cn/overview/
sharding-jdbc事务:https://blog.csdn.net/yanyan19880509/article/details/78335935
1简介
通过docker搭建四台mysql,两主,每台一从;springboot搭建简单的web项目,并配置sharding-jdbc实现分库分表+独写分离;
sharding-jdbc是在datasource层做的代理,对应用透明,只需在datasource配置好读写分离/分库分表策略即可,原理可参见官网;
2 docker搭建mysql
硬件环境:虚拟机 centos7 4G内存 20G硬盘 mysql5.7.13
1 安装docker : yum install docker 并启动
2 获取mysql镜像: docker pull mysql:5.7.13
3 在虚拟机上配置四台mysql配置文件my.cnf,目录结构如下,主要是配置log-bin和server-id,主从之间server-id不能相同
tip:可以先启动一台mysql容器,然后拷贝容器的配置到本地 docker cp mysql:/etc/mysql/my.cnf /etc/mysql/master-order0/,再在这配置中修改log-bin和server-id
4 启动mysql容器 :
docker run --name master-order0 -d -p 13306:3306 -v /etc/mysql/master-order0/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.13 //主 master0
docker run --name master-order1 -d -p 23306:3306 -v /etc/mysql/master-order1/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.13 //主master1
docker run --name slave-order0 -d -p 13307:3306 -v /etc/mysql/slave-order0/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.13 //从slave0 作为master0的从
docker run --name slave-order1 -d -p 23307:3306 -v /etc/mysql/master-order0/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.13 //从slave1 作为master1的从
说明 --name 指定容器名称,-d后台运行 -v 使用本地配置代替容器自身的配置文件 -e 设置mysql root密码
配置主从:配置前需要关闭虚拟机防火墙 : service firewalld stop
进入 slave-order0: docker exec -it slave-order0 /bin/bash
进入mysql:mysql -uroot -p123456
配置主从: change master to MASTER_HOST='主机ip',MASTER_PORT=13306,MASTER_USER='ROOT',MASTER_PASSWORD='123456',
MASTER_LOG_FILE='主中的log-bin名称',MASTER_LOG_POS=主机log-bin的pos ;
其中MASTER_LOG_FILE和MASTER_LOG_POS需要到主上查看 ,进入主master-order0数据库,show master status G 可以查看到这两个参数
另外的master-order1和slave-order1配置方式相同;
在两主上建立相同的库和表,库 tyyd; 每个库上新建两张表 order0,order1 表结构相同,表id不要设置为自增,由sharding-jdbc生成;
可以到从数据库上查看到对应的库和表也已经生成;
3springboot+sharding-jdbc搭建web项目
idea中新建web项目,引入sharding-jdbc maven依赖
<!-- https://mvnrepository.com/artifact/io.shardingsphere/sharding-jdbc -->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc</artifactId>
<version>3.0.0.M2</version>
</dependency>
1 配置sharding-jdbc 分库分表+独写分离 datasource(也可以采用只独写分离或只分库分表),可以有多种配置方式,我采用的是代码的配置方式:
/** * @author cgl * @date 2018/8/15 11:12 * <p> * 读写分离+分库分表 */ @Configuration public class ShardingJDBCSplitAndMsDataSourceConfig { @Bean DataSource getDataSource() throws SQLException { ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration(); shardingRuleConfig.getTableRuleConfigs().add(getOrderTableRuleConfiguration()); // shardingRuleConfig.getTableRuleConfigs().add(getOrderItemTableRuleConfiguration()); // shardingRuleConfig.getBindingTableGroups().add("t_order, t_order_item");
//分库策略:根据参数userId取摸计算,此处的ds和下面一行中的order都是逻辑库/表名 shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
//分表策略:根据参数id取模计算 shardingRuleConfig.setDefaultTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "order${id % 2}")); shardingRuleConfig.setMasterSlaveRuleConfigs(getMasterSlaveRuleConfigurations()); //实际项目需自定义keyGenerator或使用默认的 shardingRuleConfig.setDefaultKeyGenerator(System::currentTimeMillis ); return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, new HashMap<>(), new Properties()); } TableRuleConfiguration getOrderTableRuleConfiguration() { TableRuleConfiguration result = new TableRuleConfiguration(); result.setLogicTable("order"); result.setActualDataNodes("ds${0..1}.order${0..1}"); result.setKeyGeneratorColumnName("id"); return result; } List<MasterSlaveRuleConfiguration> getMasterSlaveRuleConfigurations() { MasterSlaveRuleConfiguration masterSlaveRuleConfig1 = new MasterSlaveRuleConfiguration("ds0", "ds_master_0", Arrays.asList("ds_master_0_slave_0")); MasterSlaveRuleConfiguration masterSlaveRuleConfig2 = new MasterSlaveRuleConfiguration("ds1", "ds_master_1", Arrays.asList("ds_master_1_slave_0")); return Lists.newArrayList(masterSlaveRuleConfig1, masterSlaveRuleConfig2); } Map<String, DataSource> createDataSourceMap() { final Map<String, DataSource> result = new HashMap<>(); result.put("ds_master_0", createDatasource("com.mysql.jdbc.Driver", "jdbc:mysql://192.168.137.128:13306/tyyd?useUnicode=true&characterEncoding=utf-8&useSSL=false", "root", "123456")); result.put("ds_master_1", createDatasource("com.mysql.jdbc.Driver", "jdbc:mysql://192.168.137.128:23306/tyyd?useUnicode=true&characterEncoding=utf-8&useSSL=false", "root", "123456")); result.put("ds_master_0_slave_0", createDatasource("com.mysql.jdbc.Driver", "jdbc:mysql://192.168.137.128:13307/tyyd?useUnicode=true&characterEncoding=utf-8&useSSL=false", "root", "123456")); result.put("ds_master_1_slave_0", createDatasource("com.mysql.jdbc.Driver", "jdbc:mysql://192.168.137.128:23307/tyyd?useUnicode=true&characterEncoding=utf-8&useSSL=false", "root", "123456")); return result; } private HikariDataSource createDatasource(String driverClassName, String jdbcUrl, String userName, String password) { HikariConfig hikariConfig0 = new HikariConfig(); hikariConfig0.setDriverClassName(driverClassName); hikariConfig0.setJdbcUrl(jdbcUrl); hikariConfig0.setUsername(userName); hikariConfig0.setPassword(password); return new HikariDataSource(hikariConfig0); } }
2 编写order Controller crud操作
package com.sj.web.controller; import com.sj.client.request.order.OrderAddRequest; import com.sj.client.request.order.OrderUpdateRequest; import com.sj.client.response.order.OrderDetail; import com.sj.client.service.order.OrderService; import com.sj.web.dto.BaseResponse; import com.sj.web.dto.order.OrderDTO; import org.apache.commons.lang3.RandomUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; import java.util.Optional; import java.util.stream.Collectors; /** * @author cgl * @date 2018/8/16 15:00 */ @RestController @RequestMapping("/order") public class OrderController { @Autowired private OrderService orderService; @RequestMapping("/get/{id}/{userId}") public OrderDTO getOrderInfo(@PathVariable("id") long id,@PathVariable("userId") long userId){ Optional<OrderDetail> orderDetailOptional = orderService.getOrderInfoByOrderId(id,userId); if(orderDetailOptional.isPresent()){ OrderDetail detail = orderDetailOptional.get(); return convert(detail); } return new OrderDTO(); } private OrderDTO convert(OrderDetail detail) { OrderDTO dto=new OrderDTO(); dto.setId(detail.getId()); dto.setPrice(detail.getPrice()); dto.setProdId(detail.getProdId()); dto.setUserId(detail.getUserId()); return dto; } @RequestMapping("/delete/{id}/{userId}") public BaseResponse delete(@PathVariable("id") long id,@PathVariable("userId") long userId){ orderService.deleteOrder(id,userId); return new BaseResponse(); } @RequestMapping("/update") public BaseResponse update(@RequestBody OrderDTO order){ OrderUpdateRequest updateOrder=new OrderUpdateRequest(); updateOrder.setId(order.getId()); updateOrder.setPrice(order.getPrice()); updateOrder.setProdId(order.getProdId()); updateOrder.setUserId(order.getUserId()); orderService.updateOrder(updateOrder); return new BaseResponse(); } @RequestMapping("/add") public BaseResponse add(@RequestBody OrderDTO order){ OrderAddRequest request=new OrderAddRequest(); request.setPrice(order.getPrice()); request.setProdId(order.getProdId()); request.setUserId(RandomUtils.nextInt(10,20)); orderService.insertOrder(request); BaseResponse baseResponse = new BaseResponse(); return baseResponse; } @RequestMapping("/getlist/{userId}") public BaseResponse orderList(@PathVariable long userId){ List<OrderDetail>orders= orderService.getOrderListByUserId(userId); List<OrderDTO> orderDTOS = orders.stream().map(this::convert).collect(Collectors.toList()); BaseResponse response=new BaseResponse(); response.setSuccess(true); response.setCode(200); response.setData(orderDTOS); return response; } }
其他service层代码就是简单调用,mapper代码
@Mapper public interface OrderMapper { @Select("select id,prod_id prodId,user_id userId,price from order where id =#{id} and user_id=#{userId}") Order selectByPrimaryKey(@Param("id") long id,@Param("userId") long userId);
//此处不应出现id,否则sharding-jdbc不会自动生成id @Insert("insert into order (prod_id,user_id,price) value(#{prodId},#{userId},#{price} )") void insertOrder(Order order); @Delete("delete from order where id=#{id} and user_id=#{userId}") void deleteById(@Param("id") long id,@Param("userId")long userId); @Update("update order set prod_id=#{prodId},user_id=#{userId},price=#{price} where id=#{id}") void updateOrder(Order order); @Select("select id,prod_id prodId,user_id userId,price from order where user_id=#{userId}") List<Order> selectListByUserId(long userId); }
4测试
新增/获取/获取列表 等都正常。测试结果略
以上是这两天学习sharding-jdbc的简单入门demo,前路漫漫~~