如何使用KingShard构建MySQL集群

最新推荐文章于 2024-10-31 15:36:20 发布

星河_赵梓宇

最新推荐文章于 2024-10-31 15:36:20 发布

阅读量781

点赞数

分类专栏： Unix&Unix-Like MySQL集群文章标签：数据库中间件

Unix&Unix-Like 同时被 2 个专栏收录

399 篇文章 5 订阅

订阅专栏

MySQL集群

20 篇文章 1 订阅

订阅专栏

1. KingShard的应用场景

现在越来越多的互联网公司仍在大量使用MySQL来存储各种类型的关系数据。随着数据量和流量的增加，开发人员不得不考虑一些与MySQL相关的新问题。

读/写拆分。随着前端应用程序发送的流量不断增加，MySQL的一个实例无法容纳所有查询。此时，我们必须将读取查询发送到从站以进行负载平衡。
MySQL中一个表的容量。如果在系统设计的开始阶段，您还没有考虑表格分片，这将使您的系统难以保持高性能。
MySQL维护操作。如果没有代理，您应该在源代码中配置主服务器和从服务器主机。升级MySQL服务器时，前端应用程序必须进行相关规定。
连接池。前端应用程序通过与MySQL建立新连接来发送查询，并在不再需要发送查询时关闭连接。这些操作的额外性能成本不容忽视。如果在前端应用程序和MySQL之间添加连接池，并且前端应用程序可以从连接池中选择连接，则会提高系统性能。
SQL日志。当程序出现问题时，通常我们希望获得程序发送的一些SQL日志。例如，我们想知道哪个SQL被发送到哪个DB后端。通过检查日志，它可以帮助我们更快地找到问题。

面对这些问题，我们可以在客户端代码中实现每个功能。但这也使客户的灵活性降低。我多年来一直致力于数据库开发，我相信我们可以使用MySQL代理更有效地解决问题，这就是我创建这个项目的原因。在本文中，我将向您展示kingshard如何解决上述问题。

2.安装KingShard

（1）设置配置文件

kingshard使用配置文件（ks.yaml）运行。在运行kingshard之前，需要配置文件。这里我给出一个配置文件作为演示，我们只需要修改一些配置选项，不需要从头开始重写配置文件。

# server listen addr
addr : 0.0.0.0:9696
# the web api server
web_addr : 0.0.0.0:9797
#HTTP Basic Auth
web_user : admin
web_password : admin

# user list with user name and password
user_list:
-
user :  kingshard
password : kingshard

#if set log_path, the sql log will write into log_path/sql.log,the system log
#will write into log_path/sys.log
#log_path : /Users/flike/log

# log level[debug|info|warn|error],default error
log_level : debug
#if set log_sql(on|off) off,the sql log will not output
#log_sql: off 
#only log the query that take more than slow_log_time ms
#slow_log_time : 100
# the path of blacklist sql file
# all these sqls in the file will been forbidden by kingshard
#blacklist_sql_file: /Users/flike/blacklist
# only allow this ip list ip to connect kingshard
#allow_ips: 127.0.0.1
# the default charset of kingshard is utf8.
#proxy_charset: utf8mb4

# node is an agenda for real remote mysql server.
nodes :
- 
    name : node1 

    # default max conns for mysql server
    max_conns_limit : 8

    # all mysql in a node must have the same user and password
    user :  kingshard 
    password : kingshard

    # master represents a real mysql master server 
    master : 127.0.0.1:3306

    # slave represents a real mysql slave server,and the number after '@' is 
    # read load weight of this slave.
    slave : 
    down_after_noalive : 32
- 
    name : node2 

    # default max conns for mysql server
    max_conns_limit : 8

    # all mysql in a node must have the same user and password
    user :  kingshard 
    password : kingshard

    # master represents a real mysql master server 
    master : 192.168.59.103:3307

    # slave represents a real mysql slave server 
    slave : 

    # down mysql after N seconds noalive
    # 0 will no down
    down_after_noalive: 32

# schema list include all user's schema
# schema defines which db can be used by client and this db's sql will be executed in which nodes
schema_list :
-
    user: kingshard
    nodes: [node1,node2]
    default: node1      
    shard:
    -   
        db : kingshard
        table: test_shard_hash
        key: id
        nodes: [node1, node2]
        type: hash
        locations: [4,4]

    -   
        db : kingshard
        table: test_shard_range
        key: id
        type: range
        nodes: [node1, node2]
        locations: [4,4]
        table_row_limit: 10000

分片注释：

kingshard支持两种分片类型：范围和哈希。
需要手动在正确的数据库中创建子表。格式是table_%4d。换句话说，子表的索引是由四位数组成的整数。如table_name_0000,table_name_0012。
处理unshading表的所有SQL都将被发送到默认节点。

（2）安装并运行

1. Install Go
2. git clone https://github.com/flike/kingshard.git src/github.com/flike/kingshard
3. cd src/github.com/flike/kingshard
4. source ./dev.sh
5. make
6. set the config file (etc/ks.yaml)
7. run kingshard (./bin/kingshard -config=etc/multi.yaml)

3.分片

我用kingshard构建了一个mysql集群，拓扑结构如下所示。

3.1实际的分片示例

3.1.1手动创建子表

我在node1和node2中创建了8个子表，每个节点有4个子表。test_shard_hash_0000, test_shard_hash_0001, test_shard_hash_0002, test_shard_hash_0003在node1和test_shard_hash_0004, test_shard_hash_0005, test_shard_hash_0006, test_shard_hash_0007node2中。create table sql如下：

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也将被发送到多个数据库，如果插入操作跨多个数据库，kingshard将把查询发送到多个数据库。查询如下：

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)

以及相应的日志如下：

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)

