【Cetus】Cetus-sharding版本

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

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

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

  12. # Admin Configuration
  13. admin-address=127.0.0.1:5678
  14. admin-username=admin
  15. admin-password=admin

  17. # Backend Configuration
  18. default-db=hash_db
  19. default-username=appuser1

  21. # Log Configuration
  22. log-file=cetus.log
  23. 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)

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

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

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

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

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

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

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

  51. Database changed
  52. mysql> CREATE TABLE `stable1` (
  53.     -> `emp_no` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '賬號ID',
  54.     -> `role_id` smallint(6) NOT NULL DEFAULT '0' COMMENT '角色ID',
  55.     -> PRIMARY KEY (`emp_no`)
  56.     -> ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='管理員';
  57. 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)

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


  20. mysql> select * from range_db.stable2;      ----私有表，是在data2上
  21. Empty set (0.00 sec)

  23. mysql> select * from hash_db.stable1;       ----私有表，在data1上
  24. +--------+---------+
  25. | emp_no | role_id |
  26. +--------+---------+
  27. | 2 | 2 |
  28. | 4 | 4 |
  29. | 11999 | 3 |
  30. | 125999 | 1 |
  31. +--------+---------+
  32. 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)

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



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

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

相關文章