【Cetus】Cetus-sharding版本

小亮520cl發表於2018-03-28
1.shard.conf
  1. [root@iZ2ze66bhrbxkc31nljgjnZ cetus_sharding]# more conf/shard.conf
  2. [cetus]
  3. daemon = true

  4. # Loaded Plugins
  5. plugins=shard,admin

  6. # Proxy Configuration
  7. proxy-address=127.0.0.1:1234
  8. proxy-backend-addresses=47.93.243.162:3306@data1,47.93.243.162:3308@data2

  9. # Admin Configuration
  10. admin-address=127.0.0.1:5678
  11. admin-username=admin
  12. admin-password=admin

  13. # Backend Configuration
  14. default-db=hash_db
  15. default-username=appuser1

  16. # Log Configuration
  17. log-file=cetus.log
  18. log-level=debug


2.sharding.json
  1. [root@iZ2ze66bhrbxkc31nljgjnZ cetus_sharding]# vi conf/sharding.json
  2. {
  3.     {"table": "stable1", "db": "hash_db", "group": "data1","data2"},
  4.   "vdb": [
  5.     {
  6.       "id": 1,
  7.       "type": "int",
  8.       "method": "hash",
  9.       "num": 4,
  10.       "partitions": {"data1": [0,1], "data2": [2,3]}
  11.     },
  12.     {
  13.       "id": 2,
  14.       "type": "int",
  15.       "method": "range",
  16.       "num": 0,
  17.       "partitions": {"data1": 124999, "data2": 249999}
  18.     }
  19.   ],
  20.   "table": [
  21.     {"vdb": 1, "db": "hash_db", "table": "dept_emp", "pkey": "emp_no"},
  22.     {"vdb": 2, "db": "range_db", "table": "sale_emp", "pkey": "emp_no"}
  23.   ],
  24.   "single_tables": [
  25.     {"table": "stable1", "db": "hash_db", "group": "data1"},
  26.     {"table": "stable2", "db": "range_db", "group": "data2"}
  27.   ]
  28. }

3.模擬資料
  1. mysql> use hash_db;
  2. Database changed
  3. mysql> CREATE TABLE `dept_emp` (
  4.     -> `emp_no` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '賬號ID',
  5.     -> `role_id` smallint(6) NOT NULL DEFAULT '0' COMMENT '角色ID',
  6.     -> PRIMARY KEY (`emp_no`)
  7.     -> ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='管理員';
  8. 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)

  9. mysql> insert into dept_emp(emp_no,role_id) values (125999,1),(2,2),(11999,3),(4,4);
  10. Query OK, 4 rows affected (0.05 sec)

  11. mysql> select * from dept_emp;
  12. +--------+---------+
  13. | emp_no | role_id |
  14. +--------+---------+
  15. | 4 | 4 |
  16. | 2 | 2 |
  17. | 11999 | 3 |
  18. | 125999 | 1 |
  19. +--------+---------+
  20. 4 rows in set (0.00 sec)

  21. mysql> use range_db;
  22. Database changed
  23. mysql> CREATE TABLE `sale_emp` (
  24.     -> `emp_no` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '賬號ID',
  25.     -> `role_id` smallint(6) NOT NULL DEFAULT '0' COMMENT '角色ID',
  26.     -> PRIMARY KEY (`emp_no`)
  27.     -> ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='管理員';
  28. 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)

  29. mysql> insert into sale_emp(emp_no,role_id) values (125999,1),(2,2),(11999,3),(4,4);
  30. Query OK, 4 rows affected (0.03 sec)

  31. mysql> CREATE TABLE `stable2` (
  32.     -> `emp_no` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '賬號ID',
  33.     -> `role_id` smallint(6) NOT NULL DEFAULT '0' COMMENT '角色ID',
  34.     -> PRIMARY KEY (`emp_no`)
  35.     -> ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='管理員';
  36. Query OK, 0 rows affected (0.03 sec)

  37. mysql> insert into stable2(emp_no,role_id) values (125999,1),(2,2),(11999,3),(4,4);
  38. Query OK, 4 rows affected (0.01 sec)
  39. Records: 4 Duplicates: 0 Warnings: 0

  40. mysql> use hash_db;
  41. Reading table information for completion of table and column names
  42. You can turn off this feature to get a quicker startup with -A

  43. Database changed
  44. mysql> CREATE TABLE `stable1` (
  45.     -> `emp_no` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '賬號ID',
  46.     -> `role_id` smallint(6) NOT NULL DEFAULT '0' COMMENT '角色ID',
  47.     -> PRIMARY KEY (`emp_no`)
  48.     -> ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='管理員';
  49. 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節點上查的資料
  1. mysql> select * from hash_db.dept_emp;   ---hash分表,分表正常
  2. +--------+---------+
  3. | emp_no | role_id |
  4. +--------+---------+
  5. | 4 | 4 |
  6. +--------+---------+
  7. 1 row in set (0.00 sec)

  8. mysql> select * from range_db.sale_emp;      ----range分表,分表正常
  9. +--------+---------+
  10. | emp_no | role_id |
  11. +--------+---------+
  12. | 2 | 2 |
  13. | 4 | 4 |
  14. | 11999 | 3 |
  15. +--------+---------+
  16. 3 rows in set (0.00 sec)


  17. mysql> select * from range_db.stable2;      ----私有表,是在data2上
  18. Empty set (0.00 sec)

  19. mysql> select * from hash_db.stable1;       ----私有表,在data1上
  20. +--------+---------+
  21. | emp_no | role_id |
  22. +--------+---------+
  23. | 2 | 2 |
  24. | 4 | 4 |
  25. | 11999 | 3 |
  26. | 125999 | 1 |
  27. +--------+---------+
  28. 4 rows in set (0.00 sec)

3308節點上查到的資料
  1. mysql> select * from hash_db.dept_emp;            ----hash分表正常
  2. +--------+---------+
  3. | emp_no | role_id |
  4. +--------+---------+
  5. | 2 | 2 |
  6. | 11999 | 3 |
  7. | 125999 | 1 |
  8. +--------+---------+
  9. 3 rows in set (0.00 sec)

  10. mysql> select * from range_db.sale_emp;           ----range分表正常
  11. +--------+---------+
  12. | emp_no | role_id |
  13. +--------+---------+
  14. | 125999 | 1 |
  15. +--------+---------+
  16. 1 row in set (0.00 sec)



  17. mysql> select * from range_db.stable2;         ----私有表 在data2上
  18. +--------+---------+
  19. | emp_no | role_id |
  20. +--------+---------+
  21. | 2 | 2 |
  22. | 4 | 4 |
  23. | 11999 | 3 |
  24. | 125999 | 1 |
  25. +--------+---------+
  26. 4 rows in set (0.00 sec)

  27. mysql> select * from hash_db.stable1;         -----私有表在data1上
  28. 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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章