≡ Menu

Shell Script To Backup MySql Database Server

Save the script and run it as a cron job:
@daily /path/to/yourmysql.sh

{ 54 comments… add one }
  • Rodrigo November 29, 2010, 4:56 pm

    Does it output some information if something goes wrong? i´m trying to write to a logfile, just wondering it outputs success or failure results? Sorry, i just lack the skills to tell if it does so.

    TIA,
    Rodrigo

  • Martin W November 9, 2010, 6:25 pm

    F##ing Great! Thanks a lot for the good work!

  • tim September 16, 2010, 4:18 pm

    This script is a lifesaver, thank you.

    I do have a quick question, for the IGGY, do you put the databases you do not want backed up just separated by commas?

    • Vivek Gite September 19, 2010, 3:36 pm

      No, just separated by white space:

      IGGY="test db2 db4"
  • Chris August 23, 2010, 2:22 am

    For some reason when I tried this it seemed to work — it created the .gz files in my backup directory. But when I tried an experiment of dropping one of the databases and then using the backed-up .gz file to restore from (via cpanel utility), it created an empty database with no tables in it. Not sure why :-(

    • Josh September 20, 2010, 11:54 pm

      I have this exact same problem. Does anyone have any ideas about this?

      • Vivek Gite September 21, 2010, 7:05 pm

        If your file name is db2.11_02_35am.gz, then use the following commands to reinstall the database:

        gunzip db2.11_02_35am.gz
        ls -l db2*
        mysql -u root -p -e 'create database db2;'
        mysql -u root -p db2 < db2.11_02_35am
  • Gaurav Talwar July 13, 2010, 9:31 am

    Excellent script .. Thanks mate

  • Paul d'Aoust July 8, 2010, 7:17 pm

    Thanks so much for taking the time to put up this script; you’re a real life-saver. I realised today (after a big scare with fsck) that I have no facility for backing up my databases on my development machine! I shall be adding this forthwith.

  • Terry April 29, 2010, 11:49 pm

    Thank you for the handy script.

    At least on our Fedora 10 installation, the gzip binary looks for an env var GZIP and uses it as a default argument list if it’s set.

    So this
    $GZIP -9
    is equivalent to this (for our binaries)
    /bin/gzip /bin/gzip -9

    Since I wasn’t running the script as root, this manifested itself as a somewhat misleading permissions error that took a while to track down since I mistook the /bin/gzip.gz as a reference to a helper binary (like fsck.ext2, for instance):
    gzip: /bin/gzip.gz: Permission denied

    Could I request that something besides GZIP be used (e.g. GZIPBIN, GZ)?

    Thanks!

  • shreyas April 27, 2010, 7:02 pm

    if i need to backup each database in seperate file and want to know whether the databse dump was succesful or not.

    could anyone please help with that

  • Avin March 30, 2010, 5:47 am

    Nice Post.. I have use this post as you say and It work exactly as I want. but Is it possible to copy backup directory to other server for security If this server is totally crash? Only daily folder has to copy to other server.

  • Zeb Evans February 27, 2010, 5:02 am

    Just a note that will hopefully help others. I’m just starting to learn shell scripting all my other ones have been using phpcli so take this with a grain of salt. I was getting a bunch of these errors.

    ==: unexpected operator

    I had to change these lines and use the single = comparison instead of ==

    [ “$db” = “$i” ] && skipdb=1 || :
    done
    fi

    if [ “$skipdb” = “-1” ] ; then

    • Trev April 13, 2010, 8:38 pm

      I would think that changing the operator to = would be assigning the “$i” to “$db”

      From my knowledge of bash scripting, or the use of bourne shell commands, it is necessary for the use of == for comparisons as well as the spaces around the subscript operators.

      Did changing those to = instead of == allow it to run properly?

  • Deven J February 4, 2010, 3:56 pm

    Undoubtedly, very simple and useful.

  • No Brains!! September 9, 2009, 12:44 pm

    Forgive the ignorance but would you have to stop the mySQL service before running this script?

    • Vivek Gite September 11, 2009, 11:45 am

      No

      • sukhbir September 29, 2009, 5:34 am

        this script is very good.

        Can anybody having a script to extract database from MYSQLDUMP.

        Regards,

        • Hunner October 21, 2009, 4:05 am

          `mysql -u $user -p < backup.dump` will take a dump file and put it back into the DB.

  • Brian Nettles July 16, 2009, 9:50 pm

    Incredible!!!!!!!

    I have wasted hours trying to do this in perl. One small copy and paste from your site and all of the mysql databases are now being automatically backed up. Thank you so much. You are worth your weight in gold.

  • fRank May 15, 2009, 10:13 am
    #!/bin/bash
    NOW=`date +"%Y-%m"`;
    BACKUPDIR="location/of/your/backup/dir/$NOW";
    ### Server Setup ###
    #* MySQL login user name *#
    MUSER="user";
    #* MySQL login PASSWORD name *#
    MPASS="pass";
    #* MySQL login HOST name *#
    MHOST="your-mysql-ip";
    MPORT="your-mysql-port";
    # DO NOT BACKUP these databases
    IGNOREDB="
    information_schema
    mysql
    test
    "
    #* MySQL binaries *#
    MYSQL=`which mysql`;
    MYSQLDUMP=`which mysqldump`;
    GZIP=`which gzip`;
    # assuming that /nas is mounted via /etc/fstab
    if [ ! -d $BACKUPDIR ]; then
      mkdir -p $BACKUPDIR
    else
     :
    fi
    # get all database listing
    DBS="$(mysql -u $MUSER -p$MPASS -h $MHOST -P $MPORT -Bse 'show databases')"
    # SET DATE AND TIME FOR THE FILE
    NOW=`date +"d%dh%Hm%Ms%S"`; # day-hour-minute-sec format
    # start to dump database one by one
    for db in $DBS
    do
            DUMP="yes";
            if [ "$IGNOREDB" != "" ]; then
                    for i in $IGNOREDB # Store all value of $IGNOREDB ON i
                    do
                            if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then
                                    DUMP="NO";         # SET value of DUMP to "no"
                                    #echo "$i database is being ignored!";
                            fi
                    done
            fi
            if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database
                    FILE="$BACKUPDIR/$NOW-$db.gz";
                    echo "BACKING UP $db";
                    $MYSQLDUMP --add-drop-database --opt --lock-all-tables -u $MUSER -p$MPASS -h $MHOST -P $MPORT $db | gzip > $FILE
            fi
    done
    
  • Gilnei Moraes April 22, 2009, 1:04 pm

    Just my one cent:
    run rsync daemon using the backup directory just to be sure you are playing safe. Is worthless to backup MySQL not having a copy in remote location in case of a server crash.

  • DVD March 20, 2009, 2:13 pm

    Nice script.

  • ajax December 7, 2008, 6:09 pm

    all, you are done great server works,

    thx

  • Stephen Reese August 23, 2008, 3:19 am

    Great script! I really like the addition of the which command.

    • ron May 15, 2010, 11:48 pm

      this looks super cool, however can anyone explain me the meaning of

      MYSQL=”$(which mysql)”
      MYSQLDUMP=”$(which mysqldump)”
      CHOWN=”$(which chown)”
      CHMOD=”$(which chmod)”
      GZIP=”$(which gzip)”

      what does the which command do ? do we have to write tht

      • marties June 17, 2010, 3:06 pm

        it gives the full path of the executable

        which mysql
        /usr/bin/mysql

  • Thiago July 3, 2008, 2:13 pm

    It`s very good

    thanks

Security: Are you a robot or human?

Leave a Comment