跳至正文
  • 38 views
  • 10 min read

influxdb 1.8数据备份与还原

新浪微博 豆瓣 QQ 百度贴吧 QQ空间

需求

为了保障数据的安全性,一般都会有数据备份、还原的需求,本篇章来介绍一下 InfluxDB 数据库的备份与还原方式。

influxdb版本

InfluxDB version: 1.8.0

官网文档关于备份与还原说明

https://docs.influxdata.com/influxdb/v1.8/administration/backup_and_restore/

本篇章主要介绍单台 InfluxDB 的备份以及还原。

启用influxdb用于备份与还原的RPC端口服务

influxdb数据库默认只启用了 8086 的数据库访问端口服务,而如果要使用 备份 与 还原 的功能,则需要单独另外启用一个端口服务。配置如下:

在 root 用户的级别下,在 influxdb 的配置文件influxdb.conf中,取消注释 bind-address

# Bind address to use for the RPC service for backup and restore.
bind-address = "127.0.0.1:8088"

可以看到默认是 8088 的端口号,这个是可以自己去修改的,在这里我就采用默认的端口号了,如果需要提供远程访问服务,则需要修改一下 IP地址 为远程访问IP,不然默认是只允许 127.0.0.1 本地访问的。

配置好了之后,重启一下 influxdb 数据库。

启用端口服务之后,官网的执行 backup 示例

$ influxd backup -portable -database mydatabase -host <remote-node-IP>:8088 /tmp/mysnapshot

使用我用 telegraf 采集的数据库来测试执行备份一下,如下:

# 执行兼容性备份 telegraf 数据库的数据 至 /tmp/telegraf_snapshot 中。
root@9aaa0db75c4d:~# influxd backup -portable -database telegraf -host 127.0.0.1:8088 /tmp/telegraf_snapshot
2020/06/15 06:51:03 backing up metastore to /tmp/telegraf_snapshot/meta.00
2020/06/15 06:51:03 backing up db=telegraf
2020/06/15 06:51:03 backing up db=telegraf rp=autogen shard=3 to /tmp/telegraf_snapshot/telegraf.autogen.00003.00 since 0001-01-01T00:00:00Z
2020/06/15 06:51:03 backing up db=telegraf rp=autogen shard=5 to /tmp/telegraf_snapshot/telegraf.autogen.00005.00 since 0001-01-01T00:00:00Z
2020/06/15 06:51:03 backing up db=telegraf rp=autogen shard=11 to /tmp/telegraf_snapshot/telegraf.autogen.00011.00 since 0001-01-01T00:00:00Z
2020/06/15 06:51:03 backing up db=telegraf rp=autogen shard=14 to /tmp/telegraf_snapshot/telegraf.autogen.00014.00 since 0001-01-01T00:00:00Z
2020/06/15 06:51:03 backing up db=telegraf rp=autogen shard=20 to /tmp/telegraf_snapshot/telegraf.autogen.00020.00 since 0001-01-01T00:00:00Z
2020/06/15 06:51:03 backing up db=telegraf rp=autogen shard=26 to /tmp/telegraf_snapshot/telegraf.autogen.00026.00 since 0001-01-01T00:00:00Z
2020/06/15 06:51:04 backing up db=telegraf rp=autogen shard=32 to /tmp/telegraf_snapshot/telegraf.autogen.00032.00 since 0001-01-01T00:00:00Z
2020/06/15 06:51:04 backing up db=telegraf rp=autogen shard=40 to /tmp/telegraf_snapshot/telegraf.autogen.00040.00 since 0001-01-01T00:00:00Z
2020/06/15 06:51:04 backing up db=telegraf rp=autogen shard=44 to /tmp/telegraf_snapshot/telegraf.autogen.00044.00 since 0001-01-01T00:00:00Z
2020/06/15 06:51:04 backup complete:
2020/06/15 06:51:04     /tmp/telegraf_snapshot/20200615T065103Z.meta
2020/06/15 06:51:04     /tmp/telegraf_snapshot/20200615T065103Z.s3.tar.gz
2020/06/15 06:51:04     /tmp/telegraf_snapshot/20200615T065103Z.s5.tar.gz
2020/06/15 06:51:04     /tmp/telegraf_snapshot/20200615T065103Z.s11.tar.gz
2020/06/15 06:51:04     /tmp/telegraf_snapshot/20200615T065103Z.s14.tar.gz
2020/06/15 06:51:04     /tmp/telegraf_snapshot/20200615T065103Z.s20.tar.gz
2020/06/15 06:51:04     /tmp/telegraf_snapshot/20200615T065103Z.s26.tar.gz
2020/06/15 06:51:04     /tmp/telegraf_snapshot/20200615T065103Z.s32.tar.gz
2020/06/15 06:51:04     /tmp/telegraf_snapshot/20200615T065103Z.s40.tar.gz
2020/06/15 06:51:04     /tmp/telegraf_snapshot/20200615T065103Z.s44.tar.gz
2020/06/15 06:51:04     /tmp/telegraf_snapshot/20200615T065103Z.manifest
root@9aaa0db75c4d:~# 
root@9aaa0db75c4d:~# ls /tmp/
telegraf_snapshot
root@9aaa0db75c4d:~# ls /tmp/telegraf_snapshot/
20200615T065103Z.manifest  20200615T065103Z.s11.tar.gz    20200615T065103Z.s20.tar.gz  20200615T065103Z.s3.tar.gz   20200615T065103Z.s40.tar.gz  20200615T065103Z.s5.tar.gz
20200615T065103Z.meta       20200615T065103Z.s14.tar.gz    20200615T065103Z.s26.tar.gz  20200615T065103Z.s32.tar.gz  20200615T065103Z.s44.tar.gz
root@9aaa0db75c4d:~#

从上面可以看出应该成功可以备份数据了,另外 backup 命令还可以多种使用方式,例如:全库备份、根据时间段数据单库备份 等等。下面来继续看看。

Backup 命令

InfluxDB 的 backup 命令可以生成一定的数据,并且创建保存至指定的目录中。在目录中将会根据 UTC 时间戳进行命令保存。

从上面的执行示例可以看到生成了存储文件如下:

root@9aaa0db75c4d:~# ls -ll /tmp/telegraf_snapshot/
total 14752
-rw------- 1 root root    1791 Jun 15 06:51 20200615T065103Z.manifest
-rw-r--r-- 1 root root    1187 Jun 15 06:51 20200615T065103Z.meta
-rw------- 1 root root  326755 Jun 15 06:51 20200615T065103Z.s11.tar.gz
-rw------- 1 root root 2533498 Jun 15 06:51 20200615T065103Z.s14.tar.gz
-rw------- 1 root root 2148018 Jun 15 06:51 20200615T065103Z.s20.tar.gz
-rw------- 1 root root 3175105 Jun 15 06:51 20200615T065103Z.s26.tar.gz
-rw------- 1 root root  983673 Jun 15 06:51 20200615T065103Z.s3.tar.gz
-rw------- 1 root root 1835600 Jun 15 06:51 20200615T065103Z.s32.tar.gz
-rw------- 1 root root 2179108 Jun 15 06:51 20200615T065103Z.s40.tar.gz
-rw------- 1 root root  185219 Jun 15 06:51 20200615T065103Z.s44.tar.gz
-rw------- 1 root root 1704912 Jun 15 06:51 20200615T065103Z.s5.tar.gz
root@9aaa0db75c4d:~#
  • 20200615T065103Z.meta 则是 metastore 副本数据,包含 用户名以及密码。
  • 20200615T065103Z.s14.tar.gz 为分片副本数据。
  • 20200615T065103Z.manifestJSON 格式文件,用于描述搜集的备份数据内容说明。

另外,要注意的一个地方就是 backup 备份的数据是忽略 WAL 文件以及在内存中的缓存数据的。

backup 命令的参数说明

root@9aaa0db75c4d:~# influxd backup -h

Creates a backup copy of specified InfluxDB OSS database(s) and saves the files in an Enterprise-compatible
format to PATH (directory where backups are saved). 

