MariaDB and Nextcloud

[ernie]

Dear All

Thanks to devs for this new version and all extensions.

I am interested by nexcloud + mariaDB. It will be good to have a How To on this subject and I am ready to write it.
But I need to know the main steps and how to link nextcloud and MariaDB. Then I will test in order to find the right way.
In the past I used owncloud and there was a script available for automatic installation of owncloud + MariaDB, so no difficulties for me.

It will be good to add a section MariaDB in service section of the forum.

BR

[doktornotor]

Should be fairly easy:

0/ Make a backup

Extensions > NextOwnCloud > Backup button (on the Configuration tab).

1/ Initial MariaDB configuration:

Note: Beware of the XigmaNAS bugs noted here. The init script needs fixing as of XigmaNAS 11.2.0.4.6171. Fixed in XigmaNAS 11.2.0.4.6177.

In Services - MariaDB, configure things like this:

Home Directory: pick a location somewhere on your datavol, e.g.

Code: Select all

/mnt/datavol/mysql

Additional Parameter:

Code: Select all

transaction_isolation = READ-COMMITTED
binlog_format = ROW
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=1
query_cache_type = 1

[server]
query_cache_limit = 2M
query_cache_min_res_unit = 2k
query_cache_size = 64M
tmp_table_size= 64M
max_heap_table_size= 64M

[client]
default-character-set = utf8mb4

