Shell Script To Backup MySql Database Server

by on April 9, 2008 · 53 comments

  1. #!/bin/bash
  2. # Shell script to backup MySql database
  3. # To backup Nysql databases file to /backup dir and later pick up by your
  4. # script. You can skip few databases from backup too.
  5. # For more info please see (Installation info):
  6. # http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html
  7. # Last updated: Aug - 2005
  8. # --------------------------------------------------------------------
  9. # This is a free shell script under GNU GPL version 2.0 or above
  10. # Copyright (C) 2004, 2005 nixCraft project
  11. # Feedback/comment/suggestions : http://cyberciti.biz/fb/
  12. # -------------------------------------------------------------------------
  13. # This script is part of nixCraft shell script collection (NSSC)
  14. # Visit http://bash.cyberciti.biz/ for more information.
  15. # -------------------------------------------------------------------------
  16.  
  17. MyUSER="SET-MYSQL-USER-NAME" # USERNAME
  18. MyPASS="SET-PASSWORD" # PASSWORD
  19. MyHOST="localhost" # Hostname
  20.  
  21. # Linux bin paths, change this if it can not be autodetected via which command
  22. MYSQL="$(which mysql)"
  23. MYSQLDUMP="$(which mysqldump)"
  24. CHOWN="$(which chown)"
  25. CHMOD="$(which chmod)"
  26. GZIP="$(which gzip)"
  27.  
  28. # Backup Dest directory, change this if you have someother location
  29. DEST="/backup"
  30.  
  31. # Main directory where backup will be stored
  32. MBD="$DEST/mysql"
  33.  
  34. # Get hostname
  35. HOST="$(hostname)"
  36.  
  37. # Get data in dd-mm-yyyy format
  38. NOW="$(date +"%d-%m-%Y")"
  39.  
  40. # File to store current backup file
  41. FILE=""
  42. # Store list of databases
  43. DBS=""
  44.  
  45. # DO NOT BACKUP these databases
  46. IGGY="test"
  47.  
  48. [ ! -d $MBD ] && mkdir -p $MBD || :
  49.  
  50. # Only root can access it!
  51. $CHOWN 0.0 -R $DEST
  52. $CHMOD 0600 $DEST
  53.  
  54. # Get all database list first
  55. DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"
  56.  
  57. for db in $DBS
  58. do
  59. skipdb=-1
  60. if [ "$IGGY" != "" ];
  61. then
  62. for i in $IGGY
  63. do
  64. [ "$db" == "$i" ] && skipdb=1 || :
  65. done
  66. fi
  67.  
  68. if [ "$skipdb" == "-1" ] ; then
  69. FILE="$MBD/$db.$HOST.$NOW.gz"
  70. # do all inone job in pipe,
  71. # connect to mysql using mysqldump for select mysql database
  72. # and pipe it out to gz file in backup dir :)
  73. $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
  74. fi
  75. done

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



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

Click here to subscribe via email.

  • Pingback: mysql in fedora core 4 - The UNIX Forums

  • Thiago

    It`s very good

    thanks

  • Stephen Reese

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

  • ajax

    all, you are done great server works,

    thx

  • DVD

    Nice script.

  • Gilnei Moraes

    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.

  • fRank
    #!/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
    
  • Brian Nettles

    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.

  • No Brains!!

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

  • Vivek Gite

    No

  • sukhbir

    this script is very good.

    Can anybody having a script to extract database from MYSQLDUMP.

    Regards,

  • Hunner

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

  • Deven J

    Undoubtedly, very simple and useful.

  • Zeb Evans

    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

  • Avin

    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.

  • Trev

    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?

  • shreyas

    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

  • Terry

    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!

  • ron

    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

    it gives the full path of the executable

    which mysql
    /usr/bin/mysql

Previous Script:

Next Script: