Script Categories

Shell Script To Dump All MySQL Databases Every Hour To NAS Storage

Posted in Backup » MySQL

+ Download this script

+ Modify settings according to your setup

+ Install cron job as follows to run script every hour

# Backup database every 1 hr to /nas
@hourly /root/scripts/db1hr.backup.sh >/dev/null 2>&1
 

Sample Shell Script To Dump All MySQL Databases

#!/bin/bash
# A simple shell script to backup all MySQL Server Database
# Dump all MySQL database every hour from raid10 db disk to /nas/mysql
# Each dump will be line as follows:
# Directory:  /nas/mysql/mm-dd-yyyy
# File: mysql-DBNAME.04-25-2008-14:23:40.gz
# Full path: /nas/mysql/mm-dd-yyyy/mysql-DBNAME.04-25-2008-14:23:40.gz
# -------------------------------------------------------------------------
# Copyright (c) 2005 nixCraft project <http://cyberciti.biz/fb/>
# This script is licensed under GNU GPL version 2.0 or above
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------
NOW=$(date +"%m-%d-%Y") # mm-dd-yyyy format
FILE="fs-full-$NOW.tar.gz"
BAK="/nas/mysql/${NOW}"
 
### Server Setup ###
#* MySQL login user name *#
MUSER="root"
 
#* MySQL login PASSWORD name *#
MPASS="YOUR-PASSWORD"
 
#* MySQL login HOST name *#
MHOST="127.0.0.1"
 
#* MySQL binaries *#
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
 
# assuming that /nas is mounted via /etc/fstab
if [ ! -d $BAK ]; then
  mkdir -p $BAK
else
 :
fi
# get all database listing
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
 
# start to dump database one by one
for db in $DBS
do
 FILE=$BAK/mysql-$db.$NOW-$(date +"%T").gz
 # gzip compression for each backup file
 $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done

Download - Email this to a friend - Printable version

Leave a Reply

We encourage your comments, and suggestions. But please stay on topic, be polite, and avoid spam. Thank you very much for stopping by our site!

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Tags: , , , , , , , , , , , , , , ~ Last updated on: April 26, 2008