at the moment mysqldump (22 minutes exec time, 5 GB Dump), when to use xtrabackup?

Question

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?

Answer 1

There are two ways to detect when your mysqldump fails: One you use already:

if [ $? -eq 0 ] ; then
    cleanup
fi

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.

Answer 2

Can't you test this on a test machine? I don't mean to be rude, but only you can really tell if XtraBackup is better for you. What I do know is that Percona is a quality outfit and that their offerings are always worth a look.

I'm guessing about your scenario, but any backup will depend more on your disk config than on a particular programme (RAID config &c.). There are four things to be considered in any IT setup - hardware, networking, OS and software (and maybe 5 - people :-) ).

Have you also looked at MyLVMBackup? Zmanda? If you're considering XtraBackup, you must be on Linux - these also work on that platform.