young138120 commented on issue #25260:
URL:
https://github.com/apache/shardingsphere/issues/25260#issuecomment-1517456269
server.yaml
```yaml
######################################################################################################
#
# If you want to configure governance, authorization and proxy properties,
please refer to this file.
#
######################################################################################################
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: shardingsphere_proxy_jn
server-lists: 1.1.1.1:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
# overwrite: true
#这里配置的是客户端链接proxy的授权信息，可以针对用户控制访问后面的数据源信息
authority:
users:
- user: dw_admin@%
password: Jn@123!@#
privilege:
type: ALL_PERMITTED
# 暂时用xa试下情况
transaction:
defaultType: XA
providerType: Atomikos
sqlParser:
# 是否解析 SQL 注释
sqlCommentParseEnabled: false
# SQL 语句本地缓存配置项
sqlStatementCache:
# 本地缓存初始容量
initialCapacity: 2000
# 本地缓存最大容量
maximumSize: 65535
# 解析树本地缓存配置项
parseTreeCache:
initialCapacity: 128
maximumSize: 1024
#
logging:
loggers:
- loggerName: ShardingSphere-SQL
additivity: true
level: DEBUG
props:
enable: true
#
props:
system-log-level: DEBUG
#一次查询请求在每个数据库实例中所能使用的最大连接数。
max-connections-size-per-query: 1
# 用于设置任务处理线程池的大小。每个 ShardingSphereDataSource 使用一个独立的线程池，同一个 JVM
的不同数据源不共享线程池。
kernel-executor-size: 16
#在 ShardingSphere-Proxy 中设置传输数据条数的 IO 刷新阈值
proxy-frontend-flush-threshold: 128
proxy-hint-enabled: false
# sql-show is the same as props in logger ShardingSphere-SQL, and its
priority is lower than logging rule
sql-show: true
# 在程序启动和更新时，是否检查分片元数据的结构一致性。
check-table-metadata-enabled: false
# Proxy 后端与数据库交互的每次获取数据行数（使用游标的情况下）。数值增大可能会增加 ShardingSphere Proxy
的内存使用。默认值为 -1，代表设置为 JDBC 驱动的最小值。
proxy-backend-query-fetch-size: -1
# Proxy 前端 Netty 线程池线程数量，默认值 0 代表使用 Netty 默认值。netty默认值为核数的2倍
proxy-frontend-executor-size: 0
# 可选选项：OLAP、OLTP。OLTP 选项可能会减少向客户端写入数据包的时间开销，但如果客户端连接数超过
proxy-frontend-executor-size，尤其是执行慢 SQL 时，它可能会增加 SQL 执行的延迟甚至阻塞其他客户端的连接。
proxy-backend-executor-suitable: OLAP
# 允许连接 Proxy 的最大客户端数量，默认值 0 代表不限制。
proxy-frontend-max-connections: 0
# 联邦查询执行器类型，包括：NONE，ORIGINAL，ADVANCED。
sql-federation-type: ADVANCED
# Proxy 通过配置文件指定 MySQL 的版本号，默认版本：5.7.22。
proxy-mysql-default-version: 5.7.22
proxy-default-port: 3307
proxy-netty-backlog: 1024
cdc-server-port: 33071
```
config-sharding.yaml
```yaml
######################################################################################################
#
# Here you can configure the rules for the proxy.
# This example is configuration of sharding rule.
#
######################################################################################################
databaseName: jn_proxy_db
dataSources:
ds_2019:
url:
jdbc:mysql://1.1.1.1:3306/prod_2019?characterEncoding=UTF-8&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_2022:
url:
jdbc:mysql://1.1.1.1:3306/prod_2022?characterEncoding=UTF-8&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_2020:
url:
jdbc:mysql://2.2.2.2:3306/prod_2020?characterEncoding=UTF-8&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_2021:
url:
jdbc:mysql://2.2.2.2:3306/prod_2021?characterEncoding=UTF-8&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_now:
url: jdbc:mysql://3.3.3.3:3306/prod?characterEncoding=UTF-8&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
or_order_state_record:
actualDataNodes:
ds_${2019..2022}.or_order_state_record,ds_now.or_order_state_record
or_order_task_state :
actualDataNodes:
ds_${2019..2022}.or_order_task_state,ds_now.or_order_task_state
or_task_node:
actualDataNodes: ds_${2019..2022}.or_task_node,ds_now.or_task_node
or_order_task:
actualDataNodes: ds_${2019..2022}.or_order_task,ds_now.or_order_task
or_order_other:
actualDataNodes: ds_${2019..2022}.or_order_other,ds_now.or_order_other
databaseStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: order_id_fetch
or_handle_user:
actualDataNodes: ds_${2019..2022}.or_handle_user,ds_now.or_handle_user
databaseStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: order_id_fetch
or_rescue_order:
actualDataNodes:
ds_${2019..2022}.or_rescue_order,ds_now.or_rescue_order
databaseStrategy:
standard:
shardingColumn: book_time
shardingAlgorithmName: time_range
# tableStrategy:
# standard:
# shardingColumn: order_id
# shardingAlgorithmName: t_order_inline
# keyGenerateStrategy:
# column: order_id
# keyGeneratorName: snowflake
# auditStrategy:
# auditorNames:
# - sharding_key_required_auditor
# allowHintDisable: true
bindingTables:
-
or_rescue_order,or_order_state_record,or_order_task_state,or_task_node,or_order_task,or_order_other,or_handle_user
broadcastTables:
- cs_customer
defaultDatabaseStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: order_id_fetch
defaultTableStrategy:
none:
# defaultAuditStrategy:
# auditorNames:
# - sharding_key_required_auditor
# allowHintDisable: true
shardingAlgorithms:
# database_inline:
# type: INLINE
# props:
# algorithm-expression: ds_${user_id % 2}
time_range:
type: INTERVAL
props:
datetime-pattern: "yyyy-MM-dd HH:mm:ss" # 分片字段格式
datetime-lower: "2019-01-31 00:00:00" # 范围下限
datetime-upper: "2022-12-31 23:59:59" # 范围上限
sharding-suffix-pattern: "yyyy" # 分片名后缀，可以是MM，yyyyMMdd等。
datetime-interval-amount: 1 # 分片间隔，这里指一个月
datetime-interval-unit: "Years" # 分片间隔单位
order_id_fetch:
type: JN_ORDERID_SHARDING
# keyGenerators:
# snowflake:
# type: SNOWFLAKE
# auditors:
# sharding_key_required_auditor:
# type: DML_SHARDING_CONDITIONS
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
