docker搭建mysql主从
2.链接liunx主从数据库- 链接3306端口数据库
- 链接3309端口数据库
- 水平分库分表（classes班级公共表,student学生分表分库）
- classes班级公共表(student_db_1,student_db_2数据同步)
- student学生表分库(按班级id分库，偶数student_db_1库，奇数student_db_2库)
- student学生表分表(按学生id分表，偶数student1表，奇数student2表)
- sql
DROp TABLE IF EXISTS `classes`; CREATE TABLE `classes` ( `c_id` int(11) NOT NULL COMMENT '班级id', `cname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班级', PRIMARY KEY (`c_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT; -- ---------------------------- -- Records of classes -- ---------------------------- -- ---------------------------- -- Table structure for student1 -- ---------------------------- DROP TABLE IF EXISTS `student1`; CREATE TABLE `student1` ( `s_id` bigint(50) NOT NULL COMMENT '学生id', `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名', `classes_id` int(11) NULL DEFAULT NULL COMMENT '班级id', PRIMARY KEY (`s_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT; -- ---------------------------- -- Records of student1 -- ---------------------------- -- ---------------------------- -- Table structure for student2 -- ---------------------------- DROP TABLE IF EXISTS `student2`; CREATE TABLE `student2` ( `s_id` bigint(50) NOT NULL COMMENT '学生id', `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名', `classes_id` int(11) NULL DEFAULT NULL COMMENT '班级id', PRIMARY KEY (`s_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = COMPACT;
- application.properties
server.port=56082
spring.application.name = shopping
spring.profiles.active = local
server.servlet.context-path = /shopping
spring.main.allow-bean-definition-overriding = true
mybatis.configuration.map-underscore-to-camel-case = true
# 真实数据源定义 m为主库 s为从库
spring.shardingsphere.datasource.names = m1,m2,s1,s2
spring.shardingsphere.datasource.m1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m1.url = jdbc:mysql://192.168.201.101:3306/student_db_1?useUnicode=true&characterEncoding=utf-8
spring.shardingsphere.datasource.m1.username = root
spring.shardingsphere.datasource.m1.password = 123456
spring.shardingsphere.datasource.m2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m2.url = jdbc:mysql://192.168.201.101:3306/student_db_2?useUnicode=true&characterEncoding=utf-8
spring.shardingsphere.datasource.m2.username = root
spring.shardingsphere.datasource.m2.password = 123456
spring.shardingsphere.datasource.s1.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s1.url = jdbc:mysql://192.168.201.101:3309/student_db_1?useUnicode=true&characterEncoding=utf-8
spring.shardingsphere.datasource.s1.username = root
spring.shardingsphere.datasource.s1.password = root
spring.shardingsphere.datasource.s2.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s2.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s2.url = jdbc:mysql://192.168.201.101:3309/student_db_2?useUnicode=true&characterEncoding=utf-8
spring.shardingsphere.datasource.s2.username = root
spring.shardingsphere.datasource.s2.password = root
# 主库从库逻辑数据源定义 ds1为student_db_1 ds2为student_db_2
spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=s1
spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=m2
spring.shardingsphere.sharding.master-slave-rules.ds2.slave-data-source-names=s2
# 默认分库策略，以classes_id为分片键，分片策略为classes_id % 2 + 1，也就是classes_id为双数的数据进入ds1，为单数的进入ds2
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = classes_id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = ds$->{classes_id % 2 + 1}
# student分表策略，分布在ds1,ds2的student1 student2表，
# 分片策略为s_id% 2 + 1，s_id生成为雪花算法，为双数的数据进入student1表，为单数的进入student2表
spring.shardingsphere.sharding.tables.student.actual-data-nodes = ds$->{1..2}.student$->{1..2}
spring.shardingsphere.sharding.tables.student.table-strategy.inline.sharding-column =s_id
spring.shardingsphere.sharding.tables.student.table-strategy.inline.algorithm-expression =student$->{s_id % 2 + 1}
spring.shardingsphere.sharding.tables.student.key-generator.column=s_id
spring.shardingsphere.sharding.tables.student.key-generator.type=SNOWFLAKE
# 设置classes为广播表(公共表)，每次更新操作会发送至所有数据源
spring.shardingsphere.sharding.broadcast-tables=classes
# 打开sql输出日志
spring.shardingsphere.props.sql.show = true
swagger.enable = true
logging.level.root = info
logging.level.org.springframework.web = info
logging.level.com.itheima.dbsharding = debug
5.pom.xml
org.springframework.boot spring-boot-starter-parent 2.5.5
6.实体类io.springfox springfox-swagger2 2.9.2 io.springfox springfox-swagger-ui 2.9.2 org.projectlombok lombok 1.18.0 javax.interceptor javax.interceptor-api 1.2 mysql mysql-connector-java 5.1.47 org.mybatis.spring.boot mybatis-spring-boot-starter 2.0.0 com.alibaba druid-spring-boot-starter 1.1.16 org.apache.shardingsphere sharding-jdbc-spring-boot-starter 4.0.0-RC1 com.baomidou mybatis-plus-boot-starter 3.1.0 com.baomidou mybatis-plus-generator 3.1.0 org.mybatis mybatis-typehandlers-jsr310 1.0.2 org.springframework.boot spring-boot-starter-test junit junit 4.13.2 test
import lombok.Data;
@Data
public class Classes {
private Integer cId;
private String cname;
}
@Data
public class Student {
private Long sId;
private String name;
private Integer classesId;
}
7.dao层
@Mapper
@Component
public interface ClassesDao {
//添加班级基本信息
@Insert("insert into classes(c_id,cname) value(#{cId},#{cname})")
@Options(useGeneratedKeys = true,keyProperty = "c_id",keyColumn = "id")
int insertClasses(Classes classes);
//删除班级
@Delete("DELETE FROM classes WHERe c_id=#{cid};")
int deleteClasses(@Param("cid") Integer cid);
//获取所有班级id
@Select("select DISTINCT c_id from classes ")
List findByClassIds();
//通过班级id查询班级名称
@Select("select * from classes where cname=#{cname};")
int findClassesId(@Param("cname") String cname);
}
@Mapper
@Component
public interface StudentDao {
//添加学生基本信息
@Insert("insert into student(name,classes_id) value(#{name},#{classesId})")
int insertStudent(Student student);
//通过班级id查询班级所有学生
@Select("select * from student where classes_id=#{cid}")
public List findStudentByClassesId(@Param("cid") Integer cid);
//根据id更新学生
@Update("update student set name=#{name},classes_id=#{classesId} where s_id = #{sId}")
int updateStudent(Student student);
//根据学生id找学生
@Select("select * from student where s_id = #{id}")
Student findStudentById(Long id);
//删除学生
@Delete("delete from student where s_id = #{sId}")
int deletesStudent(Long sId);
}
8.Test测试类
@RunWith(SpringRunner.class)
@SpringBootTest(classes = ProductShardingApplication.class)
public class ShardingTest {
@Resource
ClassesDao classesDao;
@Resource
StudentDao studentDao;
//添加班级基本信息
@Test
public void addClasses(){
for (int i = 1; i <=10 ; i++) {
Classes classes = new Classes();
classes.setCId(i);
classes.setCname("java"+i+"班");
classesDao.insertClasses(classes);
System.out.println(classes);
}
}
//删除班级
@Test
public void deleteClasses(){
classesDao.deleteClasses(2);
}
//添加学生基本信息
@Test
public void addStudent(){
Random random = new Random();
List classIds = classesDao.findByClassIds();
for (int i = 1; i <=10 ; i++) {
Student student=new Student();
student.setName("张三"+i);
student.setClassesId(classIds.get(random.nextInt(classIds.size())));
studentDao.insertStudent(student);
}
}
//根据班级名查询学生
@Test
public void findStudentByClassesName(){
//要查询的班级id
int classesId = classesDao.findClassesId("java9班");
studentDao.findStudentByClassesId(classesId).forEach(System.out::println);
}
//修改学生信息
@Test
public void updateStudent(){
Student student = studentDao.findStudentById(654973121329102849L);
student.setName("张三丰");
int i = studentDao.updateStudent(student);
System.out.println(i==1);
}
//批量删除学生信息
@Test
public void deleteStudent(){
List list=new ArrayList<>();
list.add(654973121274576897L);
for (Long thisStuId : list) {
int i = studentDao.deletesStudent(thisStuId);
System.out.println(i==1);
}
}
}
9.数据查看
](https://img-blog.csdnimg.cn/abdf9f195dfc4d95a9916ed41cd11fc8.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5YiY5Y2O5by6Li4u,size_20,color_FFFFFF,t_70,g_se,x_16)
