1. 环境软件版本
环境&软件 | 版本 |
---|
虚拟机&VMware Workstation Pro | VMwareworkstation15.5.6 |
服务器&Centos | CentOS-7-x86_64-DVD-1503-01.iso |
数据库&mysql | mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar |
远程连接&Xshell | 5 |
远程文件传输&Xftp | 5 |
2. 环境架构设计
机器名称 | IP | 角色 | 权限 |
---|
mysql-master1 | 192.168.5.128 | mysql主库1 | 读写 |
mysql-slave1 | 192.168.5.129 | mysql从库1 | 读 |
mysql-slave2 | 192.168.5.131 | mysql从库2 | 读 |
mysql-master2 | 192.168.5.133 | mysql主库1 | 读写 |
mysql-slave3 | 192.168.5.134 | mysql从库3 | 读 |
mysql-slave4 | 192.168.5.135 | mysql从库4 | 读 |
三、搭建步骤
- 对6台服务器,每台安装mysql,分成2组搭建mysql主从复制,一主2从
- master1,master2执行sql脚本,创建数据库表c_order1,c_order2,用于分库分表操作。
CREATE TABLE `c_order1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被删\r\n除',
`user_id` int(11) NOT NULL COMMENT '⽤户id',
`company_id` int(11) NOT NULL COMMENT '公司id',
`publish_user_id` int(11) NOT NULL COMMENT 'B端⽤户id',
`position_id` int(11) NOT NULL COMMENT '职位ID',
`resume_type` int(2) NOT NULL DEFAULT '0' COMMENT '简历类型:\r\n0附件 1在线',
`status` varchar(256) NOT NULL COMMENT '投递状态 投递状态\r\nWAIT-待处理 AUTO_FILTER-⾃动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝\r\nARRANGE_INTERVIEW-通知⾯试',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL COMMENT '处理时间',
PRIMARY KEY (`id`),
KEY `index_userId_positionId` (`user_id`,`position_id`),
KEY `idx_userId_operateTime` (`user_id`,`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=533958753645494273 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `c_order2` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`is_del` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否被删\r\n除',
`user_id` int(11) NOT NULL COMMENT '⽤户id',
`company_id` int(11) NOT NULL COMMENT '公司id',
`publish_user_id` int(11) NOT NULL COMMENT 'B端⽤户id',
`position_id` int(11) NOT NULL COMMENT '职位ID',
`resume_type` int(2) NOT NULL DEFAULT '0' COMMENT '简历类型:\r\n0附件 1在线',
`status` varchar(256) NOT NULL COMMENT '投递状态 投递状态\r\nWAIT-待处理 AUTO_FILTER-⾃动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝\r\nARRANGE_INTERVIEW-通知⾯试',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL COMMENT '处理时间',
PRIMARY KEY (`id`),
KEY `index_userId_positionId` (`user_id`,`position_id`),
KEY `idx_userId_operateTime` (`user_id`,`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=533958753645494273 DEFAULT CHARSET=utf8mb4;
- 创建maven项目,引入依赖
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.complie.sourceEncoding>UTF-8</project.complie.sourceEncoding>
<shardingsphere.version>4.1.0</shardingsphere.version>
<springboot.version>2.2.5.RELEASE</springboot.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>${springboot.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<version>${springboot.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>${springboot.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${shardingsphere.version}</version>
</dependency>
</dependencies>
- 主配置信息application.properties
spring.profiles.active=sharding-database
spring.shardingsphere.props.sql.show=true
- sharding-jdbc配置信息
#datasource
spring.shardingsphere.datasource.names=master1,slave1,slave2,master2,slave3,slave4
#master1,slave2为主库
#slave1,slave2,master3,master4为从库
spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://192.168.5.128/lagou
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root
spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.5.129/lagou
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=root
spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://192.168.5.131/lagou
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=root
spring.shardingsphere.datasource.master2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master2.jdbc-url=jdbc:mysql://192.168.5.133/lagou
spring.shardingsphere.datasource.master2.username=root
spring.shardingsphere.datasource.master2.password=root
spring.shardingsphere.datasource.slave3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave3.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave3.jdbc-url=jdbc:mysql://192.168.5.134/lagou
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=root
spring.shardingsphere.datasource.slave4.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave4.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave4.jdbc-url=jdbc:mysql://192.168.5.135/lagou
spring.shardingsphere.datasource.slave4.username=root
spring.shardingsphere.datasource.slave4.password=root
#sharding-database
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.c_order.database-strategy.inline.algorithm-expression=master$->{user_id % 2 + 1}
#读写分离
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave1,slave2
spring.shardingsphere.sharding.master-slave-rules.master2.master-data-source-name=master2
spring.shardingsphere.sharding.master-slave-rules.master2.slave-data-source-names=slave3,slave4
#id生成策略,使用雪花算法生成id
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE
## 实际操作节点,具体操作的库和表
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master${1..2}.c_order${1..2}
### 分表规则:对id取模2,奇偶分表
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.c_order.table-strategy.inline.algorithm-expression=c_order${id % 2 + 1}
- 实体类Order
@Entity
@Table(name = "c_order")
public class Order implements Serializable {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "is_del")
private Boolean isDel;
@Column(name = "user_id")
private Integer userId;
@Column(name = "company_id")
private Integer companyId;
@Column(name = "publish_user_id")
private Integer publishUserId;
@Column(name = "position_id")
private Integer positionId;
@Column(name = "resume_type")
private Integer resumeType;
@Column(name = "status")
private String status;
@Column(name = "create_time")
private Date createTime;
@Column(name = "update_time")
private Date updateTime;
........
节省部分代码,get,set方法
- 操作数据库的jpa接口
import com.lagou.entity.Order;
import org.springframework.data.jpa.repository.JpaRepository;
public interface OrderRepository extends JpaRepository<Order,Long> {
}
- springboot启动类
@SpringBootApplication
public class RunBoot {
}
- Test测试方法
@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class TestShardingDatabase {
@Autowired
private OrderRepository orderRepository;
@Test
public void testAdd(){
for (int i = 0; i < 10; i++) {
Order order = new Order();
order.setDel(true);
order.setUserId(order.hashCode());
order.setCompanyId(i);
order.setPublishUserId(i);
order.setPositionId(i);
order.setResumeType(i);
order.setStatus(String.valueOf(i));
order.setCreateTime(new Date());
order.setUpdateTime(new Date());
orderRepository.save(order);
}
}
@Test
public void testGet(){
List<Order> orderList = orderRepository.findAll();
for (Order order : orderList) {
System.out.println(order);
}
}
}
- 进行插入数据操作,使用user_id进行分库,使用id进行分表
- 对从库进行查询
- 数据库表内容