管理端命令

kingshard的管理端口复用了工作端口，通过特定的关键字来标示，目前支持对后端DB常用的管理操作。kingshard支持了多用户，

只有root用户才有权限操作admin相关命令。

管理端的命令格式，可以分为两类：

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表示给对象的赋值。

平滑上（下）线后端DB

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


#添加一个新的slave到node1
admin node(opt,node,k,v) values('add','node1','slave','127.0.0.1:3306')

#删除node1上的一个slave。注意：只能删除slave，不能删除master
admin node(opt,node,k,v) values('del','node1','slave','127.0.0.1:3306')

#将一个slave设置为下线状态
admin node(opt,node,k,v) values('down','node1','slave','127.0.0.1:3306')

#将一个slave设置为上线状态
admin node(opt,node,k,v) values('up','node1','slave','127.0.0.1:3306')

#将master设置为下线状态
admin node(opt,node,k,v) values('down','node1','master','127.0.0.1:3306')

#将master设置为上线状态
admin node(opt,node,k,v) values('up','node1','master','127.0.0.1:3306')

查看kingshard配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68


#查看kingshard全局配置
mysql> admin server(opt,k,v) values('show','proxy','config');
+--------------+----------------+
| Key          |   Value        |
+--------------+----------------+
| Addr         | 127.0.0.1:9696 |
| User_List    | root,kingshard |
| LogPath      | ./             |
| LogLevel     | debug          |
| LogSql       | on             |
| SlowLogTime  | 10             |
| Nodes_Count  | 2              |
| Nodes_List   | node1,node2    |
| ClientConns  | 32             |
| ClientQPS    | 15             |
| ErrLogTotal  | 12             |
| SlowLogTotal | 26             |
+--------------+----------------+
6 rows in set (0.00 sec)

ClientConns:客户端连接数
ClientQPS:客户端的QPS大小
ErrLogTotal:kingshard启动以来产生的错误日志个数
SlowLogTotal:kingshard启动以来产生的慢日志个数

#查看node状态
mysql> admin server(opt,k,v) values('show','node','config');
+-------+--------------------+--------+-------+-------------------------------+---------+----------+------------+---------------+--------------+
| Node  | Address            | Type   | State | LastPing                      | MaxConn | IdleConn | CacheConns | PushConnCount | PopConnCount |
+-------+--------------------+--------+-------+-------------------------------+---------+----------+------------+---------------+--------------+
| node1 | 127.0.0.1:3306      | master | up    | 2015-08-07 15:54:44 +0800 CST  | 512     | 509      | 2          | 6301447       | 6300936      |
| node2 | 192.168.59.103:3307 | master | up    | 2015-08-07 15:54:44 +0800 CST  | 512     | 509      | 2          | 6301447       | 6300936      |
+-------+--------------------+--------+-------+-------------------------------+---------+----------+------------+---------------+--------------+
2 rows in set (0.00 sec)

#查看schema配置

mysql> admin server(opt,k,v) values('show','schema','config');
+-----------+-----------+------------------+---------+------+--------------+-----------+---------------+
| User      | DB        | Table            | Type    | Key  | Nodes_List   | Locations | TableRowLimit |
+-----------+-----------+------------------+---------+------+--------------+-----------+---------------+
| kingshard | kingshard |                  | default |      | node1        |           | 0             |
| kingshard | kingshard | test_shard_hash  | hash    | id   | node1, node2 | 4, 4      | 0             |
| kingshard | kingshard | test_shard_range | range   | id   | node1, node2 | 4, 4      | 10000         |
| root      | kingshard |                  | default |      | node1        |           | 0             |
+-----------+-----------+------------------+---------+------+--------------+-----------+---------------+

3 rows in set (0.00 sec)

#查看白名单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)

#查看黑名单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)

修改kingshard配置

为保证kingshard的安全性，管理端命令，只能通过root用户来操作，其他用户不能操作。也就是说用户列表中，需要配置一个root用户

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


#关闭sql日志打印
admin server(opt,k,v) values('change','log_sql','off')

#开启sql日志打印
admin server(opt,k,v) values('change','log_sql','on')

#修改慢sql日志时间, 单位ms
admin server(opt,k,v) values('change','slow_log_time','50');

