MySQL Backup

From KdjWiki

Jump to: navigation, search


Contents

Preparation

Create backup user

You should create a MySQL user that has access to all the databases you want to backup. I recommend this be a different user to the primary one you use for each database to improve portability and help track database activity.

You can either use the mysql from the command line or an other application (such as phpmyadmin) that you are most comfortable with. You will need to ensure the user has the following permissions on each database:

  • USAGE
  • SELECT
  • LOCK TABLES

If using the command line: 

mysql -u {user with access} -p -h {mysql server/host name} {database name}
mysql> GRANT USAGE, SELECT, LOCK TABLES
    ON `{database name}`.* TO '{backup user name}'@'{mysql server/host name}'
    IDENTIFIED BY '{backup user password}';
mysql> quit

NOTE: Pay careful attention to the quotes being used, ` is not the same as '

Create Scripts

As you will see, I make hourly, daily, and monthly backups. They will cycle and overwrite each other (i.e. the 2:00am backup file will get overridden by the next one each day at 2:00am), but this still leaves my database backup folder with up to 67 (admittedly gzipped up) dumps for each database. This may be a bit extreme for you and as such you should tailor the schedule to your own needs (maybe only run the daily and monthly backups).

Master Backup Script

NOTE: You should replace the host (set to mysql.myserver.com) with the correct domain name (such as localhost).

db_backup.sh 

#!/bin/bash

cd $(dirname "${0}")

verify=''

schedule=`echo "${0}" | grep -i daily`
if [ "${schedule}" == "" ]; then
	schedule=`echo "${0}" | grep -i hourly`
	if [ "${schedule}" == "" ]; then
		# monthly
		date=$(date +%m)
		fn="-month-${date}.sql"
		verify="yes"
	else
		# hourly
		date=$(date +%H)
		fn="-hour-${date}.sql"
	fi
else
	# daily
	date=$(date +%d)
	fn="-day-${date}.sql"
fi

host="mysql.myserver.com"
dumpopts="-a --add-drop-table --allow-keywords -i -c -Q"

zip="/bin/gzip"
grep="/bin/grep"
mysql="/usr/bin/mysql"
mysqldump="/usr/bin/mysqldump"

if [ -z "${3}" ]; then
	echo "Usage: ${0} backup-user backup-user-password backup-directory"
	echo "NOTE: backup-directory should NOT end in a backslash (/)"
	exit 1
fi

usr="${1}"
pwd="${2}"
dir="${3}"

# for each database:
# NOTE: pipe additional "${grep} -v {database name}" for each database you want to exclude
for db in `echo "show databases" | ${mysql} -h ${host} -u ${usr} -p${pwd} | ${grep} -v Database | ${grep} -v information_schema` ;
do
	file="${dir}/${db}${fn}"
	cmd="${mysqldump} ${dumpopts} -h ${host} -u ${usr} --password=${pwd} ${db}"

	echo "Backing up ${db} (to ${file})..."
	ret=`${cmd} > "${file}"`

	if [ "${ret}" != "" ]; then
		echo "BACKUP ERROR: MySQL backup output => ${ret}"
	else
		[ -f "${file}.gz" ] && rm -f "${file}.gz"

		echo "Compressing ${file}..."
		ret=`${zip} "${file}"`

		if [ -f "${file}" ] || [ ! -f "${file}.gz" ]; then
			echo "BACKUP ERROR: Can't zip ${file}"
			if [ ! -f "${file}" ]; then
				echo "File not found"
			fi
		else
			if [ "${verify}" == "yes" ]; then
				# TODO: verify database "${db}" from "${file}.gz"
			fi
		fi
	fi
done

exit 0

Make it executable: 

  $ chmod +x db_backup.sh

Hourly Backup

Create a link to db_backup.sh for hourly backups: 

  $ ln -s db_backup.sh db_hourly_backup.sh

Create script to call daily backup:

backup_db_hourly.sh 

#!/bin/bash

cd $(dirname "${0}")

if [ -z "${1}" ]; then
	echo "Usage: $0 backup-directory"
	echo "NOTE: backup-directory should NOT end in a backslash (/)"
	exit 1
fi

dir="${1}"

echo "Starting hourly backup (`date`)..."
./db_hourly_backup.sh backup_user backup_password "${dir}"
echo "Hourly backup complete (`date`)..."

make executable: 

  $ chmod +x backup_db_hourly.sh

Daily Backup

Create a link to db_backup.sh for daily backups: 

  $ ln -s db_backup.sh db_daily_backup.sh

Create script to call daily backup:

backup_db_daily.sh 

#!/bin/bash

cd $(dirname "${0}")

if [ -z "${1}" ]; then
	echo "Usage: $0 backup-directory"
	echo "NOTE: backup-directory should NOT end in a backslash (/)"
	exit 1
fi

dir="${1}"

echo "Starting daily backup (`date`)..."
./db_daily_backup.sh backup_user backup_password "${dir}"
echo "Daily backup complete (`date`)..."

make executable: 

  $ chmod +x backup_db_daily.sh

Monthly Backup

Create a link to db_backup.sh for monthly backups: 

  $ ln -s db_backup.sh db_monthly_backup.sh

Create script to call monthly backup:

backup_db_monthly.sh 

#!/bin/bash

cd $(dirname "${0}")

if [ -z "${1}" ]; then
	echo "Usage: $0 backup-directory"
	echo "NOTE: backup-directory should NOT end in a backslash (/)"
	exit 1
fi

dir="${1}"

echo "Starting monthly backup (`date`)..."
./db_monthly_backup.sh backup_user backup_password "${dir}"
echo "Monthly backup complete (`date`)..."

make executable: 

  $ chmod +x backup_db_monthly.sh

Schedule

Create a directory to hold your database backups: 

  $ mkdir database

Depending on your set-up - you may be able to create cron jobs in /etc/cron.hourly, /etc/cron.daily, and /etc/cron.monthly directories which will automatically get run every hour, day, and month respectively. Otherwise, you will need to add the tasks to your /etc/crontab file.

NOTE: If you are wondering why I have the backup_db_hourly.sh, backup_db_daily.sh, and backup_db_monthly.sh scripts instead of running the db_hourly_backup.sh, db_daily_backup.sh, and db_monthly_backup.sh scripts directly, it is mainly because they contain the backup user and password details which should be (at least a bit) secured from prying eyes. I don't know the visibility of other people's crontabs and/or process lists, but I this should make the credentials a bit less visible.

For standard crontab file 

# hourly database backup - every XX:15
15 * * * * /path/to/backup_db_hourly.sh /path/to/db_backup > /var/log/backup_db_hourly.log

# daily database backup - every 02:35
35 2 * * * /path/to/backup_db_daily.sh /path/to/db_backup > /var/log/backup_db_daily.log

# monthly database backup - every 01-XX-XXXX (or XX-01-XXXX for Americans)
*  * 1 * * /path/to/backup_db_monthly.sh /path/to/db_backup > /var/log/backup_db_monthly.log

For vixie-cron (/etc/cron.hourly, /etc/cron.daily, /etc/cron.monthly)

/etc/cron.hourly/db_backup.cron 

#!/bin/bash
/path/to/backup_db_hourly.sh /path/to/db_backup > /var/log/backup_db_hourly.log

/etc/cron.daily/db_backup.cron 

#!/bin/bash
/path/to/backup_db_daily.sh /path/to/db_backup > /var/log/backup_db_daily.log

/etc/cron.monthly/db_backup.cron 

#!/bin/bash
/path/to/backup_db_monthly.sh /path/to/db_backup > /var/log/backup_db_monthly.log

Restoring

What good is backing up your database unless you are able to restore from it?

Create restore database

You should create a scrap database for performing restores against. You may want to restore directly to your target database, but I strongly recommend you run against a non-active database first to verify the restore functions correctly/completes.

You can either use the mysql from the command line for creating this database, or a tool (such as phpmyadmin) if you are more comfortable with that.

If using the command line: 

mysql -u {user with access} -p -h {mysql server/host name}
mysql> CREATE DATABASE restore_db;
mysql> quit

Create restore user

You should create a MySQL user that has access to all the databases you want to restore. I recommend this be a different user to both the primary one you use for each database and the backup user (who should only have read-only access to the databases).

Similar to create the backup user above, you should create the user with the following permissions on each database:

  • USAGE
  • DROP
  • CREATE
  • ALTER
  • SELECT
  • INSERT
  • LOCK TABLES

If using the command line: 

mysql -u {user with access} -p -h {mysql server/host name} restore_db
mysql> GRANT USAGE, DROP, CREATE, ALTER, SELECT, INSERT, LOCK TABLES
    ON restore_db.* TO '{restore user name}'@'{mysql server/host name}'
    IDENTIFIED BY '{restore user password}';
mysql> quit

Restore script

NOTE: You should replace the host (set to mysql.myserver.com) with the correct domain name (such as localhost).

db_restore.sh 

#!/bin/bash

cd $(dirname "${0}")

host="mysql.myserver.com"

zip="/bin/gzip"
grep="/bin/grep"
unzip="/bin/gunzip"
filer="/usr/bin/file"
mysql="/usr/bin/mysql"

if [ -z "${4}" ]; then
	echo "Usage: ${0} restore-user restore-user-password restore-tmp-database backup-file"
	exit 1
fi

usr="${1}"
pwd="${2}"
db="${3}"
file="${4}"

if [ ! -f "${file}" ]; then
	echo "ERROR: Can't restore non-existent backup (${file})"
	exit 1
fi

zipped=`${filer} ${file} | grep $(basename ${zip})`

if [ "${zipped}" == "" ]; then
	# not zipped
	ret=`${mysql} -h ${host} -u ${usr} -p${pwd} "${db}" < "${file}"`
else
	# zipped
	ret=`${unzip} -c "${file}" | ${mysql} -h ${host} -u ${usr} -p${pwd} "${db}"`
fi

if [ "${ret}" != "" ]; then
	echo "Restoring ${db} from ${file} (to ${db})..."
	echo "RESTORE ERROR: MySQL restore output => ${ret}"
	exit 1
fi

exit 0

make executable: 

  $ chmod +x db_restore.sh

Testing the restore

Run the db_restore script: 

  $ ./db_restore.sh {restore user} {restore password} restore_db {backup file}

If you get any output, you should investigate.


NOTE: If you get a message such as: 

  ERROR: Can't restore non-existent backup (xxx.sql)

You need to make sure you are passing the full path along with the backup file.

Regular restore verification

You should try to restore your databases from backup on a regular basis (such as once a month) to ensure the backups are behaving as expected. You don't want to discover that some bad characters are being inserted into your backups which render them invalid only when you have a failure!

This can be achieved by updating the master backup script (db_backup.sh) to call the restore script. For example, in the backup script above you can change: 

# TODO: verify database "${db}" from "${file}.gz"

to something like: 

rest_usr="{restore user}"
rest_pwd="{restore user password}"

ret=`./db_restore.sh "${rest_usr}" "${rest_pwd}" "restore_db" "${file}.gz"`
if [ "${ret}" != "" ]; then
	echo "ERROR VERIFYING BACKUP: ${ret}"
fi

Off site Backup

If you want the further security of having a duplicate copy of your backup files - including an off-site version, you should see Backing up with Rsync.

Retrieved from "http://wiki.accordingtokris.com/index.php?title=MySQL_Backup"