求解,在5.1.3版本下,如果有存在多个库的情况下,在其中一个库中使用DistSQL配置分片规则后,会出现其他库消失的情况,重启后又恢复正常,这是什么情况?
请提供一下复现的配置和步骤呢,方便调查问题。
mysql> ADD RESOURCE ds_0 (
-> HOST=127.0.0.1,
-> PORT=3306,
-> DB=demo_ds_0,
-> USER=root,
-> PASSWORD=123456
-> ), ds_1(
-> HOST=127.0.0.1,
-> PORT=3306,
-> DB=demo_ds_1,
-> USER=root,
-> PASSWORD=123456
-> );
Query OK, 0 rows affected (0.38 sec)
mysql> CREATE SHARDING TABLE RULE t_order (
-> RESOURCES(ds_0, ds_1),
-> SHARDING_COLUMN=order_id,TYPE(NAME=MOD,PROPERTIES("sharding-count"=4))
-> );
Query OK, 0 rows affected (0.17 sec)
mysql> show databases;
+--------------------+
| schema_name |
+--------------------+
| information_schema |
| performance_schema |
| test |
| sharding_db |
| mysql |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> CREATE SHARDING TABLE RULE t_order_item (
-> RESOURCES(ds_0, ds_1),
-> SHARDING_COLUMN=order_id,TYPE(NAME=MOD,PROPERTIES("sharding-count"=4))
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> show databases;
+--------------------+
| schema_name |
+--------------------+
| information_schema |
| performance_schema |
| test |
| sharding_db |
| mysql |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> create sharding binding table rules(t_order,t_order_item);
Query OK, 0 rows affected (0.08 sec)
mysql> show databases;
+--------------------+
| schema_name |
+--------------------+
| information_schema |
| performance_schema |
| test |
| sharding_db |
| mysql |
| sys |
+--------------------+
6 rows in set (0.00 sec)
@novice-gamer 我使用 master 分支不能重现你的问题,请试试最新的 commit 呢? 或者提供更多信息,包括:
- mode 配置 (server.yaml)
- commit 版本
我重新clone的master分支最新的版本,还是出现相同的问题:
[root@localhost application]# mysql -uroot -p -P3307 -h192.168.1.4
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37-ShardingSphere-Proxy 5.1.3-SNAPSHOT-dirty-7c56088
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
Empty set (0.03 sec)
mysql> create schema bid_analyse_dev;
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+-----------------+
| schema_name |
+-----------------+
| bid_analyse_dev |
+-----------------+
1 row in set (0.00 sec)
mysql> create database bid_analyse;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+-----------------+
| schema_name |
+-----------------+
| bid_analyse_dev |
| bid_analyse |
+-----------------+
2 rows in set (0.00 sec)
mysql> use bid_analyse_dev;
Database changed
mysql> ADD RESOURCE ds_0 (
-> URL="jdbc:mysql://192.168.1.6:3306/bid_analyse_dev_0?serverTimezone=UTC&useSSL=false",
-> USER=root,
-> PASSWORD="xxxx",
-> PROPERTIES("maxPoolSize"=200,"minPoolSize"=20)
-> ),
-> ds_1 (
-> URL="jdbc:mysql://192.168.1.7:3306/bid_analyse_dev_1?serverTimezone=UTC&useSSL=false",
-> USER=root,
-> PASSWORD="xxxxxx",
-> PROPERTIES("maxPoolSize"=200,"minPoolSize"=20)
-> ),
-> ds_2 (
-> URL="jdbc:mysql://192.168.1.8:3306/bid_analyse_dev_2?serverTimezone=UTC&useSSL=false",
-> USER=root,
-> PASSWORD="xxxxxx",
-> PROPERTIES("maxPoolSize"=200,"minPoolSize"=20)
-> ),
-> ds_3 (
-> URL="jdbc:mysql://192.168.1.9:3306/bid_analyse_dev_3?serverTimezone=UTC&useSSL=false",
-> USER=root,
-> PASSWORD="xxxxxxx",
-> PROPERTIES("maxPoolSize"=200,"minPoolSize"=20)
-> );
Query OK, 0 rows affected (1.04 sec)
mysql> show databases;
+-----------------+
| schema_name |
+-----------------+
| bid_analyse |
| bid_analyse_dev |
+-----------------+
2 rows in set (0.00 sec)
mysql> CREATE SHARDING ALGORITHM bid_analyse_dev_csys_company_buffer_info_33_inline (
-> TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=4)
-> ));
Query OK, 0 rows affected (0.29 sec)
mysql> CREATE SHARDING ALGORITHM csys_company_buffer_info_33_inline (
-> TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=2)
-> ));
Query OK, 0 rows affected (0.12 sec)
mysql> CREATE SHARDING TABLE RULE csys_company_buffer_info_33 (
-> DATANODES("ds_${0..3}.csys_company_buffer_info_33_${0..1}"),
-> DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=tag,SHARDING_ALGORITHM=bid_analyse_dev_csys_company_buffer_info_33_inline),
-> TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=company_id,SHARDING_ALGORITHM=csys_company_buffer_info_33_inline)
-> );
Query OK, 0 rows affected (0.89 sec)
mysql> CREATE SHARDING BINDING TABLE RULES (csys_company_buffer_info_33);
Query OK, 0 rows affected (0.10 sec)
mysql> show databases;
+-----------------+
| schema_name |
+-----------------+
| bid_analyse_dev |
+-----------------+
1 row in set (0.00 sec)
server.yaml:
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: sharding_proxy_dev
server-lists: zk_host_1:2181,zk_host_2:2181,zk_host_3:2181,zk_host_4:2181,zk_host_5:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
overwrite: false
rules:
- !AUTHORITY
users:
- root@%:root
- sharding@:sharding
provider:
type: ALL_PERMITTED
# - !TRANSACTION
# defaultType: XA
# providerType: Atomikos
# - !SQL_PARSER
# sqlCommentParseEnabled: true
# sqlStatementCache:
# initialCapacity: 2000
# maximumSize: 65535
# parseTreeCache:
# initialCapacity: 128
# maximumSize: 1024
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-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
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
# and block other clients if client connections are more than `proxy-frontend-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
# Available proxy backend driver type: JDBC (default), ExperimentalVertx
proxy-backend-driver-type: JDBC
proxy-mysql-default-version: 5.7.37 # In the absence of schema name, the default version will be used.
proxy-default-port: 3307 # Proxy default port.
proxy-netty-backlog: 1024 # Proxy netty backlog.
没有,我只是设置了这个用户允许访问指定的库
了解,这个问题和 GitHub 上一个 issue 相似,应该在 master 分支已经修复了,可以从 master 构建并验证。
mysql> ADD RESOURCE ds_0 (
→ HOST=127.0.0.1,
→ PORT=3306,
→ DB=demo_ds_0,
→ USER=root,
→ PASSWORD=123456
→ ), ds_1(
→ HOST=127.0.0.1,
→ PORT=3306,
→ DB=demo_ds_1,
→ USER=root,
→ PASSWORD=123456
→ );
[/quote]
这一步添加数据源只是临时的呀,重启了就没了,没有写入到配置里去
想要对配置进行持久化,需要先将 Proxy 配置为集群模式,这样配置动态更新的配置会持久化到治理中心(zk 或 etcd)中。
zk,怎么配置呀,集群配好了,但不会:统一配置管理!有没有什么讲解的网页,找了找都是配集群的后面的就没了!
zk 配置在 server.yaml 的 mode 配置中:
#mode:
# type: Cluster
# repository:
# type: ZooKeeper
# props:
# namespace: governance_ds
# server-lists: localhost:2181
# retryIntervalMilliseconds: 500
# timeToLiveSeconds: 60
# maxRetries: 3
# operationTimeoutMilliseconds: 500
在集群模式下,用 DistSQL 操作即可动态管理规则配置,变动会持久化到 zk 节点里。
mysql> ADD RESOURCE ds_0 (
→ HOST=127.0.0.1,
→ PORT=3306,
→ DB=demo_ds_0,
→ USER=root,
→ PASSWORD=123456
→ ), ds_1(
→ HOST=127.0.0.1,
→ PORT=3306,
→ DB=demo_ds_1,
→ USER=root,
→ PASSWORD=123456
→ );
Query OK, 0 rows affected (0.38 sec)
REGISTER STORAGE UNIT ds_0 (
HOST=“127.0.0.1”,
PORT=3306,
DB=“db_user”,
USER=“root”,
PASSWORD=“root123”
);
REGISTER STORAGE UNIT ds_1 (
HOST=“172.30.1.18”,
PORT=3306,
DB=“db_user”,
USER=“root”,
PASSWORD=“root123”
);
这两个添加数据源的方式有什么不同?
收到,非常 谢谢