Migrating grafana data to new database

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 ??

Did you get any errors when importing the database dump? If you did, you may need to manually fix any issues in the SQL so they can import properly. If not, you’ll want to look at the dashboard table and see whether the entries have valid json in the data column.

No errors in importing as well data is valid , and find out one more thing,
Only Dashboards in General Tab/ With out any folder is getting issue , where as Dashboards in any Folder is working as usala, no errors.

Json data from sqlitedb and mysql are same and there is no changes in data …

There’s a bunch of data types that need to be converted if you’re going from sqlite to postgres. I’ve successfully used this tool to migrate a 5.4.3 installation: https://github.com/haron/grafana-migrator

Did you get it working?

Can I use AWS RDS mysql as a database for grafana? If so, can you please let me know how can I restore grafana.db sqlite3 to mysql? I was able to convert the sqlite db to mysql and configured grafana to use mysql, but I am seeing empty dashboards. Can you please help?

@thachnv92 How did you resolve ERROR 1146 (42S02) at line 1: Table ‘grafana.alert’ doesn’t exist?

Thx for this solution, helped me to migrate to maridb in minutes!! GREAT

I am having the same problem as “raja4frnz” where after the data transfer to mysql dashboards do not show a title, or any panels. (with the exception of 1 dashboard) The dashboard titles do show in the dashboard list.
I am using:
Grafana v6.4.3 (commit: 3a2bfb7)
MariaDB Server: 5.5.64
CentOS Linux release 7.5.1804
Procedure used: Shut down Grafana, saved grafana.db, ran the new version of dcech’s script to get sqls, changed grafana.ini to point to mysql db, brought up grafana, waited 3 minutes and shutdown grafana, imported sql data into mysql db, started grafana.
On the manage dashboard page I can see all the dashboard titles but when selecting them only 1 works. I have confirmed that the data field of the dashboard table contains JSON data for all 3 dashboards.
In examining the sql data exported from the sqlite3 db I see that the one dashboard that works was saved in hexidecimal format and not json as the other 2 were.
I am testing this migration in a lab setup before touching production.
Any help will be appreciated.

So sorry, Long time I don’t login to this community.
I don’t remember it @@

worked like a charm!

This script has now been slightly modified (to handle some little issues I ran into recently with a migration from sqlite to MySQL), and has been posted to a public GitHub repo here:

By putting this script in a repo, we can more easily keep it up to date, track changes, publish an associated README, etc.

Enjoy!
Eric

Hi
I get the below error when I try to import the database:
ERROR 1062 (23000) at line 201808: Duplicate entry '281536' for key 'annotation.PRIMARY'

Hi,

When I import to database I get this error

ERROR 1146 (42S02) at line 1: Table ‘grafana_serverdb3.alert’ doesn’t exist