How to Convert Roundcube to SQLite

Overview

The /usr/local/cpanel/scripts/convert_roundcube_mysql2sqlite script updates the Roundcube configuration to use individual SQLite databases instead of a single MySQL® database. This document explains how to use that script.

The SQLite database provides the following benefits over a MySQL database:

• You can restore Roundcube data more easily. The system stores each email user’s Roundcube data in a separate SQLite database. This behavior allows you to restore data for a single user and not the entire system.
• You can track disk usage properly because the system stores the Roundcube data within a cPanel user’s /home directory.
• You can locate problematic accounts more easily because the Roundcube PHP processes run as the cPanel user.
• A single point of failure does not exist, and database corruptions only impact a single user, not the entire system.

Path and filename for the SQLite database

The SQLite database uses the following path and filename, where username represents your cPanel account username and domain represents your domain name:

/home/username/etc/domain/username.rcube.db

Convert Roundcube to SQLite database

To convert the Roundcube database in cPanel & WHM from MySQL to SQLite, run the following command:

/usr/local/cpanel/scripts/convert_roundcube_mysql2sqlite

After you convert the system to a SQLite database, you can use this script to troubleshoot issues with specific users.

To convert the Roundcube data for a single cPanel user from MySQL to SQLite, run the same command with the user parameter, where user represents the database user:

/usr/local/cpanel/scripts/convert_roundcube_mysql2sqlite user

Migrate a Roundcube MySQL database to a SQLite database

To migrate a Roundcube database from one cPanel & WHM server to another and convert the data from a MySQL database to a SQLite database, perform the following steps:

1. On the source cPanel & WHM server, create a backup of the Roundcube database. To do this, run the following command, where roundcube represents the Roundcube database:

   mysqldump roundcube > roundcube.dump

2. On the destination cPanel & WHM server, create a temporary MySQL database. To do this, follow the directions in our MySQL Databases documentation.

3. On the destination cPanel & WHM server, open the /usr/local/cpanel/scripts/convert_roundcube_mysql2sqlite script and convert the following code snippet to a comment:

   1 2 3 4 5

   --> unless ( defined $opt_dbname ) { Cpanel::Email::RoundCube::archive_and_drop_mysql_roundcube( $has_db, $logger ); } <--

4. On the destination cPanel & WHM server, import the Roundcube data to the database and convert the data to SQLite. Run the following command on the destination cPanel & WHM server, where user represents the database user and exampledatabase represents the database for which to import the Roundcube data:

   /usr/local/cpanel/scripts/convert_roundcube_mysql2sqlite user exampledatabase

5. The server will attempt to convert the Roundcube mailbox. If it encounters any errors, it will repeat the process until the conversion is successful.