Migrate owncloud 5 from SQLite3 to MySQL¶

Maybe at some point you realize, that it was not a good idea to have owncloud initially set up with the SQLite-Backend and want to change to MySQL.

Preliminary Note¶

this is basically taken from http://fabianpeter.de/cloud/owncloud-migrating-from-sqlite-to-mysql/

and extended with information from

• http://trac.edgewall.org/wiki/SqLiteToMySql
• http://pario.no/2008/01/27/mysql-create-utf8-database/
• http://www.redmine.org/boards/2/topics/12793

Migration¶

Install MySQL and necessary php-modules¶

yum install mysql mysql-server php-mysql

Setup MySQL¶

We need to make sure that MySQL is started up automatically at system start and need to set a root password for MySQL

chkconfig mysqld on
/etc/init.d/mysqld start
/usr/bin/mysql_secure_installation

Create owncloud Database¶

mysql -p
CREATE DATABASE owncloud CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANT ALL PRIVILEGES ON owncloud.* TO owncloud IDENTIFIED BY "pasword";
flush privileges;
exit

Dump SQLite3-Database¶

sqlite3 /var/www/html/owncloud/data/owncloud.db .dump > /tmp/dump.sql

Convert SQLite3 to MySQL¶

Since MySQL uses a pretty special comment/escape format and SQLite uses some commands MySQL doesn’t understand, the SQL output has to be converted. Since my database is quite big and I don’t want to repeat the work others did before me, a little Google magic came up with a python script that does the converter job.

#! /usr/bin/env python

import sys

def main():
    print "SET sql_mode='NO_BACKSLASH_ESCAPES';" 
    lines = sys.stdin.read().splitlines()
    for line in lines:
        processLine(line)

def processLine(line):
    if (
        line.startswith("PRAGMA") or 
        line.startswith("BEGIN TRANSACTION;") or
        line.startswith("COMMIT;") or
        line.startswith("DELETE FROM sqlite_sequence;") or
        line.startswith("INSERT INTO \"sqlite_sequence\"")
       ):
        return
    line = line.replace("AUTOINCREMENT", "AUTO_INCREMENT")
    line = line.replace("DEFAULT 't'", "DEFAULT '1'")
    line = line.replace("DEFAULT 'f'", "DEFAULT '0'")
    line = line.replace(",'t'", ",'1'")
    line = line.replace(",'f'", ",'0'")
    in_string = False
    newLine = @
    for c in line:
        if not in_string:
            if c == "'":
                in_string = True
            elif c == '"':
                newLine = newLine + '`'
                continue
        elif c == "'":
            in_string = False
        newLine = newLine + c
    print newLine

if __name__ == "__main__":
    main()

download above file to your /home, make it executable and perform the conversion

chmod a+x /~/sqlite3-to-mysql.py
cat /tmp/dump.sql | python sqlite3-to-mysql.py > /tmp/owncloud.sql

Import the dump¶

One thing to note is that SQLite uses utf8 as default. If you have your server configure with encoding other than utf8 and you can't change that, you are going to get your data bad encoded.

Despite the fact that you may probably already set your MySQL database to use utf8 as a default character set, you could have problems when you import data. It's very important to keep in mind that your connection encoding counts. So be sure that whatever software you use as client you set your charset as utf8. As an example for the console client:

mysql -u owncloud -p owncloud --default-character-set=utf8 < /tmp/owncloud.sql

Change owncloud-config¶

While most people seem to change the “installed” key in the config array (config/config.php) from true to false, I found it simpler to just add the correct database values.

...
‘dbtype’ => ‘mysql’,
‘dbname’ => ‘owncloud’,
‘dbuser’ => ‘owncloud’,
‘dbpassword’ => ‘password’,
‘dbhost’ => ’127.0.0.1',
‘dbtableprefix’ => ‘oc_’,
...