Migration from sqlite3 to MySQL database

mysql

#1

Hi all !
I try to migrate db of my grafana from sqlite3 to mysql database.
I took my grafana.db file and converted it to sql dump file. I’ve tryed to make it by different ways many times and in the end got luck with converting sqlite db to SQL dump.
SQL dump was successfully uploaded to the my MySQL db. But when I restarted the grafana-server and go to the grafana --> Dashboards --> Home there I saw list of my dashboards but if I choose one of the dashboards and try to view, dashboard is empty (no graphs), datasources presents but without any credentials.
I think it’s due to that in db we have a lot of JSON code and I don’t know why but mysql cant properly read it from db.
Maybe some one could give me some advice how to fix it.


Migrating grafana data to new database
#2

+1 , I took a script from another topic - Migrating grafana data to new database

And unfortunately during the import of the dump i receive
[root@xxxxxxxxxxxxxxxxx]# mysql -u grafana -p -h xx.xx.xx.xx grafana < grafana.sql
Enter password:
ERROR 1136 (21S01) at line 2963: Column count doesn’t match value count at row 1
INSERT INTO annotation VALUES(4,1,2,0,1094,2,0,‘alert’,‘Panel Title alert’,‘OK’,’’,‘pending’,‘ok’,’{}’,1504025910,0);
Also can you share some info about
https://github.com/grafana/grafana/tree/05d43999dc83c2adc5bda27eb8e41e0b762c35ea/pkg/services/sqlstore ?

Thanks !


#3

Hey !
I’ve solved my issue and successfully moved grafana from sqlite to mysql.
Just make next.

  1. Create the db and user for grafana
  2. Download from server you grafana.db sqlite database file on your loacal laptop or pc
  3. Go to this link https://www.rebasedata.com/convert-sqlite-to-mysql-online it’s perfect online converter from sqlite to mysql.
  4. After that upload that dump to your grafana db, if you see again empty dashboards just restore them from version history, but at first give them names which was previously.

#4

Hey a big thank you mate! I save me a lot of time!
Some additional notes regarding this topic:

  1. If you want to transfer the grafana.db to another host make sure they are on the same major version. Learned that the hard way(i know it’s logical , but … )
  2. Make sure your dashboards have at least one additional revision expect the initial. Otherwise you can restore it and it will be a blank page(dashboard)

#5

Hi @zaharievasen @funkerman
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.


#6

Could be two issues :

  1. you missed the comma between two values or column name

  2. you put extra values or an extra column name

Can’t guessing further without sql dump analysis


#7

Check your grafana.sql dump in line 797


#8

Hi
I did it.
Because old version, database haven’t a few tables.
After I upgrade to new version. I did it.


#10

I’d like to thank every body for each contribution. I followed your tips and did it great with additional steps, though.
My goal was to migrate from Grafana V4.6 using sqlite3 to Grafana V5.4 using MySQL:

  1. As advised I let Grafana V5.4 create the MySQL database structure.
  2. Then, after using dcech’s script "sqlitedump.sh grafana.db > grafana.sql; mysql grafana < grafana.sql "

I needed to execute additional commands to get it right. As I was switching from Grafana V4.6 to Grafana V5.4 there were missing some fields in the former dB structure. In the elder version there were fewer fields.

  1. I needed to add the table fields in the INSERT clause
    # Agregar los nombres de los campos
    echo $(./backup_SQL_agregar_campos.sh) grafana.sql | sh > grafana_fields.sql
    mysql -uroot -pquetejedi -f grafana < grafana_fields.sql

  2. In Grafana V5 any dashboard needs to have an uid in order to be accessed
    # Cargarmos uid. Debe tener un tamaño entre 9 y 12
    (IFS=$'\n';for uid in $(mysql -N grafana -e "select id from dashboard WHERE uid is null"); do echo $uid | awk '{printf("mysql grafana -e \"UPDATE dashboard SET uid = '\''%09i'\'' WHERE id = %s\"\n", $0, $0)}' | sh; done;)

  3. Then, I came along that there is no Read Only Editor permission any longer so I had to switch it to Viewer
    # No hay mas read only user en la nueva version. Los cambiamos por view
    (IFS=$'\n';for id in $(mysql -N -uroot -pquetejedi grafana -e "select id from org_user WHERE role = 'Read Only Editor'"); do echo $id | awk '{printf("mysql -uroot -pquetejedi grafana -e \"UPDATE org_user SET role = '\''Viewer'\'' WHERE id = %s\"\n", $0)}' | sh; done;)

The following is the script for adding field values in every INSERTE CLAUSE (step 3)

backup_SQL_agregar_campos.sh
#!/bin/bash
DB=/var/lib/grafana/grafana.db
echo -n "sed '"
puntoycoma=0
TABLES=$(sqlite3 $DB .tables | sed -r 's/(\S+)\s+(\S)/\1\n\2/g' | grep -v migration_log)

for t in $TABLES; do
echo ".schema $t " | sqlite3 /var/lib/grafana/grafana.db | sed 's/,/\n/g' | grep -v "^$" | awk -v tabla=$t -v puntoycoma=$puntoycoma 'BEGIN{FS="`"; estado="fuera"; coma=" "; string="INSERT INTO "tabla" ("}
{
 if(match($0,/CREATE TABLE/))
  {estado="comienza"}
 else if(match($0,/);/) && estado != "finalizado")
  {estado="sale"}

 switch(estado)
 {
 case "comienza":   	estado="dentro";
                        break;

 case "dentrocoma":     coma=", ";
 case "dentro":         string=sprintf("%s%s`%s`", string, coma, $2);
                        estado="dentrocoma";
                        break;

 case "sale":           string=sprintf("%s%s`%s`) VALUES", string, coma, $2);
                        estado="finalizado";
                        break;
 }
}
END{printf("%ss/INSERT INTO %s VALUES/%s/g", puntoycoma==1?"; ":"", tabla, string)}'
puntoycoma=1
done
echo "' "