InfluxDB 安装部署 | HelloDog

前言

InfluxDB 是专为时序数据设计的数据库，能支撑大量的读写负载，是一个高性能的时序数据 datastore。

InfluxDB is the Time Series Database in the TICK Stack

更新历史

2019年07月25日 - 更新influx命令使用技巧
2019年04月05日 - 初稿

阅读原文 - https://wsgzao.github.io/post/influxdb/

InfluxDB - https://www.influxdata.com/time-series-platform/influxdb/

InfluxDB 简介

InfluxDB is the Time Series Database in the TICK Stack

InfluxData’s TICK Stack is built around InfluxDB to handle massive amounts of time-stamped information. This time series database provides support for your metrics analysis needs, from DevOps Monitoring, IoT Sensor data, and Real-Time Analytics. Users can adapt their SQL skills with InfluxQL, so they can get up to speed on this time series database.

默认预留端口:

8086，HTTP API
8088，RPC 端口，用于备份和恢复

NTP 服务:
InfluxDB 使用机器本地时间作为 timestamp，需要机器之间使用 NTP 进行同步；如果没有同步的话，写入的时间序列数据可能会不准确

InfluxDB 安装

InfluxDB 里存储的数据被称为时间序列数据, InfluxDB 存储方式跟传统关系型数据库不同的是：传统关系型数据库通过数据库 + 表 + 字段组织数据，InfluxDB 通过指标、标签、字段组织数据，时间戳是默认的索引列，标签跟字段其实就相当于关系型数据库中的字段，只不过标签会被索引，而字段不会。

Grafana 默认支持的数据源：Graphite，InfluxDB，OpenTSDB，Prometheus，Elasticsearch，CloudWatch
Grafana 支持同时绑定多套数据源，根据自己需求管理即可，这里以InfluxDB为例。

https://portal.influxdata.com/downloads

# install influxdb
cat > /etc/yum.repos.d/influxdb.repo << 'EOF'
[influxdb]
name = InfluxDB Repository - RHEL \$releasever
baseurl = https://repos.influxdata.com/rhel/\$releasever/\$basearch/stable
enabled = 1
gpgcheck = 1
gpgkey = https://repos.influxdata.com/influxdb.key
EOF

# yum install influxdb
yum install -y influxdb

# start and enable influxdb
sudo systemctl start influxdb
sudo systemctl enable influxdb
sudo systemctl status influxdb

# 通过influx命令进入cli命令行
influx
Connected to http://localhost:8086 version 1.4.2
InfluxDB shell version: 1.4.2
>

# 查看用户
SHOW USERS

# 创建用户
CREATE USER influx WITH PASSWORD 'influx' WITH ALL PRIVILEGES

# 查看用户
SHOW USERS

# 创建数据库
CREATE DATABASE test

# 查看数据库
SHOW DATABASES

# Using 数据库
USE test

# 插入数据
INSERT cpu,host=192.168.1.1 load=0.1,usage=0.2

# 查询所有数据
SELECT * FROM "cpu"
SELECT "host","load","usage" FROM "cpu"

# 根据条件查询
SELECT "host","load","usage" FROM "cpu" WHERE "host" = '192.168.1.1'
SELECT "host","load","usage" FROM "cpu" WHERE "usage" > 0.1

# 创建数据库
CREATE DATABASE "db_name"
# 显示所有数据库
SHOW DATABASES
# 删除数据库
DROP DATABASE "db_name"

# 使用数据库
USE mydb
# 显示该数据库中的表
SHOW MEASUREMENTS
# 删除表
DROP MEASUREMENT "t_name"

# 简单查询
SELECT * FROM codis_usage ORDER BY time DESC LIMIT 3
# 最近60min内的数据
SELECT * FROM codis_usage WHERE time >= now() - 60m;
# 获取最近更新数据，并转换为当前时间
precision rfc3339
select * from codis_usage order by time desc limit 10;


# 查询保存策略
show retention policies on codis
name    duration shardGroupDuration replicaN default
----    -------- ------------------ -------- -------
autogen 0s       168h0m0s           1        true

# 创建新的 Retention Policies 并设置为默认值
# duration 保留多少天
# replication 副本数
create retention policy "rp_14d" ON "codis" duration 14d replication 1 default

# 恢复默认策略（永久保存）
alter retention policy "autogen" on "codis" duration 0s replication 1 default


# 创建数据库 API
curl -i -XPOST http://localhost:8086/query --data-urlencode "q=CREATE DATABASE test"

# 写入数据 API

# 写入单条
curl -i -XPOST http://localhost:8086/write?db=test --data-binary "cpu,host=192.168.1.3 load=0.1,usage=0.33"
curl -i -XPOST http://localhost:8086/write?db=test --data-binary "cpu,host=192.168.1.3 load=0.1,usage=0.33 6666666666666666666"