请注意，前两个查询已发送到node2中的master，最后两个SQL已发送到node1中的master。

然后我们发送选择查询以获取记录-kingshard支持跨节点的选择操作。选择查询如下：

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)

由于分片类型是散列，因此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

如果选择SQL的查询条件相等，则kingshard将根据条件计算子表的索引。例如：

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分片的更新操作

更新SQL只会发送到一个数据库，如果更新操作accoss多数据库，kingshard将响应错误消息。

Kingshard支持在一个节点中更新操作accoss子表。例如如下：

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

如果需要更新的记录位于多个数据库中，则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将查询发送到指定节点

有时我们要查询的表不在默认节点中。我们可以使用kingshard提供的功能来解决这个问题。您只需添加注释即可在查询前指定节点。例如如下：

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)

3.3将读取查询发送给master

Kingshard可以拆分读/写查询，但有时我们可能希望将读取查询发送给master，但不要使用事务。你可以/*master*/在select SQL中添加一个comment（）select /*master*/ * from stu;，这个sql会发送给master。当你使用mysql客户端来测试这个函数时，你需要使用参数：' - c'来连接mysql服务器以便保留评论。例如如下：

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功能支持

Kingshard还支持最常用的功能，例如max, min, count, sum，也支持order by。例如如下：

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)

相应的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

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.一个节点中的事务

kingshard支持仅在一个数据库中执行事务。如果事务处理多个数据库，kingshard将响应错误消息。例如如下：

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)

5.KingShard的管理命令

kingshard的admin命令如下：

5.1数据库操作

#add a slave in node1
admin node(opt,node,k,v) values(‘add’,’node1’,’slave’,’127.0.0.1:3306’)

#delete slave in node1, ps:master can't been remove.
admin node(opt,node,k,v) values(‘del’,’node1’,’slave’,’127.0.0.1:3306’)

#set slave down
admin node(opt,node,k,v) values(‘down’,’node1’,’slave’,’127.0.0.1:3306’)

#set slave up
admin node(opt,node,k,v) values(‘up’,’node1’,’slave’,’127.0.0.1:3306’)

#set master down
admin node(opt,node,k,v) values(‘down’,’node1’,’master’,’127.0.0.1:3306’)

#set master up
admin node(opt,node,k,v) values(‘up’,’node1’,’master’,’127.0.0.1:3306’)

5.2查看kingshard的状态

#view the config of kingshard
mysql> admin server(opt,k,v) values('show','proxy','config');
+-------------+----------------+
| Key         | Value          |
+-------------+----------------+
| Addr        | 127.0.0.1:9696 |
| User        | kingshard      |
| Password    | kingshard      |
| LogLevel    | debug          |
| Nodes_Count | 2              |
| Nodes_List  | node1,node2    |
+-------------+----------------+
6 rows in set (0.00 sec)

#view the status of node
mysql> admin server(opt,k,v) values('show','node','config');
+-------+---------------------+--------+-------+-------------------------------+-------------+----------+
| Node  | Address             | Type   | State | LastPing                      | MaxIdleConn | IdleConn |
+-------+---------------------+--------+-------+-------------------------------+-------------+----------+
| node1 | 127.0.0.1:3306      | master | up    | 2015-08-07 15:54:44 +0800 CST | 16          | 1        |
| node2 | 192.168.59.103:3307 | master | up    | 2015-08-07 15:54:44 +0800 CST | 16          | 1        |
+-------+---------------------+--------+-------+-------------------------------+-------------+----------+
2 rows in set (0.00 sec)

#view the config of schema
mysql> admin server(opt,k,v) values('show','schema','config');
+-----------+------------------+---------+------+--------------+-----------+---------------+
| DB        | Table            | Type    | Key  | Nodes_List   | Locations | TableRowLimit |
+-----------+------------------+---------+------+--------------+-----------+---------------+
| kingshard |                  | default |      | node1        |           | 0             |
| kingshard | test_shard_hash  | hash    | id   | node1, node2 | 4, 4      | 0             |
| kingshard | test_shard_range | range   | id   | node1, node2 | 4, 4      | 10000         |
+-----------+------------------+---------+------+--------------+-----------+---------------+
3 rows in set (0.00 sec)

#view the config of white list ip
mysql> admin server(opt,k,v) values('show','allow_ip','config');
+--------------+
| AllowIP      |
+--------------+
| 127.0.0.1    |
| 192.168.10.1 |
+--------------+
2 rows in set (0.00 sec)

#view the config of black list sql
mysql> admin server(opt,k,v) values('show','black_sql','config');
+-------------------------------+
| BlackListSql                  |
+-------------------------------+
| select * from sbtest1         |
| select * from sbtest1 limit ? |
+-------------------------------+
2 rows in set (0.00 sec)

5.3更改kingshard的配置

#turn off the sql log
admin server(opt,k,v) values('change','log_sql','off')

#turn on the sql log
admin server(opt,k,v) values('change','log_sql','on')

#change the threshold of slow log time
admin server(opt,k,v) values('change','slow_log_time','50');

#add white list ip
admin server(opt,k,v) values('add','allow_ip','127.0.0.1');

#delete white list ip
admin server(opt,k,v) values('del','allow_ip','127.0.0.1');

#add black list sql
admin server(opt,k,v) values('add','black_sql','select count(*) from sbtest1')

#delete black list sql
admin server(opt,k,v) values('del','black_sql','select count(*) from sbtest1')

#save config
admin server(opt,k,v) values('save','proxy','config')

5.4支持LVS / Keepalived

#show status of kingshard
admin server(opt,k,v) values('show','proxy','status')

#change status of kingshard online/offline
admin server(opt,k,v) values('change','proxy','online')