Moving from SQLite to MySQL

Category: LiveConfig
Created: 2020-04-09

When a LiveConfig installation has more than about 400-500 hosting subscriptions, it’s usually more efficient to use MySQL or MariaDB as backend database instead of the bundled SQLite database.

This article describes the steps required for migrating the data from SQLite into a MySQL-/MariaDB database.

Step 1: exporting the SQLite database

Download the tool lcdbdump and create a MySQL-compatible dump of the SQLite database with it:

root@srv:~# wget
root@srv:~# chmod 755 lcdbdump
root@srv:~# ./lcdbdump /var/lib/liveconfig/liveconfig.db dump.sql

Step 2: create the MySQL database

Login in as root to MySQL and create a new database for LiveConfig. You can use any arbitrary name for the new database.

mysql> create database LIVECONFIG;

Then create a new database user with any (but safe!) password, like:

mysql> grant all on LIVECONFIG.* to "liveconfig"@"localhost"
       identified by "SaFePaSsWoRd";

If MySQL is running on another server than LiveConfig, insert the respective IP address instead of localhost.

Now import the prepared MySQL tables. You find them as compressed MySQL dump in /usr/share/doc/liveconfig/:

root@srv:~# zcat /usr/share/doc/liveconfig/db-mysql.sql.gz | mysql -u liveconfig -p -h localhost LIVECONFIG
Enter password: ************

Step 3: import the data

Now import the data which was exported during the first step:

root@srv:~# mysql -u liveconfig -p -h localhost LIVECONFIG < dump.sql
Enter password: ************

Step 4: using the MySQL database

Open the configuration file /etc/liveconfig/liveconfig.conf and edit the database settings, e.g.:

db_driver   = mysql
db_host     = localhost
db_name     = LIVECONFIG
db_user     = liveconfig
db_password = SaFePaSsWoRd

Now restart LiveConfig (service liveconfig restart). If LiveConfig shouldn’t start, have a look at the log file (/var/log/liveconfig/liveconfig.log).