Owncloud server migrate SQLite3 to MySQL » Historie » Revision 2
Revision 1 (Jeremias Keihsler, 13.01.2017 18:24) → Revision 2/3 (Jeremias Keihsler, 13.01.2017 18:24)
h1. 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. h2. 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 h2. Migration h3. Install MySQL and necessary php-modules <pre><code class="bash"> yum install mysql mysql-server php-mysql </code></pre> h3. 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@ <pre><code class="bash"> chkconfig mysqld on /etc/init.d/mysqld start /usr/bin/mysql_secure_installation </code></pre> h3. Create owncloud Database <pre><code class="sql"> class="bash"> 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 </code></pre> h3. Dump SQLite3-Database <pre><code class="bash"> sqlite3 /var/www/html/owncloud/data/owncloud.db .dump > /tmp/dump.sql </code></pre> h3. 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. <pre><code class="python"> #! /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() </code></pre> download above file to your @/home@, make it executable and perform the conversion <pre><code class="bash"> chmod a+x /~/sqlite3-to-mysql.py cat /tmp/dump.sql | python sqlite3-to-mysql.py > /tmp/owncloud.sql </code></pre> h3. 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: <pre><code class="bash"> mysql -u owncloud -p owncloud --default-character-set=utf8 < /tmp/owncloud.sql </code></pre> h3. 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. <pre> <pre php config/config.php> ... ‘dbtype’ => ‘mysql’, ‘dbname’ => ‘owncloud’, ‘dbuser’ => ‘owncloud’, ‘dbpassword’ => ‘password’, ‘dbhost’ => ’127.0.0.1', ‘dbtableprefix’ => ‘oc_’, ... </pre>