1、数据库水平和垂直分割
1.1、垂直分割
1.1.1、业务维度
数据库
根据业务去划分数据库，比如：订单库、排期库、支付库、基础库、等。较简单
表
根据表的结构去划分，比如用户表中可能含有用户的基础信息和一些另外其它信息的描述，此时可以划分成两张表，一张表为用户的基础信息，另外一张表包含用户的证件照，或者工作经历等等描述。
1.2、水平分割
1.2.1、数据维度
数据库
其实就是同一个数据库分割成多个数据库，名字不同 比如：user_db，可以分割成user_db1，user_db2，user_db3等等
表
其实就是同一张表分割成多张表，名字不同 比如：user，可以分割成user_1，user_2，等等
2、初始化数据库
DROP TABLE IF EXISTS `order_1`;
CREATE TABLE `order_1` (
`order_id` bigint NOT NULL COMMENT '订单id',
`price` decimal(10, 2) NOT NULL COMMENT '订单价格',
`user_id` bigint NOT NULL COMMENT '下单用户id',
`status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for order_2
-- ----------------------------
DROP TABLE IF EXISTS `order_2`;
CREATE TABLE `order_2` (
`order_id` bigint NOT NULL COMMENT '订单id',
`price` decimal(10, 2) NOT NULL COMMENT '订单价格',
`user_id` bigint NOT NULL COMMENT '下单用户id',
`status` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单状态',
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
3、创建SpringBoot工程
sharding-jdbc
3.1、水平分表
pom
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.10</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.sharding</groupId>
<artifactId>sharding-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sharding-jdbc</name>
<description>sharding-jdbc</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<!--
4.1.1版本的sharding不能使用druid-spring-boot-starter
必须单独引入druid
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.13</version>
</dependency>
-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.13</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
application.yml
# 水平分表配置
# 规则，order_id为偶数时，order_id % 2 + 1 = 1，所以进入order_1表
# order_id为奇数时，order_id % 2 + 1 = 2，所以进入order_2表
spring:
main:
# 一个实体类可以对应多张表
allow-bean-definition-overriding: true
shardingsphere:
props:
sql:
show: true
datasource:
# 数据源名称，自定
names: s1
s1:
# 数据源指定
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/sharding-jdbc?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=true&serverTimezone=Asia/Shanghai
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
sharding:
tables:
# 此处order是指规则名称，自定
order:
# 主表分片规则表名
actual-data-nodes: s1.order_$->{1..2}
# 主键策略
key-generator:
column: order_id
type: SNOWFLAKE
table-strategy:
# 表分片策略
# 水平分表，order_id % 2 + 1 = 1进入order_1 order_id % 2 + 1 = 2 进入order_2
inline:
algorithm-expression: order_$->{order_id % 2 + 1}
sharding-column: order_id
server:
servlet:
encoding:
charset: utf-8
enabled: true
force: true
mybatis-plus:
mapper-locations: classpath*:/mapper/*.xml
type-aliases-package: com.shardingjdbc.domain
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
logging:
level:
root: info
org.springframework.web: info
com.shardingjdbc: debug
druid.sql: debug
省略代码生成
测试
package com.shardingjdbc;
import com.shardingjdbc.domain.Order;
import com.shardingjdbc.domain.SysDictionary;
import com.shardingjdbc.domain.User;
import com.shardingjdbc.mapper.DictionaryMapper;
import com.shardingjdbc.mapper.OrderMapper;
import com.shardingjdbc.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.util.DigestUtils;
import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.Date;
@SpringBootTest
class ShardingJdbcApplicationTests {
@Resource
private OrderMapper orderMapper;
@Test
void contextLoads() {
Order order = new Order();
order.setOrderId(2L);
order.setPrice(new BigDecimal(100));
order.setStatus("Normal");
order.setUserId(1L);
orderMapper.insert(order);
}
}
3.2、动态数据源+水平分库分表
3.2.1、创建数据库
sharding-jdbc_1
sharding-jdbc_2
表和sharding-jdbc一样。
3.2.2、配置文件修改
application.yml
# 水平分库分表
# 规则：
# user_id为偶数时，进入sharding-jdbc_1库
# user_id为奇数时，进入sharding-jdbc_2库
# order_id为偶数时，order_id % 2 + 1 = 1，所以进入order_1表
# order_id为奇数时，order_id % 2 + 1 = 2，所以进入order_2表
#
spring:
main:
# 一个实体类可以对应多张表
allow-bean-definition-overriding: true
shardingsphere:
props:
sql:
show: true
datasource:
# 数据源名称，自定
names: ds1,ds2
ds1:
# 数据源指定
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/sharding-jdbc_1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=true&serverTimezone=Asia/Shanghai
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
ds2:
# 数据源指定
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/sharding-jdbc_2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=true&serverTimezone=Asia/Shanghai
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
sharding:
tables:
# 此处order是指规则名称，自定
order:
# 数据库以及数据表的节点信息
actual-data-nodes: ds$->{1..2}.order_$->{1..2}
# 主键策略
key-generator:
column: order_id
type: SNOWFLAKE
data-strategy:
# 数据库分片策略
# 水平分库，user_id % 2 + 1 = 1进入sharding-jdbc_1 user_id % 2 + 1 = 2 进入sharding-jdbc_2
inline:
algorithm-expression: ds$->{user_id % 2 + 1}
sharding-column: user_id
table-strategy:
# 表分片策略
# 水平分表，order_id % 2 + 1 = 1进入order_1 order_id % 2 + 1 = 2 进入order_2
inline:
algorithm-expression: order_$->{order_id % 2 + 1}
sharding-column: order_id
server:
servlet:
encoding:
charset: utf-8
enabled: true
force: true
mybatis-plus:
mapper-locations: classpath*:/mapper/*.xml
type-aliases-package: com.shardingjdbc.domain
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
logging:
level:
root: info
org.springframework.web: info
com.shardingjdbc: debug
druid.sql: debug
测试
@Test
void contextLoads() {
Order order = new Order();
order.setOrderId(2L);
order.setPrice(new BigDecimal(100));
order.setStatus("Normal");
order.setUserId(1L);
orderMapper.insert(order);
}
3.3、单独测试动态数据源切换
3.3.1、初始化数据库
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id',
`username` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户名',
`password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '密码',
`create_id` bigint NULL DEFAULT NULL COMMENT '创建人id',
`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
`update_id` bigint NULL DEFAULT NULL COMMENT '修改人id',
`update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
`is_delete` tinyint(1) NULL DEFAULT NULL COMMENT '是否删除1：删除 0：未删除',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (9, '123', '202cb962ac59075b964b07152d234b70', NULL, '2023-02-25 16:06:57', NULL, '2023-02-25 16:06:57', 0);
INSERT INTO `user` VALUES (1648601512115937282, 'test', '21232f297a57a5a743894a0e4a801fc3', 1, '2023-04-19 16:16:45', 1, '2023-04-19 16:16:45', 0);
SET FOREIGN_KEY_CHECKS = 1;
3.3.2、配置文件
# 水平分库分表
# 规则：
# user_id为偶数时，进入sharding-jdbc_1库
# user_id为奇数时，进入sharding-jdbc_2库
# order_id为偶数时，order_id % 2 + 1 = 1，所以进入order_1表
# order_id为奇数时，order_id % 2 + 1 = 2，所以进入order_2表
#
spring:
main:
# 一个实体类可以对应多张表
allow-bean-definition-overriding: true
shardingsphere:
props:
sql:
show: true
datasource:
# 数据源名称，自定
names: ds1,ds2,ds3
ds1:
# 数据源指定
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/sharding-jdbc_1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=true&serverTimezone=Asia/Shanghai
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
ds2:
# 数据源指定
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/sharding-jdbc_2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=true&serverTimezone=Asia/Shanghai
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
ds3:
# 数据源指定
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost:3306/cloud_note?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=true&serverTimezone=Asia/Shanghai
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
sharding:
tables:
user:
# 数据库以及数据表的节点信息
actual-data-nodes: ds$->{3}.user
# 主键策略
key-generator:
column: id
type: SNOWFLAKE
table-strategy:
inline:
algorithm-expression: user
sharding-column: id
# 此处order是指规则名称，自定
order:
# 数据库以及数据表的节点信息
actual-data-nodes: ds$->{1..2}.order_$->{1..2}
# 主键策略
key-generator:
column: order_id
type: SNOWFLAKE
data-strategy:
# 数据库分片策略
# 水平分库，user_id % 2 + 1 = 1进入sharding-jdbc_1 user_id % 2 + 1 = 2 进入sharding-jdbc_2
inline:
algorithm-expression: ds$->{user_id % 2 + 1}
sharding-column: user_id
table-strategy:
# 表分片策略
# 水平分表，order_id % 2 + 1 = 1进入order_1 order_id % 2 + 1 = 2 进入order_2
inline:
algorithm-expression: order_$->{order_id % 2 + 1}
sharding-column: order_id
server:
servlet:
encoding:
charset: utf-8
enabled: true
force: true
mybatis-plus:
mapper-locations: classpath*:/mapper/*.xml
type-aliases-package: com.shardingjdbc.domain
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
logging:
level:
root: info
org.springframework.web: info
com.shardingjdbc: debug
druid.sql: debug
3.3.3、省略代码生成
3.3.4、测试
@Test
void contextLoads2() {
User user = new User();
user.setUsername("test");
user.setPassword(DigestUtils.md5DigestAsHex("admin".getBytes()));
user.setCreateId(1L);
user.setCreateTime(new Date());
user.setUpdateId(1L);
user.setUpdateTime(new Date());
user.setDelete(false);
userMapper.insert(user);
}
4、Docker搭建MySQL读写分离
4.1、关于MySQL密码
介绍
从 MySQL 8.0.4 开始，MySQL 默认身份验证插件从 mysql_native_password 改为 caching_sha2_password 。相应地，libmysqlclient 也使用 caching_sha2_password 作为默认的身份验证机制。
起因
在这之前 MySQL 5.6/5.7 使用的默认密码插件是 mysql_native_password。mysql_native_password 的特点是不需要加密的连接。该插件验证速度特别快，但是不够安全，因为，mysql_native_password 使用的是于 SHA1 算法，NIST（美国国家标准与技术研究院）在很早之前就已建议停止使用 SHA1 算法，因为 SHA1 和其他哈希算法（例如 MD5）容易被破解。
其实从 MySQL 5.6 开始就引入了更安全的认证机制：ha256_password 认证插件。它使用一个加盐密码（salted password）进行多轮 SHA256 哈希（数千轮哈希，暴力破解更难），以确保哈希值转换更安全。但是，建立安全连接和多轮 hash 加密很耗费时间。虽然安全性更高，但是验证速度不够快。
改进
MySQL 试图结合二者的优点。于是在 MySQL-8.0.3 引入了一个新的身份验证插件 caching_sha2_password ，作为sha256_password的代替方案，在sha256_password 的基础上进行了改进补上了短板，既解决安全性问题又解决性能问题。与此同时 sha256_password将退出时代的浪潮。MySQL 预计在未来版本中将其删除。使用 sha256_password 进行身份验证的 MySQL 账户建议转为 caching_sha2_password。
4.2、搭建主库
主库定义为增删改操作
# 安装docker省略
# 创建本地MySQL映射挂载的目录
$>mkdir -p /opt/mysql-master/data /opt/mysql-master/logs /opt/mysql-master/conf
# 在/opt/mysql/conf中创建 *.cnf 文件
$>cd /opt/mysql/conf
$>vim my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password
# 同一局域网内注意要唯一,在一个主从复制集群中要唯一,值范围1-255
server-id=11
# 开启二进制日志功能，可以随便取（关键）
log-bin=master-bin
# 二级制日志格式，有三种 row，statement，mixed
binlog-format=ROW
# 同步的数据库名称,如果不配置，表示同步所有的库
binlog-do-db=sharding-db
# 屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 是否只读,1 代表只读, 0 代表读写
read-only=0
# 启动MySQL容器并将数据,日志,配置文件映射挂载到本机
$>docker run --name mysql-master -v /opt/mysql-master/conf:/etc/mysql/conf.d -v /opt/mysql-master/logs:/logs -v /opt/mysql-master/data:/var/lib/mysql --restart=always -p 3311:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
4.2.1、创建mysql主从复制账户
# 登录mysql-master容器
$>docker exec -it mysql-master /bin/bash
# 登录MySQL
$>mysql -u root -p
# 输入密码。回车
# 创建用于主从复制的用户和密码
mysql>CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
# 设置该用户任意ip都可访问
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
# 刷新生效
mysql>flush privileges;
# 查看各用户加密插件
mysql>SELECT Host, User, plugin from user;
image-20230421095229880.png
# 注意看只有Host为% User为root的用户是我之前设置过的，没设置的话，默认全部是caching_sha2_password
# 设置刚刚的slave用户密码插件，否则连不上
mysql>ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
# 然后查看主库状态,从库配置需要使用
mysql>show master status;
image-20230421095526287.png
4.3、搭建从库
从库定义为读操作
# 安装docker省略
# 创建本地MySQL映射挂载的目录
$>mkdir -p /opt/mysql-slave/data /opt/mysql-slave/logs /opt/mysql-slave/conf
# 在/opt/mysql/conf中创建 *.cnf 文件
$>cd /opt/mysql/conf
$>vim my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password
# 同一局域网内注意要唯一
server-id=12
# 开启二进制日志功能，可以随便取（关键）
log-bin=master-bin
# 二级制日志格式，有三种 row，statement，mixed
binlog-format=ROW
# 同步的数据库名称,如果不配置，表示同步所有的库
binlog-do-db=sharding-db
#屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 是否只读,1 代表只读, 0 代表读写
read_only=1
# 启动MySQL容器并将数据,日志,配置文件映射挂载到本机
$>docker run --name mysql-slave -v /opt/mysql-slave/conf:/etc/mysql/conf.d -v /opt/mysql-slave/logs:/logs -v /opt/mysql-slave/data:/var/lib/mysql --restart=always -p 3312:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:8.0 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
4.3.1、开启复制
# 登录mysql-slave
$>docker exec -it mysql-slave /bin/bash
# 登录MySQL
$>mysql -u root -p
# 输入密码。回车
# 使用slave账户从主库复制
mysql>change master to master_host= '192.168.20.100', master_user='slave', master_password='123456',master_port=3311, master_log_file='master-bin.000001', master_log_pos=879;
# 开启slave模式
mysql>start slave;
# 查看是否成功
mysql>show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.20.100
Master_User: slave
Master_Port: 3311
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 157
Relay_Log_File: f4465bd7c49e-relay-bin.000003
Relay_Log_Pos: 375
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 157
Relay_Log_Space: 762
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
Master_UUID: b652e9ab-dfdd-11ed-9205-0242ac110003
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
4.4、测试
- 手动在主从库，创建数据库：sharding-db
- 在主库sharding-db中创建一张表
- 观察从库，sharding-db中是否会复制
