You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/09/08 07:26:44 UTC
[GitHub] [shardingsphere] tristaZero opened a new issue #7324: [New feature] How to use RDL to create Sharding databases and tables?
tristaZero opened a new issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324
Hi, community,
#6466 mainly finished the RDL new feature for 5.x release. This issue introduces how to use RDL to create Sharding DBs and tables for your production or test environment.
### What's the RDL?
RDL means rule definition language to create your distributed DBs, Tables by SQL. In other words, this is a specific SQLs for ShardingSphere to configure Sharding tables.
### Pre-work
1. Start the service of MySQL instances
2. Create MySQL databases (Viewed as the resources for ShardingProxy)
3. Create a role or user with creating privileges for ShardingProxy
4. Start the service of Zookeeper (For persisting configuration)
### Initialize ShardingProxy
1. Add `governance` and `authentication` setting item to the `server.yaml` (Please refer to the example in this file)
2. Start the ShardingProxy ([Instruction](https://shardingsphere.apache.org/document/current/en/quick-start/shardingsphere-proxy-quick-start/))
### Create Sharding DBs and Tables
1. Connect to ShardingProxy
2. Create a sharding database
```SQL
create database ss1;
```
3. Use the sharding database
```SQL
use ss1;
```
2. Add database resources for this sharding DB
```SQL
create datasources (
ds0=127.0.0.1:3306:demo_ds_2:root,
ds1=127.0.0.1:3306:demo_ds_3:root)
```
3. Create Sharding rules for sharding tables
```SQL
create shardingrules (
t_order=hash_mod(order_id, 4),
t_item=mod(item_id, 2)
)
```
Here `hash_mode` and `mod` are auto sharding algorithm. Please visit [auto-sharding-algorithm](https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/sharding/) to learn more.
4. Create tables
```SQL
CREATE TABLE `t_order` (
`order_id` int NOT NULL,
`user_id` int NOT NULL,
`status` varchar(45) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
CREATE TABLE `t_item` (
`item_id` int NOT NULL,
`order_id` int NOT NULL,
`user_id` int NOT NULL,
`status` varchar(45) DEFAULT NULL,
PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lidonghai11 commented on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
lidonghai11 commented on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-690892484
zookeeper-3.4.5 can't work with the current latest proxy. But zookeeper-3.6.2 worked well. I suggest that you should investigate the reason why zookeeper-3.4.5 cant work with current proxy.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lidonghai11 edited a comment on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
lidonghai11 edited a comment on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-690143541
I did the 'Pre-work'. and did the 'initialize ShardingProxy'.But when I start the ShardingProxy,I cant see the port listening
on '3307',and I can't see anything error in the log.
Is there something wrong with me?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-690853904
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lidonghai11 edited a comment on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
lidonghai11 edited a comment on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-690143541
I did the 'Pre-work'. and did the 'initialize ShardingProxy'.But when I start the ShardingProxy,I cant see the port listening
on '3307',and I can't see anything error in the log.
Is there something wrong with me
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-719366485
@lidonghai11 Thanks for you continued effort for this feature. :-)
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lidonghai11 commented on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
lidonghai11 commented on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-718390503
> ```sql
> mysql> sctl:explain select * from t_order;
> +-----------------+-------------------------+
> | datasource_name | sql |
> +-----------------+-------------------------+
> | ds_0 | select * from t_order_0 |
> | ds_0 | select * from t_order_1 |
> | ds_1 | select * from t_order_0 |
> | ds_1 | select * from t_order_1 |
> +-----------------+-------------------------+
> 4 rows in set (0.07 sec)
> ```
**Thanks for adding this function, now we can see the instance where each sub-table is located**
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lidonghai11 commented on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
lidonghai11 commented on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-690892484
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lidonghai11 commented on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
lidonghai11 commented on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-690143541
I did the 'Pre-work'. and did the 'initialize ShardingProxy'.But when I start the ShardingProxy,I cant see the port listening
on '3307',and I can't see anything error in the log.
There is something wrong with my
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero closed issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-691598515
Thanks for your feedback. @lidonghai11
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lidonghai11 edited a comment on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
lidonghai11 edited a comment on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-699608723
##Some test cases
1、If I don't 'Add database resources for this sharding DB' or 'Create Sharding rules for sharding tables', when I create table,there is nothing response. Hope can get some error response. Below are my test steps：
A:NO DataSource
CREATE DATABASE ss2;
use ss2
CREATE shardingrules (
u_order=hash_mod(order_id, 8),
u_item=mod(item_id, 4)
)
CREATE TABLE `u_order` (
`order_id` int NOT NULL,
`user_id` int NOT NULL,
`status` varchar(45) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Nothing response
B:NO sharding rules
CREATE DATABASE ss6;
use ss6;
CREATE datasources (
ds0=10.222.23.144:3306:xdb_0:root:12345,
ds1=10.222.23.145:3306:xdb_1:root:12345
);
CREATE TABLE `u_order` (
`order_id` int NOT NULL,
`user_id` int NOT NULL,
`status` varchar(45) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Nothing response
2、DROP DB only removes the logic sharding schema
Hope to clear all the stuff stored in zk and backend databases,If don't ,we may get some conflict with the old tables.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-707588076
```sql
mysql> sctl:explain select * from t_order;
+-----------------+-------------------------+
| datasource_name | sql |
+-----------------+-------------------------+
| ds_0 | select * from t_order_0 |
| ds_0 | select * from t_order_1 |
| ds_1 | select * from t_order_0 |
| ds_1 | select * from t_order_1 |
+-----------------+-------------------------+
4 rows in set (0.07 sec)
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lidonghai11 commented on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
lidonghai11 commented on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-690892484
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lidonghai11 edited a comment on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
lidonghai11 edited a comment on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-718389111
> ##Some test cases
> 1、If I don't 'Add database resources for this sharding DB' or 'Create Sharding rules for sharding tables', when I create table,there is nothing response. Hope can get some error response. Below are my test steps：
> A:NO DataSource
> CREATE DATABASE ss2;
>
> use ss2
>
> CREATE shardingrules (
> u_order=hash_mod(order_id, 8),
> u_item=mod(item_id, 4)
> )
>
> CREATE TABLE `u_order` (
> `order_id` int NOT NULL,
> `user_id` int NOT NULL,
> `status` varchar(45) DEFAULT NULL,
> PRIMARY KEY (`order_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
>
> Nothing response
>
> B:NO sharding rules
> CREATE DATABASE ss6;
>
> use ss6;
>
> CREATE datasources (
> ds0=10.222.23.144:3306:xdb_0:root:12345,
> ds1=10.222.23.145:3306:xdb_1:root:12345
> );
>
> CREATE TABLE `u_order` (
> `order_id` int NOT NULL,
> `user_id` int NOT NULL,
> `status` varchar(45) DEFAULT NULL,
> PRIMARY KEY (`order_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
>
> Nothing response
>
> 2、DROP DB only removes the logic sharding schema
> Hope to clear all the stuff stored in zk and backend databases,If don't ,we may get some conflict with the old tables.
**The first item has been fixed, thanks to the community！！！**
**When the logical database is deleted, the metadata and routing rules are successfully cleared, but the real back-end tables are not cleared.**
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-690853904
@lidonghai11 Thanks for your feedback, my test worked well.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-690853904
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lidonghai11 commented on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
lidonghai11 commented on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-718389111
> ##Some test cases
> 1、If I don't 'Add database resources for this sharding DB' or 'Create Sharding rules for sharding tables', when I create table,there is nothing response. Hope can get some error response. Below are my test steps：
> A:NO DataSource
> CREATE DATABASE ss2;
>
> use ss2
>
> CREATE shardingrules (
> u_order=hash_mod(order_id, 8),
> u_item=mod(item_id, 4)
> )
>
> CREATE TABLE `u_order` (
> `order_id` int NOT NULL,
> `user_id` int NOT NULL,
> `status` varchar(45) DEFAULT NULL,
> PRIMARY KEY (`order_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
>
> Nothing response
>
> B:NO sharding rules
> CREATE DATABASE ss6;
>
> use ss6;
>
> CREATE datasources (
> ds0=10.222.23.144:3306:xdb_0:root:12345,
> ds1=10.222.23.145:3306:xdb_1:root:12345
> );
>
> CREATE TABLE `u_order` (
> `order_id` int NOT NULL,
> `user_id` int NOT NULL,
> `status` varchar(45) DEFAULT NULL,
> PRIMARY KEY (`order_id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
>
> Nothing response
>
> 2、DROP DB only removes the logic sharding schema
> Hope to clear all the stuff stored in zk and backend databases,If don't ,we may get some conflict with the old tables.
**The first item has been fixed, thanks to the community！！！**
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lidonghai11 commented on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
lidonghai11 commented on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-690892484
zookeeper-3.4.5 can't work with the current latest proxy. But zookeeper-3.6.2 worked well. I suggest that you should investigate the reason why zookeeper-3.4.5 cant work with current proxy.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lidonghai11 edited a comment on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
lidonghai11 edited a comment on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-699608723
##Some test cases
1、If I don't 'Add database resources for this sharding DB' or 'Create Sharding rules for sharding tables', when I create table,there is nothing response. Hope can get some error response. Below are my test steps：
A:NO DataSource
CREATE DATABASE ss2;
use ss2
CREATE shardingrules (
u_order=hash_mod(order_id, 8),
u_item=mod(item_id, 4)
)
CREATE TABLE `u_order` (
`order_id` int NOT NULL,
`user_id` int NOT NULL,
`status` varchar(45) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Nothing response
B:NO sharding rules
CREATE DATABASE ss6;
use ss6;
CREATE datasources (
ds0=10.222.23.144:3306:xdb_0:root:0gvzJr66iNs5,
ds1=10.222.23.145:3306:xdb_1:root:0gvzJr66iNs5
);
CREATE TABLE `u_order` (
`order_id` int NOT NULL,
`user_id` int NOT NULL,
`status` varchar(45) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Nothing response
2、DROP DB only removes the logic sharding schema
Hope to clear all the stuff stored in zk and backend databases,If don't ,we may get some conflict with the old tables.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lidonghai11 commented on issue #7324: [New feature] How to use RDL to create Sharding databases and tables online?
Posted by GitBox <gi...@apache.org>.
lidonghai11 commented on issue #7324:
URL: https://github.com/apache/shardingsphere/issues/7324#issuecomment-699608723
##Some test cases
1、If I don't 'Add database resources for this sharding DB' or 'Create Sharding rules for sharding tables', when I create table,there is nothing response. Hope can get some error response. Below are my test steps：
A:NO DataSource
CREATE DATABASE ss2;
use ss2
CREATE shardingrules (
u_order=hash_mod(order_id, 8),
u_item=mod(item_id, 4)
)
CREATE TABLE `u_order` (
`order_id` int NOT NULL,
`user_id` int NOT NULL,
`status` varchar(45) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Nothing response
B:NO sharding rules
CREATE DATABASE ss6;
use ss6;
CREATE datasources (
ds0=10.222.23.144:3306:xdb_0:root:0gvzJr66iNs5,
ds1=10.222.23.145:3306:xdb_1:root:0gvzJr66iNs5
);
CREATE TABLE `u_order` (
`order_id` int NOT NULL,
`user_id` int NOT NULL,
`status` varchar(45) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Nothing response
2、DROP DB only removes the logic sharding schema
Hope to clear all the stuff store in zk and backend databases,If don't ,we may get some conflict with the old tables.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org