License Portal
Deutsch

Moving from SQLite to MySQL

Category: LiveConfig
Created: 2020-04-09
Updated: 2024-08-14

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

Switching from MySQL to SQLite

If (for whatever reason) you want (or need) to switch from a MySQL database back to SQLite, please proceed as follows:

1. update the SQLite database schema

The SQLite database of LiveConfig is always located at /var/lib/liveconfig/liveconfig.db. If you have been using LiveConfig with MySQL for a long time, the SQLite database schema must be updated. This is quite simple:

  • stop LiveConfig (service liveconfig stop)
  • change the database back to SQLite in /etc/liveconfig/liveconfig.conf (db_driver = sqlite)
  • start LiveConfig (service liveconfig start) (during this, LiveConfig updates the database schema)
  • stop LiveConfig again (service liveconfig stop).

If there were any problems, check the log file under /var/log/liveconfig/liveconfig.log.

2. export the MySQL database

Now export the MySQL data. Adapt the MySQL user name and password accordingly from the configuration in liveconfig.conf:

root@srv:~# echo “PRAGMA foreign_keys=off;” >dump.sql
root@srv:~# mysqldump --no-create-info --compact --compatible=ansi --skip-extended-insert -u liveconfig -p LIVECONFIG >>dump.sql
root@srv:~# sed -i -e 's/\\'\''/'\'''\''/g' -e 's/\\r/\r/g' -e 's/\\n/\n/g' dump.sql| grep -v 'sqlite_'

3. create a new SQLite database

Use the following commands to create a new SQLite database and import the data:

root@srv:~# sqlite3 /var/lib/liveconfig/liveconfig.db '.schema' | grep -v 'sqlite_' >schema.sql
root@srv:~# sqlite3 liveconfig-new.db <schema.sql
root@srv:~# sqlite3 liveconfig-new.db <dump.sql
root@srv:~# chmod 0600 liveconfig-new.db
root@srv:~# chown liveconfig:liveconfig liveconfig-new.db

You can then replace the file /var/lib/liveconfig/liveconfig.db with the newly created liveconfig-new.db.

If there are warnings or error messages during any of the steps, it is best to contact us at support@liveconfig.com.