#添加白名单IP
#支持IP或IP段
admin server(opt,k,v) values('add','allow_ip','192.168.14.0/24');
admin server(opt,k,v) values('add','allow_ip','192.168.15.1');

#删除白名单IP
admin server(opt,k,v) values('del','allow_ip','127.0.0.1');

#添加黑名单sql语句
admin server(opt,k,v) values('add','black_sql','select count(*) from sbtest1')

#删除黑名单sql语句
admin server(opt,k,v) values('del','black_sql','select count(*) from sbtest1')

#保存当前配置
admin server(opt,k,v) values('save','proxy','config')

上面是通过命令方式修改部分kingshard的配置，但更推荐通过修改配置文件，然后动态加载的方式来热加载配置文件，步骤如下：

1.修改kingshard 正在使用的配置文件，不能是kingshard未使用的文件

2.向kingshard发送USR1信号，kingshard就会加载新配置文件的全部内容

1

kill -USR1 pid_of_kingshard

支持LVS/Keepalived

1
2
3
4
5


#查看kingshard运行状态
admin server(opt,k,v) values('show','proxy','status')

#改变kingshard运行状态 online: 在线 offline: 下线
admin server(opt,k,v) values('change','proxy','online')

Web API

接口定义和参数说明

采用restful风格API设计。
假设kingshard的Web Server IP和端口是：127.0.0.1：9797，web用户名：admin，密码：admin

API接口导航

查看node的状态
添加slave
删除slave
设置slave状态
设置master状态
查看proxy状态
设置proxy状态
查看proxy的schema
查看proxy的客户端白名单
添加proxy的客户端白名单
删除proxy的客户端白名单
查看proxy的black_sql
增加proxy的black_sql
删除proxy的black_sql
设置proxy的slow sql开关
查看proxy的slow sql的时间
设置proxy的slow sql的时间
保存proxy的配置

1
2
3
4
5


Action:GET
URL:http://127.0.0.1:9797/api/v1/nodes/status
参数：无
返回结果：node数组，node中包含Master和Slave信息，
字段意思参考配置文件说明

示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


curl -X GET \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  127.0.0.1:9797/api/v1/nodes/status
 返回结果：
 [
    {
        "node": "node1",
        "address": "127.0.0.1:3307",
        "type": "master",
        "status": "up",
        "laste_ping": "2016-09-24 17:17:52 +0800 CST",
        "max_conn": 32,
        "idle_conn": 8,
        "cache_conn":12,
        "push_conn_count":32,
        "pop_conn_count":0
    },
    {
        "node": "node2",
        "address": "127.0.0.1:3309",
        "type": "master",
        "status": "up",
        "laste_ping": "2016-09-24 17:17:52 +0800 CST",
        "max_conn": 32,
        "idle_conn": 8,
        "cache_conn":12,
        "push_conn_count":32,
        "pop_conn_count":0
    }
]

1
2
3
4
5
6


Action:POST
URL:http://127.0.0.1:9797/api/v1/nodes/slaves
参数：
- node：节点名字
- addr：slave的IP和端口
返回结果：成功:"ok",失败："error message"

示例

1
2
3
4
5
6


 curl -X POST \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  -d '{"node":"node1","addr":"127.0.0.1:3309"}' \
  127.0.0.1:9797/api/v1/nodes/slaves
  返回结果："ok"

1
2
3
4
5
6


Action:DELETE
URL:http://127.0.0.1:9797/api/v1/nodes/slaves
参数：
- node：节点名字
- addr：slave的IP和端口
返回结果：成功:"ok",失败："error message"

示例

1
2
3
4
5
6


 curl -X DELETE \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  -d '{"node":"node1","addr":"127.0.0.1:3309"}' \
  127.0.0.1:9797/api/v1/nodes/slaves
  返回结果："ok"

1
2
3
4
5
6
7


Action:PUT
URL：http://127.0.0.1:9797/api/v1/nodes/slaves/status
参数：
- opt："up" or "down"，上线或下线slave
- node：节点名字
- addr：slave的IP和端口
返回结果：成功:"ok",失败："error message"

示例

