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.

  • TurboPT

    Q1: …but i have not see this being used as mysqldump anywhere.
    A1: see the last if condition.

    Q2a: is INFORMATION_SCHEMA backed up in your script ?
    Q2b: Is this useful to backup?
    A2a: …if it appears in the show databases result, and it is not set to be skipped in the script, then yes.
    A2b: could be. The point is data recovery. Preview that database to see what you could potentially lose if not.

    Q3: …any advantage for backing up each database?
    A3: this is merely an example that you can tailor to your need(s). Having them separately could be advantageous when not “all databases” need to be restored.

  • Ashok Kumar

    I want to use this mysql backup script with incremental an weekly full backup.

    Can you i use this for this purpose and also facing error during the run this script.

    Error:- [: 70: -1: unexpected operator

    Please help

  • Ashok

    I am facing error during run the script

    [: 64: =-1: unexpected operator

  • Narendra Gollapilli

    You can the below lines before creating directory for today’s backup.

    RDIR=”$DEST/mysql/$(date -d “7 day ago” +”%d%m%Y”)”
    RDIR=
    if [ -d $RDIR ];
    then
    rmdir $RDIR;
    echo “Removing MySQL backup of date $(date -d “7 day ago” +”%d%m%Y”)”
    fi
    Thanks,
    Narendra Gollapilli

  • Narendra Gollapilli

    The above lines i have mentioned for backup rotation for 7 days i.e removing data older than 7 days.

  • mccoy

    hi,

    instead of # DO NOT BACKUP these databases,
    how can i do it as they other way around? (selecting specific database).

    i am very new to linux. please help. thanks alot!

  • Arvind

    you can do so by modifying

    IGGY=””

    DBS=””
    or
    DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’ |grep )”
    as it will return a list of your databases only which contain the given text.

    It works for my without any error as i saved this as .sh file

  • Niftyapple

    Here is a few tips for things that I ran into.

    First off, root doesnt have access to locked tables, so this is what I did to fix that.

    Edit this line:
    $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE

    To:
    $MYSQLDUMP –skip-lock-tables -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE

    By adding the –skip-lock-tables, the command will ignore the locktable feedback and continue on what it can actually do.

    Secondly, I also was recieving an error like “\r': command not found”. This is caused from the way DOS(windows) writes a return key. This can be fixed by the following 2 commands:

    apt-get install dos2unix #if it is not installed already
    dos2unix /path/to/file.sh

    I hope this helps someone.
    Enjoy

  • Peter

    Nice little script, handy and quick to use.

    Ashok: Try to delete one of the equal signs on the actual rows you are having problems with.

  • Pedro Braconnot

    Using Narendra Gollapilli’s prune lines in Ubuntu Server 11.04, had to change from “rmdir” to:
    rm -rf $RDIR;
    First day so I did not test the seven days cicle, other than that it is working. Also have to replace html left and right “” for “.
    About Niftyapple’s –skip-lock-tables note that html on this page replaced — for –.
    The correct is –skip-lock-tables
    Thanks!

  • Pedro Braconnot

    I mean TWO dashes, sorry it did with me again – – .
    Wordpress changes a lot of code stuff…
    two – to one —

  • Daniel Clarks

    great!

  • Fachrian Noor

    Can you help my problem ?

    in line 55 = DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’)”

    when i try to execute, this syntax has a problem, can anyone help to fix it ?

Previous Script:

Next Script: