Proper Backup Techniques?

MySQL & MariaDB Percona XtraBackup
1

Hello,
I would like to get a peer review of my backup script. Am I doing ok? Any recommendations?

We are backing up on the weekends, and I’m not concerned about a database outage at the time of backups. They take less than 5 minutes at this time so performance is quite acceptable.

Without further ado, here is the meaty part:

suffix=“POOH”
BACKUP_DIR=/home/mysql.backup
LOG_DIR=/var/log/mysql.backup
BACKUP_NAME=“full-$suffix”
FULL_BACKUP_DIR=“$BACKUP_DIR/$BACKUP_NAME”

echo “flush tables with read lock; set global read_only = ON;” | mysql --defaults-file=/var/lib/mysql/defaults.cnf
innobackupex --defaults-file $TMPFILE --no-timestamp $FULL_BACKUP_DIR > $LOG_DIR/$suffix 2>&1
innobackupex --defaults-file $TMPFILE --apply-log --no-timestamp $FULL_BACKUP_DIR >> $LOG_DIR/$suffix 2>&1
pt-table-checksum --defaults-file=/var/lib/mysql/defaults.cnf --chunk-size=1000000 --chunk-size-limit=0 --recursion-method none >/dev/null
echo “set global read_only = OFF; unlock tables;” | mysql --defaults-file=/var/lib/mysql/defaults.cnf

cd $FULL_BACKUP_DIR
mkdir external_files

Columns: db tbl this_crc this_cnt

echo “use percona; select db,tbl,this_crc,this_cnt from checksums;” | mysql --defaults-file=/var/lib/mysql/defaults.cnf > external_files/checksums.$suffix

2

Hi GreyGnome;

You do not need to flush tables yourself or set it to read_only. Xtrabackup/Innobackupex will handle the flushing for you, and enabling read-only on the server defeats a lot of the purpose of a hot backup.

-Scott

3

Thanks for the reply. But then there’s a possibility of modification between the backup and checksum times, is there not?

4

Hi GreyGnome;

I guess I am unsure of what you are trying to accomplish then. Generally speaking, the backup and running of pt-table-checksum are used for separate purposes. The backup portion is fairly straight forward; you just want a valid and consistent point-in-time backup, which Xtrabackup will give you. Then when you run pt-table-checksum, you are generally making sure that the slave(s) are in-sync with the master.

So the question is, what is your intended purpose with running pt-table-checksum in this case? Is your backup on a slave, and you are wanting to check each time to make sure the slave is in-sync with the master? Or some other purpose?

-Scott