Help needed backing up databases planning for a QTS upgrade to 4.3.x

[raidforsaving]

I need to solve which db app is installed and active for a couple of apps of interest. And then I need to backup the databases, and plan for whatever is needed to upgrade QTS from 4.2.4 to 4.3.x.

It seems like there is a DB app installed by default in 4.3.x which is modern (security patched) and I MAY switch to that if it continues to be patched OR if I have to due to the old DB app being broken.

Apps:

• NextCloud
• PHPMyAdmin

Help me answer/solve these questions/issues please:

1. I believe that I installed QMariaDB over 2 years ago to be able to work with OwnCloud (which I've since uninstalled but the db remains). I now use NextCloud.
   I'm a bit confused if any of my NAS upgrades in the past 2 years have reintroduced/altered the database engine installed.
   It appears that QMariaDB is used for all db's right now. Please help me confirm.
2. Once that is known, I will backup the databases (I have a script for that with QMariaDB that seems to work for 1 database at a time).
3. Finally, I need to know what to do during an upgrade to 4.3.x. I will address the NextCloud config for repointing the DB as needed.
   1. Will QMariaDB still work?
   2. Do I need to remove it?
4. Do I need to restore my database(s) into some new DB engine?

Here is some info to help identify where things are at:

QNAP QTS Version 4.2.4
NAS: TS-651

• In the AppCenter, QMariaDB 5.5.44.002 appears
• In the SQL Server config it says QMariaDB 5.5.44.002 is used and is enabled on port 3306.
• In phpMyAdmin, there are databases for information_schema, mysql, nc, owncloud, performance_schema and test
  (nc is for Nextcloud, I think test was me manually testing creating a db in phpmyadmin)
• in the phpmyadmin User Accounts section, it shows user accoutns related to both owncloud and nextcloud, as well as root.

Code: Select all

[/share/CACHEDEV1_DATA] # ls -a 
./                .QKVM/            .php_session_sys/ .tmp/             Storage/
../               .antivirus/       .qbox_log_queue/  .torrent/         Web/
.@backup_config/  .appDB/           .qpkg/            .versioning/      aquota.user
.@backup_qbox/    .idmap/           .rnd              Backups/          homes/
.@mysql/          .ldapdb/          .samba/           Download/         htdocs/
.@qmariadb/       .locks/           .spool/           Multimedia/       lost+found/
.@qsync/          .log/             .swap/            Public/
.@twonkymedia.db/ .php_session/     .system/          Recordings/

Notice that both .@mysql and .@qmariadb exist.

qMariaDB seems to be active and in use. (I have no clue though, so please help me)

Code: Select all

[/share/CACHEDEV1_DATA] # ls -lta .@qmariadb/data
-rw-r-----    1 admin    administ   5242880 Jul 15 13:23 ib_logfile0
-rw-rw----    1 admin    administ 139359198 Jul 15 13:23 mysql-bin.000048
-rw-r-----    1 admin    administ  35651584 Jul 15 13:22 ibdata1
-rw-r-----    1 admin    administ   5242880 Jul 15 08:58 ib_logfile1
drwxrwxrwx    7 admin    administ      4096 May 27 12:20 ./
-rw-r-----    1 admin    administ    134617 May 27 12:20 server.err
-rw-rw----    1 admin    administ       912 May 27 12:20 mysql-bin.index
-rw-rw----    1 admin    administ     16384 May 27 12:20 aria_log.00000001
-rw-rw----    1 admin    administ        52 May 27 12:20 aria_log_control
-rw-rw----    1 admin    administ       264 May 27 12:20 mysql-bin.000047
-rw-rw----    1 admin    administ       264 May 27 12:12 mysql-bin.000046
-rw-rw----    1 admin    administ       264 May 27 12:09 mysql-bin.000045
-rw-rw----    1 admin    administ       264 May 27 12:08 mysql-bin.000044
-rw-rw----    1 admin    administ 137290957 May 26 10:00 mysql-bin.000043
-rw-rw----    1 admin    administ       563 Apr  9 18:24 mysql-bin.000042
-rw-rw----    1 admin    administ       563 Apr  9 18:23 mysql-bin.000041
-rw-rw----    1 admin    administ       264 Apr  9 18:08 mysql-bin.000040
-rw-rw----    1 admin    administ  34517646 Apr  9 17:53 mysql-bin.000039
drwx------    2 admin    administ      4096 Dec 15  2016 nc/
-rw-rw----    1 admin    administ       862 Nov 19  2016 mysql-bin.000038
-rw-rw----    1 admin    administ       563 Nov 19  2016 mysql-bin.000037
-rw-rw----    1 admin    administ       264 Nov 19  2016 mysql-bin.000036
-rw-rw----    1 admin    administ       563 Nov 19  2016 mysql-bin.000035
-rw-rw----    1 admin    administ   2070793 Nov 18  2016 mysql-bin.000034
-rw-rw----    1 admin    administ       264 Nov  8  2016 mysql-bin.000033
-rw-rw----    1 admin    administ       563 Nov  8  2016 mysql-bin.000032
-rw-rw----    1 admin    administ     16438 Nov  8  2016 mysql-bin.000031
-rw-rw----    1 admin    administ       563 Nov  8  2016 mysql-bin.000030
-rw-rw----    1 admin    administ       264 Nov  8  2016 mysql-bin.000029
-rw-rw----    1 admin    administ   1795624 Nov  7  2016 mysql-bin.000028
drwx------    2 admin    administ      4096 Oct 29  2016 owncloud/
-rw-rw----    1 admin    administ      1345 Oct 27  2016 mysql-bin.000027
-rw-rw----    1 admin    administ       264 Oct 27  2016 mysql-bin.000026
-rw-rw----    1 admin    administ       264 Oct 27  2016 mysql-bin.000025
-rw-rw----    1 admin    administ       264 Oct 27  2016 mysql-bin.000024
-rw-rw----    1 admin    administ   7811158 Oct 27  2016 mysql-bin.000023
-rw-rw----    1 admin    administ       264 Aug  7  2016 mysql-bin.000022
-rw-rw----    1 admin    administ       563 Aug  7  2016 mysql-bin.000021
-rw-rw----    1 admin    administ  15061898 Aug  6  2016 mysql-bin.000020
-rw-rw----    1 admin    administ       264 Jul 18  2016 mysql-bin.000019
-rw-rw----    1 admin    administ       563 Jul 18  2016 mysql-bin.000018
-rw-rw----    1 admin    administ     10868 Jul 17  2016 mysql-bin.000017
-rw-rw----    1 admin    administ       264 Jul 17  2016 mysql-bin.000016
-rw-rw----    1 admin    administ       264 Jul 16  2016 mysql-bin.000015
-rw-rw----    1 admin    administ   7934876 Jul 12  2016 mysql-bin.000014
-rw-rw----    1 admin    administ  15685178 Apr 21  2016 mysql-bin.000013
-rw-rw----    1 admin    administ       264 Sep 27  2015 mysql-bin.000012
-rw-rw----    1 admin    administ       264 Sep 25  2015 mysql-bin.000011
-rw-rw----    1 admin    administ   1528435 Sep 25  2015 mysql-bin.000010
-rw-rw----    1 admin    administ       288 Sep 19  2015 mysql-bin.000009
-rw-rw----    1 admin    administ   1841606 Sep 19  2015 mysql-bin.000008
-rw-rw----    1 admin    administ       288 Sep 19  2015 mysql-bin.000007
-rw-rw----    1 admin    administ       264 Sep 19  2015 mysql-bin.000006
-rw-rw----    1 admin    administ    120863 Sep 19  2015 mysql-bin.000005
-rw-rw----    1 admin    administ    303700 Sep 12  2015 mysql-bin.000004
-rw-rw----    1 admin    administ       264 Sep 12  2015 mysql-bin.000003
-rw-rw----    1 admin    administ     33046 Aug 19  2015 mysql-bin.000002
-rw-rw----    1 admin    administ       264 Aug 19  2015 mysql-bin.000001
drwx------    2 admin    administ      4096 Aug 19  2015 mysql/
drwx------    2 admin    administ      4096 Aug 19  2015 performance_schema/
-rw-r--r--    1 admin    administ      3439 Aug 19  2015 upgrade.log
drwx------    2 admin    administ      4096 Aug 19  2015 test/
drwxr-xr-x    4 admin    administ      4096 Aug 19  2015 ../

This dir listing seems to show that owncloud had been installed here, but nothing else. Which implies to me that it can be ignored/deleted. Also, if this could conflict with the 4.3.x upgrade, please let me know.

Code: Select all

[/share/CACHEDEV1_DATA] # ls -alt .@mysql/
drwxrwxrwx   36 admin    administ      4096 Jun 13 22:56 ../
-rw-rw----    1 admin    administ     52892 Aug 19  2015 server.err
-rw-rw----    1 admin    administ   5242880 Aug 19  2015 ib_logfile0
-rw-rw----    1 admin    administ   5242880 Aug 19  2015 ib_logfile1
-rw-rw----    1 admin    administ  18874368 Aug 19  2015 ibdata1
drwxrwxrwx    5 admin    administ      4096 Aug 17  2015 ./
drwx------    2 admin    administ      4096 Apr 19  2015 owncloud/
drwx------    2 admin    administ      4096 Sep  4  2014 mysql/
drwx------    2 admin    administ      4096 Sep  4  2014 test/

[schumaku]

Hi,

Can't get rid of the impression I'v answered very similar questions before...

I believe that I installed QMariaDB over 2 years ago to be able to work with OwnCloud (which I've since uninstalled but the db remains). I now use NextCloud.
I'm a bit confused if any of my NAS upgrades in the past 2 years have reintroduced/altered the database engine installed.
It appears that QMariaDB is used for all db's right now. Please help me confirm.

Leaving the Marvell Kirkwood NAS alone, the user facing SQL engine in place is MariaDB. Note there is a second instance for the NAS internal usage, ie. for the Media Library database.

Once that is known, I will backup the databases (I have a script for that with QMariaDB that seems to work for 1 database at a time).

Export all databases. Note MySQL and MariaDB is binary compatible. Ideally, use the MariaDB tools to dump the database content.

Finally, I need to know what to do during an upgrade to 4.3.x. I will address the NextCloud config for repointing the DB as needed.
Will QMariaDB still work?
Do I need to remove it?

Remove the QMariaDB package from the App Center once updated - it's no longer required (and will cause issues). MariaDB will be standard.

Notice that both .@mysql and .@qmariadb exist.

The first one wasn't used or touched for years. The second one will be retained and carried forward.

[raidforsaving]

Thanks for the post. I'm just being paranoid about my knowledge and trying to be thorough in verification. Measure twice, cut once. I almost forgot about the mariadb/qmariadb stuff. I plan on trying an upgrade this weekend. As far as other posts. I couldn't be sure that I fully understood the engine changes that have happened. But backups are crucial to avoiding issues. So I'm in a good place to try.

[raidforsaving]

I have performed an upgrade to 4.3.3.0229 Build 20170624 without any issue. All the databases were retained without issue (so far).

Process followed:
1. Backup all databases using mysqldump
2. Backup nextcloud using an old owncloud backup script hacked for nextcloud. (app dir backed up)
3. Backup Qapache config files of value manually
4. Perform upgrade of QTS
5. Upgrade all apps to QTS 4.3.3 compatible versions
6. Uninstall QMariaDB and reboot NAS.
7. Uninstall Qapache and Nextcloud (x86 versions)
8. Install Qapache and Nextcloud (x86-64 versions)
9. Update Qapache and NextCloud configs from backups by comparing each file of interest for changes required.