≡ Menu

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

Sample Shell Script To Dump All MySQL Databases

{ 6 comments… add one }
  • Farhan Umer December 23, 2011, 2:47 pm

    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 am

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

    • Vivek Gite July 15, 2009, 8:09 pm

      Thanks for the heads-up.

  • XAN April 14, 2009, 8:12 pm

    Thank you Vivek it’s a great work!

  • Vinayak April 13, 2009, 11:25 am

    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 October 2, 2008, 6:26 pm

    Elegant and very useful.
    I love it, thanks.

Security: Are you a robot or human?

Leave a Comment