≡ Menu

MySql backup script

This mysql database shell (bash download link at the end of script) script can be used:

1) To backup databases file /backup dir and later pick up by your script

2) You can skip few databases from backup!

Download script from above url.

Open file backup.bash.txt and customize it:
MyUSER=”YOUR-MYSQL-USER” # mysql username
MyPASS=”YOUR-MYSQL-USER-PASSWORD” # mysql password
MyHOST=”localhost” # mysql Hostname

Optional setup the name of database which you do not wish to backup:
IGGY="test db2 db3"

Rename backup.bash.txt file to mysqlbackup.bash and execute it as follows
$ ./mysqlbackup.bash

You may need to setup a cron job to take backup everyday.

Share this on:

Your support makes a big difference:
I have a small favor to ask. More people are reading the nixCraft. Many of you block advertising which is your right, and advertising revenues are not sufficient to cover my operating costs. So you can see why I need to ask for your help. The nixCraft, takes a lot of my time and hard work to produce. If you use nixCraft, who likes it, helps me with donations:
Become a Supporter →    Make a contribution via Paypal/Bitcoin →   

Don't Miss Any Linux and Unix Tips

Get nixCraft in your inbox. It's free:



{ 23 comments… add one }
  • Anonymous August 24, 2005, 9:16 pm

    the link to the script is missing? :)

  • Vivek August 24, 2005, 11:01 pm

    Opps.

    Thanks, just fixed URL.

  • NINjak October 23, 2005, 5:52 pm

    Great mysql backup script, a big thanks for it…

    I was thinking about it to write it myself but this is (almost) what i wanted.

  • Anonymous December 9, 2005, 12:12 pm

    i am not getting how to use that script canu plz expalin . My user name in “root” hostname is napta

  • nixcraft December 9, 2005, 1:36 pm

    First download file at shell prompt (login as root user)

    wget http://cyberciti.biz/download/bash_scripting/backup/mysql-backup.bash

    2) Edit file in vi editor
    vi mysql-backup.bash

    Setup three variable as follows:

    MyUSER=”root”
    MyPASS=”SET-MYSQL-PASSWORD”
    MyHOST=”napta”

    3) Save script and exit shell prompt

    4) Setup execute permission
    chmod +x mysql-backup.bash

    5) Execute script
    ./mysql-backup.bash

    6)In few mintues backup will be dumped in
    /backup/mysql directory

    If you have more questions feel free to reply back

  • Jette February 25, 2007, 10:29 am

    When running this script in cron, I get:

    Line 56: -u: command not found

    Line 56 is:
    DBS=”$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse ‘show databases’)”

    It works fine, when I run it by hand.

    ??

  • nixCraft February 25, 2007, 1:56 pm

    Make sure variable MYSQL is pointing to correct mysql client

  • Jette February 25, 2007, 7:15 pm

    Problem solved…

    When running the script in cron, you must replace the ‘which’ commands with the actual paths to chown, chmod, mysql etc…

    Tip to FreeBSD users:
    Replace this line…
    $CHOWN 0.0 -R $DESTT
    ..with…
    $CHOWN -R root $DEST

    This script is simple and easy to use. It works like a charm. Thanks :-)

  • Jack March 12, 2007, 2:53 pm

    when run in prompt works fine (./mysql-backup.bash)

    But why it does not work in crontab ?
    in /var/log/cron , the only message we have is “(root) CMD (mysql_backup.bash)

  • nixCraft March 12, 2007, 5:28 pm

    Jack,

    Setup correct PATH variable for cronjob and it should work. Or modify script and specify bin path as per your UNIX/BSD/Linux distro i.e.
    MYSQL="/usr/bin/mysql"
    MYSQLDUMP="/usr/bin/mysqldump"
    CHOWN="/bin/chown"
    CHMOD="/bin/chmod"
    GZIP="/bin/gzip"

  • Jack March 12, 2007, 8:57 pm

    nixcraf ,
    I tried exactly as you wrote and it did not work.
    When I tried this (I have FreeBSD 6.x , mysql 4.x):
    MYSQL=”/usr/local/bin/mysql”
    MYSQLDUMP=”/usr/local/bin/mysqldump”
    CHOWN=”/usr/sbin/chown”
    CHMOD=”/bin/chmod”
    GZIP=”/usr/bin/gzip”

    Anyway, Thanks ,
    It works fine in crontab now .

  • Najm May 24, 2007, 1:20 pm

    Hi,

    I really do appreciate the work but, please if you can help me to work on WinXP or from where I can find the backup script for windows operating system. Thanks in advance.

  • Marten June 30, 2007, 10:22 am

    the link to the script is broken.

  • nixCraft June 30, 2007, 2:09 pm

    Marten,

    I don’t see a problem; I can browse the link.

  • Ciprian October 4, 2007, 6:36 am

    Hello guys.

    I am looking up how to backup a table into a specified folder/file (my own path) of my remote server.
    The purpose is I want to change the db and tables CHARSET to UTF-8 automatically (this would be done by the installer script of the app I distribute).
    At this point, I came into this problem:
    mysql_query(“SELECT * FROM config INTO OUTFILE ‘./test.txt’ FIELDS TERMINATED BY ‘,’;”, $conn);
    I put the test.txt file into the php script directory, I CHMODed it to 777, but no luck. What am I missing?

    Is there a way to actually dump the database or each table into a specified folder (backup) of a script running on my server? (eg. public_html/script/db_backup/test.txt)?

  • karo October 20, 2007, 12:20 am

    What is the

    # Store list of databases
    DBS=””

    leave bank or can insert an option

  • Ravi Shankar November 6, 2007, 7:42 am

    Hi Dear ,
    I am glad to find this script .
    Its working fine.
    But I have ome another problem regarding backup.
    I am dexcribing the problem below
    “I have to take backup of mysql table from remote machine and insert it into mylocal machine .”
    I have created one such script. Thats working as well.But there is still some problem regarding peformance of the script.

    If some-one reexecute the xcript it reinsert the data .
    which should not be done .
    I am pasting the code as well .
    Kindly suggest me the bugs and inhancement if there are any..

    ***************************************************

    #!/bin/bash
    PING=”/bin/ping”
    host=”192.168.1.160″
    $PING -c1 $host>/dev/null
    if [ “$?” != “0” ] ; then
    echo “source host unreachable”
    else
    echo “going to take backup ”
    mysql -u ravi -h 192.168.1.160 –password=shankar test -e ‘select * from usermst’ > /home/ravi/reg.txt
    if [ “$?” != “0” ] ; then
    echo “some error while taking backup so removing temprory file”
    rm /home/ravi/reg.txt
    else
    echo “preparing to insert data”

    mysql -u root test -e ‘load data local infile “/home/ravi/reg.txt” into table usermst’
    if [ “$?” != “0” ] ; then
    echo “could not insert data so removing temporary file”
    rm /home/ravi/reg.txt
    else
    echo “data has been inserted”
    rm /home/ravi/reg.txt
    echo temporary text file has been removed
    fi
    fi
    fi

    ***************************************************
    thanks
    Ravi Shankar

  • antony October 15, 2008, 5:41 am

    i fount this one very helpful. thanks a lot

  • China pictures October 24, 2008, 1:57 am

    I don’t want to write the password in the script, is there a way like with postgresql (pg_pass file), but with MySQL ?

  • Mark April 6, 2009, 3:52 pm

    Thanks for the script, if you run this script more than once a day, i believe it overwrites itself. I changed line 75 to this so you can run it more than once a day:

    NOW=”$(date +”%d-%m-%Y-%I-%M”)”

  • cyberdesigner.net July 27, 2009, 8:29 am

    @Mark:

    better use
    NOW=”$(date +”%d-%m-%Y-%H-%M”)”

    because if you make backups on each hour you have 2009-07-27-10-00 twice a day ;-)

  • Lucian Daniliuc June 12, 2011, 11:10 am

    Great script! Thanks a lot!

  • Ô±Õ¬Õ¥Ö„Õ½Õ¡Õ¶Õ¤Ö€ May 21, 2012, 10:35 am

    Hello
    I must repeat Karo’s question:
    What is the

    # Store list of databases
    DBS=””
    leave bank or can insert an option


    Also i include some dbs in iggy field

    IGGY=”test db2 db3″

    but script backups all databases of the server (even test and information_schema)

    How to fix it ?

Leave a Comment