# ShardingSphere-demo **Repository Path**: code-demo-space/sharding-sphere-demo ## Basic Information - **Project Name**: ShardingSphere-demo - **Description**: Apache ShardingSphere使用Demo - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2021-04-23 - **Last Updated**: 2021-08-31 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # sharding-poxy-lenove 本项目中，各个模块的描述如下： > - CustomeAlgorithm：自定义的分片算法模块 > - sharding-proxy-bootstrap：重写Sharding-Proxy中的sharding-proxy-bootstrap模块 > - sharding-proxy-data：现有环境的Sharding-proxy配置 ## Sharding-proxy安装示例 1. 拉取镜像 ``` docker pull apache/sharding-proxy ``` 2. 下载压缩包 ```shell https://ftp.wayne.edu/apache/shardingsphere/4.1.1/apache-shardingsphere-4.1.1-sharding-proxy-bin.tar.gz tar -xvf apache-shardingsphere-4.1.1-sharding-proxy-bin.tar.gz cd apache-shardingsphere-4.1.1-sharding-proxy-bin cd lib mkdir -p /data/sharding-proxy/lib/ cp * /data/sharding-proxy/lib/ cd /data/sharding-proxy/lib rm sharding-proxy-bootstrap-4.1.1.jar ``` 3. 配置server.yaml ```shell mkdir -p /data/sharding-proxy/config cd /data/sharding-proxy/config vi server.yaml ``` 添加如下内容： ```yaml authentication: users: a_appconnect: password: 9c#ewLKe authorizedSchemas: upsell_proxy props: max.connections.size.per.query: 1 acceptor.size: 16 # The default value is available processors count * 2. executor.size: 16 # Infinite by default. proxy.frontend.flush.threshold: 128 # The default value is 128. proxy.transaction.type: LOCAL proxy.opentracing.enabled: false proxy.hint.enabled: false query.with.cipher.column: true sql.show: true allow.range.query.with.inline.sharding: false ``` 4. 配置 config-sharding.yaml ```shell mkdir -p /data/sharding-proxy/config cd /data/sharding-proxy/config vi config-sharding.yaml ``` 内容如下： ```yaml schemaName: upsell_proxy dataSources: ds_0: url: jdbc:postgresql://10.122.64.93:5432/upsell?useUnicode=true&characterEncoding=UTF-8&useSSL=false username: a_appconnect password: 9c#ewLKe connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 shardingRule: tables: idl_xaas_sales_cdl_act: actualDataNodes: ds_0.idl_xaas_sales_cdl_act tableStrategy: standard: shardingColumn: input_time preciseAlgorithmClassName: com.lenove.sharding.proxy.algorithm.HalfYearAlgorithm bindingTables: - idl_xaas_sales_cdl_act defaultDatabaseStrategy: none: defaultTableStrategy: none: ``` 5. 上传建表语句，如：ds_0.idl_xaas_sales_cdl_act.sql ```shell mkdir -p /data/sharding-proxy/config/sql cd /data/sharding-proxy/config/sql ##sql语句上传到该路径下 ``` SQL内容示例： ```sql CREATE TABLE IF NOT EXISTS "public"."idl_xaas_sales_cdl_act" ( "id" varchar(64) NOT NULL, "fiscal_year_period" varchar(10), "fiscal_year" varchar(10), "fiscal_period" varchar(10), "profit_center" varchar(255), "profit_center_doc" varchar(255), "profit_center_doc_item" varchar(255), "company_code" varchar(255), "business_area" varchar(255), "account" varchar(255), "z_acitivty" varchar(255), "local_amount" varchar(255), "local_currency" varchar(255), "credit" varchar(255), "debit" varchar(255), "usd_amount" varchar(255), "quantity" int8, "created_date" varchar(255), "created_time" varchar(255), "text" varchar(255), "posting_date" varchar(255), "billing_number" varchar(255), "billing_item_number" varchar(255), "reference_procedure" varchar(255), "plant" varchar(255), "service_material" varchar(255), "cost_center" varchar(255), "vendor" varchar(255), "purchase_number" varchar(255), "purchase_item_number" varchar(255), "so_number" varchar(255), "so_item_number" varchar(255), "moving_type" varchar(255), "document_type" varchar(255), "zsale_item" varchar(255), "sales_doc" varchar(255), "sales_doc_item" varchar(255), "contract_start_date" varchar(255), "contract_end_date" varchar(255), "sales_office" varchar(255), "sales_org" varchar(255), "distribution_channel" int8, "division" varchar(255), "so_type" varchar(255), "so_created_date" varchar(255), "input_time" varchar(20), CONSTRAINT "idl_xaas_sales_cdl_act_pkey" PRIMARY KEY ("id") ); ``` 6. 上传自定义算法包和重写的sharding-proxy-bootstrap包 ```shell mkdir -p /data/sharding-proxy/ext-lib cd /data/sharding-proxy/ext-lib ## 这两个jar包放到这个路径下 ``` 7. 启动容器 ```shell docker run -d --name sharding-proxy -v /data/sharding-proxy/conf:/opt/sharding-proxy/conf -v /data/sharding-proxy/ext-lib:/opt/sharding-proxy/ext-lib -v /data/sharding-proxy/lib:/opt/sharding-proxy/lib -e PORT=3308 -p 13308:3308 apache/sharding-proxy:latest ``` 8. 查看启动日志 ```shell docker logs -f sharding-proxy ``` 管理命令： ``` docker start sharding-proxy docker stop sharding-proxy docker restart sharding-proxy ``` 9. 测试代理数据库的连接 ```shell psql -U a_appconnect -h 10.122.30.61 -p 13308 -d upsell_proxy ``` 测试查询数据： ```sql select * from idl_xaas_sales_cdl_act; ``` 测试插入数据： ```sql INSERT INTO "idl_xaas_sales_cdl_act"("id", "fiscal_year_period", "fiscal_year", "fiscal_period", "profit_center", "profit_center_doc", "profit_center_doc_item", "company_code", "business_area", "account", "z_acitivty", "local_amount", "local_currency", "credit", "debit", "usd_amount", "quantity", "created_date", "created_time", "text", "posting_date", "billing_number", "billing_item_number", "reference_procedure", "plant", "service_material", "cost_center", "vendor", "purchase_number", "purchase_item_number", "so_number", "so_item_number", "moving_type", "document_type", "zsale_item", "sales_doc", "sales_doc_item", "contract_start_date", "contract_end_date", "sales_office", "sales_org", "distribution_channel", "division", "so_type", "so_created_date", "input_time") VALUES ('100001', '202009', '2020', '09', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', 10001, '20201021', '0950', '10001', '20201021', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '202010210951', '2021102', '10001', '10001', 10001, '10001', '10001', '20211021', '2020-09-17 12:06:00'); INSERT INTO "idl_xaas_sales_cdl_act"("id", "fiscal_year_period", "fiscal_year", "fiscal_period", "profit_center", "profit_center_doc", "profit_center_doc_item", "company_code", "business_area", "account", "z_acitivty", "local_amount", "local_currency", "credit", "debit", "usd_amount", "quantity", "created_date", "created_time", "text", "posting_date", "billing_number", "billing_item_number", "reference_procedure", "plant", "service_material", "cost_center", "vendor", "purchase_number", "purchase_item_number", "so_number", "so_item_number", "moving_type", "document_type", "zsale_item", "sales_doc", "sales_doc_item", "contract_start_date", "contract_end_date", "sales_office", "sales_org", "distribution_channel", "division", "so_type", "so_created_date", "input_time") VALUES ('100002', '202009', '2020', '09', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', 10001, '20201021', '0950', '10001', '20201021', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '202010210951', '2021102', '10001', '10001', 10001, '10001', '10001', '20211021', '2020-02-17 02:06:00'); INSERT INTO "idl_xaas_sales_cdl_act"("id", "fiscal_year_period", "fiscal_year", "fiscal_period", "profit_center", "profit_center_doc", "profit_center_doc_item", "company_code", "business_area", "account", "z_acitivty", "local_amount", "local_currency", "credit", "debit", "usd_amount", "quantity", "created_date", "created_time", "text", "posting_date", "billing_number", "billing_item_number", "reference_procedure", "plant", "service_material", "cost_center", "vendor", "purchase_number", "purchase_item_number", "so_number", "so_item_number", "moving_type", "document_type", "zsale_item", "sales_doc", "sales_doc_item", "contract_start_date", "contract_end_date", "sales_office", "sales_org", "distribution_channel", "division", "so_type", "so_created_date", "input_time") VALUES ('200001', '202009', '2020', '09', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', 10001, '20201021', '0950', '10001', '20201021', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '202010210951', '2021102', '10001', '10001', 10001, '10001', '10001', '20211021', '2021-05-17 06:06:00'); INSERT INTO "idl_xaas_sales_cdl_act"("id", "fiscal_year_period", "fiscal_year", "fiscal_period", "profit_center", "profit_center_doc", "profit_center_doc_item", "company_code", "business_area", "account", "z_acitivty", "local_amount", "local_currency", "credit", "debit", "usd_amount", "quantity", "created_date", "created_time", "text", "posting_date", "billing_number", "billing_item_number", "reference_procedure", "plant", "service_material", "cost_center", "vendor", "purchase_number", "purchase_item_number", "so_number", "so_item_number", "moving_type", "document_type", "zsale_item", "sales_doc", "sales_doc_item", "contract_start_date", "contract_end_date", "sales_office", "sales_org", "distribution_channel", "division", "so_type", "so_created_date", "input_time") VALUES ('200002', '202009', '2020', '09', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', 10001, '20201021', '0950', '10001', '20201021', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '10001', '202010210951', '2021102', '10001', '10001', 10001, '10001', '10001', '20211021', '2021-11-17 16:06:00'); ``` ### 连接环境 > 主机：10.122.30.61 > 端口：13308 > 数据库：upsell_proxy > 用户名：a_appconnect > 密码：9c#ewLKe