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

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
Get the latest tutorials on SysAdmin, Linux/Unix, Open Source, and DevOps topics:
CategoryList of Unix and Linux commands
File Management cat
Firewall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilities dig host ip nmap
OpenVPN CentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Manager apk apt
Processes Management bg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searching grep whereis which
User Information groups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPN CentOS 8 Debian 10 Firewall Ubuntu 20.04
6 comments… add one
  • Farhan Umer Dec 23, 2011 @ 14:47

    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
    
  • fRank May 13, 2009 @ 11:04

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

  • XAN Apr 14, 2009 @ 20:12

    Thank you Vivek it’s a great work!

  • Vinayak Apr 13, 2009 @ 11:25

    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.

  • Samir Oct 2, 2008 @ 18:26

    Elegant and very useful.
    I love it, thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.