MySql backup script

by on January 2, 2005 · 23 comments· LAST UPDATED January 30, 2007

in

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.

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 23 comments… read them below or add one }

1 Anonymous August 24, 2005 at 9:16 pm

the link to the script is missing? :)

Reply

2 Vivek August 24, 2005 at 11:01 pm

Opps.

Thanks, just fixed URL.

Reply

3 NINjak October 23, 2005 at 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.

Reply

4 Anonymous December 9, 2005 at 12:12 pm

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

Reply

5 nixcraft December 9, 2005 at 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

Reply

6 Jette February 25, 2007 at 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.

??

Reply

7 nixCraft February 25, 2007 at 1:56 pm

Make sure variable MYSQL is pointing to correct mysql client

Reply

8 Jette February 25, 2007 at 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 :-)

Reply

9 Jack March 12, 2007 at 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)

Reply

10 nixCraft March 12, 2007 at 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"

Reply

11 Jack March 12, 2007 at 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 .

Reply

12 Najm May 24, 2007 at 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.

Reply

13 Marten June 30, 2007 at 10:22 am

the link to the script is broken.

Reply

14 nixCraft June 30, 2007 at 2:09 pm

Marten,

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

Reply

15 Ciprian October 4, 2007 at 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)?

Reply

16 karo October 20, 2007 at 12:20 am

What is the

# Store list of databases
DBS=””

leave bank or can insert an option

Reply

17 Ravi Shankar November 6, 2007 at 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

Reply

18 antony October 15, 2008 at 5:41 am

i fount this one very helpful. thanks a lot

Reply

19 China pictures October 24, 2008 at 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 ?

Reply

20 Mark April 6, 2009 at 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”)”

Reply

21 cyberdesigner.net July 27, 2009 at 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 ;-)

Reply

22 Lucian Daniliuc June 12, 2011 at 11:10 am

Great script! Thanks a lot!

Reply

23 Ալեքսանդր May 21, 2012 at 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 ?

Reply

Leave a Comment

Previous post:

Next post: