How to monitor regular MySQL backups.

Question

I am a developer and I am discussing with my sysadmin how to backup the MySQL database we are using.

The previous sysadmin told me he had started backing up the database, but the new guy, who isn't as experienced is offering to help me develop a script.

First of all is it normal that the developer is expected to be responsible for the database backups? Every other place I have worked, the sysadmin has taken care of it, leavening me to do development.

So at the moment I have a Perl script that calls a mysql-dump, and backs it up along with the code. Now for some reason the backups in the middle of the nighht fail, and create a truncated version of the database. I developed this as a temporary solution, and when I needed it a couple of weeks ago, the backup had failed. For this reason I would prefer that the sysadmin takes responsibility, as I have code to develop, and don't need to be checking cron job outputs every day.

So what is a reliable way of backing up the database every hour, and getting a warning if the backup fails.

Answer 1

The typical scenario I have seen is that business owner or his delegate decides on the retention, acceptable dataloss and recovery point objectives as the owner is the one paying the resulting bills.

The DBA (and/or sysadmin) get an idea of the application profile (complex transactions or simple table updates, back-up window, database size, data growth, number of changed rows etc.) and decide on an appriopiate backup strategy to meet those requirements.

At that point the business owner usually decides that the initial platinum backup requirements are too expensive, so rinse and repeat.

Developers don't even get access to production systems and even if they do; they're not responsible for daily backups. (Although it's good practice to ensure you have a back-up before doing any work on the database structure).

So I agree with you, make it somebody elses problem.

---

If your application normally runs transactions that update multiple tables you'd want to run mysqldump with the table lock options to ensure consistency. But that will block all updates to your database for the duration of the backup job, so doing that every hour is often a Bad Idea.

The binary log allows for point in time restores and incremental backups.

Take a look at http://dev.mysql.com/doc/refman/5.7/en/backup-methods.html

---

A simple script that makes a back-up of all databases on a MySQL server, each database to an individual file. It will only e-mail you in case of problems. As long as cron runs, the back-ups will be created. As always, also test your restore capabilities!:

#!/bin/bash

# Simple script to create logical backups of all MySQL databases on
# a server. by http://serverfault.com/users/37681/hbruijn
# Free to use and modify as neeeded.

#======================================================================
# Define paths to system binaries
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
GZIP="/bin/gzip"
MAIL="/bin/mailx"

# MySQL credentials used for reading the databases.
# either the MySQL DBA account "root"
# or alternatively create a dedicated read-only backup user
# with the following GRANT statement:
# mysql>  GRANT SELECT,RELOAD,SUPER,REPLICATION CLIENT ON *.* TO \
#  backupuser@<this IP or localhost>  identified by 'Very_s3cr3t_passW0rd';
MYHOST="localhost"  # localhost or remote ip-address
MYUSER="backupuser"
MYPASS="Very_s3cr3t_passW0rd"

# Local filesystem or network share to dump back-ups
# Good practice to have file back-ups on their own filesystem
# and not on the root filesystem.
MYBAKDIR="/backups"

# Keep 1 week worth of MySQL backups under $MYBAKDIR
MYDIR=$(date +MySQL/%A)

# Mail errors to somebody in charge
ERROR_RCPT=Herman@example.com

# The rest shouldn't need much tuning
#=====================================================================

errormail(){
cat << EOF | $MAIL -s "MySQL back-up failed !" $ERROR_RCPT
        This is an automatic warning message.

        The MySQL back-up on server: $(hostname) has failed with the following
        errors:

        $1

        Please take appropiate action.

        Thanks in advance.
EOF
exit 1 ;
}

if ! test -d $MYBAKDIR ; then
 mkdir -p $MYBAKDIR || errormail "Backup directory $MYBAKDIR does not exist and could not be created."
fi

if test -d "$MYBAKDIR/$MYDIR" ; then
  rm -rf "$MYBAKDIR/$MYDIR" || errormail "Expired backups from $MYBAKDIR/$MYDIR could not be removed."
fi

mkdir -p "$MYBAKDIR/$MYDIR" || errormail "Todays backup directory $MYBAKDIR/$MYDIR could not be created."

# Generate list with all databases
DATABASES=$(echo "show databases" | $MYSQL -h $MYHOST -u $MYUSER -p$MYPASS |grep -v ^Database$) || errormail "Unable to connect to MySQL database server on $MYHOST please check the supplied credentials"

# Make a logical backup of each database
for DB in $DATABASES
do
  $MYSQLDUMP  -h $MYHOST -u $MYUSER -p$MYPASS --opt --single-transaction $DB > $MYBAKDIR/$MYDIR/$DB.sql  || errormail "Unable to create backup from $DB "
  $GZIP $MYBAKDIR/$MYDIR/$DB.sql  || errormail "Unable to compress $MYBAKDIR/$MYDIR/$DB.sql "

done