0

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?

  • 2
    XtraBackup give a lot more feature than will help as you grow, such as incremental backups and significantly faster restore times. If I were you, I would invest the time in preparing XtraBackup full and incremental backups scripts and a restore script to go with them. Better now when it is quick to perform the tests, than down the line when your backup reaches 100+Gb... – Dave Rix Feb 24 '16 at 13:19
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.

  • where is the file backup.sql exactly? I don't find log message Backup completed successfully in /var/log/*. Please provide more details. – Ivanov Mar 1 '16 at 18:37
  • I assumed: mysqldump > backup.sql and tail -1 to that will give you status. – mysql_user Mar 2 '16 at 8:24
  • As per your script you might need to zcat ${DIR}/${PREFIX}`date +%Y%m%d-%H%M%S`.sql.bz2 | tail -1 – mysql_user Mar 2 '16 at 8:25
0

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.

  • I don't mind being downvoted - however, I would like a reason for the downvote. TIA. – Vérace Feb 24 '16 at 14:35
  • Excellent answer, perfectly in line with the standard "It Depends" – Max Vernon Feb 24 '16 at 14:48
  • Thanks for that @MaxVernon - lots of st in life depends on other st :-). Maybe the problem was that I didn't explicitly answer this part of the question "we don't notice when backup is failing"... That bit I, (maybe nobody) can answer. If your backups are failing then change your backup strategy. From the post, it would appear to be a regular occurrence - hence my suggestions. This (1000 odd downloads/week - good rep (4.9/5) might be an excellent replacement for their script. – Vérace Feb 24 '16 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.