shardingsphere-proxy 实现mysql单库分表

MonkeyKing.sun

于 2023-06-28 16:21:01 发布

阅读量456

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/sunyuhua_keyboard/article/details/131435453

版权

1、docker安装mysql5.7版本

拉取mysql的镜像

docker pull mysql:5.7

创建mysql的配置目录，日志目录，数据存储的目录

mkdir -p /home/sunyuhua/docker/mysql/conf
mkdir -p /home/sunyuhua/docker/mysql/logs
mkdir -p /home/sunyuhua/docker/mysql/data

启动mysql的容器

docker run -p 3306:3306 --name mysql  --restart=always -v /home/sunyuhua/docker/mysql/conf:/etc/mysql/conf.d -v /home/sunyuhua/docker/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

修改 mysql的配置

/home/sunyuhua/docker/mysql/conf/my.cnf

[client]
port =3306
socket =/var/run/mysqld/mysqld.sock
[mysql]
socket =/var/run/mysqld/mysqld.sock
[mysql_safe]
socket =/var/run/mysqld/mysqld.sock
nice =0


[mysqld]
server-id = 1
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
log-bin=mysql-bin
binlog-format=row
binlog-row-image=full
max_connections=600

重新mysql

docker restart mysql

链接mysql，测试

docker exec -it mysql /bin/bash

mysql -uroot -proot

2、docker 安装zookeeper

拉取zookeeper镜像

docker pull zookeeper

运行zookeeper

docker run -d -e TZ="Asia/Shanghai"  -p 2181:2181 -v /home/sunyuhua/docker/zookeeper:/data --name zookeeper --restart always zookeeper

检查zookeeper安装成功

docker exec -it zookeeper bash

./bin/zkCli.sh

3、docker安装shardingsphere-proxy

docker run -d --name tmp --entrypoint=bash apache/shardingsphere-proxy

docker cp tmp:/opt/shardingsphere-proxy/conf /home/sunyuhua/docker/shardingsphere-proxy/conf

docker rm tmp

启动shardingsphere-proxy

docker run -d -v /home/sunyuhua/docker/shardingsphere-proxy/conf:/opt/shardingsphere-proxy/conf -v /home/sunyuhua/docker/shardingsphere-proxy/ext-lib:/opt/shardingsphere-proxy/ext-lib -v /home/sunyuhua/docker/shardingsphere-proxy/logs:/opt/shardingsphere-proxy/logs -e PORT=3308 -p3308:3308  --restart always  --name sharding-proxy apache/shardingsphere-proxy

验证是否安装成功

 docker exec -it sharding-proxy /bin/bash

4、配置分库分表的准备mysql的表结构

create database demo_ds;

show databases;

use demo_ds;


