Own Dropbox on ownCloud. Backup data from smartphone. Server part

Setting up MariaDB. We launch the “Installation Wizard”. I gave the following answers to his questions:

evgeniy@test:~$ sudo mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password:<пароль для root>
Re-enter new password:<пароль для root>
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] n
 ... skipping.

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Checking the version, so what is access to the administrator console

evgeniy@test:~$ mysqladmin -u root -p version
Enter password:
mysqladmin  Ver 9.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version          10.3.34-MariaDB-0ubuntu0.20.04.1
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 1 hour 34 min 35 sec

Threads: 6  Questions: 74  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 26  Queries per second avg: 0.013

What could be the problem. This command gave me an access error. If you run it with sudo, then the command worked correctly. A similar story was with the team "mysql -u root"
This can be fixed with the following set of commands:

evgeniy@test:~$ sudo mysql -u root
[mysql] use mysql;
[mysql] update user set plugin='' where User="root";
[mysql] flush privileges;
[mysql] \q

The command from the instructions for ownCloud

evgeniy@test:~$ sudo sed -i “/\[mysqld\]/atransaction-isolation=READ-COMMITTED\nperformance_schema=on” /etc/mysql/mariadb.conf.d/50-server.cnf

Restart the database server and check its status:

evgeniy@test:~$ sudo systemctl restart mariadb
evgeniy@test:~$ sudo systemctl status mariadb
● mariadb.service - MariaDB 10.3.34 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Sun 2022-06-05 00:03:15 MSK; 31s ago
       Docs: man:mysqld(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 1253 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 1264 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 1266 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   &>
    Process: 1350 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 1352 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 1319 (mysqld)
     Status: "Taking your SQL requests now..."
      Tasks: 31 (limit: 1103)
     Memory: 168.2M
     CGroup: /system.slice/mariadb.service
             └─1319 /usr/sbin/mysqld

Jun 05 00:03:15 test.owncloud.local systemd[1]: Started MariaDB 10.3.34 database server.
Jun 05 00:03:15 test.owncloud.local /etc/mysql/debian-start[1354]: Upgrading MySQL tables if necessary.
Jun 05 00:03:15 test.owncloud.local /etc/mysql/debian-start[1357]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Jun 05 00:03:15 test.owncloud.local /etc/mysql/debian-start[1357]: Looking for 'mysql' as: /usr/bin/mysql
Jun 05 00:03:15 test.owncloud.local /etc/mysql/debian-start[1357]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Jun 05 00:03:15 test.owncloud.local /etc/mysql/debian-start[1357]: This installation of MariaDB is already upgraded to 10.3.34-MariaDB.
Jun 05 00:03:15 test.owncloud.local /etc/mysql/debian-start[1357]: There is no need to run mysql_upgrade again for 10.3.34-MariaDB.
Jun 05 00:03:15 test.owncloud.local /etc/mysql/debian-start[1357]: You can use --force if you still want to run mysql_upgrade
Jun 05 00:03:15 test.owncloud.local /etc/mysql/debian-start[1365]: Checking for insecure root accounts.
Jun 05 00:03:15 test.owncloud.local /etc/mysql/debian-start[1369]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables
lines 1-27/27 (END)

Got it sorted out. Move on

Data directory transfer. By default, data is stored in the “/var/www/owncloud/data/“. It is more correct to store data on a separate drive, perhaps a network or RAID array. Further, it is assumed that the target data volume is mounted along the path “/mnt/owncloud“. Instructionhow to do it on the official website. But it has inaccuracies with the paths. At first, I did according to the instructions, and it didn’t work for me – the files were not available. I created a new user and his file directory was in the correct path and accessible. Comparing the records in the tables showed where the error is.
We put ownCloud into service mode and stop Apache:

evgeniy@test:~$ cd /var/www/owncloud
evgeniy@test:/var/www/owncloud$ sudo -u www-data php occ maintenance:mode --on
Maintenance mode enabled
Please also consider to stop the web server on all ownCloud instances

evgeniy@test:/var/www/owncloud$ sudo systemctl stop apache2.service

Create a directory if it hasn’t already been created and move the files into it:

evgeniy@test:/var/www/owncloud$ sudo mkdir /mnt/owncloud
evgeniy@test:/var/www/owncloud$ sudo chown www-data /mnt/owncloud
evgeniy@test:/var/www/owncloud$ sudo rsync -avz /var/www/owncloud/data /mnt/owncloud
sending incremental file list
data/
data/.htaccess
data/.ocdata
data/htaccesstest.txt
data/index.html
data/owncloud.log
data/admin/
data/admin/cache/
data/admin/files/
data/admin/files/ownCloud Manual.pdf
data/admin/files/Documents/
data/admin/files/Documents/Example.odt
data/admin/files/Photos/
data/admin/files/Photos/Lake-Constance.jpg
data/admin/files/Photos/Portugal.jpg
data/admin/files/Photos/Teotihuacan.jpg
data/avatars/
data/files_external/
data/files_external/rootcerts.crt

sent 6,985,083 bytes  received 277 bytes  13,970,720.00 bytes/sec
total size is 8,178,313  speedup is 1.17

Database manipulation. We go to the database server as a client:

evgeniy@test:/var/www/owncloud$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 45
Server version: 10.3.34-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Choosing our base:

MariaDB [(none)]> use owncloud
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

We look at the list of users and change the path for each. If several users have already been created, then use the field id for their identification. If you do this procedure immediately after installing ownCloud, then there will be only one user − admin.

MariaDB [owncloud]> select * from oc_accounts;
+----+-------+---------+---------------+--------------+-------+------------+------------------+------------------------------+-------+
| id | email | user_id | lower_user_id | display_name | quota | last_login | backend          | home                         | state |
+----+-------+---------+---------------+--------------+-------+------------+------------------+------------------------------+-------+
|  1 | NULL  | admin   | admin         | admin        | NULL  | 1654386521 | OC\User\Database | /var/www/owncloud/data/admin |     1 |
+----+-------+---------+---------------+--------------+-------+------------+------------------+------------------------------+-------+
1 row in set (0.000 sec)

MariaDB [owncloud]> UPDATE oc_accounts SET home="/mnt/owncloud/data/admin" WHERE id=1;
Query OK, 1 row affected (0.033 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Now we need to make a selection by tasks and check if any task has an old path. I didn’t find any of those.

MariaDB [owncloud]> select * from oc_jobs where class="OC\Log\Rotate";
Empty set (0.000 sec)

MariaDB [owncloud]> select * from oc_jobs;
+----+----------------------------------------------------+----------+------------+--------------+-------------+--------------------+
| id | class                                              | argument | last_run   | last_checked | reserved_at | execution_duration |
+----+----------------------------------------------------+----------+------------+--------------+-------------+--------------------+
|  1 | OCA\Files\BackgroundJob\ScanFiles                  | null     | 1654386430 |   1654386430 |           0 |                  0 |
|  2 | OCA\Files\BackgroundJob\DeleteOrphanedItems        | null     | 1654386437 |   1654386437 |           0 |                  0 |
|  3 | OCA\Files\BackgroundJob\CleanupFileLocks           | null     | 1654386444 |   1654386444 |           0 |                  0 |
|  4 | OCA\Files\BackgroundJob\CleanupPersistentFileLocks | null     | 1654386477 |   1654386477 |           0 |                  0 |
|  5 | OCA\DAV\CardDAV\SyncJob                            | null     | 1654386505 |   1654386505 |           0 |                  0 |
|  6 | OCA\DAV\BackgroundJob\CleanProperties              | null     | 1654386522 |   1654386521 |           0 |                  0 |
|  7 | OCA\Activity\BackgroundJob\EmailNotification       | null     | 1654386739 |   1654386739 |           0 |                  0 |
|  8 | OCA\Activity\BackgroundJob\ExpireActivities        | null     | 1654386749 |   1654386749 |           0 |                  0 |
|  9 | OCA\Federation\SyncJob                             | null     | 1654386752 |   1654386752 |           0 |                  0 |
| 10 | OCA\Files_Sharing\DeleteOrphanedSharesJob          | null     | 1654386761 |   1654386761 |           0 |                  0 |
| 11 | OCA\Files_Sharing\ExpireSharesJob                  | null     | 1654386770 |   1654386770 |           0 |                  0 |
| 12 | OCA\Files_Sharing\External\ScanExternalSharesJob   | null     | 1654386775 |   1654386775 |           0 |                  0 |
| 13 | OCA\Files_Trashbin\BackgroundJob\ExpireTrash       | null     | 1654386795 |   1654386795 |           0 |                  0 |
| 14 | OCA\Files_Versions\BackgroundJob\ExpireVersions    | null     | 1654386833 |   1654386833 |           0 |                  0 |
| 15 | OCA\Market\CheckUpdateBackgroundJob                | null     |          0 |   1654386024 |           0 |                 -1 |
| 16 | OCA\UpdateNotification\Notification\BackgroundJob  | null     |          0 |   1654386025 |           0 |                 -1 |
| 17 | \OC\Authentication\Token\DefaultTokenCleanupJob    | null     |          0 |   1654386026 |           0 |                 -1 |
+----+----------------------------------------------------+----------+------------+--------------+-------------+--------------------+
17 rows in set (0.000 sec)

If there is a task, then run the query:

MariaDB [owncloud]> UPDATE oc_jobs SET argument = REPLACE(argument, '\\/var\\/www\\/owncloud\\/data\\/', '\\/mnt\\/owncloud/data\\/')   WHERE id = <id of the incorrect record>;

where in substitute the id with the record that has the wrong path.
Exiting the database:

MariaDB [owncloud]> \q
Bye

Checking paths in applications. If some application has an old path, then change it (command below). In my case, there was no such application.

evgeniy@test:/var/www/owncloud$ sudo -u www-data php ./occ config:list
{
    "system": {
        "instanceid": "oc8mwxq3rfxl",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "localhost",
            "172.16.1.213",
            "test.owncloud.local"
        ],
        "datadirectory": "\/mnt\/owncloud\/data",
        "overwrite.cli.url": "http:\/\/localhost",
        "dbtype": "mysql",
        "version": "10.10.0.3",
        "dbname": "owncloud",
        "dbhost": "localhost",
        "dbtableprefix": "oc_",
        "mysql.utf8mb4": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "allow_user_to_change_mail_address": "",
        "logtimezone": "UTC",
        "apps_paths": [
            {
                "path": "\/var\/www\/owncloud\/apps",
                "url": "\/apps",
                "writable": false
            },
            {
                "path": "\/var\/www\/owncloud\/apps-external",
                "url": "\/apps-external",
                "writable": true
            }
        ],
        "installed": true,
        "maintenance": false
    },
    "apps": {
        "activity": {
            "enabled": "yes",
            "installed_version": "2.7.0",
            "types": "filesystem"
        },
        "backgroundjob": {
            "lastjob": "14"
        },
        "comments": {
            "enabled": "yes",
            "installed_version": "0.3.0",
            "types": "logging,dav"
        },
        "configreport": {
            "enabled": "yes",
            "installed_version": "0.2.1",
            "types": "filesystem"
        },
........
        "updatenotification": {
            "enabled": "yes",
            "installed_version": "0.2.1",
            "types": ""
        }
    }
}

Replacement command (not tested):

evgeniy@test:/var/www/owncloud$ sudo -u www-data php ./occ config:app:set --value /mnt/owncloud/data <название приложения> <имя параметра, содержащего путь к каталогу>

And finally, we fix the path in the settings file, the parameter data directory:

evgeniy@test:/var/www/owncloud$ sudo vim /var/www/owncloud/config/config.php
<?php
$CONFIG = array (
  'instanceid' => 'oc8mwxq3rfxl',
  'passwordsalt' => 'ahNk4VxR1R5DeZyJ9ly8wsdqCXo1z3',
  'secret' => 'b8xm+wxPuQFyuW0syudVGGsKQwgfnTFoMEBm6cZA00Wm2zG0',
  'trusted_domains' =>
  array (
    0 => 'localhost',
    1 => '172.16.1.213',
    2 => 'test.owncloud.local',
  ),
  'datadirectory' => '/mnt/owncloud/data',
  'overwrite.cli.url' => 'http://localhost',
  'dbtype' => 'mysql',
  'version' => '10.10.0.3',
  'dbname' => 'owncloud',
  'dbhost' => 'localhost',
  'dbtableprefix' => 'oc_',
  'mysql.utf8mb4' => true,
  'dbuser' => 'oc_admin',
  'dbpassword' => 'WeBkvHU7WqHPQ/7Sxm0Oq4rVs1vAEh',
  'allow_user_to_change_mail_address' => '',
  'logtimezone' => 'UTC',
  'apps_paths' =>
  array (
    0 =>
    array (
      'path' => '/var/www/owncloud/apps',
      'url' => '/apps',
      'writable' => false,
    ),
    1 =>
    array (
      'path' => '/var/www/owncloud/apps-external',
      'url' => '/apps-external',
      'writable' => true,
    ),
  ),
  'installed' => true,
  'maintenance' => false,
);

We return ownCloud to combat mode and start Apache:

evgeniy@test:/var/www/owncloud$ sudo -u www-data php occ maintenance:mode --off
ownCloud is in maintenance mode - no app have been loaded

Maintenance mode disabled

evgeniy@test:/var/www/owncloud$ sudo systemctl start apache2.service

114