Grafana — Database migration from Sqlite3 to MySQL

Shouldn’t it be Sqlite3?

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

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

⚠️ 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

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

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

Create various MySQL tables

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)

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!

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

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store