如何利用一個數據庫中間件擴展 MySQL 集群：kingshard 使用指南

jopen 9年前發布 | 18K 次閱讀 kingshard MySQL 數據庫服務器

上次寫了一篇有關kingshard架構設計的文章,得到了很多熱心網友的關注。其中有網友提到：希望再寫一篇關于如何利用kingshard搭建一個可擴展的MySQL集群的文檔。利用假期時間，寫了一篇kingshard使用指南，在這篇文章中，我將結合自己對MySQL Proxy的理解，為大家講述如何正確使用kingshard數據庫中間件。

1. kingshard的應用場景

現在很多互聯網公司還是在大量使用MySQL來存儲各種類型的關系型數據。隨著訪問量和數據量的增長，開發者不得不考慮一些MySQL相關的新問題：

讀寫分離問題。由于前端應用訪問量增加，單臺MySQL不足以支撐整個系統的寫入和查詢操作。這時候，我們不得不將一些耗時的查詢操作分散到多個slave上。

單表容量問題。如果在系統設計之初，沒有考慮到分表問題。隨著數據量的增長，單表容量越來越大。作者見過單表容量5億條記錄，然后一個簡單的delete操作都會引起系統慢日志，而且有可能導致MySQL IO瞬發性的飆升。很多同學可能會想到，在查詢的字段上加上索引，但當數據量增長到這么大的時候，即使加上索引效果也不明顯了。歸根結底，就是單表數據量太大，導致MySQL即使通過索引定位數據，仍然需要掃描很多記錄。

數據庫的運維問題。如果在代碼中配置主庫和從庫host，系統運行當然也是沒問題的。但這樣大大增加了運維工作的壓力，比如：MySQL數據庫IO壓力由于訪問量的增加居高不下，DBA需要添加一臺slave，這時候就不得不修改代碼，然后打包并上線。還有很多非常實際的例子，在這就不一一列舉。

連接池。前端應用頻繁連接MySQL，由此給MySQL帶來的額外性能消耗也是不容忽視的。如果通過增加一個連接池，每個DB緩存一定數量的MySQL連接，當有應用需要連接后端的MySQL，直接從連接池里取出一個已建好的連接來發送SQL請求，這樣會大大加快數據查詢速度。而且可以降低MySQL的性能消耗。

SQL日志。在程序出現問題時，我們希望得到一些SQL日志，比如，什么時刻哪條SQL發送到哪一臺DB上了。通過查看這種日志能夠幫助我們快速定位問題。

面對這些問題，我們可以在客戶端代碼中逐一實現。但這樣也會使得客戶端越來越重，不那么靈活。作者一直從事數據庫相關工作的開發，正是基于數據庫開發的痛點，設計和實現了kingshard數據庫中間件。kingshard對上述5類問題都有比較合適的解決方案。下面對kingshard的主要功能，逐個介紹并演示一下。

2. 安裝和啟動說明

(1). 設置配置文件

下面給出一個配置文件范例，用戶可以自行按照自己的需求逐項配置：

# kingshard的地址和端口
addr : 127.0.0.1:9696
連接kingshard的用戶名和密碼
user :  kingshard
password : kingshard
log級別，[debug|info|warn|error],默認是error
log_level : debug
日志文件路徑，如果不配置則會輸出到終端。
log_path : /Users/flike/log
只允許下面的IP列表連接kingshard，如果不配置則對連接kingshard的IP不做限制。
allow_ips: 127.0.0.1
一個node節點表示mysql集群的一個數據分片，包括一主多從（可以不配置從庫）
nodes :

#node節點名字
name : node1 
# 連接池中默認的空閑連接數
idle_conns : 16
# kingshard連接該node中mysql的用戶名和密碼，master和slave的用戶名和密碼必須一致
user :  kingshard 
password : kingshard
# master的地址和端口 
master : 127.0.0.1:3306
# slave的地址和端口，可不配置
#slave : 192.168.0.12@2,192.168.0.13@3
#kingshard在300秒內都連接不上mysql，kingshard則會下線該mysql
down_after_noalive : 300


  name : node2 
  idle_conns : 16
  rw_split: true
  user :  kingshard 
  password : kingshard
  master : 192.168.59.103:3307
  slave : 
  down_after_noalive: 100
