官方链接：https://docs.influxdata.com/influxdb/v1.8/query_language/

1、简单操作

1.1、登录数据库

influx -username admin -host 10.0.0.92 -port 31523 -database db_test
influx -precision s -username admin -host 10.0.0.92 -port 31523 -database db_test

1.2、查看信息

show databases;
show measurements;
show retention policies on db_test;
show continuous queries;
show tag values from kafka_offset with key="env"
show tag values from kafka_offset with key in ("env", "group_id")
show tag values from kafka_offset with key="group_id" where env='prod'

1.3、进入表-子表

use db_name.db_policy;
use db_test.two_month;

1.4、查询数据结构

select * from table_name;
show tag keys from gateway_rate_limit;
show field keys from gateway_rate_limit;

1.5、查询数据内容

precision rfc3339/ms/s/min/h
select * from service_requests_total order by time desc limit 10
select * from exception_aware_log where business='CAOAN' and log_level='ERROR' order by time desc limit 10;
select * from two_month.exception_aware_log where log_level='ERROR' and time > now() - 5d order by time;

1.6、创建数据库并授权

CREATE USER admin WITH PASSWORD '<your_pass>' WITH ALL PRIVILEGES
create database db_test01;
CREATE USER user_rw WITH PASSWORD '<your_pass>';
GRANT ALL privileges ON db_test01 TO user_rw

1.7、RETENTION POLICY

ALTER RETENTION POLICY autogen ON db_test DURATION 0s REPLICATION 1 SHARD DURATION 168h DEFAULT;
CREATE RETENTION POLICY one_month ON db_test DURATION 672h REPLICATION 1 SHARD DURATION 24h;
CREATE RETENTION POLICY two_month ON db_test DURATION 1440h REPLICATION 1 SHARD DURATION 24h;
CREATE RETENTION POLICY one_year ON db_test DURATION 1440h REPLICATION 1 SHARD DURATION 24h;
CREATE RETENTION POLICY "default" ON db_test DURATION 168h REPLICATION 1 SHARD DURATION 24h DEFAULT;

1.8、删除数据

delete from kafka_offset where group_id='INFO_OTHER'

1.9、CONTINUOUS QUERY

CREATE CONTINUOUS QUERY cq_api_5_minute_p99 ON db_test BEGIN SELECT percentile(response_time, 99) INTO db_test."default".api_5_minute_p99 FROM db_test.one_month.api_metric_time WHERE response_time > 0 GROUP BY time(5m), api END;

CREATE CONTINUOUS QUERY cq_api_5_minute_p95 ON db_test BEGIN SELECT percentile(response_time, 95) INTO db_test."default".api_5_minute_p95 FROM db_test.one_month.api_metric_time WHERE response_time > 0 GROUP BY time(5m), api END;

CREATE CONTINUOUS QUERY cq_internal_api_5_minute_p99 ON db_test BEGIN SELECT percentile(response_time, 99) INTO db_test."default".internal_api_5_minute_p99 FROM db_test.one_month.internal_api_metric_time WHERE response_time > 0 GROUP BY time(5m), api END;

CREATE CONTINUOUS QUERY cq_internal_api_5_minute_p95 ON db_test BEGIN SELECT percentile(response_time, 95) INTO db_test."default".internal_api_5_minute_p95 FROM db_test.one_month.internal_api_metric_time WHERE response_time > 0 GROUP BY time(5m), api END;

1.10、外部执行查询语句

influx -username influxro -password '<password>'  -database 'db_test' -format=csv -execute "SELECT percentile FROM api_time_p90 WHERE api = '/api/v1/createOrder'" > /data/backup/influxbak/data.csv

influx -username admin -password <password> -database db_test01 -format=csv -execute "SELECT * FROM db_test01.autogen.table_01 where time < 1663141565568420791"

2、备份

2.1、采用offline方式

influxd backup /data/backup/influxbak/new
influxd backup -database db_test -retention default /data/backup/influxbak/new

influxd restore -metadir /var/lib/influxdb/meta /data/new
influxd restore -database db_test -datadir /var/lib/influxdb/data /data/new

sudo chown -R influxdb:influxdb /var/lib/influxdb
service influxdb start

3、数据导入与导出

3.1、数据导出

influx -ssl -host 127.0.0.1 -port 8086 -username prometheus -password your_password  -database db_test -precision rfc3339 -format=csv -execute "SELECT mean(value) FROM aaa WHERE (instance_id = '2zexd13kzu31z2w0rn30y' AND metric_name = 'net_tx.rate') AND time >= now() - 168h GROUP BY time(1m)" > nat.csv 

influx -ssl -host 127.0.0.1 -port 8086 -username prometheus -password your_password  -database db_test -format=csv -execute "SELECT mean(value) FROM cbwp WHERE (instance_id = '2zexd13kzu31z2w0rn30y' AND metric_name = 'net_tx.rate') AND time >= now() - 168h GROUP BY time(1m)" > nat.csv 

influx -ssl -host 127.0.0.1 -port 8086 -username prometheus -password your_password  -database db_test -format=csv -execute "SELECT * FROM cbwp" > nat.csv 
influx -ssl -host 127.0.0.1 -port 8086 -username prometheus -password your_password  -database db_test -format=csv -execute "SELECT * FROM cbwp where time >= now() - 8h" > nat.csv 

influx -host 172.16.212.11 -port 8086 -username user_rw -password your_password -database db_test01 -format=csv -execute "SELECT * FROM table_01 where time < 1663141565568420791" > history.csv

3.2、导入csv数据到influxdb

# https://github.com/Bugazelle/export-csv-to-influx
<!-- 
csv格式要求如下：
time,mean
2022-06-16T10:49:00Z,
2022-06-16T10:50:00Z,14505152
2022-06-16T10:51:00Z,25204076
2022-06-16T10:52:00Z,25596240
2022-06-16T10:53:00Z,29919640
2022-06-16T10:54:00Z,16613368 
-->

cut -f 2-4 -d ',' history.csv > history_cut.csv

pip3 install ExportCsvToInflux
export_csv_to_influx --csv nat.csv --dbname db_test --measurement table_test --time_column time --field_columns 'Value,value' --tag_columns 'instance_id,instance_name,metric_name,user_id' --user admin --server 10.0.0.92:31523

/usr/local/python3/bin/export_csv_to_influx --csv history_cut.csv --dbname db_test01 --measurement table_01 --time_column time --field_columns 'value' --tag_columns 'server_id' --user user_rw  --password your_password --server 172.16.212.22:8086

4、数据同步

4.1、SUBSCRIPTIONS（>=1.6）

create SUBSCRIPTION db_test01_sub on db_test01.autogen DESTINATIONS ALL 'http://172.16.212.11:8086'
show SUBSCRIPTIONS
DROP SUBSCRIPTION "db_test01_sub" ON "db_test01"."autogen"

influx -username admin -password nTuZ7eit5t5GO8vR -execute "create SUBSCRIPTION db_test01_sub on db_test01.autogen DESTINATIONS ALL 'http://user_rw:password@172.16.212.22:8086'" -database db_test01

create SUBSCRIPTION db_test01_sub on db_test01.autogen DESTINATIONS ALL 'http://user_rw:password@172.16.212.22:8086'
create SUBSCRIPTION db_test01_default on db_test01."default" DESTINATIONS ALL 'http://user_rw:password@172.16.212.22:8086'