103 lines
3.3 KiB
Plaintext
103 lines
3.3 KiB
Plaintext
<!-- { "title": "Migrating a Grafana configuration database from SQLite to MySQL/MariaDB" } -->
|
|
|
|
|
|
<h1>#title#</h1>
|
|
<p>
|
|
In the default installation <a href="https://grafana.com">Grafana</a> is using a SQLite database for its configuration and session data. Certainly, this is working very well in most situations.
|
|
</p>
|
|
|
|
<p>
|
|
Nevertheless I preferred to use my anyhow existing MariaDB server.
|
|
</p>
|
|
|
|
<p>
|
|
So, I had to migrate the already existing SQLite database containing the Grafana configuration to MySQL syntax. Not to difficult, but at least it was not working without some adjustments. I found some hints how to do this migration. However, at least one hint I found more than once pointed to a paid migration service - no, thank you.
|
|
</p>
|
|
|
|
<p>
|
|
I think for text processing using regular expressions Perl is the language of choice. so I prepared a small processing frame:
|
|
<pre><code class="perl">
|
|
#!/usr/bin/perl -w
|
|
use strict;
|
|
|
|
while (<>) {
|
|
chomp;
|
|
|
|
print "$_\n";
|
|
}
|
|
</code></pre>
|
|
</p>
|
|
|
|
<p>
|
|
I deployed a dedicated test instance of a MariaDB server using Docker on my laptop, created a database on that MariaDB server
|
|
|
|
<pre><code class="SQL">
|
|
create database grafana;
|
|
create user 'grafana'@'%' identified by 'test123';
|
|
grant all privileges on grafana.* to 'grafana'@'%';
|
|
flush privileges;
|
|
</code></pre>
|
|
|
|
and used a small shell script to export the SQLite database into a SQL file, pass it through the above Perl script and feed it into the MariaDB server:
|
|
|
|
<pre><code class="shell">
|
|
#!/bin/bash
|
|
|
|
|
|
DB_HOST=127.0.0.1
|
|
DB_ROOT_PASS=geheim123
|
|
DB_NAME=grafana
|
|
DB_USER=grafana
|
|
DB_PASS=test123
|
|
|
|
|
|
cat grafana.in | sqlite3 grafana.db && \
|
|
cat grafana.sql | ./sqlite2mariadb.pl > new.sql && \
|
|
echo "drop database $DB_NAME; create database $DB_NAME;' | \
|
|
mysql -h $DB_HOST -u root --password=$DB_ROOT_PASS mysql && \
|
|
cat new.sql | mysql -h $DB_HOST -u $DB_USER --password=$DB_PASS --abort-source-on-error -v $DB_NAME
|
|
</code></pre>
|
|
</p>
|
|
|
|
<p>
|
|
Inspecting the error messages from the import into the MariaDB I extended the Perl script more and more and finally ended up with this code:
|
|
|
|
<pre><code class="perl">
|
|
#!/usr/bin/perl -w
|
|
|
|
use strict;
|
|
|
|
|
|
while (<>) {
|
|
chomp;
|
|
s/^BEGIN TRANSACTION;//;
|
|
s/^PRAGMA .+?;//;
|
|
s/AUTOINCREMENT/AUTO_INCREMENT/;
|
|
s/TEXT PRIMARY KEY/VARCHAR(1024) PRIMARY KEY/;
|
|
s/` TEXT /` MEDIUMTEXT /;
|
|
s/`for` INTEGER/`for` BIGINT/;
|
|
s/`frequency` INTEGER/`frequency` BIGINT/;
|
|
s/`epoch` INTEGER/`epoch` BIGINT/;
|
|
s/`epoch_end` INTEGER/`epoch_end` BIGINT/;
|
|
s/`created` INTEGER/`created` BIGINT/;
|
|
s/`updated` INTEGER/`updated` BIGINT/;
|
|
s/^.+? sqlite_sequence.*$//;
|
|
s/^CREATE INDEX `.+$//;
|
|
print "$_\n";
|
|
}
|
|
</code></pre>
|
|
</p>
|
|
|
|
<p>
|
|
Maybe on a different installation or using a future version of Grafana you would run into more issues. In that case: just put additional substitutions into the script.
|
|
</p>
|
|
|
|
<p>
|
|
When the whole stuff ran through without errors I backupped the configuration and storage volumes of my Grafana container, created new ones with copies of the old ones. I removed the <tt>grafana.db</tt> file from the storage volume and modified the <tt>grafana.ini</tt> in the configuration volume to use the MariaDB server, loaded the transformed SQL into the productive MariaDB server and restarted the Grafana container with the new volumes.
|
|
</p>
|
|
|
|
<p>
|
|
Suprisingly it works immediately.
|
|
</p>
|
|
|