Index of /andrejciho/Linux/Nightly MySQL Backup

Post Title Posted Description
Back Parent Directory
Nightly MySQL Backup 2006-12-23 15:43

Simple script to nightly backup your DB and keep last 7 backups.

For a long time I thought it would be nice to have some kind of automatic regular backup procedure of all of my and my clients’ MySQL databases. I just never quite found the time to research it. Then… the other day, I REALLY wished I would have had a backup.

Either way, after the other day, I did some research and found out how simple it was. So this post isn’t really anything new, just a short compilation of what I found online.

(You will need shell access to the hosting server and your hosting company will need to allow you to run scripts and use cron.)

Backup Script

First you’ll need a backup script that will dump (export, not destroy) the database. I found this one on and really liked it because it keeps last seven backups – every time you run it, it adds a new backup and gets rid of the oldest one. I had to add the –host= part, because my hosting company has the databases spread across multiple servers. So here’s what I have:


#Define your variables here:

#  Make sure output directory exists.
if [ ! -d $BACKUP_DIR ]; then
    mkdir -p $BACKUP_DIR

#  Rotate backups
for j in  6 5 4 3 2 1 0; do
    for i in $BACKUP_DIR/$BMYSQL_DBNAME.gz.$j; do
        if [ -e $i ]; then
            mv $i ${i/.$j/}.$(( $j + 1 ));

mysqldump --host=$BMYSQL_HOST --user=$BMYSQL_USER --pass=$BMYSQL_PWD $BMYSQL_DBNAME | gzip > $BACKUP_DIR/$BMYSQL_DBNAME.gz.0

Once you have this script in place, make sure it has the right permissions – my hosting company requires this: -rwx–x— with the default group.

Schedule your script to run

I found this helpful wiki article at Dreamhost. So I did the following:

crontab -e

Borrowing from Dreamhost’s Handy Crontab Header and adding a line that will run my script at 1:15am every day, this is what I have:

# minute (0-59),
# |      hour (0-23),
# |      |       day of the month (1-31),
# |      |       |       month of the year (1-12),
# |      |       |       |       day of the week (0-6 with 0=Sunday).
# |      |       |       |       |       commands
15       1       *       *       *       /home/path/to/your/script

Save the file (don’t forget to leave the last line blank). To see if the file saved correctly, just list the crontab file by this command:

crontab -l

and you should see it printed on the screen. That’s it!

Comments (1) to “Nightly MySQL Backup”

  1. This is great, but note that on Ubuntu /bin/sh is actually dash, which means that this script will fail. To fix it, just use /bin/bash at the top instead of /bin/sh

    Also, instead of using crontab -e, you can simply save this file in /etc/cron.daily

This website is powered by WordPress