MySQL Backup Bash Shell Script

Which MySQL backup scripts do I use ? The question has come up a few times already so decided to post what MySQL backup script I am using right now. I’ve used quite a few different ones all using some form of mysqldump Update: script has evolved into mysqlmybackup.sh details here.

The current MySQL backup shell script I use was written by Vivek Gite.  I altered the script to also add hour, minute and seconds in the time stamp for the mysqldump sql backup file. Settings to customise the backup script to your needs are pretty self explanatory.

The mysqldump sql backup files will be located at /backup/mysql by default. Change the location to suite your own needs. Also change CHOWN and CHMOD values if you want the backup sql files to be accessible by a user other than root. You’ll also be able to set which MySQL database names you can ignore in the backup process.

Save below script as mysqlbackup.sh and CHMOD +x the file and use a cronjob to schedule when to run mysqlbackup.sh. I have a scheduled cronjob to automatically backup my MySQL databases (lightly used ones) every 12 hours.

#!/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-%H%M%S")"
 
# 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

Previous:
Next:



MaxCDN Site Acceleration

Get Cost Effective Content Delivery
Unbeatable speed, stats, & price
http://www.maxcdn.com



No Comments

Trackbacks/Pingbacks

  1. vBulletin hosting – List of questions to ask a new web host | vbtechsupport.com - [...] (+ MySQL plugin for MySQL backup) for peace of mind.MySQL automatic backups: I’d also invest in a automated MySQL…
  2. mysqlmybackup.sh - multi feature MySQL database backup and restore script with Percona XtraBackup & Mydumper tool support | vbtechsupport.com - [...] on June 20th, 2011. I compiled the script based on the modified MySQL mysqldump backup script by Vivek Gite…

Leave a Comment