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

by on April 26, 2008 · 6 comments

+ Download this script

+ Modify settings according to your setup

+ Install cron job as follows to run script every hour

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

Sample Shell Script To Dump All MySQL Databases

  1. #!/bin/bash
  2. # A simple shell script to backup all MySQL Server Database
  3. # Dump all MySQL database every hour from raid10 db disk to /nas/mysql
  4. # Each dump will be line as follows:
  5. # Directory: /nas/mysql/mm-dd-yyyy
  6. # File: mysql-DBNAME.04-25-2008-14:23:40.gz
  7. # Full path: /nas/mysql/mm-dd-yyyy/mysql-DBNAME.04-25-2008-14:23:40.gz
  8. # -------------------------------------------------------------------------
  9. # Copyright (c) 2005 nixCraft project <http://cyberciti.biz/fb/>
  10. # This script is licensed under GNU GPL version 2.0 or above
  11. # -------------------------------------------------------------------------
  12. # This script is part of nixCraft shell script collection (NSSC)
  13. # Visit http://bash.cyberciti.biz/ for more information.
  14. # -------------------------------------------------------------------------
  15. # Last updated: Jul-16-2009 - Fixed a small bug
  16. # - Make sure NAS really mounted on $NAS
  17. # -------------------------------------------------------------------------
  18. NOW=$(date +"%m-%d-%Y") # mm-dd-yyyy format
  19. FILE="" # used in a loop
  20. NASBASE="/nas" # NAS Mount Point
  21. BAK="${NAS}/mysql/${NOW}" # Path to backup dir on $NAS
  22.  
  23. ### Server Setup ###
  24. #* MySQL login user name *#
  25. MUSER="root"
  26.  
  27. #* MySQL login PASSWORD name *#
  28. MPASS="YOUR-PASSWORD"
  29.  
  30. #* MySQL login HOST name *#
  31. MHOST="127.0.0.1"
  32.  
  33. #* MySQL binaries *#
  34. MYSQL="$(which mysql)"
  35. MYSQLDUMP="$(which mysqldump)"
  36. GZIP="$(which gzip)"
  37.  
  38. # Make sure nas is really mounted
  39. mount | awk '{ print $3}' |grep -w $NASBASE >/dev/null
  40. if [ $? -ne 0 ]
  41. then
  42. echo "Error: NAS not mounted at $NASBASE, please mount NAS server to local directory and try again."
  43. exit 99
  44. fi
  45.  
  46. ### NAS MUST BE MOUNTED in Advance ###
  47. # assuming that /nas is mounted via /etc/fstab
  48. if [ ! -d $BAK ]; then
  49. mkdir -p $BAK
  50. else
  51. :
  52. fi
  53.  
  54. # get all database listing
  55. DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
  56.  
  57. # start to dump database one by one
  58. for db in $DBS
  59. do
  60. FILE=$BAK/mysql-$db.$NOW-$(date +"%T").gz
  61. # gzip compression for each backup file
  62. $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
  63. done


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

Click here to subscribe via email.

  • Samir

    Elegant and very useful.
    I love it, thanks.

  • Vinayak

    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.

  • XAN

    Thank you Vivek it’s a great work!

  • fRank

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

  • Vivek Gite

    Thanks for the heads-up.

  • Farhan Umer

    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
    

Previous Script:

Next Script: