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

+ 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

1 2	# 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.
# -------------------------------------------------------------------------
# Last updated: Jul-16-2009 - Fixed a small bug
#			    - Make sure NAS really mounted on $NAS
# -------------------------------------------------------------------------
NOW=$(date +"%m-%d-%Y") # mm-dd-yyyy format
FILE=""			# used in a loop
NASBASE="/nas"		# NAS Mount Point	
BAK="${NAS}/mysql/${NOW}" # Path to backup dir on $NAS

### 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)"

# Make sure nas is really mounted 
mount | awk '{ print $3}' |grep -w $NASBASE >/dev/null 
if [ $? -ne 0 ] 
then
	echo "Error: NAS not mounted at $NASBASE, please mount NAS server to local directory and try again."
	exit 99
fi

### NAS MUST BE MOUNTED in Advance ###
# 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

#!/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

# -------------------------------------------------------------------------

# 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.

# -------------------------------------------------------------------------

# Last updated: Jul-16-2009 - Fixed a small bug

# - Make sure NAS really mounted on $NAS

# -------------------------------------------------------------------------

NOW=$(date +"%m-%d-%Y") # mm-dd-yyyy format

FILE="" # used in a loop

NASBASE="/nas" # NAS Mount Point

BAK="${NAS}/mysql/${NOW}" # Path to backup dir on $NAS

### 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)"

# Make sure nas is really mounted

mount | awk '{ print $3}' |grep -w $NASBASE >/dev/null

if [ $? -ne 0 ]

then

echo "Error: NAS not mounted at $NASBASE, please mount NAS server to local directory and try again."

exit 99

### NAS MUST BE MOUNTED in Advance ###

# assuming that /nas is mounted via /etc/fstab

if [ ! -d $BAK ]; then

mkdir -p $BAK

else

# 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

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

6 comment

Took the idea from the same script and made this one:

save all mysql databases into a compressed file like /root/mysqlbackup.23.12.11.tar.gz

#!/bin/bash

#############
#Setting File Name
#############

d=$(date | awk '{ print $3 }')
m=$(date +"%m")
y=$(date +%Y | cut -c3-4)
#echo "myfile.$d.$m.$y"

#############
#credentials
#############

MUSER="root"
MPASS="youpassword"
TPATH="/tmp/mysqlbackup"
BPATH="/root"

############
#Tool
############

MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
TAR="$(which tar)"
                                      
############
#Create tmp dir
############

if [ ! -d $TPATH ]; then
  mkdir -p $TPATH
echo "Directory Created"
else
echo "Directory Already Exists" 
fi

############
#backup
############

DBS="$(mysql -u root -p$MPASS -Bse 'show databases')"
for db in $DBS
do
$MYSQLDUMP -u $MUSER -p$MPASS $db > $TPATH/$db.sql
done

############
#Compress and remove tmp dir
############

echo "Compressing Databases"
$TAR zcf $BPATH/mysqlbackups.$d.$m.$y.tar.gz $TPATH

echo "Removing Temparory Directory"
/bin/rm -rf $TPATH

#!/bin/bash

#############

#Setting File Name

#############

d=$(date | awk '{ print $3 }')

m=$(date +"%m")

y=$(date +%Y | cut -c3-4)

#echo "myfile.$d.$m.$y"

#############

#credentials

#############

MUSER="root"

MPASS="youpassword"

TPATH="/tmp/mysqlbackup"

BPATH="/root"

############

#Tool

############

MYSQL="$(which mysql)"

MYSQLDUMP="$(which mysqldump)"

TAR="$(which tar)"

############

#Create tmp dir

############

if [ ! -d $TPATH ]; then

mkdir -p $TPATH

echo "Directory Created"

else

echo "Directory Already Exists"

############

#backup

############

DBS="$(mysql -u root -p$MPASS -Bse 'show databases')"

for db in $DBS

$MYSQLDUMP -u $MUSER -p$MPASS $db > $TPATH/$db.sql

done

############

#Compress and remove tmp dir

############

echo "Compressing Databases"

$TAR zcf $BPATH/mysqlbackups.$d.$m.$y.tar.gz $TPATH

echo "Removing Temparory Directory"

/bin/rm -rf $TPATH

Better remove this line FILE=”fs-full-$NOW.tar.gz” because you already used FILE=$BAK/mysql-$db.$NOW-$(date +”%T”).gz

Vivek Gite says:
July 15, 2009 at 8:09 pm
Thanks for the heads-up.
Reply

Thank you Vivek it’s a great work!

This script is very much useful.

After taking backup I want to dump the same in another Database [In some aother machine] How to do that?

The database name and everything should remain same in the another DB also.

My intention is to create Mirror Database.

Elegant and very useful.
I love it, thanks.

nixCraft

nixCraft

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

Sample Shell Script To Dump All MySQL Databases

Share this on:

6 comment

Leave a Comment Cancel reply