(NextCloud documentation reference: https://docs.nextcloud.com/server/14/ad ... b-database)

Passphrase: Use some random long string.

Tick the "Enable" box and click Apply. You should have MariaDB running now.

Now, use SSH to log on to your NAS as root

Check the Home Directory (e.g. /mnt/datavol/mysql), should have bunch of files and dirs there.
Check /var/db/mysql/my.cnf and see whether it has your "Additional Parameter" configured.

If everything looks good, run

Code: Select all

mysql_secure_installation

Follow the prompts there, make sure to choose a good password for root, I'd suggest sticking with defaults for other steps.

2/ Creating a DB Nextcloud

The example steps below assume that:
- you have Nextcloud 14 installed
- the Nextcloud DB user is nextclouduser
- the Nextcloud DB user's password is t0ps3cret
- the database name will be nextclouddb
- the lighttpd webserver is running as user www (check Services > Webserver > Permission if unsure)

Obviously, you can (and should) adjust the username, password and db name as desired.

While still connected via SSH, run:

Code: Select all

mysql -uroot -p

Supply your root password configured in step 1. Now, you are in MariaDB command prompt

Run the following commands (press Enter after each line):

Code: Select all

CREATE USER 'nextclouduser'@'localhost' IDENTIFIED BY 't0ps3cret';
CREATE DATABASE IF NOT EXISTS nextclouddb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON nextclouddb.* TO 'nextclouduser'@'localhost' IDENTIFIED BY 't0ps3cret';
FLUSH privileges;
quit

(NextCloud documentation reference: https://docs.nextcloud.com/server/14/ad ... ation.html)

Now you are back in FreeBSD shell.

3/ Converting Nextcloud from SQLite to MariaDB

While in shell, change your directory to where your Nextcloud install is (see Extensions - NextOwnCloud - Document Root if you don't know). E.g., /mnt/datavol/nextcloud/www

Code: Select all

cd /mnt/datavol/nextcloud/www

And run the db conversion:

Code: Select all

sudo -u www php occ db:convert-type --all-apps --password="t0ps3cret" mysql nextclouduser 127.0.0.1 nextclouddb

(NextCloud documentation reference: https://docs.nextcloud.com/server/14/ad ... rsion.html)

If things go well, you should see something like this in output:

Code: Select all

The process control (PCNTL) extensions are required in case you want to interrupt long running commands - see http://php.net/manual/en/book.pcntl.php
The current PHP memory limit is below the recommended value of 512MB.
Creating schema in new database
The following tables will not be converted:
oc_privatedata

If you want to continue, press Y and Enter.

Code: Select all

Continue with the conversion (y/n)? [n] y
oc_accounts
 6/6 [============================] 100%oc_activity
    0 [>---------------------------]oc_activity_mq
    0 [>---------------------------]oc_addressbookchanges
chunked query, 2 chunks
 1332/1332 [============================] 100%oc_addressbooks
 2/2 [============================] 100%oc_appconfig
 241/241 [============================] 100%oc_authtoken
 1/1 [============================] 100%oc_bruteforce_attempts
 1/1 [============================] 100%oc_calendar_invitations
    0 [>---------------------------]oc_calendar_resources
    0 [>---------------------------]oc_calendar_rooms
    0 [>---------------------------]oc_calendarchanges
    0 [>---------------------------]oc_calendarobjects
    0 [>---------------------------]oc_calendarobjects_props
    0 [>---------------------------]oc_calendars
 1/1 [============================] 100%oc_calendarsubscriptions
    0 [>---------------------------]oc_cards
 6/6 [============================] 100%oc_cards_properties
 25/25 [============================] 100%oc_comments
    0 [>---------------------------]oc_comments_read_markers
    0 [>---------------------------]oc_credentials
    0 [>---------------------------]oc_dav_shares
    0 [>---------------------------]oc_directlink
    0 [>---------------------------]oc_federated_reshares
    0 [>---------------------------]oc_file_locks
    0 [>---------------------------]oc_filecache
 204/204 [============================] 100%oc_files_trash
    0 [>---------------------------]oc_flow_checks
    0 [>---------------------------]oc_flow_operations
    0 [>---------------------------]oc_group_admin
    0 [>---------------------------]oc_group_folders
    0 [>---------------------------]oc_group_folders_groups
    0 [>---------------------------]oc_group_user
 1/1 [============================] 100%oc_groups
 1/1 [============================] 100%oc_jobs
 26/26 [============================] 100%oc_ldap_group_mapping
    0 [>---------------------------]oc_ldap_group_members
    0 [>---------------------------]oc_ldap_user_mapping
 5/5 [============================] 100%oc_migrations
Skipping migrations table because it was already filled by running the migrations
oc_mimetypes
 32/32 [============================] 100%oc_mounts
 1/1 [============================] 100%oc_notifications
    0 [>---------------------------]oc_notifications_pushtokens
    0 [>---------------------------]oc_oauth2_access_tokens
    0 [>---------------------------]oc_oauth2_clients
    0 [>---------------------------]oc_preferences
 68/68 [============================] 100%oc_properties
    0 [>---------------------------]oc_schedulingobjects
    0 [>---------------------------]oc_share
    0 [>---------------------------]oc_share_external
    0 [>---------------------------]oc_storages
 7/7 [============================] 100%oc_systemtag
    0 [>---------------------------]oc_systemtag_group
    0 [>---------------------------]oc_systemtag_object_mapping
    0 [>---------------------------]oc_trusted_servers
    0 [>---------------------------]oc_twofactor_backupcodes
    0 [>---------------------------]oc_twofactor_providers
 1/1 [============================] 100%oc_twofactor_totp_secrets
    0 [>---------------------------]oc_users
 1/1 [============================] 100%oc_users_external
    0 [>---------------------------]oc_vcategory
    0 [>---------------------------]oc_vcategory_to_object
    0 [>---------------------------]oc_whats_new
    0 [>---------------------------]
     

Two more to go, first make sure the DB is actually using the utf8mb4 encoding (the conversion routine does not seem to do this), and finally check things (and fix up if needed):

Code: Select all

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

(Nextcloud documentation reference: https://docs.nextcloud.com/server/14/ad ... te-support)


4. Finished.

Everything should be done and ready now, and you should be able to log on to your Nextcloud instance as before.

[ernie]

Thanks.
Today I haven't installed nextcloud and for a new installation as explained here:
viewtopic.php?t=11718

what we need to do for the use of mariaDB ?

Do I choose mariadb on the first log in page ?
Sure before mariadb must be activated on xigmanas.

BR

[doktornotor]

what we need to do for the use of mariaDB ?

Do I choose mariadb on the first log in page ?
Sure before mariadb must be activated on xigmanas.

For a fresh install of NC? Just step 1.

In the wizard, select the Storage and Database and fill in the details (root username, password you selected when running mysql_secure_installation, 127.0.0.1 as server and choose any DB name for Nextcloud DB.)

https://docs.nextcloud.com/server/14/ad ... y-location
https://docs.nextcloud.com/server/14/ad ... ase-choice

[Shperrung]

Hi, doctornotor
Thank you for your fix. Could you explain in details how to apply it?
Am I right:
1. I need to create the file "script.sh", for example
2. Open it in Tools - File Editor
3. Copy full text of your fix and past it into the file "script.sh". Save
3. System-advanced-Command Scripts: I type the path to "script.sh", set it as "post-initialisation". Restart system
?

Should be fairly easy:

0/ Make a backup

Extensions > NextOwnCloud > Backup button (on the Configuration tab).

1/ Initial MariaDB configuration:

Note: Beware of the XigmaNAS bugs noted here. The init script needs fixing as of XigmaNAS 11.2.0.4.6171.

[doktornotor]

Thank you for your fix. Could you explain in details how to apply it?

- SSH in, or use Tools > File Editor.
- Edit /etc/rc.d/mysqldb and replace the two broken lines (prefixed with - in the diff) with the fixed ones (prefixed with + in the diff). Note: Do not put the + in there, only the rest of the line.
- Run.

Code: Select all

/etc/rc.d/mysqldb restart

If you do not need phpMyAdmin, you do not need to edit anything, just set mysqldb_conf variable in System > Advanced > rc.conf; the value should be /var/db/mysql/my.cnf (and restart MariaDB as noted above, or reboot).

Fixed in XigmaNAS 11.2.0.4.6177, just upgrade.

[Shperrung]

Thank you for clarification.
Will that fix alive after system restart?
PS: It works! I corrected config and get MariDB web page.

[ms49434]

Thank you for clarification.
Will that fix alive after system restart?
PS: It works! I corrected config and get MariDB web page.

A bugfix release (6177) has just been published.

[doktornotor]

Thank you for clarification.
Will that fix alive after system restart?
PS: It works! I corrected config and get MariDB web page.

A bugfix release (6177) has just been published.

Thanks, adjusted my howto.

[Snufkin]

Does NextOwnCloud extension backup script need update for MariaDB?

[doktornotor]

Does NextOwnCloud extension backup script need update for MariaDB?

Well, not exactly... the current script simply won't touch MariaDB (unless you've placed it to a subdir of NC datadir, pretty bad idea IMO). Since rsync of a live MySQL DB seems like an obviously stupid idea, I'd say a generic script to backup the whole MariaDB would be much better (ideally included in XigmaNAS itself and available for scheduling via cron).

IOW, you need to take care of backing up the DB yourself ATM.

[Snufkin]

IOW, you need to take care of backing up the DB yourself ATM.

That's exactly what I was afraid of.

To my very limited knowledge about database backup this procedure must include special steps to stop the database, export it and restart. As far as I understand, Nextcloud maintenance mode is made specifically for this purpose.

Ok, SQLite will live a little longer in my setup.

[doktornotor]

To my very limited knowledge about database backup this procedure must include special steps to stop the database, export it and restart. As far as I understand, Nextcloud maintenance mode is made specifically for this purpose.

You can use phpMyAdmin to dump (export) the databases via GUI (put NC to maintenance before that sounds like a good idea).

[Shperrung]

Second day trying to setup NC and MariaDB. Previous combination NC with MySQL (from OBY) was easier.
Found the following:
Access to web page MariaDB: name root, password empty
localhost:3307 is the address for the first setup NC.
The last attempt to setup NC stopped at warning message about need to change access rights for data folder for 770

[doktornotor]

Access to web page MariaDB: name root, password empty

Leaving the default unsecured configuration is a terrible setup. Yuck.

You really SHOULD run

Code: Select all

mysql_secure_installation

from shell ASAP.

[Shperrung]

I meant that root and empty password are default for access to web gui. I changed password at first logon and made database/user required for NC using web gui.

[doktornotor]

I meant that root and empty password are default for access to web gui.

What web gui?

[raulfg3]

I meant that root and empty password are default for access to web gui.

What web gui?

phpMyAdmin

please read the readme: https://sourceforge.net/projects/xigman ... .0.4.6177/

[Shperrung]

URL /phpMyAdmin/index.php from Services-MariaDB page.
I made NC-MariaDB setup from SSH as you described above. My previous settings nextcloud data base in this GUI was wrong. Your method is right.
Do we need to restrict remote access to root?

[doktornotor]

I meant that root and empty password are default for access to web gui.

What web gui?

phpMyAdmin

please read the readme: https://sourceforge.net/projects/xigman ... .0.4.6177/

Ah, this... I'd say this whole thing should somehow be automated and a root password required in the GUI on enabling the service (using expect and some shell scripting, or whatever)... Some hints:
https://gist.github.com/Mins/4602864
https://gist.github.com/vdvm/24754bf1aee6fd85e1aa