Migrating grafana data to new database

We’re moving our Grafana instances to new HA servers, so we need to move the data from sqlite to a MySQL/MariaDB (Galera) cluster. I’m curious if anyone has developed any handy tools for doing such a migration?

Using the API and tools like wizzy is clean, but won’t move user data, so I think some sort of direct SQL migration is required. However, I’m thinking it’s best to let Grafana create the schema in MySQL, rather than trying to translate the schema from a database dump. Maybe it won’t be too bad to just extract the INSERTs from a dump and load them into MySQL?

Any thoughts?

Thanks,
Kevin

This is the LATEST version of a small script I’ve used successfully to dump data from a grafana sqlite db in a format that works with mysql for migrating instances from sqlite to mysql:

#!/bin/bash
DB=$1
TABLES=$(sqlite3 $DB .tables | sed -r 's/(\S+)\s+(\S)/\1\n\2/g' | grep -v migration_log)
for t in $TABLES; do
    echo "TRUNCATE TABLE $t;"
done
for t in $TABLES; do
    echo -e ".mode insert $t\nselect * from $t;"
done | sqlite3 $DB

Use it like sqlitedump.sh grafana.db > grafana.sql to create the script to insert data into the MySQL database.

Start Grafana and let it set up the db structure in MySQL, then you can run the sql to populate the content (be warned it truncates the tables first, so any existing data in mysql will be lost)
mysql grafana < grafana.sql

6 Likes

Thanks @dcech, worked great! I had to make a couple tweaks, notably sqlite .tables printed in columns, so grep was removing several other tables from the list.

  #!/bin/sh
 DB=$1
-TABLES=$(sqlite3 $DB .tables | grep -v migration_log)
+TABLES=$(sqlite3 $DB .tables | sed -r 's/\s+/\n/g' | grep -v -e migration_log -e '^$')
 for t in $TABLES; do
     echo "TRUNCATE TABLE $t;"
 done
 for t in $TABLES; do
-    echo ".mode insert $t\nselect * from $t;"
+    echo -e ".mode insert $t\nselect * from $t;"
 done | sqlite3 $DB
3 Likes

Hi dcech!

I test the script and it worked well, but it doesn’t migrate the user table of the sqlite3. Is there a solution for this! Thank you!

Did you use the updated script @retzkek posted?

Yes! I use both scripts.

Just truncate this tables:

TRUNCATE TABLE alert
TRUNCATE TABLE dashboard_tag
TRUNCATE TABLE playlist
TRUNCATE TABLE star
TRUNCATE TABLE alert_notification
TRUNCATE TABLE dashboard_version
TRUNCATE TABLE playlist_item
TRUNCATE TABLE temp_user
TRUNCATE TABLE annotation
TRUNCATE TABLE data_source
TRUNCATE TABLE plugin_setting
TRUNCATE TABLE test_data
TRUNCATE TABLE dashboard
TRUNCATE TABLE org
TRUNCATE TABLE quota
TRUNCATE TABLE dashboard_snapshot
TRUNCATE TABLE org_user
TRUNCATE TABLE session

Thank you!

What is the output of sqlite3 grafana.db .tables?

imagen

Try the updated script in my post above.

1 Like

Thanks @dcech it works!

1 Like

I had to change the first line to
#!/bin/bash
to get this to work. It was breaking on the -e option to echo

Hi @dcech
I try import database to mysql, but i have a error about all tables.

ERROR 1146 (42S02) at line 1 in file: ‘grafana.sql’: Table ‘grafana.alert’ doesn’t exist
ERROR 1146 (42S02) at line 2 in file: ‘grafana.sql’: Table ‘grafana.dashboard_tag’ doesn’t exist
ERROR 1146 (42S02) at line 3 in file: ‘grafana.sql’: Table ‘grafana.playlist’ doesn’t exist
ERROR 1146 (42S02) at line 4 in file: ‘grafana.sql’: Table ‘grafana.star’ doesn’t exist
ERROR 1146 (42S02) at line 5 in file: ‘grafana.sql’: Table ‘grafana.alert_notification’ doesn’t exist

Please help me to resolve it.
Thanks.

Hi @dcech @retzkek @ingenieria5 @mkfort
I have a issue.
I can’t import data from table: data_soure and dashboard to mysql.
Error:

ERROR 1136 (21S01) at line 797 in file: ‘grafana.sql’: Column count doesn’t match value count at row 1

Please help me to resolve it.
Thanks.

You need to make sure that the database you’re importing into was created by the same version of grafana that the export was from. That error indicates that you created the database with a newer version, so it has columns that are not in the dump.

Yes.
I did it.
I must upgrade my server to new version.

Hello,

I’m attempting to use dcech’s script to migrate from grafana 4.2.0/sqllite to grafana 5.1.3/mysql (percona 5.7.18 to be exact). The steps I’ve taken are:

  • copied the existing sqllite db to a new 4.x instance,
  • installed the 5.x rpm and restarted grafana-server
  • dumped the sqllite DB via the script
  • Re-point the new instance to use the mysql server so it creates all the grafana tables

but when I go to import the dump into mysql I’m getting this:

# mysql < upgrade.sql
ERROR 1366 (HY000) at line 176: Incorrect string value: '\xFF\xFF\xFF\xC2\xFF\xFF...' for column 'data' at row 1

Everything I’ve read says this is a UTF8 encoding problem and to ensure that the DB is utf8mb4, and it is:

mysql> use grafana;
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.01 sec)

mysql>

I’ve tried removing the offending rows (to the tune of hundreds of rows) and even removing all the insert statements for the dashboard_version table altogether (this is the table it’s trying to insert into on line 176, but the error just moves to another line.

If anyone has any thoughts on where I’m failing on this, I’d appreciate it, thanks!

Hi
You must upgrade from 4.5 to 5.2 (You should backup grafana.db to new directory)
Then you use this script to convert to sqlite (Migrating grafana data to new database)
Good luck ^^

Hi There,

Thanks for taking the time to reply. I did upgrade from the grafana version of 4 that I’m running (4.2.0) to 5.1.3 before doing the dump (the first two bullet points in my first message). Are you saying that I explicitly must go from version 4.5 -> to version 5.2 in order to make this work?

Yes.
You may upgrade to newest version.

Hi ,

I am trying to migrate sqlite3 to mysql database using the scripts written by @dcech @retzkek. I am able to see all the data in mysql, but Dashboards dont have any content. like in below .

which is similar problem like – Migration from sqlite3 to MySQL database

but , I can’t upload db to https://www.rebasedata.com/convert-sqlite-to-mysql-online because of internal restrictions.

Grafana version : Grafana v5.1.4 (a5fe24fcc)
sqlite version: sqlite-3.7.17-8.el7.x86_64
mysql version: 5.7.24-0ubuntu0.18.04.1

can any one help me out to fix this issue ??