How do you want to keep the archive? Are you planning to own the file? The reason I'm asking is that for example we can get a backup of the database but it can be stored for up to five years and then will be practically unusable. We don't have a swift/ceph container to hold archival data (there are some ideas for it: T350924: Swift container for archived mariadb tables)

For now the plan was to dump in on miscweb1003, then let Bacula back it up and keep it there indefinitely. So the side question would be if we can define a backup in Bacula to be a forever-archive.

We are aware of the wider question if we should have a generic place to store private dumps/files forever but that can be figured out later.

Additionally we have scraped static HTML files which should be more convenient to look at it. The dump is just extra and "in case".

in short: yes, we are ok owning the file.

(since both db dumps and bacula backups are limited to 5 years that's an issue to talk about but for the purpose of this task you can ignore it and let it be our problem of the future).

@jcrespo adding Jaime to the thread.

Change #1124076 had a related patch set uploaded (by Jcrespo; author: Jcrespo):

[operations/puppet@production] dbbackups: Setup temporary archival job to archive RT database

https://gerrit.wikimedia.org/r/1124076

Change #1124076 merged by Jcrespo:

[operations/puppet@production] dbbackups: Setup temporary archival job to archive RT database

https://gerrit.wikimedia.org/r/1124076

I've backed into the archival pool, and then restored the files onto dbprov1003.eqiad.wmnet:/var/tmp/bacula-restores/srv/backups/dumps/latest/dump.rt.2025-03-03--11-06-30:

614278  Restore       53    1.598 G  OK       03-Mar-25 12:10 RestoreFiles

@Dzahn Can you check those files, as recovered, are ok for you to use? I didn't use mysqldump, but mydumper (our standard backup tool) that creates mysqldump-like files, but compressed and one per table?

Once you confirm the backup is acceptable, I will remove the temporary files above and we can close this as resolved.

Thank you very much, @jcrespo. Give me a moment to figure out where we can actually store this file.

I could not just rsync from dbprov1003 to other hosts. At least not without creating new puppet commits or stopping puppet and manually installing rsync and messing with firewall configs on either source or destination.

So have to use scp -3 or something.. takes some time but is in progress!

@Dzahn transfer.py was precisely built to handle database backups, and requires no further installation on all wmf hosts (it is how database snapshot backups are created and restored)!

e.g.:

cumin1002> sudo transfer.py --encrypt dbprov1003.eqiad.wmnet:/var/tmp/bacula-restores/srv/backups/dumps/latest/dump.rt.2025-03-03--11-06-30 yourhost.eqiad.wmnet:/var/tmp
2025-03-05 07:56:41  INFO: About to transfer /var/tmp/bacula-restores/srv/backups/dumps/latest/dump.rt.2025-03-03--11-06-30 from dbprov1003.eqiad.wmnet to ['yourhost.eqiad.wmnet']:['/var/tmp'] (1598373094 bytes)
2025-03-05 07:59:37  INFO: Parallel checksum of source on dbprov1003.eqiad.wmnet and the transmitted ones on yourhost.eqiad.wmnet match.
2025-03-05 07:59:38  INFO: 1598373094 bytes correctly transferred from dbprov1003.eqiad.wmnet to yourhost.eqiad.wmnet
2025-03-05 07:59:39  INFO: Cleaning up....

Thank you @jcrespo this would be very convenient! I tried it. It failed though with ERROR: iptables execution failed. I assume it's not compatible with hosts already switched to nftables.

Thanks, Jaime! Sounds good :)

@Dzahn do you think we can drop this database from m1 and remove all the grants etc? It is fine if you'd like more time to pass by, but just asking if we can create a task to track this clean up.
Thanks!

@Marostegui Yes, we can drop this db and remove all grants!

Created T388437 for that.

In T385901#10620198, @Dzahn wrote:

Created T388437 for that.

Thanks, I had created T388432, but I just merged that one into yours