基于新版本 v1.6.2,centOS

CLI使用HTTPAPI通过8086(默认的,可以在配置文件中修改)端口与influxDB直接交互,influx命令在所有influxDB的包中都默认包含

注意:在InfluxDB当中,并没有表(table)这个概念,取而代之的是MEASUREMENTS,MEASUREMENTS的功能与传统数据库中的表一致,因此我们也可以将MEASUREMENTS称为InfluxDB中的表

启动influx

influx命令在/usr/bin/influx

1
2
3
[root@test25 ~]# find / -name influx
/usr/bin/influx
[root@test25 ~]#

启动influxd

  • service influxdb start
  • influxd
1
2
[root@test25 ~]# service influxdb start
[root@test25 ~]#

influx参数

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
[root@test25 ~]# influx --help
Usage of influx:
-version
Display the version and exit.
-host 'host name'
Host to connect to.
-port 'port #'
Port to connect to.
-socket 'unix domain socket'
Unix socket to connect to.
-database 'database name'
Database to connect to the server.
-password 'password'
Password to connect to the server. Leaving blank will prompt for password (--password '').
-username 'username'
Username to connect to the server.
-ssl
Use https for requests.
-unsafeSsl
Set this when connecting to the cluster using https and not use SSL verification.
-execute 'command'
Execute command and quit.
-format 'json|csv|column'
Format specifies the format of the server responses: json, csv, or column.
-precision 'rfc3339|h|m|s|ms|u|ns'
Precision specifies the format of the timestamp: rfc3339, h, m, s, ms, u or ns.
-consistency 'any|one|quorum|all'
Set write consistency level: any, one, quorum, or all
-pretty
Turns on pretty print for the json format.
-import
Import a previous database export from file
-pps
How many points per second the import will allow. By default it is zero and will not throttle importing.
-path
Path to file to import
-compressed
Set to true if the import file is compressed

Examples:

# Use influx in a non-interactive mode to query the database "metrics" and pretty print json:
$ influx -database 'metrics' -execute 'select * from cpu' -format 'json' -pretty

# Connect to a specific database on startup and set database context:
$ influx -database 'metrics' -host 'localhost' -port '8086'
[root@test25 ~]#
参数 描述
-version 显示版本号并退出
-host ‘host name’ 连接到远程主机
-port ‘port #’ 连接远程主机端口
-socket ‘unix domain socket’ 连接unix套接字
-database ‘database name’ 连接到服务器的数据库
-password ‘password’ 连接到服务器的密码
-username ‘username’ 连接到服务器的用户名
-ssl 使用https请求
-unsafeSsl 使用https连接到集群时,不要使用SSL验证。
-execute ‘command’ 执行命令并退出
-format ‘json|csv|column’ 格式指定了服务器响应的格式:json、csv或column.默认是column
-precision ‘rfc3339|h|m|s|ms|u|ns’ 指定时间戳的格式精度: rfc3339 (YYYY-MM-DDTHH:MM:SS.nnnnnnnnnZ), h (hours), m (minutes), s (seconds), ms (milliseconds), u (microseconds), ns (nanoseconds)..
-consistency ‘any|one|quorum|all’ 设置写一致性级别: any, one, quorum, or all
-pretty 打开美化json打印
-import 导入备份的数据库文件
-pps 导入允许每秒多少个点。默认情况下,它是零,不会限制进口
-path import的文件路径
-compressed 如果导入文件被压缩,则设置为true

给出一些demo

  • 显示版本 -version
1
2
[root@test25 ~]# influx -version
InfluxDB shell version: 1.6.2

连接指定主机 -host

1
2
3
4
[root@test25 ~]# influx -host localhost
Connected to http://localhost:8086 version 1.6.1
InfluxDB shell version: 1.6.2
> quit

用使用端口连接主机 -port

1
2
3
4
[root@test25 ~]# influx -host localhost -port 8086
Connected to http://localhost:8086 version 1.6.1
InfluxDB shell version: 1.6.2
> quit

连接指定数据库 -database

1
2
3
4
[root@test25 ~]# influx -host localhost -port 8086 -database mydb
Connected to http://localhost:8086 version 1.6.1
InfluxDB shell version: 1.6.2
> quit

使用指定的用户名和密码(添加用户见下面) -password -username

1
2
3
4
[root@test25 ~]# influx -host localhost -port 8086 -database mydb -username 'yueling' -password '123456'
Connected to http://localhost:8086 version 1.6.1
InfluxDB shell version: 1.6.2
>

添加用户:yueling和管理员用户lingyue

1
2
3
4
5
6
7
8
9
10
11
12
13
14
[root@test25 ~]# influx -database 'mydb' -host 'localhost' -port '8086'
Connected to http://localhost:8086 version 1.6.1
InfluxDB shell version: 1.6.2
> show users
user admin
---- -----
> create user "yueling" with password '123456'
> create user "lingyue" with password '123456' with all privileges
> show users
user admin
---- -----
yueling false
lingyue true
>

执行命令并返回 -execute

1
2
3
4
5
6
7
8
9
[root@test25 ~]# influx -host localhost -port 8086 -database mydb -username 'yueling' -password '123456' -execute 'select * from cpu_load_short'
name: cpu_load_short
time direction host region value
---- --------- ---- ------ -----
1422568543702900257 in server01 us-west 2
1422568543702900257 server02 us-west 0.55
1434055562000000000 server01 us-west 0.64
1536579271011551389 server02 0.67
[root@test25 ~]#

指定返回格式 -format

1
2
3
4
5
6
7
8
9
10
11
[root@test25 ~]# influx -host localhost -port 8086 -database mydb -username 'yueling' -password '123456' -execute 'select * from cpu_load_short' -format json
{"results":[{"series":[{"name":"cpu_load_short","columns":["time","direction","host","region","value"],"values":[[1422568543702900257,"in","server01","us-west",2],[1422568543702900257,null,"server02","us-west",0.55],[1434055562000000000,null,"server01","us-west",0.64],[1536579271011551389,null,"server02",null,0.67]]}]}]}

[root@test25 ~]# influx -host localhost -port 8086 -database mydb -format json
Connected to http://localhost:8086 version 1.6.1
InfluxDB shell version: 1.6.2
> use mydb
Using database mydb
> select * from cpu_load_short
{"results":[{"series":[{"name":"cpu_load_short","columns":["time","direction","host","region","value"],"values":[[1422568543702900257,"in","server01","us-west",2],[1422568543702900257,null,"server02","us-west",0.55],[1434055562000000000,null,"server01","us-west",0.64],[1536579271011551389,null,"server02",null,0.67]]}]}]}
>

时间戳精度 -precision

1
2
3
4
5
6
7
8
[root@test25 ~]# influx -host localhost -port 8086 -database mydb -username 'yueling' -password '123456' -execute 'select * from cpu_load_short' -format column -precision ms
name: cpu_load_short
time direction host region value
---- --------- ---- ------ -----
1422568543702 in server01 us-west 2
1422568543702 server02 us-west 0.55
1434055562000 server01 us-west 0.64
1536579271011 server02 0.67

使用json美化输出 -pretty

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
[root@test25 ~]# influx -host localhost -port 8086 -database mydb -username 'yueling' -password '123456' -execute 'select * from cpu_load_short' -format json
-pretty
{
"results": [
{
"series": [
{
"name": "cpu_load_short",
"columns": [
"time",
"direction",
"host",
"region",
"value"
],
"values": [
[
1422568543702900257,
"in",
"server01",
"us-west",
2
],
[
1422568543702900257,
null,
"server02",
"us-west",
0.55
],
[
1434055562000000000,
null,
"server01",
"us-west",
0.64
],
[
1536579271011551389,
null,
"server02",
null,
0.67
]
]
}
]
}
]
}

使用文件导入数据 -import

  • 导入的文件包含两个章节
    • DDL:Data Definition Language
    • DML:Data Manipulation Language

注意文件里的时间戳,自己试验的时候记得修改使符合规则 比如当前时间戳 date +’%s’

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[root@test25 ~]# cat dataarr.txt 
# DDL
CREATE DATABASE pirates
CREATE RETENTION POLICY oneday ON pirates DURATION 1d REPLICATION 1

# DML
# CONTEXT-DATABASE: pirates
# CONTEXT-RETENTION-POLICY: oneday

treasures,captain_id=dread_pirate_roberts value=801 1536658695
treasures,captain_id=flint value=29 1536658695
[root@test25 ~]#
[root@test25 ~]# influx -import -path=dataarr.txt -precision=s
2018/09/11 17:40:39 Processed 2 commands
2018/09/11 17:40:39 Processed 2 inserts
2018/09/11 17:40:39 Failed 0 inserts
[root@test25 ~]#

如果是压缩的文件 要配合 -compressed

1
2
3
4
5
6
7
8
9
10
[root@test25 ~]# gzip dataarr.txt
[root@test25 ~]# ls
anaconda-ks.cfg dataarr.txt.gz
……

[root@test25 ~]# influx -import -path=dataarr.txt.gz -precision=s -compressed
2018/09/11 17:50:47 Processed 2 commands
2018/09/11 17:50:47 Processed 2 inserts
2018/09/11 17:50:47 Failed 0 inserts
[root@test25 ~]#

influx命令

1
2
3
4
[root@test25 ~]# influx
Connected to http://localhost:8086 version 1.6.1
InfluxDB shell version: 1.6.2
>

查看influx内部命令用法

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
> help
Usage:
connect <host:port> connects to another node specified by host:port
auth prompts for username and password
pretty toggles pretty print for the json format
chunked turns on chunked responses from server
chunk size <size> sets the size of the chunked responses. Set to 0 to reset to the default chunked size
use <db_name> sets current database
format <format> specifies the format of the server responses: json, csv, or column
precision <format> specifies the format of the timestamp: rfc3339, h, m, s, ms, u or ns
consistency <level> sets write consistency level: any, one, quorum, or all
history displays command history
settings outputs the current settings for the shell
clear clears settings such as database or retention policy. run 'clear' for help
exit/quit/ctrl+d quits the influx shell