Usage: influxd backup [options] PATH

    -portable # 以较新的InfluxDB Enterprise兼容格式生成备份文件。强烈建议所有InfluxDB OSS用户使用。
            Required to generate backup files in a portable format that can be restored to InfluxDB OSS or InfluxDB 
            Enterprise. Use unless the legacy backup is required.
    -host <host:port> # InfluxDB OSS实例的主机和端口。默认值为'127.0.0.1:8088'。远程连接所必需。例:-host 127.0.0.1:8088
            InfluxDB OSS host to back up from. Optional. Defaults to 127.0.0.1:8088.
    -db <name> # 要备份的数据库。如果未指定,则备份所有数据库。
            InfluxDB OSS database name to back up. Optional. If not specified, all databases are backed up when 
            using '-portable'.
    -rp <name> # 备份的保留策略。如果未指定,则默认为使用所有保留策略。如果指定,则需要使用 -database 指定数据库实例。
            Retention policy to use for the backup. Optional. If not specified, all retention policies are used by 
            default.
    -shard <id> # 要备份的分片的分片ID。如果指定,-retention <name> 则为必需。
            The identifier of the shard to back up. Optional. If specified, '-rp <rp_name>' is required.
    -start <2015-12-24T08:12:23Z> 
            # 设置备份数据的时间起点(RFC3339格式),不兼容-since。例:-start 2015-12-24T08:12:23Z
            Include all points starting with specified timestamp (RFC3339 format). 
            Not compatible with '-since <timestamp>'.
    -end <2015-12-24T08:12:23Z>
            # 排除指定时间戳记(RFC3339格式)之后的所有结果,不兼容-since。如果不使用-start,则将从1970-01-01开始备份所有数据。例:-end 2015-12-31T08:12:23Z
            Exclude all points after timestamp (RFC3339 format). 
            Not compatible with '-since <timestamp>'.
    -since <2015-12-24T08:12:23Z>
            # 在指定的时间戳记RFC3339格式之后执行增量备份。-start除非有旧版备份支持,否则请改用。建议使用 -start 参数。
            Create an incremental backup of all points after the timestamp (RFC3339 format). Optional. 
            Recommend using '-start <timestamp>' instead.
    -skip-errors # 跳过备份过程中发生的错误。
            Optional flag to continue backing up the remaining shards when the current shard fails to backup. 
backup: flag: help requested
root@9aaa0db75c4d:~#

大概了解了上面关于 backup 备份指令的相关参数之后,下面来应用一下。

备份数据示例

备份所有数据
influxd backup -portable <path-to-backup>

执行如下:

