Shell Script To Backup MySql Database Server

#!/bin/bash
# Shell script to backup MySql database 
# To backup Nysql databases file to /backup dir and later pick up by your 
# script. You can skip few databases from backup too.
# For more info please see (Installation info):
# https://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html 
# Last updated: Aug - 2005
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2004, 2005 nixCraft project
# Feedback/comment/suggestions : http://cyberciti.biz/fb/
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------
 
MyUSER="SET-MYSQL-USER-NAME"     # USERNAME
MyPASS="SET-PASSWORD"       # PASSWORD 
MyHOST="localhost"          # Hostname
 
# Linux bin paths, change this if it can not be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"
 
# Backup Dest directory, change this if you have someother location
DEST="/backup"
 
# Main directory where backup will be stored
MBD="$DEST/mysql"
 
# Get hostname
HOST="$(hostname)"
 
# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"
 
# File to store current backup file
FILE=""
# Store list of databases 
DBS=""
 
# DO NOT BACKUP these databases
IGGY="test"
 
[ ! -d $MBD ] && mkdir -p $MBD || :
 
# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST
 
# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"
 
for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];
    then
	for i in $IGGY
	do
	    [ "$db" == "$i" ] && skipdb=1 || :
	done
    fi
 
    if [ "$skipdb" == "-1" ] ; then
	FILE="$MBD/$db.$HOST.$NOW.gz"
	# do all inone job in pipe,
	# connect to mysql using mysqldump for select mysql database
	# and pipe it out to gz file in backup dir :)
        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
    fi
done

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

🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source, and DevOps topics via:
Category List of Unix and Linux commands
Disk space analyzers ncdu pydf
File Management cat
Firewall Alpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilities NetHogs dig host ip nmap
OpenVPN CentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Manager apk apt
Processes Management bg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searching grep whereis which
User Information groups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPN Alpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
55 comments… add one
  • Luis Anaya Oct 17, 2013 @ 20:33

    This is a really nice script although I did some changes for it, nothing major:

    – Commented the CHOWN. For some reason is was doing a root wide CHOWN, being that I am not running the backups as root, I do not need to CHOWN the files anyway, they’ll be owned by the administrative account.
    – I had to add the list of databases that I wanted not to back up, I though that just adding the ones that I wanted would exclude the ones that I did not wanted to.

    But, it is a very handy script and I appreciate you posting it. Thanks.

  • Fachrian Noor May 13, 2013 @ 0:07

    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 ?

  • Daniel Clarks Dec 5, 2012 @ 21:18

    great!

  • Pedro Braconnot Nov 23, 2012 @ 1:02

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

  • Pedro Braconnot Nov 23, 2012 @ 0:51

    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!

  • Peter Nov 9, 2012 @ 14:08

    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.

  • Niftyapple Oct 17, 2012 @ 17:14

    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

  • Arvind Aug 18, 2012 @ 6:54

    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

  • mccoy Jul 23, 2012 @ 20:34

    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!

  • Narendra Gollapilli Apr 18, 2012 @ 11:50

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

  • Narendra Gollapilli Apr 18, 2012 @ 11:49

    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

  • Ashok Apr 12, 2012 @ 5:25

    I am facing error during run the script

    [: 64: =-1: unexpected operator

  • Ashok Kumar Apr 11, 2012 @ 17:56

    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

  • james o Nov 11, 2011 @ 18:20

    thanks! works like a charm! do you rotate or, delete old backups?

  • paul Nov 3, 2011 @ 19:34

    Vivek

    I am new to MYSQL. I saw your script. Please help me understand what you are doing
    in
    MyUSER=”SET-MYSQL-USER-NAME” # USERNAME
    MyPASS=”SET-PASSWORD” # PASSWORD
    MyHOST=”localhost” # Hostname
    eg: variable MyUSER will contain SET-MYSQL-USER-NAME, but i have not see this being used as mysqldump anywhere.

    is INFORMATION_SCHEMA backed up in your script ? Is this useful to backup?

    -all-databases will dump ALL databases, is there any advantage for backing up
    each database?

    Thank You

    paull

    • TurboPT Dec 1, 2011 @ 18:18

      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.

  • Conrado Oct 31, 2011 @ 14:17

    Hi,

    I try this script in Ubuntu 10.04 and works great. When I move to centos 5.6 I get the following error:

    No such file or Directory /usr/bin/mysql

    I try the line: DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’)” manually and its works great. Any ideas?

    Thanks

    • Conrado Oct 31, 2011 @ 14:18

      Sorry. Mispelld centos 5.4

  • Lucas Oct 6, 2011 @ 7:42

    You dont need to add any databases to the “file” or “dbs” variables. Those empty values just initialize the variable. If you want to skip databases, use a standard list, no commas etc.
    Example:

    IGGY="test thisdatabase thatdatabase herdata mydata"
  • Bangon Kali Jul 5, 2011 @ 14:29

    Thanks dude! :D

  • swapnil Jul 4, 2011 @ 4:45

    # File to store current backup file
    FILE=””
    # Store list of databases
    DBS=””

    # DO NOT BACKUP these databases
    IGGY=”test”

    Hey I want some help on above.
    What shall i add in file?
    And can I add multiple dbs in DBS and IGGY ?If so? then how?gimme one example.
    THanks

    • Ryan Sep 28, 2011 @ 20:09

      You can enter databases separated with a space here that you don’t want to backup.

      Example:
      IGGY=”test test2 test3”

  • sadique Jan 10, 2011 @ 10:19

    Hi Vivek, thanks for such a good script.
    in this script you are creating one file for one db. Can I create one file for each table of a db.
    File name would be like dbname-filename-date … etc….

    Thanks in advance..

  • Eduardo Nov 30, 2010 @ 1:11

    Is possible send mysql database to e-mail with daily cron?

    thanks

  • Rodrigo Nov 29, 2010 @ 16:56

    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 Nov 9, 2010 @ 18:25

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

  • tim Sep 16, 2010 @ 16:18

    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 Sep 19, 2010 @ 15:36

      No, just separated by white space:

      IGGY="test db2 db4"
  • Chris Aug 23, 2010 @ 2:22

    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 Sep 20, 2010 @ 23:54

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

      • 🐧 Vivek Gite Sep 21, 2010 @ 19:05

        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 
        						
  • Gaurav Talwar Jul 13, 2010 @ 9:31

    Excellent script .. Thanks mate

  • Paul d'Aoust Jul 8, 2010 @ 19:17

    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 Apr 29, 2010 @ 23:49

    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 Apr 27, 2010 @ 19:02

    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 Mar 30, 2010 @ 5:47

    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 Feb 27, 2010 @ 5:02

    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 Apr 13, 2010 @ 20:38

      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?

      • Pedro Braconnot Dec 12, 2016 @ 23:29

        Zeb is right, interesting, the double equal worked for me in Ubuntu 16.04 but did not work in Ubuntu 14.04 (not the same bash versions).
        Actually, to declare or fill a variable you should not leave any space like:
        $db=$i

        This script is still useful almost 2017 so thanks again.

  • Deven J Feb 4, 2010 @ 15:56

    Undoubtedly, very simple and useful.

  • No Brains!! Sep 9, 2009 @ 12:44

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

    • 🐧 Vivek Gite Sep 11, 2009 @ 11:45

      No

      • sukhbir Sep 29, 2009 @ 5:34

        this script is very good.

        Can anybody having a script to extract database from MYSQLDUMP.

        Regards,

        • Hunner Oct 21, 2009 @ 4:05

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

  • Brian Nettles Jul 16, 2009 @ 21:50

    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
    #!/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 Apr 22, 2009 @ 13:04

    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 Mar 20, 2009 @ 14:13

    Nice script.

  • ajax Dec 7, 2008 @ 18:09

    all, you are done great server works,

    thx

  • Stephen Reese Aug 23, 2008 @ 3:19

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

    • ron May 15, 2010 @ 23:48

      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 Jun 17, 2010 @ 15:06

        it gives the full path of the executable

        which mysql
        /usr/bin/mysql

  • Thiago Jul 3, 2008 @ 14:13

    It`s very good

    thanks

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.