A MySQL database backup (mysqldump) shell script

MySQL database backup FAQ: Can you share a Linux shell script that I can use to make a MySQL or MariaDB backup (i.e., a shell script that wraps the mysqldump command)?

Introduction

Sure. I currently have a collection of websites on several different servers (including Linode and GoDaddy, so I was just spending some time trying to automate my MySQL database backups. To that end, I just created a MySQL shell script that I use on my Linux servers to make database backups with mysqldump, and I thought I’d share that script here.

Solution: My MySQL database backup (dump) shell script

Here’s the source code for my MySQL database backup shell script. An explanation follows the shell script source code.

#!/bin/sh

#----------------------------------------------------------
# a simple mysql database backup script.
# version 2, updated March 26, 2011.
# copyright 2011 alvin alexander, http://alvinalexander.com
#----------------------------------------------------------
# This work is licensed under a Creative Commons 
# Attribution-ShareAlike 3.0 Unported License;
# see http://creativecommons.org/licenses/by-sa/3.0/ 
# for more information.
#----------------------------------------------------------

# (1) set up all the mysqldump variables
FILE=minime.sql.`date +"%Y%m%d"`
DBSERVER=127.0.0.1
DATABASE=XXX
USER=XXX
PASS=XXX

# (2) in case you run this more than once a day, remove the previous version of the file
unalias rm     2> /dev/null
rm ${FILE}     2> /dev/null
rm ${FILE}.gz  2> /dev/null

# (3) do the mysql database backup (dump)

# use this command for a database server on a separate host:
#mysqldump --opt --protocol=TCP --user=${USER} --password=${PASS} --host=${DBSERVER} ${DATABASE} > ${FILE}

# use this command for a database server on localhost. add other options if need be.
mysqldump --opt --user=${USER} --password=${PASS} ${DATABASE} > ${FILE}

# (4) gzip the mysql database dump file
gzip $FILE

# (5) show the user the result
echo "${FILE}.gz was created:"
ls -l ${FILE}.gz

An explanation of my MySQL database backup shell script

Here’s a quick overview of what this shell script does:

• When this shell script is run it will create a file named something like drupal.sql.20110305.gz.
• That filename changes daily depending on the date.
• At the beginning of the shell script I set up all the variables that I need. Those are all shown in uppercase; just fill in your own username, password, etc.
• I unalias the Linux rm command because some web hosting companies like to make an alias of it. That can cause problems.
• I remove any previous backup files with today’s date on it. Those files should only exist when you run this script more than one time on any day. In general this step shouldn’t be needed, but it comes in handy if you want to make multiple backups per day.
• The mysqldump command is where the actual MySQL database dump (backup) happens. Output is saved to our file.
• In the last step I compress the file with the Linux gzip command.

If you make this shell script executable and then place an entry in your crontab file, hopefully this will make a decent little MySQL database backup shell script.

A simpler version of that backup script

If you prefer a simpler version of that shell script, I just wrote this code for a MySQL database named focus:

DBNAME=focus
DATE=`date +"%Y%m%d"`
SQLFILE=$DBNAME-${DATE}.sql
mysqldump --opt --user=root --password $DBNAME > $SQLFILE
gzip $SQLFILE

That script also runs mysqldump, prompts you for the root username, and gzip’s the resulting dump file.

MySQL backups, crontab entries, and tutorials

On a related note, if you're not familiar with the Linux crontab facility, here are some of my crontab tutorials:

• Linux crontab "every" examples (every 5 minutes, etc.)
• Example Linux crontab file format
• A Linux crontab mail command example
• How to edit your crontab file

As a quick note, if you are familiar with crontab, you know you’ll want to create an entry that looks something like this to run your MySQL database backup script:

0 5 * * * /home/al/bin/mysql-database-dump.sh

In this crontab syntax example, the MySQL database dump shell script will be run every day, at five minutes after midnight. Once again, adjust this as desired.

My MySQL database backup shell script - More features

Of course there are other things you can do with this MySQL database backup script, but I wanted to make sure I shared this MySQL backup script to help get you started.

At some point I’ll share a companion shell script that will delete the old backup files that will accumulate from this approach.

I hope this example MySQL database backup shell script has been helpful. If you have any suggestions that will improve this script for everyone, just leave a note in the Comments section below.