1.shard.conf
2.sharding.json
3.模拟数据
3306节点上查的数据
3308节点上查到的数据
问题:
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
。。。先测到这把!
- [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/