【Cetus】Cetus-sharding版本

最新推荐文章于 2024-01-11 16:38:01 发布

转载最新推荐文章于 2024-01-11 16:38:01 发布 · 232 阅读

文章标签：

#json #数据库

本文详细介绍了使用shard.conf和sharding.json进行数据库分表配置的过程，并通过具体实例展示了hash分表与range分表的不同应用场景及注意事项。

1.shard.conf

[root@iZ2ze66bhrbxkc31nljgjnZ cetus_sharding]# more conf/shard.conf
[cetus]
daemon = true
# Loaded Plugins
plugins=shard,admin
# Proxy Configuration
proxy-address=127.0.0.1:1234
proxy-backend-addresses=47.93.243.162:3306@data1,47.93.243.162:3308@data2
# Admin Configuration
admin-address=127.0.0.1:5678
admin-username=admin
admin-password=admin
# Backend Configuration
default-db=hash_db
default-username=appuser1
# Log Configuration
log-file=cetus.log
log-level=debug

2.sharding.json

[root@iZ2ze66bhrbxkc31nljgjnZ cetus_sharding]# vi conf/sharding.json
{
{"table": "stable1", "db": "hash_db", "group": "data1","data2"},
"vdb": [
{
"id": 1,
"type": "int",
"method": "hash",
"num": 4,
"partitions": {"data1": [0,1], "data2": [2,3]}
},
{
"id": 2,
"type": "int",
"method": "range",
"num": 0,
"partitions": {"data1": 124999, "data2": 249999}
}
],
"table": [
{"vdb": 1, "db": "hash_db", "table": "dept_emp", "pkey": "emp_no"},
{"vdb": 2, "db": "range_db", "table": "sale_emp", "pkey": "emp_no"}
],
"single_tables": [
{"table": "stable1", "db": "hash_db", "group": "data1"},
{"table": "stable2", "db": "range_db", "group": "data2"}
]
}

3.模拟数据

mysql> use hash_db;
Database changed
mysql> CREATE TABLE `dept_emp` (
-> `emp_no` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '账号ID',
-> `role_id` smallint(6) NOT NULL DEFAULT '0' COMMENT '角色ID',
-> PRIMARY KEY (`emp_no`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='管理员';
insert into dept_emp(emp_no,role_id) values (125999,1),(2,2),(11999,3),(4,4);Query OK, 0 rows affected (0.11 sec)
mysql> insert into dept_emp(emp_no,role_id) values (125999,1),(2,2),(11999,3),(4,4);
Query OK, 4 rows affected (0.05 sec)
mysql> select * from dept_emp;
+--------+---------+
| emp_no | role_id |
+--------+---------+
| 4 | 4 |
| 2 | 2 |
| 11999 | 3 |
| 125999 | 1 |
+--------+---------+
4 rows in set (0.00 sec)
mysql> use range_db;
Database changed
mysql> CREATE TABLE `sale_emp` (
-> `emp_no` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '账号ID',
-> `role_id` smallint(6) NOT NULL DEFAULT '0' COMMENT '角色ID',
-> PRIMARY KEY (`emp_no`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='管理员';
insert into sale_emp(emp_no,role_id) values (125999,1),(2,2),(11999,3),(4,4);Query OK, 0 rows affected (0.03 sec)
mysql> insert into sale_emp(emp_no,role_id) values (125999,1),(2,2),(11999,3),(4,4);
Query OK, 4 rows affected (0.03 sec)
mysql> CREATE TABLE `stable2` (
-> `emp_no` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '账号ID',
-> `role_id` smallint(6) NOT NULL DEFAULT '0' COMMENT '角色ID',
-> PRIMARY KEY (`emp_no`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='管理员';
Query OK, 0 rows affected (0.03 sec)
mysql> insert into stable2(emp_no,role_id) values (125999,1),(2,2),(11999,3),(4,4);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> use hash_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE `stable1` (
-> `emp_no` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '账号ID',
-> `role_id` smallint(6) NOT NULL DEFAULT '0' COMMENT '角色ID',
-> PRIMARY KEY (`emp_no`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='管理员';
insert into stable1(emp_no,role_id) values (125999,1),(2,2),(11999,3),(4,4);Query OK, 0 rows affected (0.03 sec

3306节点上查的数据

mysql> select * from hash_db.dept_emp; ---hash分表，分表正常
+--------+---------+
| emp_no | role_id |
+--------+---------+
| 4 | 4 |
+--------+---------+
1 row in set (0.00 sec)
mysql> select * from range_db.sale_emp; ----range分表，分表正常
+--------+---------+
| emp_no | role_id |
+--------+---------+
| 2 | 2 |
| 4 | 4 |
| 11999 | 3 |
+--------+---------+
3 rows in set (0.00 sec)
mysql> select * from range_db.stable2; ----私有表，是在data2上
Empty set (0.00 sec)
mysql> select * from hash_db.stable1; ----私有表，在data1上
+--------+---------+
| emp_no | role_id |
+--------+---------+
| 2 | 2 |
| 4 | 4 |
| 11999 | 3 |
| 125999 | 1 |
+--------+---------+
4 rows in set (0.00 sec)

3308节点上查到的数据

mysql> select * from hash_db.dept_emp; ----hash分表正常
+--------+---------+
| emp_no | role_id |
+--------+---------+
| 2 | 2 |
| 11999 | 3 |
| 125999 | 1 |
+--------+---------+
3 rows in set (0.00 sec)
mysql> select * from range_db.sale_emp; ----range分表正常
+--------+---------+
| emp_no | role_id |
+--------+---------+
| 125999 | 1 |
+--------+---------+
1 row in set (0.00 sec)
mysql> select * from range_db.stable2; ----私有表在data2上
+--------+---------+
| emp_no | role_id |
+--------+---------+
| 2 | 2 |
| 4 | 4 |
| 11999 | 3 |
| 125999 | 1 |
+--------+---------+
4 rows in set (0.00 sec)
mysql> select * from hash_db.stable1; -----私有表在data1上
Empty set (0.00 sec)

问题：
1.一个db只能用一种vbd规则,hash分表与range分表不能放在同一个db下
报错详情：2018-03-28 14:38:40: (critical) src/sharding-config.c:468 same db inside different vdb: db0328

2.各种类型的表join报错
mysql> select a.*,b.* from dept_emp a , stable1 b where a.emp_no=b.emp_no;
ERROR 5004 (HY000): (cetus) JOIN single-table WITH sharding-table
mysql> select a.*,b.* from hash_db.dept_emp a , range_db.sale_emp b where a.emp_no=b.emp_no;
ERROR 5004 (HY000): (proxy)JOIN must inside VDB and have explicit join-on condition
mysql> select a.*,b.* from hash_db.stable1 a , range_db.stable2 b where a.emp_no=b.emp_no;
ERROR 5004 (HY000): (cetus)JOIN multiple single-tables not allowed

。。。先测到这把！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29096438/viewspace-2152341/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29096438/viewspace-2152341/