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.

[email protected] [/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.

[email protected] [~]# 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.

[email protected] [/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.

Magesh Maruthamuthu

Love to play with all Linux distribution

You may also like...

Shares
Close
Please support the site
By clicking any of these buttons you help our site to get better