Bash – MySQL Backup Script

image_pdfimage_print

Looking for a script to backup your MySQL databases on a Linux host? Here is a simple bash script that can be used to automate the process in a cron job. The script checks the database integrity then dumps the database to a text file, tar’s it up, and the emails a confirmation of completion.

#!/bin/bash

#Simple database backup script
#By:Ronny L. Bull
#Date: 8-10-2011

SUBJECT="MySQL Backup"
FROM="alerts@yourdomain.com"
TO="youremail@yourdomain.com"
MESSAGE="MySQL backup completed"
DATESTAMP="`date '+%d%m%Y'`"
HOSTNAME="YOUR_HOST"
DBNAME="YOUR_DB"
DBUSER="YOUR_DB_USER"
DBPW="YOUR_DB_PASS"
LOC="/var/backup"

#Perform mysql check on database
nice -19 mysqlcheck -o -e -A -u${DBUSER} -p${DBPW}

#Dump the database to a .sql file
nice -19 mysqldump -u${DBUSER} -p${DBPW} -R --opt ${DBNAME} > ${LOC}/${HOSTNAME}/db/${DBNAME}.sql

#Gzip the sql file
cd ${LOC}/${HOSTNAME}/db/
tar -zcf sqldata.${DATESTAMP}.tgz *.sql

#Send email
echo "Subject: $SUBJECT
$MESSAGE" | sendmail -f ${FROM} ${TO}

exit

  1. No Comments