1. 条件准备
1.1 主机IP:192.168.186.77
- version: '3.8'
- services:
- mysql-master:
- image: mysql:latest
- container_name: mysql-master
- environment:
- MYSQL_ROOT_PASSWORD: 123456
- MYSQL_USER: master
- MYSQL_PASSWORD: 123456
- MYSQL_DATABASE: db1
- ports:
- - "3306:3306"
- volumes:
- - mysql-master-data:/var/lib/mysql
- command: --server-id=1 --log-bin=mysql-bin --binlog-format=ROW
- cap_add:
- - SYS_NICE
- security_opt:
- - seccomp:unconfined
- mysql-slave:
- image: mysql:latest
- container_name: mysql-slave
- environment:
- MYSQL_ROOT_PASSWORD: 123456
- MYSQL_USER: slave
- MYSQL_PASSWORD: 123456
- MYSQL_DATABASE: db1
- ports:
- - "3307:3306"
- volumes:
- - mysql-slave-data:/var/lib/mysql
- command: --server-id=2 --log-bin=mysql-bin --binlog-format=ROW --relay-log=relay-bin --relay-log-index=relay-bin.index
- depends_on:
- - mysql-master
- cap_add:
- - SYS_NICE
- security_opt:
- - seccomp:unconfined
- volumes:
- mysql-master-data:
- mysql-slave-data:
复制代码 注:3306端口扮演master数据库角色,3307端口扮演salve数据库角色。
1.2 主机IP:192.168.186.216
- version: '3.8'
- services:
- mysql-master:
- image: mysql:latest
- container_name: mysql-master
- environment:
- MYSQL_ROOT_PASSWORD: 123456
- MYSQL_USER: master
- MYSQL_PASSWORD: 123456
- MYSQL_DATABASE: db2
- ports:
- - "3306:3306"
- volumes:
- - mysql-master-data:/var/lib/mysql
- command: --server-id=3 --log-bin=mysql-bin --binlog-format=ROW
- cap_add:
- - SYS_NICE
- security_opt:
- - seccomp:unconfined
- mysql-slave:
- image: mysql:latest
- container_name: mysql-slave
- environment:
- MYSQL_ROOT_PASSWORD: 123456
- MYSQL_USER: slave
- MYSQL_PASSWORD: 123456
- MYSQL_DATABASE: db2
- ports:
- - "3307:3306"
- volumes:
- - mysql-slave-data:/var/lib/mysql
- command: --server-id=4 --log-bin=mysql-bin --binlog-format=ROW --relay-log=relay-bin --relay-log-index=relay-bin.index
- depends_on:
- - mysql-master
- cap_add:
- - SYS_NICE
- security_opt:
- - seccomp:unconfined
- volumes:
- mysql-master-data:
- mysql-slave-data:
复制代码 1.3 主机IP:192.168.186.216
- version: '3.8'
- services:
- mysql-master:
- image: mysql:latest
- container_name: mysql-master
- environment:
- MYSQL_ROOT_PASSWORD: 123456
- MYSQL_USER: master
- MYSQL_PASSWORD: 123456
- MYSQL_DATABASE: db3
- ports:
- - "3306:3306"
- volumes:
- - mysql-master-data:/var/lib/mysql
- command: --server-id=5 --log-bin=mysql-bin --binlog-format=ROW
- cap_add:
- - SYS_NICE
- security_opt:
- - seccomp:unconfined
- mysql-slave:
- image: mysql:latest
- container_name: mysql-slave
- environment:
- MYSQL_ROOT_PASSWORD: 123456
- MYSQL_USER: slave
- MYSQL_PASSWORD: 123456
- MYSQL_DATABASE: db3
- ports:
- - "3307:3306"
- volumes:
- - mysql-slave-data:/var/lib/mysql
- command: --server-id=6 --log-bin=mysql-bin --binlog-format=ROW --relay-log=relay-bin --relay-log-index=relay-bin.index
- depends_on:
- - mysql-master
- cap_add:
- - SYS_NICE
- security_opt:
- - seccomp:unconfined
- volumes:
- mysql-master-data:
- mysql-slave-data:
复制代码 注:每个 MySQL 实例必须有一个唯一的 server-id。这是必要的,因为在主从复制设置中,主服务器和从服务器需要能够相互识别,并制止循环复制和冲突。
1.4 简朴主从搭建过程
1.4.1 毗连主服务器
1.4.2 毗连从服务器
1.4.3 配置主服务器
- # 创建一个具有复制权限的用户
- CREATE USER 'master_slave'@'%' IDENTIFIED BY '123456' REQUIRE SSL;
- GRANT REPLICATION SLAVE ON *.* TO 'master_slave'@'%';
- FLUSH PRIVILEGES;
- SHOW MASTER STATUS;
复制代码
1.4.4 配置从服务器
- CHANGE MASTER TO
- MASTER_HOST ='192.168.186.216', # 主服务器的 IP 地址
- MASTER_USER ='master_slave', # 主服务器上配置的复制用户
- MASTER_PASSWORD ='123456', # 复制用户的密码
- MASTER_LOG_FILE ='mysql-bin.000003', # 主服务器的日志文件名
- MASTER_LOG_POS =920, # 日志文件的位置
- MASTER_SSL=1;
- START SLAVE;
- SHOW SLAVE STATUS;
复制代码
其余两台利用同样的方式进行配置。
2. 安装 Apache ShardingSphere Proxy
2.1 安装符合的JDK版本
- sudo apt install openjdk-8-jdk -y
复制代码 2.2 验证JDK版本
liber@liber-VMware-Virtual-Platform:/home/sp$ java -version
openjdk version "1.8.0_412"
OpenJDK Runtime Environment (build 1.8.0_412-8u412-ga-1~24.04.2-b08)
OpenJDK 64-Bit Server VM (build 25.412-b08, mixed mode)
2.3 利用wget下载Apache ShardingSphere Proxy
- sudo wget https://dlcdn.apache.org/shardingsphere/5.5.0/apache-shardingsphere-5.5.0-shardingsphere-proxy-bin.tar.gz
复制代码
2.4 解压Apache ShardingSphere Proxy文件
- sudo tar -zxvf apache-shardingsphere-5.5.0-shardingsphere-proxy-bin.tar.gz
复制代码 2.5 进入Apache ShardingSphere Proxy解压后的目次
- cd apache-shardingsphere-5.5.0-shardingsphere-proxy-bin
复制代码 2.5 检察MySQL的版本
找台主服务器大概从服务器输入检察数据库的版本。
2.6 下载并安装MySQL Connector/J 8.0.27
- sudo wget https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-java-8.0.27.tar.gz
复制代码
2.7 解压MySQL文件
- sudo tar -zxvf mysql-connector-java-8.0.27.tar.gz
复制代码 2.8 进入解压后的MySQL目次
- cd mysql-connector-java-8.0.27
复制代码 2.9 将 JAR 文件放置到符合的位置
需要将JAR文件复制到Apache ShardingSphere-Proxy解压目次的lib 目次中,因为我在Apache ShardingSphere-Proxy的解压目次中下载的MySQL压缩包,解压后只需要将JAR文件移动到上一级的lib目次即可。
- sudo mv mysql-connector-java-8.0.27.jar ../lib
复制代码 2.10 global.yaml
- # 回退上一级目录
- cd ..
- # 进入conf目录
- cd conf
- # 编辑global.yaml文件
- sudo nano global.yaml
复制代码 global.yaml 内容如下:
- # 释放注解
- authority:
- users:
- - user: root@%
- password: 123456
- - user: sharding
- password: 123456
- privilege:
- type: ALL_PERMITTED
- sqlParser:
- sqlStatementCache:
- initialCapacity: 2000
- maximumSize: 65535
- parseTreeCache:
- initialCapacity: 128
- maximumSize: 1024
- props:
- proxy-default-port: 3308 #修改端口,因为被占用了3307所以我修改了3308
- sql-show: true
复制代码 2.11 创建物理表
- use db1; # 每个主服务器都需要创建,只需要把db1,改db2,db3即可。
- CREATE TABLE t_order_0
- (
- order_id INT PRIMARY KEY,
- user_id INT,
- order_date DATE,
- status VARCHAR(15)
- );
- CREATE TABLE t_order_1
- (
- order_id INT PRIMARY KEY,
- user_id INT,
- order_date DATE,
- status VARCHAR(15)
- );
复制代码 注: 每个主服务器的数据库都需要建立结构相同的物理表,只是库不一样db1,db2,db3。
2.12 database-sharding.yaml
- sudo nano database-sharding.yaml
复制代码 2.1.1 配置数据库源
- dataSources:
- # 主库配置
- ds_77_master:
- url: jdbc:mysql://192.168.186.77:3306/db1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
- username: root # 数据库用户名
- password: 123456 # 数据库密码
- connectionTimeoutMilliseconds: 30000 # 连接超时时间,单位毫秒
- idleTimeoutMilliseconds: 60000 # 空闲连接超时时间,单位毫秒
- maxLifetimeMilliseconds: 1800000 # 连接最大生命周期,单位毫秒
- maxPoolSize: 50 # 连接池最大连接数
- minPoolSize: 1 # 连接池最小连接数
- # 从库配置
- ds_77_slave:
- url: jdbc:mysql://192.168.186.77:3307/db1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
- username: slave # 数据库用户名
- password: 123456 # 数据库密码
- connectionTimeoutMilliseconds: 30000 # 连接超时时间,单位毫秒
- idleTimeoutMilliseconds: 60000 # 空闲连接超时时间,单位毫秒
- maxLifetimeMilliseconds: 1800000 # 连接最大生命周期,单位毫秒
- maxPoolSize: 50 # 连接池最大连接数
- minPoolSize: 1 # 连接池最小连接数
- ds_216_master:
- url: jdbc:mysql://192.168.186.216:3306/db2?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
- username: root
- password: 123456
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 50
- minPoolSize: 1
- ds_216_slave:
- url: jdbc:mysql://192.168.186.216:3307/db2?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
- username: slave
- password: 123456
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 50
- minPoolSize: 1
- ds_18_master:
- url: jdbc:mysql://192.168.186.18:3306/db3?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
- username: root
- password: 123456
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 50
- minPoolSize: 1
- ds_18_slave:
- url: jdbc:mysql://192.168.186.18:3307/db3?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
- username: slave
- password: 123456
- connectionTimeoutMilliseconds: 30000
- idleTimeoutMilliseconds: 60000
- maxLifetimeMilliseconds: 1800000
- maxPoolSize: 50
- minPoolSize: 1
- # 设置逻辑数据库名称
- databaseName: my_database
复制代码 2.1.2 读写分离规则
- rules:
- - !READWRITE_SPLITTING
- # 定义用于读写分离的数据源配置
- dataSources:
- # 第一组读写分离规则,命名为 "readwrite_77"
- readwrite_77:
- writeDataSourceName: ds_77_master # 指定写操作的数据源为 ds_77_master(主库)
- readDataSourceNames:
- - ds_77_slave # 指定读操作的数据源列表,这里只包括 ds_77_slave(从库)
- transactionalReadQueryStrategy: PRIMARY # 事务中的读查询策略设为 PRIMARY,即事务中所有读操作都将指向主库
- loadBalancerName: random # 使用随机策略从可用的从库中选择
- readwrite_216:
- writeDataSourceName: ds_216_master
- readDataSourceNames:
- - ds_216_slave
- transactionalReadQueryStrategy: PRIMARY
- loadBalancerName: random
- readwrite_18:
- writeDataSourceName: ds_18_master # 主库
- readDataSourceNames:
- - ds_18_slave
- transactionalReadQueryStrategy: PRIMARY
- loadBalancerName: random
- # 定义负载均衡器的配置
- loadBalancers:
- random:
- type: RANDOM # 指定负载均衡器的类型为 RANDOM,随机选择读库
复制代码 2.1.3 分库分表规则
- - !SHARDING
- # 分片表的配置
- tables:
- t_order:
- # 定义分片表t_order在各个数据源的具体分布
- actualDataNodes: readwrite_77.t_order_${0..1}, readwrite_216.t_order_${0..1}, readwrite_18.t_order_${0..1}
- # 表的分片策略配置
- tableStrategy:
- standard:
- shardingColumn: order_id # 使用订单ID作为分片键
- shardingAlgorithmName: t_order_inline # 分片算法名称,指向后面定义的内联算法
- # 主键生成策略,用于插入操作时自动生成主键
- keyGenerateStrategy:
- column: order_id # 主键列
- keyGeneratorName: snowflake # 使用雪花算法生成主键
- # 默认数据库分片策略
- defaultDatabaseStrategy:
- standard:
- shardingColumn: user_id # 使用用户ID作为分片键
- shardingAlgorithmName: database_inline # 数据库分片使用的算法名称,指向后面定义的内联算法
- # 默认的表分片策略,此处未定义分片策略
- defaultTableStrategy:
- none:
- # 绑定表组,确保相互关联的表在同一数据库分片中
- bindingTables:
- - t_order # 把t_order表标记为绑定表
- # 定义使用的分片算法
- shardingAlgorithms:
- # 定义数据库分片的内联算法
- database_inline:
- type: INLINE
- props:
- algorithm-expression: "readwrite_${(user_id % 3 == 0) ? '77' : ((user_id % 3 == 1) ? '216' : '18')}"
- # 根据用户ID的值进行模3运算来决定数据分配到哪个数据源
- # 定义表分片的内联算法
- t_order_inline:
- type: INLINE
- props:
- algorithm-expression: "t_order_${order_id % 2}"
- # 根据订单ID的值进行模2运算来决定数据存储在哪个分表
- # 主键生成器配置,指定使用雪花算法生成主键
- keyGenerators:
- snowflake:
- type: SNOWFLAKE
- props:
- worker-id: 123 # 设置雪花算法的工作节点ID
复制代码 2.1.4 完备配置信息
注:分片规则根据 user_id 的值对3取模,决定将数据分片到哪个数据库, 根据 order_id 的值对2取模,决定将数据分片到哪个表。
2.1.5 参考教程文献
数据分片 :: ShardingSphere
读写分离 :: ShardingSphere
混合规则 :: ShardingSphere
2.13 启动步伐
- # 回退上一级
- cd ..
- # 进入bin目录
- cd bin
- # 启动程序
- sudo ./start.sh
复制代码 liber@liber-VMware-Virtual-Platform:/home/sp/apache-shardingsphere-5.5.0-shardingsphere-proxy-bin/conf$ cd ..
liber@liber-VMware-Virtual-Platform:/home/sp/apache-shardingsphere-5.5.0-shardingsphere-proxy-bin$ cd bin
liber@liber-VMware-Virtual-Platform:/home/sp/apache-shardingsphere-5.5.0-shardingsphere-proxy-bin/bin$ sudo ./start.sh
/usr/bin/java
we find java version: java8, full_version=1.8.0_412, full_path=/usr/bin/java
The classpath is /home/sp/apache-shardingsphere-5.5.0-shardingsphere-proxy-bin/conf:/home/sp/apache-shardingsphere-5.5.0-shardingsphere-proxy-bin/conf:.:/home/sp/apache-shardingsphere-5.5.0-shardingsphere-proxy-bin/lib/*:/home/sp/apache-shardingsphere-5.5.0-shardingsphere-proxy-bin/ext-lib/*
main class org.apache.shardingsphere.proxy.Bootstrap -1 /home/sp/apache-shardingsphere-5.5.0-shardingsphere-proxy-bin/conf 0.0.0.0 false
Starting the ShardingSphere-Proxy ... PID: 128452
Please check the STDOUT file: /home/sp/apache-shardingsphere-5.5.0-shardingsphere-proxy-bin/logs/stdout.log #日记路径可以通过cat进行检察
liber@liber-VMware-Virtual-Platform:/home/sp/apache-shardingsphere-5.5.0-shardingsphere-proxy-bin/bin$
2.15 检察日记
- cat /home/sp/apache-shardingsphere-5.5.0-shardingsphere-proxy-bin/logs/stdout.log
复制代码 - #显示该信息代表成功
- [INFO ] 2024-07-25 15:39:09.724 [main] o.a.s.d.p.c.l.PipelineContextManagerLifecycleListener - mode type is not Cluster, mode type='Standalone', ignore
- [INFO ] 2024-07-25 15:39:09.933 [main] o.a.s.p.v.ShardingSphereProxyVersion - Database type is `MySQL`, version is `8.0.27`, database name is `my_database`
- [INFO ] 2024-07-25 15:39:09.966 [main] o.a.s.p.frontend.ssl.ProxySSLContext - Proxy frontend SSL/TLS is not enabled.
- [INFO ] 2024-07-25 15:39:12.683 [main] o.a.s.p.frontend.ShardingSphereProxy - ShardingSphere-Proxy Standalone mode started successfully
复制代码 2.16 测试毗连
2.17 分库分表验证
在ShardingSphere-Proxy毗连的客户端进行操作,因为t_order是ShardingSphere-Proxy创建的逻辑表,以是现实上通过第三方毗连工具是看不到存在的实体表,比如我利用的是IDEA自带的MySQL的毗连管理工具。
- use my_database;
- INSERT INTO t_order (order_id, user_id, order_date, status) VALUES (1, 10, '2023-07-01', 'NEW');
- INSERT INTO t_order (order_id, user_id, order_date, status) VALUES (2, 20, '2023-07-02', 'SHIPPED');
- INSERT INTO t_order (order_id, user_id, order_date, status) VALUES (3, 30, '2023-07-03', 'DELIVERED');
- INSERT INTO t_order (order_id, user_id, order_date, status) VALUES (14, 40, '2023-07-04', 'RETURNED');
复制代码 运行日记:
- [INFO ] 2024-07-25 15:43:21.874 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: /* ApplicationName=IntelliJ IDEA 2024.1 */ INSERT INTO t_order (order_id, user_id, order_date, status) VALUES (1, 10, '2023-07-01', 'NEW')
- [INFO ] 2024-07-25 15:43:21.874 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_216_master ::: /* ApplicationName=IntelliJ IDEA 2024.1 */ INSERT INTO t_order_1 (order_id, user_id, order_date, status) VALUES (1, 10, '2023-07-01', 'NEW')
- 解释:分片规则根据 user_id 的值对3取模,决定将数据分片到哪个数据库, 根据 order_id 的值对2取模,决定将数据分片到哪个表。数据库取模顺序:77=>0,216=>1,18=>2
- user_id: 10%3=1,order_id: 1%2=1,所以分片到第2个数据库ds_216_master,t_order_1表。
- [INFO ] 2024-07-25 15:43:21.976 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: /* ApplicationName=IntelliJ IDEA 2024.1 */ INSERT INTO t_order (order_id, user_id, order_date, status) VALUES (2, 20, '2023-07-02', 'SHIPPED')
- [INFO ] 2024-07-25 15:43:21.976 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_18_master ::: /* ApplicationName=IntelliJ IDEA 2024.1 */ INSERT INTO t_order_0 (order_id, user_id, order_date, status) VALUES (2, 20, '2023-07-02', 'SHIPPED')
- 解释:分片规则根据 user_id 的值对3取模,决定将数据分片到哪个数据库, 根据 order_id 的值对2取模,决定将数据分片到哪个表。数据库取模顺序:77=>0,216=>1,18=>2
- user_id: 20%3=2,order_id: 2%2=0,所以分片到第3个数据库ds_18_master,t_order_0表。
- [INFO ] 2024-07-25 15:43:22.108 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: /* ApplicationName=IntelliJ IDEA 2024.1 */ INSERT INTO t_order (order_id, user_id, order_date, status) VALUES (3, 30, '2023-07-03', 'DELIVERED')
- [INFO ] 2024-07-25 15:43:22.108 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_77_master ::: /* ApplicationName=IntelliJ IDEA 2024.1 */ INSERT INTO t_order_1 (order_id, user_id, order_date, status) VALUES (3, 30, '2023-07-03', 'DELIVERED')
- 解释:分片规则根据 user_id 的值对3取模,决定将数据分片到哪个数据库, 根据 order_id 的值对2取模,决定将数据分片到哪个表。数据库取模顺序:77=>0,216=>1,18=>2
- user_id: 30%3=0,order_id: 3%2=1,所以分片到第1个数据库ds_77_master,t_order_1表。
- [INFO ] 2024-07-25 15:43:22.248 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: /* ApplicationName=IntelliJ IDEA 2024.1 */ INSERT INTO t_order (order_id, user_id, order_date, status) VALUES (14, 40, '2023-07-04', 'RETURNED')
- [INFO ] 2024-07-25 15:43:22.248 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_216_master ::: /* ApplicationName=IntelliJ IDEA 2024.1 */ INSERT INTO t_order_0 (order_id, user_id, order_date, status) VALUES (14, 40, '2023-07-04', 'RETURNED')
- 解释:分片规则根据 user_id 的值对3取模,决定将数据分片到哪个数据库, 根据 order_id 的值对2取模,决定将数据分片到哪个表。数据库取模顺序:77=>0,216=>1,18=>2
- user_id: 40%3=1,order_id: 14%2=0,所以分片到第2个数据库ds_216_master,t_order_0表。
复制代码 2.18 读写分离验证
- 运行日志:
- [INFO ] 2024-07-25 15:57:22.968 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: /* ApplicationName=IntelliJ IDEA 2024.1 */ select *from t_order
- [INFO ] 2024-07-25 15:57:22.969 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_77_slave ::: /* ApplicationName=IntelliJ IDEA 2024.1 */ select *from t_order_0 UNION ALL /* ApplicationName=IntelliJ IDEA 2024.1 */ select *from t_order_1
- [INFO ] 2024-07-25 15:57:22.969 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_216_slave ::: /* ApplicationName=IntelliJ IDEA 2024.1 */ select *from t_order_0 UNION ALL /* ApplicationName=IntelliJ IDEA 2024.1 */ select *from t_order_1
- [INFO ] 2024-07-25 15:57:22.973 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_18_slave ::: /* ApplicationName=IntelliJ IDEA 2024.1 */ select *from t_order_0 UNION ALL /* ApplicationName=IntelliJ IDEA 2024.1 */ select *from t_order_1
复制代码
3. 总结
Ubtun24.04 TLS,JDK8,shardingsphere proxy/5.5.0,MySQL Connector/J 8.0.27,仅供学习交流利用。
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。 |