# 因为是在influxdb服务本地,所以不需要使用 -host 指定远程访问IP以及端口号。执行全库数据备份至 all_datebases_snapshot 目录下
root@9aaa0db75c4d:~# influxd backup -portable /tmp/all_datebases_snapshot
2020/06/15 07:50:29 backing up metastore to /tmp/all_datebases_snapshot/meta.00
2020/06/15 07:50:29 No database, retention policy or shard ID given. Full meta store backed up.
2020/06/15 07:50:29 Backing up all databases in portable format
2020/06/15 07:50:29 backing up db=
2020/06/15 07:50:29 backing up db=_internal rp=monitor shard=39 to /tmp/all_datebases_snapshot/_internal.monitor.00039.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:29 backing up db=_internal rp=monitor shard=41 to /tmp/all_datebases_snapshot/_internal.monitor.00041.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:29 backing up db=_internal rp=monitor shard=42 to /tmp/all_datebases_snapshot/_internal.monitor.00042.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:29 backing up db=_internal rp=monitor shard=43 to /tmp/all_datebases_snapshot/_internal.monitor.00043.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:29 backing up db=_internal rp=monitor shard=45 to /tmp/all_datebases_snapshot/_internal.monitor.00045.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:29 backing up db=locust rp=autogen shard=7 to /tmp/all_datebases_snapshot/locust.autogen.00007.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:29 backing up db=telegraf rp=autogen shard=3 to /tmp/all_datebases_snapshot/telegraf.autogen.00003.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:29 backing up db=telegraf rp=autogen shard=5 to /tmp/all_datebases_snapshot/telegraf.autogen.00005.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:29 backing up db=telegraf rp=autogen shard=11 to /tmp/all_datebases_snapshot/telegraf.autogen.00011.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:29 backing up db=telegraf rp=autogen shard=14 to /tmp/all_datebases_snapshot/telegraf.autogen.00014.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:29 backing up db=telegraf rp=autogen shard=20 to /tmp/all_datebases_snapshot/telegraf.autogen.00020.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:29 backing up db=telegraf rp=autogen shard=26 to /tmp/all_datebases_snapshot/telegraf.autogen.00026.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:30 backing up db=telegraf rp=autogen shard=32 to /tmp/all_datebases_snapshot/telegraf.autogen.00032.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:30 backing up db=telegraf rp=autogen shard=40 to /tmp/all_datebases_snapshot/telegraf.autogen.00040.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:30 backing up db=telegraf rp=autogen shard=44 to /tmp/all_datebases_snapshot/telegraf.autogen.00044.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:30 backing up db=performance rp=autogen shard=8 to /tmp/all_datebases_snapshot/performance.autogen.00008.00 since 0001-01-01T00:00:00Z
2020/06/15 07:50:30 backup complete:
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.meta
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s39.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s41.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s42.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s43.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s45.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s7.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s3.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s5.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s11.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s14.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s20.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s26.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s32.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s40.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s44.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.s8.tar.gz
2020/06/15 07:50:30     /tmp/all_datebases_snapshot/20200615T075029Z.manifest
root@9aaa0db75c4d:~# 
# 查看备份好生成的数据
root@9aaa0db75c4d:~# ls -ll -h /tmp/all_datebases_snapshot/
total 16M
-rw------- 1 root root 3.1K Jun 15 07:50 20200615T075029Z.manifest 
-rw-r--r-- 1 root root 1.2K Jun 15 07:50 20200615T075029Z.meta
-rw------- 1 root root 320K Jun 15 07:50 20200615T075029Z.s11.tar.gz
-rw------- 1 root root 2.5M Jun 15 07:50 20200615T075029Z.s14.tar.gz
-rw------- 1 root root 2.1M Jun 15 07:50 20200615T075029Z.s20.tar.gz
-rw------- 1 root root 3.1M Jun 15 07:50 20200615T075029Z.s26.tar.gz
-rw------- 1 root root 961K Jun 15 07:50 20200615T075029Z.s3.tar.gz
-rw------- 1 root root 1.8M Jun 15 07:50 20200615T075029Z.s32.tar.gz
-rw------- 1 root root 248K Jun 15 07:50 20200615T075029Z.s39.tar.gz
-rw------- 1 root root 2.1M Jun 15 07:50 20200615T075029Z.s40.tar.gz
-rw------- 1 root root 244K Jun 15 07:50 20200615T075029Z.s41.tar.gz
-rw------- 1 root root 254K Jun 15 07:50 20200615T075029Z.s42.tar.gz
-rw------- 1 root root 150K Jun 15 07:50 20200615T075029Z.s43.tar.gz
-rw------- 1 root root 219K Jun 15 07:50 20200615T075029Z.s44.tar.gz
-rw------- 1 root root  95K Jun 15 07:50 20200615T075029Z.s45.tar.gz
-rw------- 1 root root 1.7M Jun 15 07:50 20200615T075029Z.s5.tar.gz
-rw------- 1 root root 1.3K Jun 15 07:50 20200615T075029Z.s7.tar.gz
-rw------- 1 root root  12K Jun 15 07:50 20200615T075029Z.s8.tar.gz
root@9aaa0db75c4d:~#
备份文件系统级别最近更改的所有数据库(使用 start 参数指定开启备份时间)
influxd backup -portable -start <timestamp> <path-to-backup>

执行如下:

# 备份 2020-06-15T00:00:00Z 开始的全库数据
root@9aaa0db75c4d:~# influxd backup -portable -start 2020-06-15T00:00:00Z /tmp/all_datebases_snapshot_20200615
2020/06/15 07:59:28 backing up metastore to /tmp/all_datebases_snapshot_20200615/meta.00
2020/06/15 07:59:28 No database, retention policy or shard ID given. Full meta store backed up.
2020/06/15 07:59:28 Backing up all databases in portable format
2020/06/15 07:59:28 backing up db=
2020/06/15 07:59:28 backing up db=_internal rp=monitor shard=39 to /tmp/all_datebases_snapshot_20200615/_internal.monitor.00039.00 with boundaries start=2020-06-15T00:00:00Z, end=0001-01-01T00:00:00Z
...
root@9aaa0db75c4d:~#
指定仅备份telegraf数据库(使用 -database 参数指定备份的数据库)
influxd backup -portable -database telegraf <path-to-backup>