1
2
3
4
5
6


 curl -X PUT \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  -d '{"opt":"up","node":"node1","addr":"127.0.0.1:3309"}' \
  http://127.0.0.1:9797/api/v1/nodes/slaves/status
  返回结果："ok"

1
2
3
4
5
6
7


Action:PUT
URL：http://127.0.0.1:9797/api/v1/nodes/masters/status
参数：
- opt："up" or "down"，上线或下线slave
- node：节点名字
- addr：slave的IP和端口
返回结果：成功:"ok",失败："error message"

示例

1
2
3
4
5
6


 curl -X PUT \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  -d '{"opt":"down","node":"node2","addr":"127.0.0.1:3309"}' \
  http://127.0.0.1:9797/api/v1/nodes/masters/status
  返回结果："ok"

1
2
3
4
5


Action:GET
URL:http://127.0.0.1:9797/api/v1/proxy/status
参数：无
返回结果："online"或者"offline"
注意：该API主要用于配合LVS平滑下线

示例

1
2
3
4
5


curl -X GET \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  http://127.0.0.1:9797/api/v1/proxy/status
 返回结果:"online"

1
2
3
4
5


Action:PUT
URL:http://127.0.0.1:9797/api/v1/proxy/status
参数：opt:"online"或者"offline"
返回结果：成功:"ok",失败："error message"
注意：该API主要用于配合LVS平滑下线

示例

1
2
3
4
5
6


curl -X PUT \
  -H 'Content-Type: application/json' \
  -u admin:admin \
   -d '{"opt":"offline"}' \
  http://127.0.0.1:9797/api/v1/proxy/status
  返回结果："ok"

1
2
3
4


Action：GET
http://127.0.0.1:9797/api/v1/proxy/schema
参数：无
返回结果:schema数组，但只有一项。字段意思参考配置文件

示例

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120


curl -X GET \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  127.0.0.1:9797/api/v1/proxy/schema
返回结果：
[
    {
        "user": "kingshard",
        "db": "kingshard",
        "Table": "",
        "Key": "",
        "Nodes": [
            "node1",
            "node2"
        ],
        "Locations": null,
        "Type": "default",
        "TableRowLimit": 0,
        "DateRange": null
    },
    {
        "user": "kingshard",
        "db": "kingshard",
        "Table": "test_shard_hash",
        "Key": "id",
        "Nodes": [
            "node1",
            "node2"
        ],
        "Locations": [
            4,
            4
        ],
        "Type": "hash",
        "TableRowLimit": 0,
        "DateRange": null
    },
    {
        "user": "kingshard",
        "db": "kingshard",
        "Table": "test_shard_range",
        "Key": "id",
        "Nodes": [
            "node1",
            "node2"
        ],
        "Locations": [
            4,
            4
        ],
        "Type": "range",
        "TableRowLimit": 10000,
        "DateRange": null
    },
    {
        "user": "kingshard",
        "db": "kingshard",
        "Table": "test_shard_time",
        "Key": "id",
        "Nodes": [
            "node1",
            "node2"
        ],
        "Locations": [
            2,
            2
        ],
        "Type": "hash",
        "TableRowLimit": 0,
        "DateRange": null
    },
    {
        "user": "kingshard",
        "db": "kingshard",
        "Table": "test_shard_month",
        "Key": "dtime",
        "Nodes": [
            "node1",
            "node2"
        ],
        "Locations": null,
        "Type": "date_month",
        "TableRowLimit": 0,
        "DateRange": [
            "201603-201605",
            "201609-201612"
        ]
    },
    {
        "user": "kingshard",
        "db": "kingshard",
        "Table": "test_shard_day",
        "Key": "mtime",
        "Nodes": [
            "node1",
            "node2"
        ],
        "Locations": null,
        "Type": "date_day",
        "TableRowLimit": 0,
        "DateRange": [
            "20160306-20160307",
            "20160308-20160309"
        ]
    },
    {
        "user": "root",
        "db": "kingshard",
        "Table": "",
        "Key": "",
        "Nodes": [
            "node1",
            "node2"
        ],
        "Locations": null,
        "Type": "default",
        "TableRowLimit": 0,
        "DateRange": null
    }
]

1
2
3
4


