Proper backups - fix several issues

[tio-trom]

The Problems:

Big database equals issues.

If the Friendica instance is very active/big then one cannot backup the database since by the time the database is created it will be modified. So it will fail like here https://paste.yunohost.org/raw/icukubaxiz

2023-09-11 00:23:05,769: WARNING - mysqldump: Error 1412: Table definition has changed, please retry transaction when dumping table `post-delivery` at row: 0`.

Currently we tried to put the instance into Maintenance before backing up the databse, see https://github.com/YunoHost-Apps/friendica_ynh/blob/master/scripts/backup (cd $install_dir && ynh_exec_as "$app" php$phpversion bin/console maintenance 1) - but for one it does not work, it won't trigger the maintenance. And second, it is not a good practice to put an instance into maintenance every day, for possibly hours.

We need to backup files too!

Friendica allows admins to store the media files in a folder rather than the database, and this is crucial for big instances. The setting is changed from here:

And it is written in the database as

MariaDB [friendica]> select * from config where cat like '%storage%';
+------+---------+-----------------+-------------------------------+
| id   | cat     | k               | v                             |
+------+---------+-----------------+-------------------------------+
| 6778 | storage | filesystem_path | s:21:"/path/to/storage";      |
| 6796 | storage | name            | s:10:"Filesystem";            |
+------+---------+-----------------+-------------------------------+

Solution:

We need to backup the database properly plus the files.

First, we should use mariadb backups that are incremental so no need to put Friendica in maintenance.

$ mariabackup --backup \
   --target-dir=/var/mariadb/backup/ \
   --user=mariabackup --password=mypassword

More info here https://mariadb.com/kb/en/full-backup-and-restore-with-mariabackup/

Second, we need to define somewhere the file storage path to then include in the backup script.

Any help with this please? I will do my best to test stuff and help, but my knowledge is limited. We desperately need a proper backup solution for friendica!