Loading ...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.
Want to stay up to date with the latest Linux tips, news and announcements? Subscribe to our free e-mail newsletter or RSS feed to get all updates.
You can Email this page to a friend.
You may also be interested in other helpful articles:
- Howto: Make consistent MySQL database backups using Solaris ZFS snapshots
- Take action or execute a command based upon shell script name
- How to MySQL backup and data recovery with mysql-zrm
- Rotate FTP Backup Using a Shell Script
- How to backup MySQL databases, web server files to a FTP server automatically
Discussion on This Article:
Leave a Reply
We encourage your comments, and suggestions. But please stay on topic, be polite, and avoid spam. Thank you very much for stopping by our site!
the link to the script is missing?
Opps.
Thanks, just fixed URL.
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.
i am not getting how to use that script canu plz expalin . My user name in “root” hostname is napta
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
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.
??
Make sure variable MYSQL is pointing to correct mysql client
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
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)
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"
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 .
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.
the link to the script is broken.
Marten,
I don’t see a problem; I can browse the link.
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)?
What is the
# Store list of databases
DBS=”"
leave bank or can insert an option
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