Grafana — Database migration from Sqlite3 to MySQL

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.

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.

  • 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:

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

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.org
Feb 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.
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.log
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_
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)
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=
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#
root@vagrant:/opt# systemctl start grafana-server

DevOps Consultant. I’m strongly focused on automation, security, and reliability.