Grafana — Database migration from Sqlite3 to MySQL

5 min readFeb 24, 2021

Shouldn’t it be Sqlite3?

I don’t say no. It is better to make it only a temporary case where you really want to only test it.

When Grafana goes live and it is heavily used in the team, you want to be sure, however, that in the event of any failure, it will be possible to restore the database quickly and easily.

This time, I will introduce the procedure for migrating from to with sqlite3 to MySQL for Grafana.

Migrating procedure from sqlite3 to MySQL

There are some things that you have to be careful about in order, but it is a method that does not cause data loss, so please proceed with confidence. There are all steps below.

Install and configure MySQL
Prepare database-migrator for Grafana
Extract sql for MySQL from grafana.db
Change the settings in grafana.ini
Create various MySQL tables
Throw data into MySQL

Install and configure MySQL:

I think there are a lot of instructions on the internet for installing MySQL on the ubuntu server. Example tutorial is below:

How To Install MySQL on Ubuntu 18.04 | DigitalOcean

MySQL is an open-source database management system, commonly installed as part of the popular LAMP (Linux, Apache…

www.digitalocean.com

⚠️ Remember that in the /etc/mysql/mysql.conf.d/mysqld.cnf file we have to add one end line for character encoding

root@vagrant:/home/vagrant# cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep character-set-server
character-set-server  = utf8mb4

If we already have installed and configured Mysql, we can start creating the Database for Grafana.

Prepare database-migrator for Grafana

database-migrator , this is a program that dumps data migration script (SQL) sqlite3from. Ok so we can clone repository to /opt directory

root@vagrant:/home/vagrant# cd /opt/
root@vagrant:/opt# git clone https://github.com/grafana/database-migrator.git
Cloning into 'database-migrator'...
remote: Enumerating objects: 25, done.
remote: Counting objects: 100% (25/25), done.
remote: Compressing objects: 100% (25/25), done.
remote: Total 25 (delta 7), reused 0 (delta 0), pack-reused 0
Unpacking objects: 100% (25/25), done.
root@vagrant:/opt# cd database-migrator/
root@vagrant:/opt/database-migrator# chmod +x sqlitedump.sh

Extract sql for MySQL from grafana.db

First we must stop grafana-server

root@vagrant:/opt/database-migrator# systemctl stop grafana-server
root@vagrant:/opt/database-migrator# systemctl status grafana-server
● grafana-server.service - Grafana instance
   Loaded: loaded (/usr/lib/systemd/system/grafana-server.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: http://docs.grafana.orgFeb 21 16:40:09 vagrant grafana-server[2548]: t=2021-02-21T16:40:09+0000 lvl=info msg="Starting DB migration" logger=migrator
Feb 21 16:40:09 vagrant grafana-server[2548]: t=2021-02-21T16:40:09+0000 lvl=info msg="Starting plugin search" logger=plugins
Feb 21 16:40:09 vagrant grafana-server[2548]: t=2021-02-21T16:40:09+0000 lvl=info msg="Registering plugin" logger=plugins name="Direct Input"
Feb 21 16:40:09 vagrant grafana-server[2548]: t=2021-02-21T16:40:09+0000 lvl=info msg="HTTP Server Listen" logger=http.server address=192.168.123.123:3000 proto
Feb 21 16:40:26 vagrant grafana-server[2548]: t=2021-02-21T16:40:26+0000 lvl=info msg="Request Completed" logger=context userId=0 orgId=0 uname= method=GET path
Feb 21 16:40:29 vagrant grafana-server[2548]: t=2021-02-21T16:40:29+0000 lvl=info msg="Successful Login" logger=http.server User=admin@localhost
Feb 21 17:02:53 vagrant systemd[1]: Stopping Grafana instance...
Feb 21 17:02:53 vagrant grafana-server[2548]: t=2021-02-21T17:02:53+0000 lvl=info msg="Shutdown started" logger=server reason="System signal: terminated"
Feb 21 17:02:53 vagrant grafana-server[2548]: t=2021-02-21T17:02:53+0000 lvl=info msg="Stopped Stream Manager"
Feb 21 17:02:53 vagrant systemd[1]: Stopped Grafana instance.

Use the above command to generate a SQLite3 database dump

root@vagrant:/opt/database-migrator# ./sqlitedump.sh /var/lib/grafana/grafana.db > /opt/db_dump.sqlroot@vagrant:/opt# cd /opt/
root@vagrant:/opt# ls -l
total 12
drwxr-xr-x 3 root root 4096 Feb 21 17:01 database-migrator
-rw-r--r-- 1 root root 2099 Feb 21 17:06 db_dump.sql
drwxr-xr-x 9 root root 4096 Mar 15  2020 VBoxGuestAdditions-5.2.34

Change the settings in grafana.ini

Now we should change settings in grafana.ini , configuration below:

Create various MySQL tables

There is no data in the database yet. So run Grafana to have all the tables automatically created.

root@vagrant:/opt# systemctl start grafana-server
root@vagrant:/opt# systemctl status grafana-server
● grafana-server.service - Grafana instance
   Loaded: loaded (/usr/lib/systemd/system/grafana-server.service; disabled; vendor preset: enabled)
   Active: active (running) since Sun 2021-02-21 17:13:38 UTC; 4s ago
     Docs: http://docs.grafana.org
 Main PID: 4916 (grafana-server)
    Tasks: 6 (limit: 4662)
   CGroup: /system.slice/grafana-server.service
           └─4916 /usr/sbin/grafana-server --config=/etc/grafana/grafana.ini --pidfile=/var/run/grafana/grafana-server.pid --packaging=deb cfg:default.paths.logFeb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Executing migration" logger=migrator id="add index server_lock.operation_
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Executing migration" logger=migrator id="create user auth token table"
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Executing migration" logger=migrator id="add unique index user_auth_token
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Executing migration" logger=migrator id="add unique index user_auth_token
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Executing migration" logger=migrator id="create cache_data table"
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Executing migration" logger=migrator id="add unique index cache_data.cach
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Created default admin" logger=sqlstore user=admin
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Starting plugin search" logger=plugins
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Registering plugin" logger=plugins name="Direct Input"
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="HTTP Server Listen" logger=http.server address=192.168.123.123:3000 proto
lines 1-19/19 (END)

Now we can check logs in /var/log/syslog and search message “HTTP Server Listen”

root@vagrant:/opt# tail -f /var/log/syslog
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Executing migration" logger=migrator id="add index server_lock.operation_uid"
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Executing migration" logger=migrator id="create user auth token table"
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Executing migration" logger=migrator id="add unique index user_auth_token.auth_token"
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Executing migration" logger=migrator id="add unique index user_auth_token.prev_auth_token"
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Executing migration" logger=migrator id="create cache_data table"
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Executing migration" logger=migrator id="add unique index cache_data.cache_key"
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Created default admin" logger=sqlstore user=admin
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Starting plugin search" logger=plugins
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="Registering plugin" logger=plugins name="Direct Input"
Feb 21 17:13:40 vagrant grafana-server[4916]: t=2021-02-21T17:13:40+0000 lvl=info msg="HTTP Server Listen" logger=http.server address=192.168.123.123:3000 protocol=http subUrl= socket=
Feb 21 17:15:10 vagrant grafana-server[4916]: t=2021-02-21T17:15:10+0000 lvl=eror msg="Failed to look up user based on cookie" logger=context error="user token not found"
Feb 21 17:15:10 vagrant grafana-server[4916]: t=2021-02-21T17:15:10+0000 lvl=info msg="Request Completed" logger=context userId=0 orgId=0 uname= method=GET path=/ status=302 remote_addr=192.168.123.1 time_ms=0 size=29 referer=http://192.168.123.123:3000/
Feb 21 17:15:17 vagrant grafana-server[4916]: t=2021-02-21T17:15:17+0000 lvl=eror msg="Invalid username or password" logger=context userId=0 orgId=0 uname= error="Invalid Username or Password" remote_addr=192.168.123.1
Feb 21 17:15:17 vagrant grafana-server[4916]: t=2021-02-21T17:15:17+0000 lvl=info msg="Request Completed" logger=context userId=0 orgId=0 uname= method=POST path=/login status=401 remote_addr=192.168.123.1 time_ms=30 size=42 referer=http://192.168.123.123:3000/login
Feb 21 17:15:19 vagrant grafana-server[4916]: t=2021-02-21T17:15:19+0000 lvl=info msg="Successful Login" logger=http.server User=admin@localhost
Feb 21 17:15:22 vagrant grafana-server[4916]: t=2021-02-21T17:15:22+0000 lvl=info msg="Request Completed" logger=context userId=1 orgId=1 uname=admin method=GET path=/login status=302 remote_addr=192.168.123.1 time_ms=5 size=24 referer=

When you find this, stop Grafana again.

root@vagrant:/opt# systemctl stop grafana-server

Throw data into MySQL

root@vagrant:/opt# mysql -u grafana -p grafana < db_dump.sql
Enter password:
root@vagrant:/opt#

If there are no errors while importing to the database, start the Grafana

root@vagrant:/opt# systemctl start grafana-server

Done migration should be successful now, You can access it and give it a try login!