InfluxDB 安装与使用
版本：1.8.10
系统：ubuntu 16.04
web：chronograf
1.安装
mkdir <database_dir>
cd <database_dir>
#添加仓库
curl -sL https://repos.influxdata.com/influxdb.key | sudo apt-key add -
source /etc/lsb-release
echo "deb https://repos.influxdata.com/${DISTRIB_ID,,} ${DISTRIB_CODENAME} stable" | sudo tee /etc/apt/sources.list.d/influxdb.list
sudo apt-get update && sudo apt-get install influxdb
sudo service influxdb start2.配置
指定配置文件地址
influxd -config /etc/influxdb/influxdb.conf开启认证
[http]
enabled = true
bind-address = ":8086"
auth-enabled = true
log-enabled = true
write-tracing = false
pprof-enabled = false
https-enabled = false
https-certificate = "/etc/ssl/influxdb.pem"重启
sudo service influxdb restart创建admin用户
influx
#创建admin给予所有权限
CREATE USER admin WITH PASSWORD [REDACTED] WITH ALL PRIVILEGES
auth
admin
admin
成功3.安装web管理
a.安装
#安装chronograf
sudo apt-get install chronograf
#参数
sudo chronograf -help
#默认是8888 和宝塔冲突了 改下端口
sudo chronograf --port 9010b.使用
打开 http://:9010 输入 账号密码
4使用
插入数据
curl -i -XPOST "http://localhost:8086/write?db=science_is_cool" --data-binary 'weather,location=us-midwest temperature=82 1465839830100400200'5.常见错误
a. invalid boolean
字符串需要用双引号包住
b. invalid field format
数据格式应当按照
<table_name>,<tag_key>=<tag_value>,<tag_key>=<tag_value> <field_key>=<field_value>,<field_key>=<field_value> timestamp
tag 和 filed 和 timestrap 中间有空格分割
c.查询的时候 where条件 tag-value 需要用单引号引起
d.InfluxDBClientError Request Entity Too Large
vim /etc/influxdb/influxdb.conf
#max-body设置为0
max-body-size = 0
#重启influxdb
sudo service influxdb restarte.MemoryError
#插入大量数据的时候会报错
插入完数据 close()一下f.mysql influxdb大批量数据导入造成influxdb崩溃
之前使用的是http，换成udp连接方式就好了
每次write后关闭链接
6.python连接influxDB
参数
- host ( str ) – 连接到 InfluxDB 的主机名，默认为 ‘localhost’
- port ( int ) – 连接到 InfluxDB 的端口，默认为 8086
- 用户名( str ) – 要连接的用户，默认为 ‘root’
- 密码( str ) – 用户的密码，默认为 ‘root’
- pool_size ( int ) – urllib3 连接池大小，默认为 10。
- database ( str ) – 要连接的数据库名称，默认为 None
- ssl ( bool ) – 使用 https 而不是 http 连接到 InfluxDB，默认为 False
- verify_ssl ( bool ) – 验证 HTTPS 请求的 SSL 证书，默认为 False
- timeout ( int ) – 请求将等待您的客户端建立连接的秒数，默认为 None
- retries ( int ) – 客户端在中止前将进行的尝试次数，默认为 3 0 - 尝试直到成功 1 - 仅尝试一次（不重试） 2 - 最多两次尝试（包括一次重试） 3 - 最多三次尝试（默认选项)
- use_udp ( bool ) – 使用 UDP 连接到 InfluxDB，默认为 False
- udp_port ( int ) – 连接到 InfluxDB 的 UDP 端口，默认为 4444
- proxies ( dict ) – 用于请求的 HTTP(S) 代理，默认为 {}
- path ( str ) – 要连接的服务器上 InfluxDB 的路径，默认为 ‘’
- cert ( str ) – 用于相互 TLS 身份验证的客户端证书信息的路径。您可以指定本地证书用作包含私钥和证书的单个文件，或作为两个文件路径的元组，默认为无
- gzip ( bool ) – 使用 gzip 内容编码来压缩请求
- session ( requests.Session ) – 允许新的客户端请求使用现有的请求会话，默认为 None
- headers ( dict ) – 添加到请求的标头，将添加“Content-Type”和“Accept”，除非它们已经存在，默认为 {}
- socket_options ( list ) - 使用自定义 tcp 套接字选项，如果未指定，则从加载默认值
HTTPConnection.default_socket_options
实例
# -*- coding: utf-8 -*-
"""Tutorial on using the InfluxDB client."""
import argparse
from influxdb import InfluxDBClient
def main(host='localhost', port=8086):
"""Instantiate a connection to the InfluxDB."""
user = 'root'
password = 'root'
dbname = 'example'
dbuser = 'smly'
dbuser_password = 'my_secret_password'
query = 'select Float_value from cpu_load_short;'
query_where = 'select Int_value from cpu_load_short where host=$host;'
bind_params = {'host': 'server01'}
json_body = [
{
"measurement": "cpu_load_short",
"tags": {
"host": "server01",
"region": "us-west"
},
"time": "2009-11-10T23:00:00Z",
"fields": {
"Float_value": 0.64,
"Int_value": 3,
"String_value": "Text",
"Bool_value": True
}
}
]
client = InfluxDBClient(host, port, user, password, dbname)
print("Create database: " + dbname)
client.create_database(dbname)
print("Create a retention policy")
client.create_retention_policy('awesome_policy', '3d', 3, default=True)
print("Switch user: " + dbuser)
client.switch_user(dbuser, dbuser_password)
print("Write points: {0}".format(json_body))
client.write_points(json_body)
print("Querying data: " + query)
result = client.query(query)
print("Result: {0}".format(result))
print("Querying data: " + query_where)
result = client.query(query_where, bind_params=bind_params)
print("Result: {0}".format(result))
print("Switch user: " + user)
client.switch_user(user, password)
print("Drop database: " + dbname)
client.drop_database(dbname)
client.close()
def parse_args():
"""Parse the args."""
parser = argparse.ArgumentParser(
description='example code to play with InfluxDB')
parser.add_argument('--host', type=str, required=False,
default='localhost',
help='hostname of InfluxDB http API')
parser.add_argument('--port', type=int, required=False, default=8086,
help='port of InfluxDB http API')
return parser.parse_args()
if __name__ == '__main__':
args = parse_args()
main(host=args.host, port=args.port)7.mysql 对比 sqlite 对比 influxdb
#数据量 3600w
select * from env_data_his_dust_meter_2 where mn = '20200409000010'
mysql->总数据为637849-查询时间为-19.649017333984375
sqlite->总数据为637849-查询时间为-6.131999254226685
influxdb->总数据为636689-查询时间为-7.540003776550293
select * from env_data_his_dust_meter_2 where mn = '20200409000010' and create_time > '2021-10-01 00:00:00'
mysql->总数据为193088-查询时间为-7.801991701126099
sqlite->总数据为193088-查询时间为-1.8469734191894531
influxdb->总数据为192484-查询时间为-2.0109972953796387# -*- coding: utf-8 -*-
# !/usr/bin/env python
# @Time : 2022/2/12 14:59
# @Author : mtl
# @Desc : ***
# @File : contrast.py
# @Software: PyCharm
import time
from influxdb import InfluxDBClient
from db_tool import getInfoToSqlAll, insert
from main import sql_db
"""
select * from env_data_his_dust_meter_2 where mn = '20200409000010'
mysql->总数据为637849-查询时间为-19.649017333984375
sqlite->总数据为637849-查询时间为-6.131999254226685
influxdb->总数据为636689-查询时间为-7.540003776550293
select * from env_data_his_dust_meter_2 where mn = '20200409000010' and create_time > '2021-10-01 00:00:00'
mysql->总数据为193088-查询时间为-7.801991701126099
sqlite->总数据为193088-查询时间为-1.8469734191894531
influxdb->总数据为192484-查询时间为-2.0109972953796387
"""
def mysql_vs_sqlite():
# #mysql
t1 = time.time()
sql = "select * from env_data_his_dust_meter_2 where mn = '20200409000010' and create_time > '2021-10-01 00:00:00' "
result = getInfoToSqlAll(sql)
t2 = time.time()
print(f"mysql->总数据为{len(result)}-查询时间为-{t2 - t1}")
#
# result = []
# #sqlite
t1 = time.time()
sd = sql_db()
sql = "select * from env_data_his_dust_meter_2 where mn = '20200409000010' and create_time > '2021-10-01 00:00:00' "
result = sd.get(sql)
t2 = time.time()
print(f"sqlite->总数据为{len(result)}-查询时间为-{t2 - t1}")
#influxdb
t1 = time.time()
client = InfluxDBClient(host='192.168.1.10', port=8086, username='admin', password='admin', database='mydb')
result = client.query("select * from env_data_his_dust_meter where mn = '20200409000010' and time > '2021-10-01 00:00:00' ")
t2 = time.time()
print(f"influxdb->总数据为{len(list(result.get_points()))}-查询时间为-{t2 - t1}")
if __name__ == '__main__':
mysql_vs_sqlite()