Database maintenance and backup

Infrastructure & Networking Nvidia Bright Cluster Manager
1

I am using bacula to get backups of our head node, and the nightly incremental backups are quite large, over 30 GB, so I was looking into what comprises so much changed data everyday.

I found mysql-bin files in /var/lib/mysql going back to the beginning about 2.5 years ago… actually that directory only comprises 1.5 GB so it’s not that significant but I didn’t even realize the system was using MySQL.  Either way, I just wanted some feedback on proper maintenance and backup of the databases… I don’t know much in that area, but I do know that taking a straight backup of /var/lib/mysql is improper.

In fact, bacula comes with a mysqldump script that is supposed to be run before the backup starts, then the dumped data files are backed up and I believe the binary data gets pruned or flushed as well.  I’ve included it below.

There looks to be additional databases in various formats in /var/lib/mysql/cmdaemon/, /var/lib/mysql/mysql/, /var/lib/mysql/performance_schema/, and /var/lib/mysql/slurm_acct_db/ as well.

Would using this script still be recommended?  I think some of the options may need to be adjusted and maybe add a few dump commands for the differing databases.  I checked the admin manual and the database setup seems rather complex but it didn’t really mention anything about maintenance/pruning and backups, except for the daily cmd backups that are put in /var/spool/cmd/backup/ which I’m of course already backing up.

Thanks

# cat mysql_binary_log_dump
#!/bin/bash
LEVEL=$1
DUMPDIR=/var/lib/mysql_dump
USER=root
PASSWORD=<pass>

if [ "$LEVEL" == "Full" -o "$LEVEL" == "Differential" ]
then
  echo "Full/Differential"
  mysqldump -u $USER --password=$PASSWORD --flush-logs --master-data=2 --all-databases --delete-master-logs > $DUMPDIR/dump.$$.sql
  mv $DUMPDIR/dump.$$.sql $DUMPDIR/dump.sql
else
  echo "Incremental"
  mysqladmin -u $USER --password=$PASSWORD flush-logs
fi
#