36

Is there a way (plugin or tool) to export the data from the database (or database itself) ? I'm looking for this feature as I need to migrate a DB from present host to another one.

Srikanta
  • 1,145
  • 2
  • 12
  • 22

7 Answers7

42

Export data:

sudo service influxdb start (Or leave this step if service is already running)
influxd backup -database grpcdb /opt/data

grpcdb is name of DB and back up will be saved under /opt/data directory in this case.

Import Data:

sudo service influxdb stop  (Service should not be running)
influxd restore -metadir /var/lib/influxdb/meta /opt/data
influxd restore -database grpcdb -datadir /var/lib/influxdb/data /opt/data
sudo service influxdb start
Ammad
  • 4,031
  • 12
  • 39
  • 62
  • 3
    When dealing with massive databases this is really the only practical method to do it. – mogul Feb 08 '17 at 14:08
  • 1
    Yes, assuming the OP is migrating to another InfluxDB host (hopefully of the same version) rather than some other type of database – James Allen Aug 03 '17 at 16:30
  • 1
    N.B.1 There is a new (and better) mechanism for backup/restoring data, see https://docs.influxdata.com/influxdb/v1.7/administration/backup_and_restore/ N.B.2. After importing data, ensure `/var/lib/influxdb` is owned by `influxdb:influxdb` again, i.e. by running `sudo chown -R influxdb:influxdb /var/lib/influxdb` – Tim Dec 26 '18 at 10:35
22

You could dump each table and load them through REST interface:

curl "http://hosta:8086/db/dbname/series?u=root&p=root&q=select%20*%20from%20series_name%3B" > series_name.json
curl -XPOST -d @series_name.json "http://hostb:8086/db/dbname/series?u=root&p=root"

Or, maybe you want to add new host to cluster? It's easy and you'll get master-master replica for free. Cluster Setup

Gareth Davidson
  • 4,857
  • 2
  • 26
  • 45
ezotrank
  • 463
  • 3
  • 6
  • 2
    or alternatively `curl -G 'http://hosta:8086/query?' --data-urlencode "db=dbname" --data-urlencode "q=SELECT * FROM series_name" – James Allen Aug 03 '17 at 16:33
  • Note that `json` import of data does not work as of influxdb v0.9 (see https://github.com/influxdata/influxdb/issues/3174), Ammad's answer works better. – Tim Nov 24 '18 at 20:30
  • Also note that doing it this way may (and most likely will) timeout during import. At least Influx 1.8 has very strict time limits for these things and you have to do it in batches. – Dalibor Filus Jun 16 '23 at 18:53
20

If I use curl, I get timeouts, and if I use influxd backup its not in a format I can read.

I'm getting fine results like this:

influx -host influxdb.mydomain.com -database primary -format csv -execute "select time,value from \"continuous\" where channel='ch123'" > outtest.csv
nont
  • 9,322
  • 7
  • 62
  • 82
18

As ezotrank says, you can dump each table. There's a missing "-d" in ezotrank's answer though. It should be:

curl "http://hosta:8086/db/dbname/series?u=root&p=root&q=select%20*%20from%20series_name%3B" > series_name.json
curl -XPOST -d @series_name.json "http://hostb:8086/db/dbname/series?u=root&p=root"

(Ezotrank, sorry, I would've just posted a comment directly on your answer, but I don't have enough reputation points to do that yet.)

Mac
  • 340
  • 1
  • 5
16

From 1.5 onwards, the InfluxDB OSS backup utility provides a newer option which is much more convenient:

-portable: Generates backup files in the newer InfluxDB Enterprise-compatible format. Highly recommended for all InfluxDB OSS users

Export

To back up everything:

influxd backup -portable <path-to-backup>

To backup only the myperf database:

influxd backup -portable -database myperf <path-to-backup>

Import

To restore all databases found within the backup directory:

influxd restore -portable <path-to-backup>

To restore only the myperf database (myperf database must not exist):

influxd restore -portable -db myperf <path-to-backup>

Additional options include specifying timestamp , shard etc. See all the other supported options here.

Neo
  • 4,640
  • 5
  • 39
  • 53
10

If You want to export in an readable format, the inspect command is to prefer. To export the database with the name HomeData the command is:

sudo influx_inspect export -waldir /var/lib/influxdb/wal -datadir /var/lib/influxdb -out "influx_backup.db" -database HomeData

The parameters for -waldir and -datdir can be found in /etc/influxdb/influxdb.conf.

To import this file again, the command is:

influx -import -path=influx_backup.db
Hanspeter
  • 183
  • 1
  • 8
4

If you have access to the machine running Influx db I would say use the influx_inspect command. The command is simple and very fast. It will dump your db in line protocol. You can then import this dump using influx -import command.

Boss Man
  • 587
  • 2
  • 12