How to migrate owncloud from SQLite to MySQL database

I have installed owncloud in our server that time i didn’t aware of database selection (SQLite or MySQL). By default owncloud installation taking SQLite database. After few days i came to know, it will not give better performance if the database is increased. So, i plan to migrate my existing installation of owncloud from SQLite to MySQL.

Follow the below steps to migrate it but only one thing, you need to create all the existing user once again, while creating it will tell, the user home directory is already there but its not a problem.

Tested Environment.

Server OS CentOS release 6.5 Server (Final)
SQLite Version 3.8.2
MySQL Version 5.5.37
ownCloud Version 6.0.4
Apache Version 2.2.27

Don’t worry, you will not lose your existing documents while migrating.

Step-1

For testing purpose, i’m going to install owncloud newly with SQLite database.
how-to-migrate-owncloud-from-sqlite-to-mysql-database-1
It will take little bit time to setup the SQLite database.
how-to-migrate-owncloud-from-sqlite-to-mysql-database-2

Step-2

After installing, i’m going to create few user’s for testing purpose. By default its shows only admin user.
how-to-migrate-owncloud-from-sqlite-to-mysql-database-3
Adding more users.
how-to-migrate-owncloud-from-sqlite-to-mysql-database-4

Step-3

See my data directory, it was shows all created users from owncloud admin page. And i have uploaded some files to all the users account for testing purpose.

root@server [/home/cloud2daygeek/public_html/owncloud/data]# ls -lh
total 48K
drwxrwx--- 10 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:48 ./
drwxr-xr-x 13 cloud2daygeek cloud2daygeek 4.0K Jul  3 13:48 ../
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:45 2daygeek/
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 14:00 admin/
-rw-r--r--  1 cloud2daygeek cloud2daygeek  268 Jul  3 14:00 .htaccess
-rw-r--r--  1 cloud2daygeek cloud2daygeek    0 Jul  3 14:00 index.html
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:46 jayani/
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:48 kaushik/
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:46 magesh/
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:47 nuvvula/
-rw-r--r--  1 cloud2daygeek cloud2daygeek    0 Jul  3 14:00 .ocdata
-rw-r-----  1 cloud2daygeek cloud2daygeek  613 Jul  3 07:47 owncloud.log
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:47 ramya/
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:47 suresh/

Step-4

Check my config.php file, it will clearly shows we are currently using SQLite database.

 GNU nano 2.0.9                                   File: config.php                                                                            

 'ocbe46c9c7aa',
  'passwordsalt' => 'b15eb001c64710362dc4c3860d1201',
  'trusted_domains' =>
  array (
    0 => 'owncloud.2daygeek.com',
  ),
  'datadirectory' => '/home/cloud2daygeek/public_html/owncloud/data',
  'dbtype' => 'sqlite3',
  'version' => '6.0.4.1',
  'installed' => true,
);

Step-5

Change the Installed option from ‘installed’ => true, ‘installed’ => false, and save the file

 GNU nano 2.0.9                                   File: config.php                                                                            

 'ocbe46c9c7aa',
  'passwordsalt' => 'b15eb001c64710362dc4c3860d1201',
  'trusted_domains' =>
  array (
    0 => 'owncloud.2daygeek.com',
  ),
  'datadirectory' => '/home/cloud2daygeek/public_html/owncloud/data',
  'dbtype' => 'sqlite3',
  'version' => '6.0.4.1',
  'installed' => false,
);

Step-6

If you want to use MySQL database, you should create the MySQL database on your server, Follow the below steps to create the MySQL Data Base.

root@server57323 [~]# MySQL -u root -p
Enter Password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7871
Server version: 5.5.37-cll MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL> create database owncloud;
Query OK, 1 row affected (0.00 sec)

MySQL> CREATE USER 'owncloud'@'localhost' IDENTIFIED BY 'owncloud';
Query OK, 0 rows affected (0.00 sec)

MySQL> GRANT ALL PRIVILEGES ON owncloud.* TO 'owncloud'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MySQL> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Step-7

Now, Again navigate the owncloud installed directory on your browser, it will ask you to create the admin username and password to manage your owncloud, here you need to Enter the database information below. Then hit Finish button. It will take few seconds to setup database.
how-to-migrate-owncloud-from-sqlite-to-mysql-database-9

Step-8

Now, you will redirect into the owncloud admin area. Here check the folders, like (documents, music & photos) everything shows it has been created seconds ago
how-to-migrate-owncloud-from-sqlite-to-mysql-database-10