# 分表規則schemas :
  #分表使用的db，所有的分表必須都在這個db中。
  db : kingshard
  #分表分布的node名字
  nodes: [node1,node2]
  rules:
      #所有未分表的SQL，都會發往默認node。
  default: node1
  shard:
  -   
      #分表名字
      table: test_shard_hash
      #分表字段
      key: id
      #分表分布的node
      nodes: [node1, node2]
      #分表類型
      type: hash
      #子表個數分布，表示node1有4個子表，                #node2有4個子表。
      locations: [4,4]
  -   
      table: test_shard_range
      key: id
      type: range
      nodes: [node1, node2]
      locations: [4,4]
      #表示每個子表包含的最大記錄數，也就是說每                #個子表最多包好10000條記錄。
      table_row_limit: 10000</pre> <p>這里著重說一下分表的配置規則： </p>


kingshard支持兩種類型的分表規則：hash和range。 
kingshard分表涉及到的子表，需要用戶在各個db手動創建好，并且格式是：table_name_%4d,也就是說子表下標由4位數組成。例如:table_name_0000,table_name_0102。 
所有操作未分表的SQL語句都將發送到默認節點。 

(2). 安裝和啟動kingshard 

安裝Go語言環境，具體步驟請Google。 
git clone https://github.com/flike/kingshard.git src/github.com/flike/kingshard 
cd src/github.com/flike/kingshard 
source ./dev.sh 
make 
設置配置文件 
運行kingshard。./bin/kingshard -config=etc/ks.yaml 

