Moving from SQLite to MySQL

Category: LiveConfig
Created: 2020-04-09
Updated: 2021-03-03

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:

• 64bit version: lcdbdump
• 32bit version: lcdbdump_i386

root@srv:~# wget http://download.liveconfig.com/tools/lcdbdump
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 user as well as a new database for LiveConfig. You can choose any arbitrary name for both the user and the database, but use a secure (!) password:

mysql> CREATE USER 'liveconfig'@'localhost' IDENTIFIED BY 'SaFePaSsWoRd';
mysql> CREATE DATABASE LIVECONFIG;
mysql> GRANT ALL PRIVILEGES ON LIVECONFIG.* TO 'liveconfig'@'localhost';

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