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.


🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 23 comments so far... add one
CategoryList of Unix and Linux commands
Disk space analyzersncdu pydf
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
23 comments… add one
  • Anonymous Aug 24, 2005 @ 21:16

    the link to the script is missing? 🙂

  • Vivek Aug 24, 2005 @ 23:01

    Opps.

    Thanks, just fixed URL.

  • NINjak Oct 23, 2005 @ 17:52

    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 Dec 9, 2005 @ 12:12

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

  • 🐧 nixcraft Dec 9, 2005 @ 13:36

    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 Feb 25, 2007 @ 10:29

    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 Feb 25, 2007 @ 13:56

    Make sure variable MYSQL is pointing to correct mysql client

  • Jette Feb 25, 2007 @ 19:15

    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 Mar 12, 2007 @ 14:53

    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 Mar 12, 2007 @ 17:28

    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 Mar 12, 2007 @ 20:57

    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 @ 13:20

    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 Jun 30, 2007 @ 10:22

    the link to the script is broken.

  • 🐧 nixCraft Jun 30, 2007 @ 14:09

    Marten,

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

  • Ciprian Oct 4, 2007 @ 6:36

    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 Oct 20, 2007 @ 0:20

    What is the

    # Store list of databases
    DBS=””

    leave bank or can insert an option

  • Ravi Shankar Nov 6, 2007 @ 7:42

    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 Oct 15, 2008 @ 5:41

    i fount this one very helpful. thanks a lot

  • China pictures Oct 24, 2008 @ 1:57

    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 Apr 6, 2009 @ 15:52

    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 Jul 27, 2009 @ 8:29

    @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 Jun 12, 2011 @ 11:10

    Great script! Thanks a lot!

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

    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 Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum