Shell Script To Backup MySql Database Server

by Vivek Gite on April 9, 2008 · 45 comments · [ vivek@nixcraft.com ]

#!/bin/bash
# Shell script to backup MySql database
# To backup Nysql databases file to /backup dir and later pick up by your
# script. You can skip few databases from backup too.
# For more info please see (Installation info):
# http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html
# Last updated: Aug - 2005
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2004, 2005 nixCraft project
# Feedback/comment/suggestions : http://cyberciti.biz/fb/
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------
 
MyUSER="SET-MYSQL-USER-NAME"     # USERNAME
MyPASS="SET-PASSWORD"       # PASSWORD
MyHOST="localhost"          # Hostname
 
# Linux bin paths, change this if it can not be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"
 
# Backup Dest directory, change this if you have someother location
DEST="/backup"
 
# Main directory where backup will be stored
MBD="$DEST/mysql"
 
# Get hostname
HOST="$(hostname)"
 
# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"
 
# File to store current backup file
FILE=""
# Store list of databases
DBS=""
 
# DO NOT BACKUP these databases
IGGY="test"
 
[ ! -d $MBD ] && mkdir -p $MBD || :
 
# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST
 
# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"
 
for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];
    then
	for i in $IGGY
	do
	    [ "$db" == "$i" ] && skipdb=1 || :
	done
    fi
 
    if [ "$skipdb" == "-1" ] ; then
	FILE="$MBD/$db.$HOST.$NOW.gz"
	# do all inone job in pipe,
	# connect to mysql using mysqldump for select mysql database
	# and pipe it out to gz file in backup dir :)
        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
    fi
done

Save the script and run it as a cron job:
@daily /path/to/yourmysql.sh

4000+ howtos and counting! If you enjoyed this article, join 45000+ others and get free email updates!

sadique January 10, 2011

Hi Vivek, thanks for such a good script.
in this script you are creating one file for one db. Can I create one file for each table of a db.
File name would be like dbname-filename-date … etc….

Thanks in advance..

Reply

swapnil July 4, 2011

# File to store current backup file
FILE=”"
# Store list of databases
DBS=”"

# DO NOT BACKUP these databases
IGGY=”test”

Hey I want some help on above.
What shall i add in file?
And can I add multiple dbs in DBS and IGGY ?If so? then how?gimme one example.
THanks

Reply

Ryan September 28, 2011

You can enter databases separated with a space here that you don’t want to backup.

Example:
IGGY=”test test2 test3”

Reply

Bangon Kali July 5, 2011

Thanks dude! :D

Reply

Lucas October 6, 2011

You dont need to add any databases to the “file” or “dbs” variables. Those empty values just initialize the variable. If you want to skip databases, use a standard list, no commas etc.
Example:

IGGY="test thisdatabase thatdatabase herdata mydata"

Reply

Conrado October 31, 2011

Hi,

I try this script in Ubuntu 10.04 and works great. When I move to centos 5.6 I get the following error:

No such file or Directory /usr/bin/mysql

I try the line: DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’)” manually and its works great. Any ideas?

Thanks

Reply

Conrado October 31, 2011

Sorry. Mispelld centos 5.4

Reply

paul November 3, 2011

Vivek

I am new to MYSQL. I saw your script. Please help me understand what you are doing
in
MyUSER=”SET-MYSQL-USER-NAME” # USERNAME
MyPASS=”SET-PASSWORD” # PASSWORD
MyHOST=”localhost” # Hostname
eg: variable MyUSER will contain SET-MYSQL-USER-NAME, but i have not see this being used as mysqldump anywhere.

is INFORMATION_SCHEMA backed up in your script ? Is this useful to backup?

-all-databases will dump ALL databases, is there any advantage for backing up
each database?

Thank You

paull

Reply

TurboPT December 1, 2011

Q1: …but i have not see this being used as mysqldump anywhere.
A1: see the last if condition.

Q2a: is INFORMATION_SCHEMA backed up in your script ?
Q2b: Is this useful to backup?
A2a: …if it appears in the show databases result, and it is not set to be skipped in the script, then yes.
A2b: could be. The point is data recovery. Preview that database to see what you could potentially lose if not.

Q3: …any advantage for backing up each database?
A3: this is merely an example that you can tailor to your need(s). Having them separately could be advantageous when not “all databases” need to be restored.

Reply

james o November 11, 2011

thanks! works like a charm! do you rotate or, delete old backups?

Reply

Ashok Kumar April 11, 2012

I want to use this mysql backup script with incremental an weekly full backup.

Can you i use this for this purpose and also facing error during the run this script.

Error:- [: 70: -1: unexpected operator

Please help

Reply

Ashok April 12, 2012

I am facing error during run the script

[: 64: =-1: unexpected operator

Reply

Narendra Gollapilli April 18, 2012

You can the below lines before creating directory for today’s backup.

RDIR=”$DEST/mysql/$(date -d “7 day ago” +”%d%m%Y”)”
RDIR=
if [ -d $RDIR ];
then
rmdir $RDIR;
echo “Removing MySQL backup of date $(date -d “7 day ago” +”%d%m%Y”)”
fi
Thanks,
Narendra Gollapilli

Reply

Narendra Gollapilli April 18, 2012

The above lines i have mentioned for backup rotation for 7 days i.e removing data older than 7 days.

Reply

Leave a Comment

You can use these HTML tags and attributes for UNIX commands or shell scripts: <strong> <em> <ol> <li> <u> <ul> <blockquote> <pre> <a href="" title="">
What is 15 + 13 ?
Please leave these two fields as-is:
Are you a human being? Solve the simple math so we know that you are a human and not a script.



Tagged as: , , , , , , , , , , , , , , ,