CREATE TABLE `t_order_0` (
  `order_id` bigint(20) unsigned NOT NULL COMMENT '主键ID',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `total_money` int(10) unsigned NOT NULL COMMENT '订单总金额',
  PRIMARY KEY (`order_id`),
  KEY `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单总表';


CREATE TABLE `t_order_1` (
  `order_id` bigint(20) unsigned NOT NULL COMMENT '主键ID',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `total_money` int(10) unsigned NOT NULL COMMENT '订单总金额',
  PRIMARY KEY (`order_id`),
  KEY `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单总表';




CREATE TABLE `t_order_item_0` (
  `order_item_id` bigint(20) unsigned NOT NULL COMMENT '子订单ID',
  `order_id` bigint(20) unsigned NOT NULL COMMENT '主键ID',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `money` int(10) unsigned NOT NULL COMMENT '子订单金额',
  PRIMARY KEY (`order_item_id`),
  KEY `idx_order_id` (`order_id`) USING BTREE,
  KEY `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单子表';




CREATE TABLE `t_order_item_1` (
  `order_item_id` bigint(20) unsigned NOT NULL COMMENT '子订单ID',
  `order_id` bigint(20) unsigned NOT NULL COMMENT '主键ID',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `money` int(10) unsigned NOT NULL COMMENT '子订单金额',
  PRIMARY KEY (`order_item_id`),
  KEY `idx_order_id` (`order_id`) USING BTREE,
  KEY `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单子表';

5、配置shardingsphere-proxy分库分表的策略

修改server.xml文件，文件地址：/home/sunyuhua/docker/shardingsphere-proxy/conf

mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: governance_ds
      server-lists: 127.0.0.1:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
#  overwrite: false
#
rules:
  - !AUTHORITY
    users:
      - root@%:root
      - sharding@:sharding
    provider:
      type: ALL_PRIVILEGES_PERMITTED
#  - !TRANSACTION
#    defaultType: XA
#    providerType: Atomikos

props:
#  max-connections-size-per-query: 1
#  kernel-executor-size: 16  # Infinite by default.
#  proxy-frontend-flush-threshold: 128  # The default value is 128.
#  proxy-opentracing-enabled: false
#  proxy-hint-enabled: false
  sql-show: true
#  check-table-metadata-enabled: false
#  show-process-list-enabled: false
#    # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
#    # The default value is -1, which means set the minimum value for different JDBC drivers.
#  proxy-backend-query-fetch-size: -1
#  check-duplicate-table-enabled: false
#  sql-comment-parse-enabled: false
#  proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
#    # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
#    # if client connections are more than proxy-frontend-netty-executor-size, especially executing slow SQL.
#  proxy-backend-executor-suitable: OLAP
#  proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
#  sql-federation-enabled: false

修改config-sharding.yaml 文件，文件地址/home/sunyuhua/docker/shardingsphere-proxy/conf


schemaName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  ds_1:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_inline
      keyGenerateStrategy:
        column: order_id
        keyGeneratorName: snowflake
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item_${0..1}
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: t_order_item_inline
      keyGenerateStrategy:
        column: order_item_id
        keyGeneratorName: snowflake
  bindingTables:
    - t_order,t_order_item
  defaultDatabaseStrategy:
    standard:
      shardingColumn: user_id
      shardingAlgorithmName: database_inline
  defaultTableStrategy:
    none:
  
  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
    t_order_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_${order_id % 2}
    t_order_item_inline:
      type: INLINE
      props:
        algorithm-expression: t_order_item_${order_id % 2}
  
  keyGenerators:
    snowflake:
      type: SNOWFLAKE
      props:
        worker-id: 123

重新启动shardingsphere

docker restart sharding-proxy

检查shardingsphere-proxy是不是启动

docker logs sharding-proxy

或者查看日志

 /opt/shardingsphere-proxy/logs/stdout.log

[INFO ] 2023-06-28 05:46:54.015 [main-SendThread(host.docker.internal:2181)] org.apache.zookeeper.ClientCnxn - Session establishment complete on server host.docker.internal/192.168.65.2:2181, session id = 0x100000045ca0001, negotiated timeout = 40000
[INFO ] 2023-06-28 05:46:54.024 [main-EventThread] o.a.c.f.state.ConnectionStateManager - State change: CONNECTED
[INFO ] 2023-06-28 05:46:54.052 [main-EventThread] o.a.c.framework.imps.EnsembleTracker - New config event received: {}
[INFO ] 2023-06-28 05:46:54.052 [main-EventThread] o.a.c.framework.imps.EnsembleTracker - New config event received: {}
Thanks for using Atomikos! Evaluate http://www.atomikos.com/Main/ExtremeTransactions for advanced features and professional support
or register at http://www.atomikos.com/Main/RegisterYourDownload to disable this message and receive FREE tips & advice
[INFO ] 2023-06-28 05:46:56.075 [main] o.apache.curator.utils.Compatibility - Using org.apache.zookeeper.server.quorum.MultipleAddresses
[INFO ] 2023-06-28 05:46:56.093 [main] o.a.s.p.i.BootstrapInitializer - Database name is `MySQL`, version is `5.7.42-log`
[INFO ] 2023-06-28 05:46:56.283 [main] o.a.s.p.frontend.ShardingSphereProxy - ShardingSphere-Proxy start success

6、插入和查询数据，检查分库分表策略是不是生效

INSERT INTO t_order(user_id,total_money) VALUES(1001,1000);
INSERT INTO t_order(user_id,total_money) VALUES(1002,1000);
INSERT INTO t_order(user_id,total_money) VALUES(1003,1000);
INSERT INTO t_order(user_id,total_money) VALUES(1004,1000);
INSERT INTO t_order(user_id,total_money) VALUES(1005,1000);
INSERT INTO t_order(user_id,total_money) VALUES(1006,1000);
INSERT INTO t_order(user_id,total_money) VALUES(1007,1000);
INSERT INTO t_order(user_id,total_money) VALUES(1008,1000);