Step-9

I have already notified you on step-3. Here i’m going to checking whether the created files is there or not. Yes its there and its clearly shows creating time 32 minutes ago
how-to-migrate-owncloud-from-sqlite-to-mysql-database-11

Step-10

Now, Again going to check the config.php file, its shows new database details. See the screen shot.

 GNU nano 2.0.9                                   File: config.php                                                                            

 'ocbe46c9c7aa',
  'passwordsalt' => 'b15eb001c64710362dc4c3860d1201',
  'trusted_domains' =>
  array (
    0 => 'owncloud.2daygeek.com',
  ),
  'datadirectory' => '/home/cloud2daygeek/public_html/owncloud/data',
  'dbtype' => 'mysql',
  'version' => '6.0.4.1',
  'dbname' => 'owncloud_cloud',
  'dbhost' => 'localhost',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'owncloud_cloud',
  'dbpassword' => 'cloud',
  'installed' => true,
);

Step-11

See the below screen shot. All the existing user directory is there.

root@server [/home/cloud2daygeek/public_html/owncloud/data]# ls -lh
total 48K
drwxrwx--- 10 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:48 ./
drwxr-xr-x 13 cloud2daygeek cloud2daygeek 4.0K Jul  3 13:48 ../
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:45 2daygeek/
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 14:00 admin/
-rw-r--r--  1 cloud2daygeek cloud2daygeek  268 Jul  3 14:00 .htaccess
-rw-r--r--  1 cloud2daygeek cloud2daygeek    0 Jul  3 14:00 index.html
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:46 jayani/
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:48 kaushik/
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:46 magesh/
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:47 nuvvula/
-rw-r--r--  1 cloud2daygeek cloud2daygeek    0 Jul  3 14:00 .ocdata
-rw-r-----  1 cloud2daygeek cloud2daygeek  613 Jul  3 07:47 owncloud.log
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:47 ramya/
drwxr-xr-x  3 cloud2daygeek cloud2daygeek 4.0K Jul  3 07:47 suresh/

But, In owncloud admin page shows only one account.
how-to-migrate-owncloud-from-sqlite-to-mysql-database-14

Step-12

Now, i’m going to create one of my existing user nuvvula and i will show the warning message because nuvvula folder is already exist in system.
how-to-migrate-owncloud-from-sqlite-to-mysql-database-16

Step-13

Now, i’m going to login nuvvula user to check whether the files are there or not.
how-to-migrate-owncloud-from-sqlite-to-mysql-database-17
Yes, everything is there in nuvvula user.
how-to-migrate-owncloud-from-sqlite-to-mysql-database-19
Now, you will clear while migrating owncloud database from SQLite to MySQL. you will not lose anything except user accounts.

We are preparing all articles in-depth to understand by all level/stage Linux administrators. If the article is useful for you, then please spend less than a minute to share your valuable comments in our commenting section.

Please stay tune with us…Good Luck.

About Magesh Maruthamuthu

Love to play with all Linux distribution

View all posts by Magesh Maruthamuthu

20 Comments on “How to migrate owncloud from SQLite to MySQL database”

  1. Thanks for this! I was getting errors in the conversion steps with the documentation. I had created a mysql db after creating the owncloud instance, and it wouldn’t let me convert to the mysql one from sqlite. After following your tut, it worked fine.

    Thanks!

  2. HI,

    I followed the steps but it seems that mysql is not showing up on my setup screen, it keeps on showing sqlite will be used as database etc..

    -OC installed in Centos 6.6 Mysql 5.5

    help?

  3. More infos after retarting my browser :
    – 7.0.2 is proposed again
    – looking on web trafic : it seems taht comparisons are made between local and cloud files, because i see sync trafic on upload and download

  4. Migrate today from SQLite to SQL Server. No problems, all documents are présents.
    BUT :
    – 1st : the space used is not the real space but the one related to the few documents created by the “new” install of the account (2.1 MB instead of about 10GB)
    – 2nd : for the version. Before migration it was proposed a new version 7.0.2, now it’s not proposed and for the updater 7.0.1 is OK
    – Restarting the OwnCloud sync application on my computer restart the upload of of my files

  5. Thanks alot for writing this article.
    Did you also perform some tests using the Owncloud encryption feature?
    I assume that encryption would break things badly, as the keys might be stored in the SQLite DB..

    Regards

Leave a Reply

Your email address will not be published. Required fields are marked *