Action：GET
URL：http://127.0.0.1:9797/api/v1/proxy/allow_ips
参数：allow_ips，ip列表数组
返回结果：IP列表

示例

1
2
3
4
5
6


curl -X GET \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  localhost:9797/api/v1/proxy/allow_ips
 返回结果：["127.0.0.1","192.168.0.14"]
 

1
2
3
4


Action：POST
URL：http://127.0.0.1:9797/api/v1/proxy/allow_ips
参数：allow_ips，ip列表数组
返回结果：成功:"ok",失败："error message"

示例

1
2
3
4
5
6
7


curl -X POST \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  -d '{"allow_ips":["127.0.0.1","192.168.14.0/24","192.168.0.223"]}' \
  127.0.0.1:9797/api/v1/proxy/allow_ips
  返回结果："ok"
 

1
2
3
4


Action：DELETE
URL：http://127.0.0.1:9797/api/v1/proxy/allow_ips
参数：allow_ips，ip列表数组
返回结果：成功:"ok",失败："error message"

示例

1
2
3
4
5
6
7


curl -X DELETE \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  -d '{"allow_ips":["192.168.0.14","192.168.0.223"]}' \
  127.0.0.1:9797/api/v1/proxy/allow_ips
  返回结果："ok"
 

1
2
3
4


Action:GET
http://127.0.0.1：9797/api/v1/proxy/black_sqls
参数：无
返回结果:sql列表

示例

1
2
3
4
5


curl -X GET \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  127.0.0.1:9797/api/v1/proxy/black_sqls
返回结果：["delete from test_shard_range","delete from test_shard_hash"]

1
2
3
4


Action:POST
http://127.0.0.1:9797/api/v1/proxy/black_sqls
参数：sql（注意：一次只能添加一条SQL）
返回结果：成功:"ok",失败："error message"

示例

1
2
3
4
5
6


curl -X POST \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  -d '{"sql":"delete from test_shard_range"}' \
  127.0.0.1:9797/api/v1/proxy/black_sqls
  返回结果："ok"

1
2
3
4


Action:DELETE
http:// 127.0.0.1:9797/api/v1/proxy/black_sqls
参数：sql（注意：一次只能删除一条SQL）
返回结果：成功:"ok",失败："error message"

示例

1
2
3
4
5
6


curl -X DELETE \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  -d '{"sql":"delete from test_shard_range"}' \
  127.0.0.1:9797/api/v1/proxy/black_sqls
  返回结果："ok"

1
2
3
4


Action:PUT
URL:http://127.0.0.1:9797/api/v1/proxy/slow_sql/status
参数：opt(可选值："on","off")
返回结果：成功:"ok",失败："error message"

示例

1
2
3
4
5
6


curl -X PUT \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  -d '{"opt":"on"}' \
  127.0.0.1:9797/api/v1/proxy/slow_sql/status
  返回结果："ok"

1
2
3
4


Action:GET
URL:http://127.0.0.1:9797/api/v1/proxy/slow_sql/time
参数：无
返回结果：slow_log 时间，单位ms

1
2
3
4
5


curl -X GET \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  127.0.0.1:9797/api/v1/proxy/slow_sql/time
  返回结果：500

1
2
3
4


Action:PUT
URL:http://127.0.0.1:9797/api/v1/proxy/slow_sql/time
参数：slow_time(慢日志时间，单位ms。执行时间超过该值的SQL会输出到慢日志文件)
返回结果：成功:"ok",失败："error message"

示例

1
2
3
4
5
6


curl -X PUT \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  -d '{"slow_time":500}' \
  127.0.0.1:9797/api/v1/proxy/slow_sql/time
  返回结果："ok"

1
2
3
4
5


Action:PUT
URL:http://127.0.0.1:9797/api/v1/proxy/config/save
参数：无
返回结果：成功:"ok",失败："error message"
说明：将kingshard的配置写入文件

示例

1
2
3
4
5


curl -X PUT \
  -H 'Content-Type: application/json' \
  -u admin:admin \
  127.0.0.1:9797/api/v1/proxy/config/save
  返回结果："ok"

转载:https://github.com/flike/kingshard/blob/master/README_ZH.md

Kingshard管理端介绍

文章目录