# 写入多条
curl -i -XPOST http://localhost:8086/write?db=test --data-binary "cpu,host=192.168.1.2 load=0.1,usage=0.22 1666666666666666661 
cpu,host=192.168.1.3 load=0.1,usage=0.33 1666666666666666661 
cpu,host=192.168.1.2 load=0.2,usage=0.22 1666666666666666662 
cpu,host=192.168.1.3 load=0.2,usage=0.33 1666666666666666662"

# 查询数据 API
curl -G http://localhost:8086/query?db=test --data-urlencode "q=SELECT * FROM  \"cpu\""

influx命令

[root@sg-gop-10-71-12-145 wangao]# influx
Connected to http://localhost:8086 version 1.7.7
InfluxDB shell version: 1.7.7
> 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/
>

用户权限管理

InfluxDB 的权限设置比较简单，只有读、写、ALL 三种，详细参考官方文档。默认不开启用户认证，需要修改配置文件：

1 2	[http] auth-enabled = true

https://docs.influxdata.com/influxdb/v1.7/administration/authentication_and_authorization/

常见命令如下：

# 授权
GRANT [READ,WRITE,ALL] ON <database_name> TO <username>
GRANT ALL PRIVILEGES TO "username"

# 撤销权限
REVOKE [READ,WRITE,ALL] ON <database_name> FROM <username>
REVOKE ALL PRIVILEGES FROM "username"

# 查看权限
SHOW GRANTS FOR <user_name>

# 显示用户
SHOW USERS

# 创建用户
CREATE USER "readonly" WITH PASSWORD 'password'

# 创建管理员权限的用户
CREATE USER "readonly" WITH PASSWORD 'password' WITH ALL PRIVILEGES

# 删除用户
DROP USER "readonly"

# 修改密码
SET PASSWORD FOR <username> = '<password>'

数据保存策略

也就是 Retention Policies，可以设置保存的时间，例如保存 30 天
 https://docs.influxdata.com/influxdb/v1.7/guides/downsampling_and_retention/

# 查询
SHOW RETENTION POLICIES ON "database_name";

# 新建
CREATE RETENTION POLICY "rp_name" ON "db_name" DURATION 30d REPLICATION 1 DEFAULT;

# 修改
ALTER RETENTION POLICY "rp_name" ON db_name DURATION 3w DEFAULT;

# 删除
DROP RETENTION POLICY "rp_name" ON "db_name";

连续查询

也就是 Continuous Queries，当数据超过保存策略里指定的时间之后，就会被删除；可以通过连续查询把原来的秒级数据，保存为分钟级或者小时级的数据，从而减小数据的占用空间。
https://docs.influxdata.com/influxdb/v1.7/query_language/continuous_queries/

#查看
SHOW CONTINUOUS QUERIES;

# 创建
CREATE CONTINUOUS QUERY cq-name ON db-name BEGIN
    SELECT mean(tbl-name) INTO newtbl-name FROM tbl-name GROUP BY time(30m) END;

# 删除
DROP CONTINUOUS QUERY <cq-name> ON <db-name>;

备份恢复

只支持全量备份，不支持增量，包括了元数据以及增量数据的备份，可以参考官方文档
 https://docs.influxdata.com/influxdb/v1.7/administration/backup_and_restore/

# 元数据备份
influxd backup <path-to-backup>

# 数据备份
influxd backup -database <mydatabase> <path-to-backup>
influxd backup -database telegraf -retention autogen -since 2016-02-01T00:00:00Z /tmp/backup
influxd backup -database mydatabase -host 10.0.0.1:8088 /tmp/remote-backup

# 恢复
influxd restore -metadir /var/lib/influxdb/meta /tmp/backup
influxd restore -database telegraf -datadir /var/lib/influxdb/data /tmp/backup

time 格式显示设置

# 方法1
$ influx -precision rfc3339

# 方法2
$ influx
  Connected to http://localhost:8086 version 0.xx.x
  InfluxDB shell 0.xx.x
  > precision rfc3339

