How to Migrate Nextcloud Database: SQLite to MariaDB

I noticed that some users have installed Nextcloud with SQLite on a production system. This is suitable for a user or two with lower server load. However, if you are considering switching to a more reliable database for improved performance, you can utilize MariaDB, MySQL, or PostgreSQL. In this article, I will guide you on how to migrate the Nextcloud database from SQLite to MariaDB.

Nextcloud_Logo

Looking to install Nextcloud 28 on Ubuntu 22.04? Click here

1- Installation and database creation

If you haven’t installed MariaDB yet, run the following command to install it, or skip this step if you have already done so. I will assume that you are using an Ubuntu server.

apt install mariadb-server php-mysql
systemctl enable mariadb

Execute the following script to secure the database server by implementing initial configurations recommended by the script. If you have already completed this step, you may skip it.

/usr/bin/mysql_secure_installation
mariadb-mysql_secure_installation

To proceed, log in to the database server and follow these steps:

  1. Create a nextcloud database.
  2. Create a user named nc_user and assign it to the newly created database.
mysql -u root -p

create database nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
grant all on nextcloud.* to 'nc_user'@'localhost' identified by 'PASSWORD';

flush privileges;
exit

2- Migrate to MariaDB

The migration process is straightforward and simple. Nextcloud occ command will do all the magic.

Change directory to your Nextcloud installation:

cd /var/www/html

To initiate the conversion, execute the following command. If your database name and username remain unchanged, there is no need to modify anything. After pressing Enter, you will be prompted to enter the password.

sudo -u www-data php occ db:convert-type --all-apps mysql nc_user localhost nextcloud

For added safety, it is recommended to run the following commands:

  1. Will add mysql.utf8mb4 to config.php file
  2. Repair the database tables
sudo -u www-data php occ maintenance:mode --on

sudo -u www-data php occ config:system:set mysql.utf8mb4 --type boolean --value="true"

sudo -u www-data php occ maintenance:repair

sudo -u www-data php occ maintenance:mode --off

Congratulations! You have successfully completed the migration process, and everything should now be functioning properly under the new database engine.