Nextcloud-snap: Add mysqldump command

[thomascobb]

In the documentation it suggests mysqldump as the way to backup the database:
https://docs.nextcloud.com/server/9/admin_manual/maintenance/backup.html

However, this snap only exposes the mysql command. I'm not sure if it is possible to backup a mysql database using just the mysql command, but if not then the mysqldump command would be needed. Knowing nothing about mysql I don't know the best way to backup the database, but it strikes me as an important thing to do! I'm guessing that if mysqldump is installed as part of mysql it is just a case of exposing it in the YAML file, and I'd be happy to have a go at that myself...

[kyrofa]

Indeed, good call! This will be a YAML-only change. Feel free to take a crack at it if you like! Let me know if you run out of time, though.

[thomascobb]

I'll have a go at this in the next few weeks... I've got a question though, would this be a sufficient backup plan:

sudo snap nextcloud disable
cp -rf /var/snap/nextcloud/common /path/to/backup/common
cp -rf /var/snap/nextcloud/current /path/to/backup/current
sudo snap nextcloud enable

If so then I guess mysqldump isn't needed...

[oparoz]

https://docs.nextcloud.com/server/10/admin_manual/maintenance/backup.html

And use a folder in /media as a destination since the snap is confined.

[thomascobb]

I didn't express that very well, what I meant to say was: I will have a go at exposing the mysqldump command so that the command in the documentation works.

However, there are some recommendations here:
https://docs.nextcloud.com/server/10/admin_manual/installation/deployment_recommendations.html?highlight=btrfs#small-workgroups-or-departments

It says to use btrfs snapshots for the database for zero downtime. Now, if I don't care about zero downtime then I can just disable the snap, copy the whole snap current and common directories (using rsync and hardlinks to minimize disk usage), and then enable the snap again. In my mind that would work in the same way as btrfs snapshots, but then I wonder why I haven't seen this documented anywhere?

[tfalencar]

I'm also looking for this. Is it possible to make the backup after an installation that didn't have the YAML change (i.e., does not have the mysqldump tool exposed), perhaps via an upgrade of the snap?

[kyrofa]

I'm also looking for this. Is it possible to make the backup after an installation that didn't have the YAML change (i.e., does not have the mysqldump tool exposed), perhaps via an upgrade of the snap?

Yes, the snap will update automatically, so any changes made here will be available to you soon!

[kyrofa]

Now, if I don't care about zero downtime then I can just disable the snap, copy the whole snap current and common directories (using rsync and hardlinks to minimize disk usage), and then enable the snap again.

Yes, that _should_ work, and should work in reverse as well, but I've not tested it. It's also important to note that specific revisions of the snap may make assumption about how $SNAP_DATA is laid out, so I'm not sure I can wholeheartedly recommend this approach for long-term backups (migrations would work fine). For that we should actually write export/import apps.

[thomascobb]

I've been trying out making /var/snap a different mount point on a btrfs file system, which seemed to be a reasonable approach, until I started getting reams of apparmor errors. I'll investigate further...

My ideal approach would use either snapshots or hardlinks to save space. I'm not sure how this would work inside the snap as you can't make any assumptions about the filesystem it is on. Tarballs would get large pretty quickly.

Are there any plans for making a backup system for the nextcloud box?

[kyrofa]

Yes, that should work, and should work in reverse as well, but I've not tested it.

I've tested it now, and it works as expected.

My ideal approach would use either snapshots or hardlinks to save space. I'm not sure how this would work inside the snap as you can't make any assumptions about the filesystem it is on. Tarballs would get large pretty quickly.

Are there any plans for making a backup system for the nextcloud box?

Consider two different kinds of backups: the kind you make regularly in order to mitigate hardware failure, and the kind you'd make right before testing something, or migrating to another system. Regular backups should be incremental, as you mentioned. However, that would require the snap can have complete ownership over these (e.g. using rsync, backing up daily into a specific path), and it's probably not the snap's responsibility to do this (e.g. not all users of the snap would have the space to account for it). I do think we can make a standalone export/import functionality, but incremental backups should be done to a different system, and are probably solely within the purview of the user. We can definitely document some suggestions, though.

[thomascobb]

I'd agree with this, and I'm happy to share my backup script when it's working. I gave up on the btrfs /var/snap mount as I can't get apparmor to stop moaning, so I'm back to rsync.

What other parts of the snap have state that need exporting like mysql? Does redis have any state that needs saving? And exporter and importer sounds like a good idea, as long as we can conditionally exclude the data directory and not tar it up so it can integrate in other backup strategies. The real advantage is that the importer could then cope with internal changes to the snap structure rather than never be able to change things internally.

[kyrofa]

And exporter and importer sounds like a good idea, as long as we can conditionally exclude the data directory and not tar it up so it can integrate in other backup strategies. The real advantage is that the importer could then cope with internal changes to the snap structure rather than never be able to change things internally.

Agreed.

What other parts of the snap have state that need exporting like mysql?

I'd say:

• MySQL database
• MySQL root.ini (assuming you're dumping everything in the above step), in $SNAP_DATA/mysql
• SSL setup (private and public keys, in $SNAP_DATA/certs)
• Nextcloud config (in $SNAP_DATA/nextcloud/config)
• Nextcloud apps (in $SNAP_DATA/nextcloud/extra-apps)
• Data, optional (in $SNAP_COMMON/nextcloud)

[thomascobb]

I'm not convinced I can contribute much effort to the importer/exporter, but I'm happy to test it out if it ever gets written.

What would you like to do with the mysqldump pull request and this associated ticket? I don't actually need it now that the rsync works, so I'm not bothered whether it gets accepted or not but maybe it's still worth having until the exporter is written.

[kyrofa]

What would you like to do with the mysqldump pull request and this associated ticket? I don't actually need it now that the rsync works, so I'm not bothered whether it gets accepted or not but maybe it's still worth having until the exporter is written.

Yes, agreed completely. I've made a new issue for the import/export feature, and merged your PR (thanks!) so this bug is considered fixed in candidate and targeted at the next release.