> precision rfc3339
> select * from codis_usage order by time desc limit 10;
name: codis_usage
time                           admin_addr         hostname              ops_fails ops_qps ops_redis_errors ops_total   product_name   proxy_addr        runtime_gc_num runtime_gc_total_pausems runtime_num_cgo_call runtime_num_goroutines runtime_num_mem_offheap runtime_num_procs rusage_cpu          rusage_mem sessions_alive sessions_total token
----                           ----------         --------              --------- ------- ---------------- ---------   ------------   ----------        -------------- ------------------------ -------------------- ---------------------- ----------------------- ----------------- ----------          ---------- -------------- -------------- -----
2019-07-25T10:48:43.186693914Z 10.71.15.97:11080  sg-gop-xxx-xxx-xxx-xx                                                gop-codis-misc 10.71.15.97:6389                                          2455469                                     49217536                8                                     456200192  58                            2bdaa076222fec82f9204310255cc2e3
2019-07-25T10:48:43.160702877Z 10.71.15.98:11080  sg-gop-xxx-xxx-xxx-xx 0         44      12258            8381660     gop-codis-misc 10.71.15.98:6389  5099           426                      2455685              266                    48037888                8                 0                   390696960  56             12404          b2d20caf9f3c9af555cb9a9fa5c07844
2019-07-25T10:48:42.838745142Z 10.71.15.96:11080  sg-gop-xxx-xxx-xxx-xx 0         0       0                0           gop-codis-data 10.71.15.96:6389  79             6                        37247                26                     0                       8                 0                   142471168  0              0              47629170465564037278443f7fc0a2b0
2019-07-25T10:48:42.786535306Z 10.71.14.113:11080 localhost.localdomain 0         9740    4                32159612367 gop-codis      10.71.15.112:6389 27922          10471                    76744489             8754                   1073741824              8                 0.809847412599755   4961280000 4304           45663317       e72634f3a81e4edcf24dca9454e22c1b
2019-07-25T10:48:42.727826769Z 10.71.14.112:11080 localhost.localdomain 0         9602    2                33816047337 gop-codis      10.71.15.112:6389 31458          11034                    77809963             8806                   1073741824              8                 0.7998623956727604  4950290432 4330           45663036       646f70b8381ca449cad4bb1316e17b78
2019-07-25T10:48:42.598859131Z 10.71.15.95:11080  sg-gop-xxx-xxx-xxx-xx 0         0       0                0           gop-codis-data 10.71.15.95:6389  80             5                        37771                26                     0                       8                 0                   140034048  0              0              939a496343900150dc87238b953bb14b
2019-07-25T10:48:42.333392816Z 10.71.15.91:11080  sg-gop-xxx-xxx-xxx-xx 0         4034    0                93719633    gop-codis-pay  10.71.15.91:6389  5122           445                      2463785              4406                   876150784               8                 0.36992110507631487 1682124800 2126           4836           64ebb14da86e74313c0f5dca10336a4d
2019-07-25T10:48:42.322101493Z 10.71.15.92:11080  sg-gop-xxx-xxx-xxx-xx 0         3721    0                89276911    gop-codis-pay  10.71.15.92:6389  5122           450                      2463671              4388                   895614976               8                 0.3399291832331154  1677733888 2117           4750           7b76feefc30f93483d7b8e5432084f34
2019-07-25T10:48:42.227508795Z 10.71.15.93:11080  sg-gop-xxx-xxx-xxx-xx 0         405     0                15300438    gop-codis-auth 10.71.15.93:6389  5096           434                      2446269              1504                   414908416               8                 0.04998992792934094 613736448  675            1592           7a320ee2bb9fe9cecfdd685a5fbfbc1c
2019-07-25T10:48:42.194695137Z 10.71.15.94:11080  sg-gop-xxx-xxx-xxx-xx 0         524     0                15128150    gop-codis-auth 10.71.15.94:6389  5096           421                      2446079              1486                   409600000               8                 0.04998935201807339 492064768  666            1546           6b516b3b0344063a6936fc13c40fc40a
>

其它

# 获取最近更新数据，并转换为当前时间
select threads_running from mysql order by time desc limit 1;
date -d @`echo 1483441750000000000 | awk '{print substr($0,1,10)}'` +"%Y-%m-%d %H:%M:%S"

# 检查系统是否存活
$ curl -sl -I localhost:8086/ping

# 简单查询
SELECT * FROM weather ORDER BY time DESC LIMIT 3;

# 指定时间范围，时间格式也可以为'2017-01-03 00:00:00'
SELECT usage_idle FROM cpu WHERE time >= '2017-01-03T12:40:38.708Z' AND time <= '2017-01-03T12:40:50.708Z';

# 最近40min内的数据
SELECT * FROM mysql WHERE time >= now() - 40m;

# 最近5分钟的秒级差值
SELECT derivative("queries", 1s) AS "queries" from "mysql" where time > now() - 5m;

# 最近5min的秒级写入
$ influx -database '_internal' -precision 'rfc3339'
      -execute 'select derivative(pointReq, 1s) from "write" where time > now() - 5m'

# 也可以通过日志查看
$ grep 'POST' /var/log/influxdb/influxd.log | awk '{ print $10 }' | sort | uniq -c
$ journalctl -u influxdb.service | awk '/POST/ { print $10 }' | sort | uniq -c

InfluxDB 配置优化

配置文件默认全部注释掉，使用默认的配置项，可以根据需要配置。每个配置项有对应的、相同功能的环境变量。

配置文件：/etc/influxdb/influxdb.conf

查看配置：influxd config

[meta]                                # 元数据
dir = /var/lib/influxdb/meta  

[data]                                # 数据/WAL
dir = /var/lib/influxdb/data  
wal-dir = /var/lib/influxdb/wal  
cache-max-memory-size = "1g"          # Cache最大可用内存

[coordinator]                         # 查询相关
query-timeout = "0s"                  # 查询最大执行时间  
log-queries-after = "0s"              # 打印慢查询

[http]           # HTTP服务
auth-enabled = false                  # 启用安全认证  
max-connection-limit = 0              # 最大连接数