3. 跨節點分表 
由于作者的只有兩臺MySQL，所以搭建了兩個節點，這兩個節點都只有一臺Master 角色的MySQL數據庫，具體的拓撲圖如下所示：  
3.1. 分表操作演示 
分表操作有hash和range兩種類型，在這里只演示hash類型的分表操作，range類型的分表類似，就不再贅述了。 
3.1.1. 手動創建子表 
在node1和node2上各創建4張子表，下面只給出在node1上test_shard_hash_0000的建表SQL語句，其他子表的建表SQL語句類似。node1包含：test_shard_hash_0000, test_shard_hash_0001, test_shard_hash_0002, test_shard_hash_0003。node2包含：test_shard_hash_0004, test_shard_hash_0005, test_shard_hash_0006, test_shard_hash_0007。 
CREATE TABLE `test_shard_hash_0000` (
`id` bigint(64) unsigned NOT NULL,
`str` varchar(256) DEFAULT NULL,
`f` double DEFAULT NULL,
`e` enum('test1','test2') DEFAULT NULL,
`u` tinyint(3) unsigned DEFAULT NULL,
`i` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 3.1.2. 分表的插入和查詢 
執行下面SQL語句，根據查詢的結果可以看出SQL語句根據分表規則落到不同的子表。查詢操作（select）可以跨多個node，當更新操作涉及到多個node時，kingshard會返回錯誤。為了保證數據一致性，kingshard不允許同時更新多個node上的子表（因為kingshard還未實現分布式事務）。但可以更新單個node上的多個子表，由單node上的事務保證。 
mysql> insert into test_shard_hash(id,str,f,e,u,i) values(15,"flike",3.14,'test2',2,3);
Query OK, 1 row affected (0.01 sec)

mysql> mysql> insert into test_shard_hash(id,str,f,e,u,i) values(7,"chen",2.1,'test1',32,3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test_shard_hash(id,str,f,e,u,i) values(17,"github",2.5,'test1',32,3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_shard_hash(id,str,f,e,u,i) values(18,"kingshard",7.3,'test1',32,3);
Query OK, 1 row affected (0.01 sec)</pre> 
對應的SQL日志如下所示： 

2015/09/02 18:48:24 - INFO - 127.0.0.1:55003->192.168.59.103:3307:insert into test_shard_hash_0007(id, str, f, e, u, i) values (15, 'flike', 3.14, 'test2', 2, 3)
2015/09/02 18:49:05 - INFO - 127.0.0.1:55003->192.168.59.103:3307:insert into test_shard_hash_0007(id, str, f, e, u, i) values (7, 'chen', 2.1, 'test1', 32, 3)
2015/09/02 18:49:51 - INFO - 127.0.0.1:55003->127.0.0.1:3306:insert into test_shard_hash_0001(id, str, f, e, u, i) values (17, 'github', 2.5, 'test1', 32, 3)
2015/09/02 18:50:21 - INFO - 127.0.0.1:55003->127.0.0.1:3306:insert into test_shard_hash_0002(id, str, f, e, u, i) values (18, 'kingshard', 7.3, 'test1', 32, 3)
 可以看到前兩條SQL發送到了node2的master上了，后兩條SQL發送到node1上的master了。 

然后我們可以用select語句查看數據，且select支持跨node查詢。 

mysql> select * from test_shard_hash where id < 18;
+----+--------+------+-------+------+------+
| id | str    | f    | e     | u    | i    |
+----+--------+------+-------+------+------+
| 17 | github |  2.5 | test1 |   32 |    3 |
|  7 | chen   |  2.1 | test1 |   32 |    3 |
| 15 | flike  | 3.14 | test2 |    2 |    3 |
+----+--------+------+-------+------+------+
3 rows in set (0.02 sec)
 因為是hash類型的分表，所以對于select范圍類型的查詢，必須查詢每一個子表。對應的SQL日志如下所示： 

2015/09/02 18:55:01 - INFO - 127.0.0.1:55003->127.0.0.1:3306:select * from test_shard_hash_0000 where id < 18
2015/09/02 18:55:01 - INFO - 127.0.0.1:55003->127.0.0.1:3306:select * from test_shard_hash_0001 where id < 18
2015/09/02 18:55:01 - INFO - 127.0.0.1:55003->127.0.0.1:3306:select * from test_shard_hash_0002 where id < 18
2015/09/02 18:55:01 - INFO - 127.0.0.1:55003->127.0.0.1:3306:select * from test_shard_hash_0003 where id < 18
2015/09/02 18:55:01 - INFO - 127.0.0.1:55003->192.168.59.103:3307:select * from test_shard_hash_0004 where id < 18
2015/09/02 18:55:01 - INFO - 127.0.0.1:55003->192.168.59.103:3307:select * from test_shard_hash_0005 where id < 18
2015/09/02 18:55:01 - INFO - 127.0.0.1:55003->192.168.59.103:3307:select * from test_shard_hash_0006 where id < 18
2015/09/02 18:55:01 - INFO - 127.0.0.1:55003->192.168.59.103:3307:select * from test_shard_hash_0007 where id < 18
 對應等值的select查詢，kingshard會計算出具體命中的子表，然后只會在相應的子表中查詢。對應的SQL如下所示： 

mysql> select * from test_shard_hash where id = 18;
+----+-----------+------+-------+------+------+
| id | str       | f    | e     | u    | i    |
+----+-----------+------+-------+------+------+
| 18 | kingshard |  7.3 | test1 |   32 |    3 |
+----+-----------+------+-------+------+------+
1 row in set (0.00 sec)
 對應的SQL日志如下所示： 

2015/09/02 18:59:37 - INFO - 127.0.0.1:55003->127.0.0.1:3306:select * from test_shard_hash_0002 where id = 18
 3.1.3. 分表的更新 

當更新的記錄落在同一個子表時，kingshard支持這類操作。在上面插入的記錄中，id為7和15的記錄都落在test_shard_hash_0007中，所以可以成功地執行下面的SQL： 

mysql> update test_shard_hash set u=123 where id = 15 or id = 7;
Query OK, 2 rows affected (0.01 sec)
 對應的SQL日志是： 

2015/09/02 19:17:27 - INFO - 127.0.0.1:55003->192.168.59.103:3307:update test_shard_hash_0007 set u = 123 where id = 15 or id = 7
 當更新的記錄落在不同的子表，只有當這些子表在同一個node中，kingshard才支持。kingshard是通過單node事務實現的，也就是說將發往同一個node的SQL都放在一個事務中執行，這些操作正確性由MySQL保證。在上述記錄中，我們可以看出id為17和18的記錄都在node1中，所以kingshard是可以執行下列SQL： 

mysql> update test_shard_hash set i=23 where id = 17 or id = 18;
Query OK, 2 rows affected (0.00 sec)
 對應的SQL日志是： 

2015/09/02 19:24:46 - INFO - 127.0.0.1:55003->127.0.0.1:3306:update test_shard_hash_0001 set i = 23 where id = 17 or id = 18
2015/09/02 19:24:46 - INFO - 127.0.0.1:55003->127.0.0.1:3306:update test_shard_hash_0002 set i = 23 where id = 17 or id = 18
 但是如果更新的記錄落在不同的node時，kingshard則會報告錯誤： 例如： 

mysql> update test_shard_hash set i=23 where id = 15 or id = 18;
ERROR 1105 (HY000): no route node
 對應的SQL日志是： 

2015/09/02 19:24:24 - ERROR - router.go:[483] - [Router] "generateUpdateSql" "update in multi node" "RouteNodeIndexs=[0 1]" conn_id=0
 3.2. 指定發送的node 

有時候我們需要操作的表，不在default node中。在kingshard中允許用戶將特定的sql路由到指定的node上。只需要在sql語句前面加上包含node名稱的注釋。 

mysql> /node2/show tables;
+-----------------------+
| Tables_in_kingshard   |
+-----------------------+
| kingshard_test_conn   |
| test_shard_hash_0004  |
| test_shard_hash_0005  |
| test_shard_hash_0006  |
| test_shard_hash_0007  |
| test_shard_range_0004 |
| test_shard_range_0005 |
| test_shard_range_0006 |
| test_shard_range_0007 |
+-----------------------+
9 rows in set (0.03 sec)
mysql> /node2/select * from kingshard_test_conn;
Empty set (0.01 sec)
mysql> /node2/desc kingshard_test_conn;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | bigint(20) unsigned   | NO   | PRI | NULL    |       |
| str   | varchar(256)          | YES  |     | NULL    |       |
| f     | double                | YES  |     | NULL    |       |
| e     | enum('test1','test2') | YES  |     | NULL    |       |
| u     | tinyint(3) unsigned   | YES  |     | NULL    |       |
| i     | tinyint(4)            | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> /node2/insert into kingshard_test_conn values(10,"hello",10.2,'test1',1,1);
Query OK, 1 row affected (0.00 sec)
mysql> /node2/select * from kingshard_test_conn;
+----+-------+------+-------+------+------+
| id | str   | f    | e     | u    | i    |
+----+-------+------+-------+------+------+
| 10 | hello | 10.2 | test1 |    1 |    1 |
+----+-------+------+-------+------+------+
1 row in set (0.00 sec)</pre> 
3.3. 強制讀主庫 

有時候在主庫中插入數據后，希望立即從主庫讀出來。在kingshard中由于讀寫分離的原因，select默認會發送到相應node的從庫上。但是只需要在select語句中加入相應的注釋項（/*master*/)，就可以將select語句發送到主庫。 

mysql> select/*master*/ * from kingshard_test_conn;
+----+----------+------+-------+------+------+
| id | str      | f    | e     | u    | i    |
+----+----------+------+-------+------+------+
|  1 | a        | 3.14 | test1 | NULL | NULL |
|  5 | ""''\abc | NULL | NULL  | NULL | NULL |
|  6 | 中國     | NULL | NULL  | NULL | NULL |
+----+----------+------+-------+------+------+
3 rows in set (0.01 sec)
 3.4. 跨node的sum和count函數 

在kingshard中，支持sum和count函數，kingshard會將相應的SQL發生到正確的DB，并將結果合并起來再返回給客戶的。例如： 

mysql> select count(id) from test_shard_hash where id > 1;
+-----------+
| count(id) |
+-----------+
|         4 |
+-----------+
1 row in set (0.02 sec)
mysql> select sum(id) from test_shard_hash where id > 1;
+---------+
| sum(id) |
+---------+
|      57 |
+---------+
1 row in set (0.02 sec)</pre> 
相應的SQL日志如下所示： 

2015/09/03 14:49:01 - INFO - 127.0.0.1:55768->127.0.0.1:3306:select count(id) from test_shard_hash_0000 where id > 1
2015/09/03 14:49:01 - INFO - 127.0.0.1:55768->127.0.0.1:3306:select count(id) from test_shard_hash_0001 where id > 1
2015/09/03 14:49:01 - INFO - 127.0.0.1:55768->127.0.0.1:3306:select count(id) from test_shard_hash_0002 where id > 1
2015/09/03 14:49:01 - INFO - 127.0.0.1:55768->127.0.0.1:3306:select count(id) from test_shard_hash_0003 where id > 1
2015/09/03 14:49:01 - INFO - 127.0.0.1:55768->192.168.59.103:3307:select count(id) from test_shard_hash_0004 where id > 1
2015/09/03 14:49:01 - INFO - 127.0.0.1:55768->192.168.59.103:3307:select count(id) from test_shard_hash_0005 where id > 1
2015/09/03 14:49:01 - INFO - 127.0.0.1:55768->192.168.59.103:3307:select count(id) from test_shard_hash_0006 where id > 1
2015/09/03 14:49:01 - INFO - 127.0.0.1:55768->192.168.59.103:3307:select count(id) from test_shard_hash_0007 where id > 1
2015/09/03 14:49:14 - INFO - 127.0.0.1:55768->127.0.0.1:3306:select sum(id) from test_shard_hash_0000 where id > 1
2015/09/03 14:49:14 - INFO - 127.0.0.1:55768->127.0.0.1:3306:select sum(id) from test_shard_hash_0001 where id > 1
2015/09/03 14:49:14 - INFO - 127.0.0.1:55768->127.0.0.1:3306:select sum(id) from test_shard_hash_0002 where id > 1
2015/09/03 14:49:14 - INFO - 127.0.0.1:55768->127.0.0.1:3306:select sum(id) from test_shard_hash_0003 where id > 1
2015/09/03 14:49:14 - INFO - 127.0.0.1:55768->192.168.59.103:3307:select sum(id) from test_shard_hash_0004 where id > 1
2015/09/03 14:49:14 - INFO - 127.0.0.1:55768->192.168.59.103:3307:select sum(id) from test_shard_hash_0005 where id > 1
2015/09/03 14:49:14 - INFO - 127.0.0.1:55768->192.168.59.103:3307:select sum(id) from test_shard_hash_0006 where id > 1
2015/09/03 14:49:14 - INFO - 127.0.0.1:55768->192.168.59.103:3307:select sum(id) from test_shard_hash_0007 where id > 1
 3.5. 跨node的order by 

kingshard支持跨node的select操作使用order by，kingshard先將合適的SQL發生到對應的node，然后將結果集在內存中排序，從而實現select的order by操作。示例如下所示： 

mysql> select * from test_shard_hash where id > 1 order by id;
+----+-----------+------+-------+------+------+
| id | str       | f    | e     | u    | i    |
+----+-----------+------+-------+------+------+
|  7 | chen      |  2.1 | test1 |  123 |    3 |
| 15 | flike     | 3.14 | test2 |  123 |    3 |
| 17 | github    |  2.5 | test1 |   32 |   23 |
| 18 | kingshard |  7.3 | test1 |   32 |   23 |
+----+-----------+------+-------+------+------+
4 rows in set (0.02 sec)
 對應的SQL日志為： 

2015/09/03 14:54:11 - INFO - 127.0.0.1:55768->127.0.0.1:3306:select * from test_shard_hash_0000 where id > 1 order by id asc
2015/09/03 14:54:11 - INFO - 127.0.0.1:55768->127.0.0.1:3306:select * from test_shard_hash_0001 where id > 1 order by id asc
2015/09/03 14:54:11 - INFO - 127.0.0.1:55768->127.0.0.1:3306:select * from test_shard_hash_0002 where id > 1 order by id asc
2015/09/03 14:54:11 - INFO - 127.0.0.1:55768->127.0.0.1:3306:select * from test_shard_hash_0003 where id > 1 order by id asc
2015/09/03 14:54:11 - INFO - 127.0.0.1:55768->192.168.59.103:3307:select * from test_shard_hash_0004 where id > 1 order by id asc
2015/09/03 14:54:11 - INFO - 127.0.0.1:55768->192.168.59.103:3307:select * from test_shard_hash_0005 where id > 1 order by id asc
2015/09/03 14:54:11 - INFO - 127.0.0.1:55768->192.168.59.103:3307:select * from test_shard_hash_0006 where id > 1 order by id asc
2015/09/03 14:54:11 - INFO - 127.0.0.1:55768->192.168.59.103:3307:select * from test_shard_hash_0007 where id > 1 order by id asc
 4. 單node的事務 

kingshard支持在單個node上執行事務，也就是說同一個事務不能跨多個node，當出現跨node的情況時，kingshard會返回錯誤給客戶端。可以跨同node上的不同子表。示例如下所示： 

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test_shard_hash(id,str,f,e,u,i) values(23,'proxy',9.2,'test1',12,3);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)</pre> 
當在一個事務中，出現跨node的SQL語句時，kingshard會返回錯誤： 

#SQL語句在node2中執行
mysql> insert into test_shard_hash(id,str,f,e,u,i) values(31,'proxy',9.2,'test1',12,3);
Query OK, 1 row affected (0.01 sec)
SQL語句在需要在node1執行，跨node了。
mysql> insert into test_shard_hash(id,str,f,e,u,i) values(40,'proxy',9.2,'test1',12,3);
ERROR 1105 (HY000): transaction in multi node</pre> 
6. kingshard的管理端操作 

kingshard的管理接口，目前還是命令行的方式。后續有時間打算將其改成web方式。管理端具體的命令可以參考文檔。管理端的命令格式，可以分為兩類： 


admin server(opt,k,v) values(action,k1,v1)。這種命令是操作整個kingshard的，其中opt表示這個操作的動作；k表示操作的對象，v表示給對象的賦值。 

admin node(opt,node,k,v) values(action,nodeName,k1,v1),這類命令表示操作node。其中opt表示這個操作的動作；node表示操作哪個node；k表示操作的對象，v表示給對象的賦值。 
</ul>
7. 總結 

kingshard開源兩個月以來，得到了很多開發者的關注。這足以證明，大家對數據庫中間件是有需求的，希望出現一款簡單好用的MySQL Proxy。kingshard經過這兩個月的迭代開發，也比較穩定了。據了解，有幾個公司正在對其進行嘗試。后續作者的主要精力會放在優化kingshard的性能上，同時完善kingshard已有的功能。如果大家對kingshard有什么想法或建議，可以發郵件聯系我（flikecn#126.com)，非常樂意和大家交流。 
原文 https://github.com/flike/kingshard/blob/master/doc/KingDoc/how_to_use_kingshard.md

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1441542037800.html

kingshard MySQL 數據庫服務器

如何利用一個數據庫中間件擴展 MySQL 集群：kingshard 使用指南

1. kingshard的應用場景

2. 安裝和啟動說明

(1). 設置配置文件

相關經驗

相關資訊

相關文檔

目錄