show databases show database names
show series show series information
show measurements show measurement information
show tag keys show tag key information
show field keys show field key information

A full list of influxql commands can be found at:
https://docs.influxdata.com/influxdb/latest/query_language/spec/
>
命令 描述
connect host:port 通过host:port连接指定服务器
auth 提示输入用户名和密码
pretty 开启/关闭美化json输出
chunked 开启/关闭服务器响应
chunk size 设置服务器响应的大小,设置为0则重置返回大小
use 切换数据库
format 指定输出格式: json, csv, or column(默认是column,当然启动的时候可以用-format指定)
precision 时间戳精度: rfc3339 (YYYY-MM-DDTHH:MM:SS.nnnnnnnnnZ), h (hours), m (minutes), s (seconds), ms (milliseconds), u (microseconds), ns (nanoseconds)
consistency 设置并发级别: any, one, quorum, or all
history 显示执行命令历史,类似linux的history
settings 输出当前的设置项
clear 清除数据库设置或者保留策略 clear [ database | db | retention policy | rp ]
exit/quit/ctrl+d 退出influx
show databases 显示所有数据库的名称
show series 显示所有series信息
show measurements 显示所有指标信息,相当于表
show tag keys 显示所有tag信息
show field keys 显示所有field信息
insert 写入数据insert into

influx命令的一些示例

  • 连接服务器、认证 connect和auth
1
2
3
4
5
6
7
8
[root@test25 ~]# influx -database 'mydb' -host 'localhost' -port '8086'
Connected to http://localhost:8086 version 1.6.1
InfluxDB shell version: 1.6.2
> connect localhost:8086
> auth
username: lingyue
password:
>
  • 显示设置 settings
1
2
3
4
5
6
7
8
9
10
11
12
> settings
Setting Value
-------- --------
Host localhost:8086
Username
Database mydb
RetentionPolicy
Pretty false
Format column
Write Consistency all
Chunked true
Chunk Size 0
  • 显示数据 show databases
1
2
3
4
5
6
7
8
9
> show databases
name: databases
name
----
jmeter
_internal
test
mydb
pirates
  • 切换database use databaseName
1
2
> use mydb
Using database mydb
  • 显示指标 show measurements
1
2
3
4
5
> show measurements
name: measurements
name
----
cpu_load_short
  • 显示tag
1
2
3
4
5
6
7
> show tag keys
name: cpu_load_short
tagKey
------
direction
host
region
  • 显示series
1
2
3
4
5
6
7
> show series
key
---
cpu_load_short,direction=in,host=server01,region=us-west
cpu_load_short,host=server01,region=us-west
cpu_load_short,host=server02
cpu_load_short,host=server02,region=us-west
  • 显示filed
1
2
3
4
5
> show field keys
name: cpu_load_short
fieldKey fieldType
-------- ---------
value float
  • 指定输出格式
1
2
3
> format json
> show databases
{"results":[{"series":[{"name":"databases","columns":["name"],"values":[["jmeter"],["_internal"],["test"],["mydb"],["pirates"]]}]}]}
  • 格式化json输出
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
> pretty
Pretty print enabled
> show databases
{
"results": [
{
"series": [
{
"name": "databases",
"columns": [
"name"
],
"values": [
[
"jmeter"
],
[
"_internal"
],
[
"test"
],
[
"mydb"
],
[
"pirates"
]
]
}
]
}
]
}
  • 显示执行命令历史 history
1
2
3
4
5
6
7
8
9
> history
create database test
show databases
……
format json
show databases
pretty
show databases
>
  • insert
    InfluxDB中没有显式的新建表的语句,只能通过insert数据的方式来建立新表
    增加数据采用insert的方式,要注意的是 InfluxDB的insert中,表名与数据之间用逗号(,)分隔,tag和field之间用 空格分隔,多个tag或者多个field之间用逗号(,)分隔
1
2
3
4
5
6
7
> INSERT treasures,captain_id=pirate_king value=2
> SHOW RETENTION POLICIES ON mydb
name duration shardGroupDuration replicaN default
---- -------- ------------------ -------- -------
autogen 0s 168h0m0s 1 true
> INSERT INTO autogen treasures,captain_id=pirate_king value=2
>
  • select 查询数据
    和sql结构一致
1
2
3
4
5
6
7
8
9
> select * from cpu_load_short
name: cpu_load_short
time direction host region value
---- --------- ---- ------ -----
1422568543702900257 in server01 us-west 2
1422568543702900257 server02 us-west 0.55
1434055562000000000 server01 us-west 0.64
1536579271011551389 server02 0.67
>
  • drop 删除measurement和database
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
> show databases
name: databases
name
----
jmeter
_internal
test
mydb
pirates
> use test
Using database test
> show measurements
name: measurements
name
----
disk_free
> drop measurement disk_free
> show measurements
> drop database test
> show databases
name: databases
name
----
jmeter
_internal
mydb
pirates
>