≡ 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.

Tweet itFacebook itGoogle+ itPDF itFound an error/typo on this page?

{ 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