We use the Puppet code for deploying MariaDB. Those are the backup options.
-
mysql::backup::mysqldump: Implements mysqldump backups.
-
mysql::backup::mysqlbackup: Implements backups with Oracle MySQL
Enterprise Backup. - mysql::backup::xtrabackup: Implements backups with
XtraBackup from Percona.
We don't own MySQL Enterprise Backup licenses.
Here the backup script. The big problem at the moment, we don't notice when backup is failing.
#!/bin/bash
#
# MySQL Backup Script
# Dumps mysql databases to a file for another backup tool to pick up.
#
# MySQL code:
# GRANT SELECT, RELOAD, LOCK TABLES ON *.* TO 'user'@'localhost'
# IDENTIFIED BY 'password';
# FLUSH PRIVILEGES;
#
##### START CONFIG ###################################################
USER=backup
DIR=/var/lib/mysql/backup
ROTATE=6
# Use a pwd file. See https://dev.mysql.com/doc/refman/5.1/en/password-security-user.html
PWD_FILE=$(mktemp ${DIR}/backup.cnf.XXXX)
echo "
[client]
password=sirugrwiuwiuwguisrkogos
" >> $PWD_FILE
PREFIX=mysql_backup_
ADDITIONAL_OPTIONS="--ignore-table=mysql.event"
##### STOP CONFIG ####################################################
PATH=/usr/bin:/usr/sbin:/bin:/sbin
set -o pipefail
cleanup()
{
find "${DIR}/" -maxdepth 1 -type f -name "${PREFIX}*.sql*" -mtime +${ROTATE} -print0 | xargs -0 -r rm -f
}
mysqldump --defaults-file=${PWD_FILE} -u${USER} --opt --flush-logs --single-transaction \
${ADDITIONAL_OPTIONS} \
--all-databases | bzcat -zc > ${DIR}/${PREFIX}`date +%Y%m%d-%H%M%S`.sql.bz2
if [ $? -eq 0 ] ; then
cleanup
fi
rm -rf $PWD_FILE
backup time
# time /usr/local/sbin/mysqlbackup.sh
real 21m35.647s
user 20m47.059s
sys 0m7.843s
backup size
# du -sh mysql_backup_20160223-123920.sql
4.9G mysql_backup_20160223-123920.sql
Is it in our use case better to use xtrabackup
? When to use xtrabackup
?
Best Answer
There are two ways to detect when your mysqldump fails: One you use already:
If this condition fails, you can conclude your backup is a failure... Another mode is to using
tail -1 backup.sql
which should tell you "Backup completed successfully"So if only worry is if-it-was-successful, then you can use above methods to determine.
Xtrabackup is physical while mysqldump is logical, which is mostly a decisive factor in choosing one over other. In case you need to restore individual table/database, with mysqldump it is possible but not with xtrabackup (unless you do complete restore). Physical backup is fast comparatively but also asks for frequent restore tests like data corruption is not easily identifiable (I think so) for hot backups. There are more pros and cons you can consider looking around but if you think this time is too much and you have identified your restore requirements fit in with Xtrabackup, go with it. Here is holland-xtrabackup setup steps if you'd like to configure it that way.
If you're worried about speed and still want a logical backup, go with mydumper.
If you're fine with physical backup go with Xtrabackup.