提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
前言
作业需求
l 集群环境
-
6台
-
主master1负责写,从slave1 slave2负责读
-
主master2负责写,从slave3 slave4负责读
l 代码
-
主要类&方法&参数&返回值及代码行标注注释
-
基于user_id对c_order表进⾏数据分⽚
-
基于master1和master2主从集群实现读写分离
l 运行效果
-
项目各个类作用介绍,重点代码行介绍,启动
-
演示基于user_id对c_order表进⾏数据分⽚,基于master1和master2主从集群实现读写分离
-
添加数据,通过Navicat展示数据分片成功,通过代码控制台真实sql展示数据分别在master1和master2进行写入
-
查询数据,通过代码控制台真实sql展示数据是通过4个从节点查询的
一、环境机器介绍
1.环境介绍涉及的各个软件的版本
软件 | 版本 |
---|---|
Mysql | mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar |
虚拟机 | VMware-workstation-full-15.1.0-13591040.exe |
OS | CentOS-7-x86_64-DVD-1810.iso |
2.介绍各个机器对应角色&作用&ip地址
机器职责 | 虚拟机 | ip |
---|---|---|
master1 | CenOS 7-01 | 192.168.142.128 |
slave3 | CenOS 7-06 | 192.168.142.129 |
slave4 | CenOS 7-04 | 192.168.142.132 |
master2 | CenOS 7-03 | 192.168.142.130 |
slave5 | CenOS 7-05 | 192.168.142.133 |
slave6 | CenOS 7-02 | 192.168.142.134 |
二、搭建
1、环境准备
首先需要6台服务器,master1,slave3,slave4按照:链接: MySQL集群搭建里目录【三、MySQL集群搭建主从复制】进行主从搭建,master2,slave5,slave6也同理
2、创建sharding-jdbc-work
1)配置文件
application.properties
spring.profiles.active=sharding-database
#打印sql数据
spring.shardingsphere.props.sql.show=true
#一、定义数据源
spring.shardingsphere.datasource.names=master1,master2,slave3,slave4,slave6
#默认连接池
#主:master1,slave3,slave4
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.142.128:3306/lagou?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.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.142.129:3306/lagou?serverTimezone=GMT%2B8&useSSL=false
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.142.132:3306/lagou?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.slave4.username=root
spring.shardingsphere.datasource.slave4.password=root
#主:master2,slave5,slave6
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.142.130:3306/lagou?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.master2.username=root
spring.shardingsphere.datasource.master2.password=root
#spring.shardingsphere.datasource.slave5.type=com.zaxxer.hikari.HikariDataSource
#spring.shardingsphere.datasource.slave5.driver-class-name=com.mysql.jdbc.Driver
#spring.shardingsphere.datasource.slave5.jdbc-url=jdbc:mysql://192.168.142.133:3306/lagou?serverTimezone=GMT%2B8&useSSL=false
#spring.shardingsphere.datasource.slave5.username=root
#spring.shardingsphere.datasource.slave5.password=root
spring.shardingsphere.datasource.slave6.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave6.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave6.jdbc-url=jdbc:mysql://192.168.142.134:3306/lagou?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.slave6.username=root
spring.shardingsphere.datasource.slave6.password=root
#读写分离
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave3,slave4
spring.shardingsphere.sharding.master-slave-rules.master2.master-data-source-name=master2
spring.shardingsphere.sharding.master-slave-rules.master2.slave-data-source-names=slave6
#二、指定分库策略sharding
#行表达式,根据id拆分
#分表 根据commpanyid拆分库,根据id拆分表
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.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}
spring.shardingsphere.sharding.tables.c_order.actual-data-nodes=master${1..2}.c_order${1..2}
spring.shardingsphere.sharding.tables.c_order.key-generator.column=id
spring.shardingsphere.sharding.tables.c_order.key-generator.type=SNOWFLAKE
2、Order类和OrderRepostory
package com.qch.entity;
import javax.persistence.*;
import java.util.Date;
/**
* @Author qch
* @Date 2021/4/18
*/
@Entity
@Table(name = "c_order")
public class Order {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private long id;
@Column(name = "is_del")
private Boolean isDel;
@Column(name = "company_id")
private Integer companyId;
@Column(name = "position_id")
private long positionId;
@Column(name = "user_id")
private Integer userId;
@Column(name = "publish_user_id")
private Integer publishUserId;
@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;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public Boolean getDel() {
return isDel;
}
public void setDel(Boolean del) {
isDel = del;
}
public Integer getCompanyId() {
return companyId;
}
public void setCompanyId(Integer companyId) {
this.companyId = companyId;
}
public long getPositionId() {
return positionId;
}
public void setPositionId(long positionId) {
this.positionId = positionId;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public Integer getPublishUserId() {
return publishUserId;
}
public void setPublishUserId(Integer publishUserId) {
this.publishUserId = publishUserId;
}
public Integer getResumeType() {
return resumeType;
}
public void setResumeType(Integer resumeType) {
this.resumeType = resumeType;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", isDel=" + isDel +
", companyId=" + companyId +
", positionId=" + positionId +
", userId=" + userId +
", publishUserId=" + publishUserId +
", resumeType=" + resumeType +
", status='" + status + '\'' +
", createTime=" + createTime +
", updateTime=" + updateTime +
'}';
}
}
OrderRepository
package com.qch.repository;
import com.qch.entity.Order;
import org.springframework.data.jpa.repository.JpaRepository;
public interface OrderRepository extends JpaRepository<Order,Long> {
}
启动类
@SpringBootApplication
public class RunBoot {
}
测试类
package com.qch;
import com.qch.entity.Order;
import com.qch.repository.OrderRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Repeat;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.Date;
import java.util.List;
import java.util.Random;
/**
* @Author qch
* @Date 2021/4/14
*/
@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunBoot.class)
public class TestShardingBase {
@Autowired
private OrderRepository bOrderRepository;
@Test
@Repeat(20)
public void addSharingOrder()
{
int userId=new Random().nextInt(10);
Order bOrder=new Order();
bOrder.setCompanyId(4444);
bOrder.setDel(false);
bOrder.setPositionId(233344);
bOrder.setUserId(userId);
bOrder.setPublishUserId(111);
bOrder.setResumeType(1);
bOrder.setStatus("AUTO");
bOrder.setCreateTime(new Date());
bOrder.setUpdateTime(new Date());
bOrderRepository.save(bOrder);
}
@Test
public void addAll()
{
List<Order> all = bOrderRepository.findAll();
all.forEach(p->{
System.out.println(p.toString());
});
System.out.println(all.size());
}
}
三、运行结果
总结
提示:这里对文章进行总结:
例如:以上就是今天要讲的内容,本文仅仅简单介绍了pandas的使用,而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。