Feb

MySQL Database Backup Script

Posted by Laz

Backup, Codebank, How To, Linux, MySQL, Scripts, Solaris, Tips, UNIX

Here we go folks, I thought I would share a handy little script with you that I use to backup all of the databases on a particular Linux/UNIX server. I do this by getting a list of the databases, and then using mysqldump to dump them all to a text file. This seems to be the best way (short of replication) to get good clean backups of the data. Toss it into a cron job and you can have it done automagically. There isn’t anything yet to rotate files, but I might add that later. Also, I am going to try and rewrite this in PERL so our Windows (and other OS’s that don’t have a shell like Bash) brethren can run this script as well. For now though, it’s written for Bash but almost any shell would work I think.

OK, onto the script. Now, I shouldn’t need to remind you that you should test this in whatever you have for a development/testing environment, just in case something were to go wrong in your environment that is different than mine. I can’t be liable or held responsible if something goes awry and you lose data if you just toss it into production without testing. There’s nothing in the script that should cause any problems, it’s only READING the databases after all, but I have to cover by arse here.

At the top of the script, fill in the variables as noted and run, it’s that easy. Check it out, it’s pretty simple actually:

#!/bin/bash
# This is a shell script that will find all of your MySQL databases and then
# dump them all to text (SQL) files in the location of your choosing.
 
# These should be the only parameters you have to set
# Set the MySQL variables
DBU="msuser" # your MySQL username
DBP="mspass" # your MySQL password
DBH="localhost" # your MySQL host
MYSQL="$(which mysql)" # These should detect the
MYSQLDUMP="$(which mysqldump)" # approriate binaries
MYSQLSHOW="$(which mysqlshow)" # automatically
 
# Set this path to where you want
# your backups to end up
BDIR=/backup/databases
 
#-------------------------------------------------------------
#----Nothing below *should* need to be modified---------------
#-------------------------------------------------------------
 
# Set the date variables
YEAR=`date | awk '{print $6}'`
MONTH=`date | awk '{print $2}'`
DAY=`date | awk '{print $3}'`
TIME=`date | awk '{print $4}' | awk -F: '{print $1"-"$2"-"$3}'`
DTE=${YEAR}.${MONTH}.${DAY}_${TIME}
 
echo
echo "Beginning database backup operations:"
echo "----------------------------------------------------"
echo
 
# Ok, now let's dump all the databases
ALLDB="$($MYSQLSHOW | egrep -v "\-|Databases" | awk '{print $2}')"
for a in $ALLDB
do
  DEST=$BDIR/${DTE}_${a}.sql
  # Uncomment this one, and comment out the one below to add compression.
  #sudo $MYSQLDUMP -u $DBU -h $DBH -p$DBP $a | $GZIP -9 > $DEST
  sudo $MYSQLDUMP -u $DBU -h $DBH -p$DBP $a > $DEST
  if [ "$?" = "0" ]; then
    echo "  Backup of database $a was successfull."
  else
    echo "  Backup of database $a failed!"
  fi
done
 
echo
echo "----------------------------------------------------"
echo "Backup complete."
echo