[slurm-users] Database backup best practices

[Ole Holm Nielsen]

Backing up the Slurm database is important for disaster recovery or 
migration of the database, but this is one of those Slurm tasks which 
isn't well documented.  I would like to find out some best practices, and 
I'm sure there's a lot of experience on this in the Slurm community.

I've been working a bit on the database backup, but I'm not blessed with a 
deep knowledge of databases.  Anyway, here's my proposal which I hope 
people would comment on:

Our setup is a slurmdbd server running on CentOS 7.9 with a MariaDB 5.5 
database from the distro.

Propositions:

1. Backup using the mysqldump command.  Only the database named 
slurm_acct_db should be backed up.  Don't back up the other system 
databases with, e.g., '--all-databases', see ref. [1].

2. Since the Slurm database uses InnoDB tables, the mysqldump command 
should include '--single-transaction' to ensure a consistent data dump and 
not locking the database, see refs. [1] and [2].

3. A number of daily backups should be made and rotated.  The logrotate 
command is an ideal tool for this.

My solution in ref. [3] is the following logrotate script 
/etc/logrotate.d/slurm_acct_db_backup which will make 8 backup versions in 
/var/log/mariadb/:

/var/log/mariadb/slurm_acct_db_backup.bz2 {
daily
dateext
dateyesterday
rotate 8
nocompress
create 640 root adm
postrotate
/usr/bin/mysqldump --user=root --password=<DATABASE-PASSWORD> 
--single-transaction -B slurm_acct_db | bzip2 > /root/slurm_acct_db_backup.bz2
endscript
}

You must first create an empty backup file:

touch /var/log/mariadb/slurm_acct_db_backup.bz2

Some further details are given in ref. [3], and this setup seems to be 
working well.

Question: Are there better ways, or can you suggest improvements to my 
approach?

Thanks,
Ole


References:

1. https://bugs.schedmd.com/show_bug.cgi?id=10295

2. https://serversforhackers.com/c/mysqldump-with-modern-mysql

3. 
https://wiki.fysik.dtu.dk/niflheim/Slurm_database#backup-and-restore-of-database

-- 
Ole Holm Nielsen
PhD, Senior HPC Officer
Department of Physics, Technical University of Denmark