执行如下:

# 使用 -database 指定备份 telegraf 数据库
influxd backup -portable -database telegraf /tmp/telegraf_snapshot
# 在指定数据库的基础上,再用 -start 指定备份的时间起点
influxd backup -portable -database telegraf -start 2020-06-15T00:00:00Z  /tmp/telegraf_snapshot_20200615
要在指定的时间间隔内备份数据库(使用 -start 和 -end 参数指定备份的时间段)
influxd backup  -portable -database mytsd -start 2017-04-28T06:49:00Z -end 2017-04-28T06:50:00Z /tmp/backup/influxdb

执行如下:

# 备份 telegraf 数据库在 2020-06-14 00:00:00 与 2020-06-16 00:00:00 之间的数据至 telegraf_snapshot_20200616 目录下
influxd backup -portable -database telegraf -start 2020-06-14T00:00:00Z -end 2020-06-16T00:00:00Z  /tmp/influxd backup -portable -database telegraf -start 2020-06-14T00:00:00Z -end 2020-06-16T00:00:00Z  /tmp/telegraf_snapshot_20200616

Restore 命令

上面介绍了使用 backup 命令来备份数据,那么肯定也要介绍如何使用 restore 命令来恢复数据。

restore 命令的参数说明

root@9aaa0db75c4d:~# influxd restore -h

Uses backup copies from the specified PATH to restore databases or specific shards from InfluxDB OSS
  or InfluxDB Enterprise to an InfluxDB OSS instance.

Usage: influxd restore -portable [options] PATH

Note: Restore using the '-portable' option consumes files in an improved Enterprise-compatible 
  format that includes a file manifest.

Options:
    -portable 
            Required to activate the portable restore mode. If not specified, the legacy restore mode is used.
            # 对InfluxDB OSS使用新的企业兼容备份格式。推荐使用该参数。可以将在InfluxDB Enterprise上创建的备份还原到InfluxDB OSS实例。

    -host  <host:port>    
            InfluxDB OSS host to connect to where the data will be restored. Defaults to '127.0.0.1:8088'.
            # InfluxDB OSS实例的主机和端口。默认值为'127.0.0.1:8088'。远程连接所必需。例:-host 127.0.0.1:8088

    -db    <name>
            Name of database to be restored from the backup (InfluxDB OSS or InfluxDB Enterprise)
            # 要从备份还原的数据库的名称。如果未指定,将还原所有数据库。

    -newdb <name> 
            Name of the InfluxDB OSS database into which the archived data will be imported on the target system. 
            Optional. If not given, then the value of '-db <db_name>' is used.  The new database name must be unique 
            to the target system.
            # 将在目标系统上导入存档数据的数据库的名称。如果未指定,则使用的-db 指定的数据库实例名称。新的数据库名称对于目标系统必须是唯一的。

    -rp    <name>
            Name of retention policy from the backup that will be restored. Optional. 
            Requires that '-db <db_name>' is specified.
            # 将从备份中恢复的保留策略的名称。需要-db设置。如果未指定,将使用所有保留策略。

    -newrp <name>
            Name of the retention policy to be created on the target system. Optional. Requires that '-rp <rp_name>' 
            is set. If not given, the '-rp <rp_name>' value is used.
    -shard <id>
            Identifier of the shard to be restored. Optional. If specified, then '-db <db_name>' and '-rp <rp_name>' are
            required.
            # 要在目标系统上创建的保留策略的名称。需要-rp设置。如果未指定,则使用该-rp值。

    PATH
            Path to directory containing the backup files.
            # 用来恢复数据的备份文件路径

restore: flag: help requested
root@9aaa0db75c4d:~#

恢复数据示例

要还原在备份目录中找到的所有数据库:
influxd restore -portable path-to-backup

执行如下:

root@9aaa0db75c4d:~# influxd restore -portable /tmp/all_datebases_snapshot
2020/06/15 08:30:14 error updating meta: DB metadata not changed. database may already exist
restore: DB metadata not changed. database may already exist
root@9aaa0db75c4d:~#

注意:如果全库的数据恢复,那么如果库名database存在,则会报错。所以全库的恢复前提最好是一个全新的influxdb服务。

还原指定telegraf数据库(telegraf数据库必须不存在):
influxd restore -portable -db telegraf path-to-backup
要将数据还原到已经存在的数据库:

从上面的示例中,我们已经知道不能直接还原数据到已经存在的数据库中。如果尝试将restore命令运行到现有数据库中,则会收到以下消息:

root@9aaa0db75c4d:~# influxd restore -portable /tmp/all_datebases_snapshot
2020/06/15 08:30:14 error updating meta: DB metadata not changed. database may already exist
restore: DB metadata not changed. database may already exist
root@9aaa0db75c4d:~#

那么如果的确需要将数据还原至现有的数据库中,在 influxdb 官网推荐的做法如下:

1.将现有数据库备份还原到临时数据库
influxd restore -portable -db telegraf -newdb telegraf_bak path-to-backup

执行如下:

root@9aaa0db75c4d:~# influxd restore -portable -db telegraf -newdb telegraf_bak /tmp/telegraf_snapshot_2020061601 
2020/06/15 09:12:59 Restoring shard 55 live from backup 20200615T091201Z.s55.tar.gz
root@9aaa0db75c4d:~#
2.(使用SELECT ... INTO语句)将数据侧载到现有目标数据库中,然后删除临时数据库。
> USE telegraf_bak
> SELECT * INTO telegraf..:MEASUREMENT FROM /.*/ GROUP BY *
> DROP DATABASE telegraf_bak

执行如下:

-- 登陆influxdb,查询所有数据库
> show databases;
name: databases
name
----
_internal
locust
telegraf -- 需要导入的现有数据库
performance
telegraf_bak -- 临时数据库
> 
-- 使用临时数据库 telegraf_bak
> use telegraf_bak;
Using database telegraf_bak
> 
-- 将临时数据库的数据插入到 telegraf 数据库中
> SELECT * INTO telegraf..:MEASUREMENT FROM /.*/ GROUP BY *
name: result
time written
---- -------
0    408
> 
-- 删除临时数据库 telegraf_bak
> DROP DATABASE telegraf_bak;

上面已经将 telegraf_bak 库的数据成功插入到 telegraf 库中,那么我还有另外一个数据,就是将查询一个时间段的数据,再插入到另一个数据库中,如下:

> show databases;
name: databases
name
----
_internal
locust
performance -- 准备查询一个时间段的时间,然后插入到 performance 数据库中
telegraf 
telegraf2
> 
-- 切换至 telegraf 数据库
> use telegraf;
Using database telegraf
-- 设置显示的时间格式
> precision rfc3339
-- 查询 telegraf 数据库中在 上海时区 ['2020-06-15 10:33:39', '2020-06-16 14:32:32'] 之间的所有measurements数据
> select * from /.*/ where time >= '2020-06-15 10:33:39' and time <= '2020-06-16 14:32:32' limit 1 tz('Asia/Shanghai');

-- 将查询出来的数据插入到 performance 中
> select * into performance..:MEASUREMENT from /.*/ where time >= '2020-06-15 10:33:39' and time <= '2020-06-16 14:32:32' tz('Asia/Shanghai');
name: result
time                 written
----                 -------
1970-01-01T00:00:00Z 3213
>
还原到已存在的保留策略:

经过上面的步骤,我们已经成功将数据返回至现有的数据库中了,当然还有还原数据保留策略的步骤,如下:

1.将保留策略还原到临时数据库。
influxd restore -portable -db telegraf -newdb telegraf_bak -rp autogen -newrp autogen_bak path-to-backup
2.侧载到目标数据库中并删除临时数据库。
> USE telegraf_bak
> SELECT * INTO telegraf.autogen.:MEASUREMENT FROM /telegraf_bak.autogen_bak.*/ GROUP BY *
> DROP DATABASE telegraf_bak
标签:

发表回复