文章目录
- 1、订单库 db_order
- 2、用户库 db_user
- 3、创建实体类 TOrder
- 4、创建实体类 TUser
- 5、创建 TOrderMapper
- 6、创建 TUserMapper
- 7、application.yml
- 8、shardingsphere.yaml
- 9、ShardingJdbcDemoApplicationTests
垂直分片：在项目中可以将垂直分片到不同库中的多张表加载到一个项目中使用。
1、订单库 db_order
3306主 -> 3316从 ：订单库 db_order
订单库将来访问压力比较大，我们要考虑主从复制
3306和3316容器我们已经在 mysql搭建主从复制的时候创建好了，参考这篇博客：mysql-搭建主从复制
1.2、创建数据库
CREATE DATABASE db_order;
USE db_order;
CREATE TABLE t_order (
id BIGINT AUTO_INCREMENT,
order_no VARCHAR(30),
user_id BIGINT,
PRIMARY KEY(id)
);
创建完之后，我们会发现从库3316没有同步创建数据库 db_order，因为当时我们在搭建mysql的主从复制的时候，只设置了复制mydb2，mydb3数据库到3316从库中。
2、用户库 db_user
3326主
因为用户的数据量没有那么大，并发操作没那么多，所以我们不需要考虑主从复制
2.1、创建 atguigu-mysql-user 容器
docker run -d \
-p 3326:3306 \
-v mysql-user-conf:/etc/mysql/conf.d \
-v mysql-user-data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name atguigu-mysql-user \
mysql:8
[root@localhost ~]# docker run -d \
> -p 3326:3306 \
> -v mysql-user-conf:/etc/mysql/conf.d \
> -v mysql-user-data:/var/lib/mysql \
> -e MYSQL_ROOT_PASSWORD=123456 \
> --name atguigu-mysql-user \
> mysql:8
d2e24da102d53cdeb3db591b8667facec3973a3fa1d2029e2d5306f3441ff574
[root@localhost ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d2e24da102d5 mysql:8 "docker-entrypoint.s…" 28 seconds ago Up 26 seconds 33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp atguigu-mysql-user
c236f876ae40 mysql:8 "docker-entrypoint.s…" 11 days ago Up 21 hours 33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp atguigu-mysql-slave1
89bb2276fc3d elasticsearch:8.8.2 "/bin/tini -- /usr/l…" 4 weeks ago Up 22 hours 0.0.0.0:9200->9200/tcp, :::9200->9200/tcp, 0.0.0.0:9300->9300/tcp, :::9300->9300/tcp elasticsearch
8c71efe9dca7 wurstmeister/zookeeper "/bin/sh -c '/usr/sb…" 4 weeks ago Up 22 hours 22/tcp, 2888/tcp, 3888/tcp, 0.0.0.0:2181->2181/tcp, :::2181->2181/tcp zookeeper
c14772057ab8 redis "docker-entrypoint.s…" 9 months ago Up 22 hours 0.0.0.0:6379->6379/tcp, :::6379->6379/tcp spzx-redis
ab66508d9441 mysql:8 "docker-entrypoint.s…" 9 months ago Up 22 hours 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp spzx-mysql
创建完容器，配置文件不需要改，因为 atguigu-mysql-user 容器 我们不需要搭建主从复制。
2.2、登录 atguigu-mysql-user 容器
[root@localhost ~]# docker exec -it atguigu-mysql-user /bin/bash
root@d2e24da102d5:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
2.3、设置密码
-- 设置密码
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
-- 刷新权限
FLUSH PRIVILEGES;
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
2.4、创建数据库
CREATE DATABASE db_user;
USE db_user;
CREATE TABLE t_user (
id BIGINT AUTO_INCREMENT,
uname VARCHAR(30),
PRIMARY KEY (id)
);
3、创建实体类 TOrder
package com.atguigu.sharding.jdbc.demo.bean;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName("t_order")
public class TOrder {
@TableId(type = IdType.AUTO)
private Long id;
private String orderNo;
private Long userId;
}
4、创建实体类 TUser
package com.atguigu.sharding.jdbc.demo.bean;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName(value = "t_user")
public class TUser {
private Long id;
private String uname;
}
5、创建 TOrderMapper
package com.atguigu.sharding.jdbc.demo.mapper;
import com.atguigu.sharding.jdbc.demo.bean.TOrder;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface TOrderMapper extends BaseMapper<TOrder> {
}
6、创建 TUserMapper
package com.atguigu.sharding.jdbc.demo.mapper;
import com.atguigu.sharding.jdbc.demo.bean.TUser;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface TUserMapper extends BaseMapper<TUser> {
}
7、application.yml
spring:
datasource:
# driver-class-name: com.mysql.cj.jdbc.Driver
# 驱动类：使用shardingjdbc提供的驱动类
driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
# username: root
# password: 123456
# url: jdbc:mysql://192.168.74.148:3306/mydb2?serverTimezone=GMT%2B8
url: jdbc:shardingsphere:classpath:shardingsphere.yaml
8、shardingsphere.yaml
# 模式配置
mode:
# shardingjdbc：使用的是单机模式
type: Standalone
repository:
type: JDBC
# 数据源配置
dataSources:
write_ds:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://192.168.74.148:3306/mydb2
username: root
password: 123456
read_ds_0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://192.168.74.148:3316/mydb2
username: root
password: 123456
user_ds:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://192.168.74.148:3326/db_user
username: root
password: 123456
order_ds:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://192.168.74.148:3306/db_order
username: root
password: 123456
rules:
# 读写分离配置：指定读交给哪个数据源 写交给哪个
- !READWRITE_SPLITTING
dataSources:
readwrite_ds:
writeDataSourceName: write_ds
readDataSourceNames:
- read_ds_0
transactionalReadQueryStrategy: PRIMARY # 事务内读请求的路由策略，可选值：PRIMARY（路由至主库）、FIXED（同一事务内路由至固定数据源）、DYNAMIC（同一事务内路由至非固定数据源）。默认值：DYNAMIC
# 配置读操作的负载均衡策略
loadBalancerName: random
loadBalancers:
random:
type: RANDOM
round_robin:
type: ROUND_ROBIN
weight:
type: WEIGHT
props:
read_ds_0: 1
# 垂直分片配置
- !SHARDING
tables:
t_user:
actualDataNodes: user_ds.t_user
t_order:
actualDataNodes: order_ds.t_order
# 输出sql：
props:
# 打印 sharding 的sql日志
sql-show: true
9、ShardingJdbcDemoApplicationTests
package com.atguigu.sharding.jdbc.demo;
import com.atguigu.sharding.jdbc.demo.bean.TOrder;
import com.atguigu.sharding.jdbc.demo.bean.TUser;
import com.atguigu.sharding.jdbc.demo.mapper.TOrderMapper;
import com.atguigu.sharding.jdbc.demo.mapper.TUserMapper;
import jakarta.annotation.Resource;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
class ShardingJdbcDemoApplicationTests {
@Resource
TUserMapper tUserMapper;
@Resource
TOrderMapper torderMapper;
@Test
void contextLoads() {
TUser tUser = new TUser();
tUser.setUname("张三");
tUserMapper.insert(tUser);
TOrder tOrder = new TOrder();
tOrder.setOrderNo("1234");
tOrder.setUserId(1L);
torderMapper.insert(tOrder);
}
}
2024-09-25T11:53:56.395+08:00 INFO 15748 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_user ( id,
uname ) VALUES ( ?,
? )
2024-09-25T11:53:56.395+08:00 INFO 15748 --- [ main] ShardingSphere-SQL Actual SQL: user_ds ::: INSERT INTO t_user ( id,
uname ) VALUES (?, ?) ::: [1838788985485291522, 张三]
2024-09-25T11:53:56.436+08:00 INFO 15748 --- [ main] ShardingSphere-SQL : Logic SQL: INSERT INTO t_order ( order_no,
user_id ) VALUES ( ?,
? )
2024-09-25T11:53:56.437+08:00 INFO 15748 --- [ main] ShardingSphere-SQL Actual SQL: order_ds ::: INSERT INTO t_order ( order_no,
user_id ) VALUES (?, ?) ::: [1234, 1]