Latest News:
2018-11-29: XigmaNAS 11.2.0.4.6229 - released!

We really need "Your" help on XigmaNAS https://translations.launchpad.net/xigmanas translations. Please help today!

Producing and hosting XigmaNAS cost money, please consider a donation to our project so we can continue to offer you the best.
We need your support! eg: PAYPAL

MariaDB and Nextcloud

Post Reply
User avatar
ernie
Forum Moderator
Forum Moderator
Posts: 1384
Joined: 26 Aug 2012 19:09
Location: France - Val d'Oise
Status: Offline

MariaDB and Nextcloud

#1

Post by ernie » 02 Nov 2018 11:00

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
NAS 1&2:
System: GA-6LXGH(BIOS: R01 04/30/2014) / 16 Go ECC
XigmaNAS 11.2.0.4.6229 embedded
NAS1: Xeon E3 1241@3.5GHz, 4HDD@2To/raidz2 (WD red), 3HDD@300Go/sas/raidz1 (Hitachi), 1SSD cache, Zlog on sas mirror
NAS2: G3220@3GHz, 3HDD@2To/raidz1 (Seagate), 1SSD cache, 1HDD@300Go/UFS
UPS: APC Back-UPS RS 900G
Case : Fractal Design XL R2

Extensions & services:
NAS1: OBI (Plex, extendedGUI, BTSync, zrep, rclone), nfs, UPS,
NAS2: OBI (extendedGUI, zrep (backup mode))

doktornotor
Advanced User
Advanced User
Posts: 155
Joined: 16 May 2017 00:22
Status: Offline

Re: MariaDB and Nextcloud

#2

Post by doktornotor » 03 Nov 2018 22:11

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. 8-)
Last edited by doktornotor on 05 Nov 2018 09:15, edited 3 times in total.

User avatar
ernie
Forum Moderator
Forum Moderator
Posts: 1384
Joined: 26 Aug 2012 19:09
Location: France - Val d'Oise
Status: Offline

Re: MariaDB and Nextcloud

#3

Post by ernie » 04 Nov 2018 13:37

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
NAS 1&2:
System: GA-6LXGH(BIOS: R01 04/30/2014) / 16 Go ECC
XigmaNAS 11.2.0.4.6229 embedded
NAS1: Xeon E3 1241@3.5GHz, 4HDD@2To/raidz2 (WD red), 3HDD@300Go/sas/raidz1 (Hitachi), 1SSD cache, Zlog on sas mirror
NAS2: G3220@3GHz, 3HDD@2To/raidz1 (Seagate), 1SSD cache, 1HDD@300Go/UFS
UPS: APC Back-UPS RS 900G
Case : Fractal Design XL R2

Extensions & services:
NAS1: OBI (Plex, extendedGUI, BTSync, zrep, rclone), nfs, UPS,
NAS2: OBI (extendedGUI, zrep (backup mode))

doktornotor
Advanced User
Advanced User
Posts: 155
Joined: 16 May 2017 00:22
Status: Offline

Re: MariaDB and Nextcloud

#4

Post by doktornotor » 04 Nov 2018 14:09

ernie wrote:
04 Nov 2018 13:37
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
Starter
Starter
Posts: 57
Joined: 04 Apr 2018 16:29
Status: Offline

Re: MariaDB and Nextcloud

#5

Post by Shperrung » 04 Nov 2018 20:46

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
?
doktornotor wrote:
03 Nov 2018 22:11
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. :!:
11.2.0.4 - Omnius (revision 6177)
ASRock J3710-ITX with cheap onboard LAN Realtek RTL8111GR; 16Gb RAM; WD 1Tbx2, WD 2Tb; UPS Powercom WOW500U.

doktornotor
Advanced User
Advanced User
Posts: 155
Joined: 16 May 2017 00:22
Status: Offline

Re: MariaDB and Nextcloud

#6

Post by doktornotor » 04 Nov 2018 20:56

Shperrung wrote:
04 Nov 2018 20:46
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.
Last edited by doktornotor on 05 Nov 2018 09:24, edited 3 times in total.

Shperrung
Starter
Starter
Posts: 57
Joined: 04 Apr 2018 16:29
Status: Offline

Re: MariaDB and Nextcloud

#7

Post by Shperrung » 04 Nov 2018 21:08

Thank you for clarification.
Will that fix alive after system restart?
PS: It works! I corrected config and get MariDB web page.
11.2.0.4 - Omnius (revision 6177)
ASRock J3710-ITX with cheap onboard LAN Realtek RTL8111GR; 16Gb RAM; WD 1Tbx2, WD 2Tb; UPS Powercom WOW500U.

User avatar
ms49434
Developer
Developer
Posts: 444
Joined: 03 Sep 2015 18:49
Location: Neuenkirchen, Germany - GMT+1
Status: Offline

Re: MariaDB and Nextcloud

#8

Post by ms49434 » 05 Nov 2018 00:09

Shperrung wrote:
04 Nov 2018 21:08
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.
1) XigmaNAS 11.2.0.4 amd64-embedded on a Dell T20 running in a VM on ESXi 6.7, 22GB out of 32GB ECC RAM, LSI 9300-8i IT mode in passthrough mode. Pool 1: 2x HGST 10TB, mirrored, SLOG: Samsung 850 Pro, L2ARC: Samsung 850 Pro, Pool 2: 1x Samsung 860 EVO 1TB , services: Samba AD, CIFS/SMB, ftp, ctld, rsync, syncthing, zfs snapshots.
2) XigmaNAS 11.2.0.4 amd64-embedded on a Dell T20 running in a VM on ESXi 6.7, 8GB out of 32GB ECC RAM, IBM M1215 crossflashed, IT mode, passthrough mode, 1x HGST 10TB , services: rsync.

doktornotor
Advanced User
Advanced User
Posts: 155
Joined: 16 May 2017 00:22
Status: Offline

Re: MariaDB and Nextcloud

#9

Post by doktornotor » 05 Nov 2018 09:16

ms49434 wrote:
05 Nov 2018 00:09
Shperrung wrote:
04 Nov 2018 21:08
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. 8-)

User avatar
Snufkin
Advanced User
Advanced User
Posts: 238
Joined: 01 Jul 2012 11:27
Location: Etc/GMT-3 (BSD style)
Status: Offline

Re: MariaDB and Nextcloud

#10

Post by Snufkin » 05 Nov 2018 09:34

Does NextOwnCloud extension backup script need update for MariaDB?
XNAS 11.2.0.4.6229 embedded, ASUS P5B-E, Intel DC E6600, 4 GB DDR2, 2 x HGST HDN726040ALE614, 2 x WDC WD5000AAKS, Ippon Back Power Pro 400

doktornotor
Advanced User
Advanced User
Posts: 155
Joined: 16 May 2017 00:22
Status: Offline

Re: MariaDB and Nextcloud

#11

Post by doktornotor » 05 Nov 2018 10:04

Snufkin wrote:
05 Nov 2018 09:34
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.

User avatar
Snufkin
Advanced User
Advanced User
Posts: 238
Joined: 01 Jul 2012 11:27
Location: Etc/GMT-3 (BSD style)
Status: Offline

Re: MariaDB and Nextcloud

#12

Post by Snufkin » 05 Nov 2018 10:27

doktornotor wrote:
05 Nov 2018 10:04
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. :arrow:
XNAS 11.2.0.4.6229 embedded, ASUS P5B-E, Intel DC E6600, 4 GB DDR2, 2 x HGST HDN726040ALE614, 2 x WDC WD5000AAKS, Ippon Back Power Pro 400

doktornotor
Advanced User
Advanced User
Posts: 155
Joined: 16 May 2017 00:22
Status: Offline

Re: MariaDB and Nextcloud

#13

Post by doktornotor » 05 Nov 2018 10:29

Snufkin wrote:
05 Nov 2018 10:27
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
Starter
Starter
Posts: 57
Joined: 04 Apr 2018 16:29
Status: Offline

Re: MariaDB and Nextcloud

#14

Post by Shperrung » 06 Nov 2018 11:24

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 :(
11.2.0.4 - Omnius (revision 6177)
ASRock J3710-ITX with cheap onboard LAN Realtek RTL8111GR; 16Gb RAM; WD 1Tbx2, WD 2Tb; UPS Powercom WOW500U.

doktornotor
Advanced User
Advanced User
Posts: 155
Joined: 16 May 2017 00:22
Status: Offline

Re: MariaDB and Nextcloud

#15

Post by doktornotor » 06 Nov 2018 17:22

Shperrung wrote:
06 Nov 2018 11:24
Access to web page MariaDB: name root, password empty
Leaving the default unsecured configuration is a terrible setup. Yuck. :shock: :cry:

You really SHOULD run

Code: Select all

mysql_secure_installation
from shell ASAP.

Shperrung
Starter
Starter
Posts: 57
Joined: 04 Apr 2018 16:29
Status: Offline

Re: MariaDB and Nextcloud

#16

Post by Shperrung » 06 Nov 2018 19:04

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.
11.2.0.4 - Omnius (revision 6177)
ASRock J3710-ITX with cheap onboard LAN Realtek RTL8111GR; 16Gb RAM; WD 1Tbx2, WD 2Tb; UPS Powercom WOW500U.

doktornotor
Advanced User
Advanced User
Posts: 155
Joined: 16 May 2017 00:22
Status: Offline

Re: MariaDB and Nextcloud

#17

Post by doktornotor » 06 Nov 2018 19:29

Shperrung wrote:
06 Nov 2018 19:04
I meant that root and empty password are default for access to web gui.
What web gui? :?: :?

User avatar
raulfg3
Site Admin
Site Admin
Posts: 4627
Joined: 22 Jun 2012 22:13
Location: Madrid (ESPAÑA)
Contact:
Status: Offline

Re: MariaDB and Nextcloud

#18

Post by raulfg3 » 06 Nov 2018 19:54

doktornotor wrote:
06 Nov 2018 19:29
Shperrung wrote:
06 Nov 2018 19:04
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/
XigmaNAS 11.2.0.4 - Omnius (revision 6195)+OBI on SUPERMICRO X8SIL-F 8GB of ECC RAM, 12x3TB disk in 3 vdev in RaidZ1 = 32TB Raw size only 22TB usable

Wiki
Last changes
Old Wiki

Shperrung
Starter
Starter
Posts: 57
Joined: 04 Apr 2018 16:29
Status: Offline

Re: MariaDB and Nextcloud

#19

Post by Shperrung » 06 Nov 2018 20:06

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?
You do not have the required permissions to view the files attached to this post.
11.2.0.4 - Omnius (revision 6177)
ASRock J3710-ITX with cheap onboard LAN Realtek RTL8111GR; 16Gb RAM; WD 1Tbx2, WD 2Tb; UPS Powercom WOW500U.

doktornotor
Advanced User
Advanced User
Posts: 155
Joined: 16 May 2017 00:22
Status: Offline

Re: MariaDB and Nextcloud

#20

Post by doktornotor » 06 Nov 2018 20:40

raulfg3 wrote:
06 Nov 2018 19:54
doktornotor wrote:
06 Nov 2018 19:29
Shperrung wrote:
06 Nov 2018 19:04
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

:?: :idea:

Post Reply

Return to “MariaDB”