If, like me, you have a database full of small tables, but all amounting to a large amount of data then dumping the data from it into a single file is handy, but not very useful when it comes to retrieving a backup of one single table.
Instead, wouldn’t it be easier to dump each individual table into it’s own file? What about keeping a week’s worth of backups? Here’s one way I backup my WordPress db with a little Bash script:
export d=`date +%u`
mkdir -p backup/$d
for i in `echo "show tables" | mysql -u username -ppassword database|grep -v Tables_in_`;
do
echo $i; mysqldump --add-drop-table --allow-keywords -q -a -c -u username -ppassword database $i > backup/$d/$i.sql
rm -f backup/$d/$i.sql.gz
gzip backup/$d/$i.sql
done
Oh, this is just so nice and 1337!!!1 Thanks for the script. Here’s a reference on how to use and format dates with BASH. I changed the first line of your code into
export d=$(date +'%Y-%m-%d')to make it store the backups in folders like/backup/2005-06-28/rather than in/backup/2/.Thanks Mathias!
Glad you find the script useful.
I only want to keep 7 days of backups. Using the day of the week guarantees that I don’t fill up my disk!
Ah, I see. I didn’t know what exactly
`date +%u`meant; I’m quite new to BASH and all. I just thought the/2/thing looked a bit silly. In fact, I do like/backup/yyyy-mm-dd/more, because I just need to think too much when using weekday numbers π I’ll try to enhance the script to make it remove folders that are older than a week, hoping this will help me in learning teh r34l 1337 sp34k.I like it.
The only bit I don’t like is that %u isn’t mentioned in the GNU date format string reference, which left me thinking to myself “okay, what could ‘2’ be?”
Sure it is!
$ man date......
%u day of week (1..7); 1 represents Monday
......
date (coreutils) 5.2.1 July 2004 DATE(1)
I use something very similar for backing up my databases. Mine includes a config file however. Each database you need backed up, just put it on it’s own line in the config file and it’ll be backed up and rsynced to a remote location. Very slick. π
Alas, for I must contest this. π
The output of
man dateon my system is pretty much identical to the reference linked to in comment 1.Of course, it’s
date (GNU sh-utils) 2.0.11. But that’s all DreamHost has. πYou are absolutely right the details or the reference is missing in date command’s manual.
format of date command is:
date [-nu] [-r seconds] [+format]
the details of +%u and all of the other formats which you can use in your scripts can be seen in “man strftime”
hope this helps.
Ouch! I rememeber having to use
dateon a Solaris box ages ago. *shudder* That was painful! It (and Solaris in general) didn’t have any of the nice GNU features I had become used to. Call me spoiled I suppose!I ended up installing a lot of GNU stuff eventually to make my life easier.
A nice, handy script. Do you think it would be safer to not include the password directly in the script, but rather move it to .my.cnf instead? That way the password won’t show up in clear text in the process list.
This is just what I was looking for to tie in with my automatic remote backup scripts. Excellent stuff.
just a date hack that makes it eaier to remove your old versions
FILE=$PREFIX`date +%d%b`.tar.gz
OLDBAK=$PREFIX`date +%d%b –date=’10 days ago’`.tar.gz
Here’s a little mod to include all databases…this way I don’t have to hardcode a list of what to backup, and then forget to modify it. It also packs everything into one daily tarball, for ease of moving to another backup source/dloading etc.
Modify date formats/paths/filenames/passwords as needed.
#MySQL backup script
export d=$(date +’%Y%m%d’)
mkdir -p backup/$d
for a in `echo “show databases” | mysql -uroot -pblah |grep -v Database`;
do
mkdir -p backup/$d/$a
for i in `echo “show tables” | mysql -uroot -pblah $a|grep -v Tables_in_`;
do
echo $i; mysqldump –add-drop-table –allow-keywords -q -a -c -uroot -pblah $a $i > backup/$d/$a/$i.sql
rm -f backup/$d/$a/$i.sql.gz
gzip backup/$d/$a/$i.sql
done
done
`tar cf $d.db.tar backup`
`gzip $d.db.tar`
rm -rf backup
Heres yet another modified backup script, I thought I’d contribute back.
# MYSQL Backup Script
# Contains portions of code from http://blogs.linux.ie/xeer/2005/06/28/simple-mysql-backup/
export d=$(date +’%Y-%m-%d’)
export savepath=’/home/public/backups/mySQL’
export usr=’backupuser’
export pwd=’replacewithyourpassword’
echo “mySQL Backup Script”
mkdir -p $savepath/$d
echo “Dumping entire database..”
mysqldump –add-drop-table –allow-keywords –all-databases -u$usr -p$pwd > $savepath/$d/all.sql
echo “Dumping individual tables…”
for a in `echo “show databases” | mysql -u$usr -p$pwd | grep -v Database`;
do
mkdir -p $savepath/$d/$a
echo “Dumping database: $a”
for i in `echo “show tables” | mysql -u$usr -p$pwd $a| grep -v Tables_in_`;
do
echo ” * Dumping table: $i”
mysqldump –add-drop-table –allow-keywords -q -a -c -u$usr -p$pwd $a $i > $savepath/$d/$a/$i.sql
done
done
echo “Archiving Files…”
tar -C$savepath -c -f$savepath/$d.tar $d
gzip $savepath/$d.tar
echo “Deleting Temp Files”
rm -rf $savepath/$d
echo “Complete”
Since Im a unix admin, I have a problem with commands that have passwords in them, since its a disaster from a security perspective (you can see the password in a process listing).
Anyway you can make an options file called /usr/local/mysql/.my.cnf which contains:
[client]
user=youruser
password=yourpassword
Then rather than use the username or password on the command line you would call mysqldump like:
mysqldump –defaults-file=/usr/local/mysql/.my.cnf –add-drop-table ΓΒ’Γ’β¬Òβ¬Ε-allow-keywords ΓΒ’Γ’β¬Òβ¬Ε-all-databases > $savepath/$d/all.sql
The options file should have appropriate permissions permissions (600).
A revised version:
# MYSQL Backup Script
# Contains portions of code from http://blogs.linux.ie/xeer/2005/06/28/simple-mysql-backup/
export d=$(date +”%Y-%m-%d”)
export savepath=”/home/user/backups/mySQL”
echo “mySQL Backup Script”
mkdir -p $savepath/$d
echo “Dumping entire database…”
mysqldump –defaults-file=/home/user/.my.cnf –add-drop-table –allow-keywords –all-databases > $savepath/$d/all.sql
echo “Dumping individual tables..”
for a in `echo “show databases” | mysql | grep -v Database`;
do
mkdir -p $savepath/$d/$a
echo “Dumping database: $a”
for i in `echo “use $a;show tables” | mysql $a| grep -v Tables_in_`;
do
echo ” * Dumping table: $i”
mysqldump –defaults-file=/home/user/.my.cnf –add-drop-table –allow-keywords -q -a -c $a $i > $savepath/$d/$a/$i.sql
done
done
echo “Archiving Files..”
tar -C$savepath -c -f$savepath/$d.tar $d
gzip $savepath/$d.tar
echo “Deleting Temp Files”
rm -rf $savepath/$d
echo “Complete”
———————–
This version will create individual folders for each database and seperate sql files for each table as well as a total sql dump in a single file. Next step – upload the tar.gz to a remote host! π
Nice Script. The last version is quite good that the first.I’m using the last version to backup my site.That Gzip facility is quite good.I modified the script so that it automatically emails the backup to the webmaster ( to me) and added cron jobs to automate it. Thn you all for this nice script.
Raja
http://www.cyese.info/
Nice backup script! To email yourself the file, you can use mpack to do all the work. Works great for me. This and crontab do the trick.
http://www.netadmintools.com/art59.html
http://www.die.net/doc/linux/man/man1/mpack.1.html
Nice start but still hacky.
Efficient backup and is a bigger issue than just a mysql specific one.
There are
My two cents:
Read http://www.mikerubel.org/computers/rsync_snapshots/index.html
Install and configure rsnapshot. It will take care of any folder you want to backup. Installs on ubuntu with apt-get.
When working with rsnapshot, your mysql dumper script just stores the data in local files without any date/time information. Those are taken care by the backup system. One cool benefit is that in space approximately equal to the original data you get many snapshots. That is thanks to elegant usage of hard links. So only files which change do increase the overall snapshot size.
How do i write bash script to drop two databases from mysql
Here’s another one…rsnapshotDB, works well with rsnapshot rsync backup.
The xml config file is a bit tricky…but makes sense if you have multiple usernames, dbs, and hosts.
http://rsnapshot.cvs.sourceforge.net/rsnapshot/rsnapshot/utils/rsnapshotdb/
rsnapshot can be found at http://www.rsnapshot.org
both
for a in `echo Γ’β¬Εshow databasesΓ’β¬Β | mysql | grep -v Database`;
and
for i in `echo Γ’β¬Εuse $a;show tablesΓ’β¬Β | mysql $a| grep -v Tables_in_`;
could be
for a in `echo Γ’β¬Εshow databasesΓ’β¬Β | mysql -s ;
for i in `echo Γ’β¬Εshow tablesΓ’β¬Β | mysql -s $a`;
to remove the unnecessary grep rows and make it look nicer
and on a system with gnu tar and bzip2
echo Γ’β¬ΕArchiving FilesΓ’β¬Β¦Γ’β¬Β
tar -C$savepath -c -f$savepath/$d.tar $d
gzip $savepath/$d.tar
echo Γ’β¬ΕDeleting Temp FilesΓ’β¬Β
rm -rf $savepath/$d
echo Γ’β¬ΕCompleteΓ’β¬Β
could be
echo Γ’β¬ΕArchiving FilesΓ’β¬Β¦Γ’β¬Β
tar -C$savepath -c -j -f$savepath/$d.tar.bz2 $d
echo Γ’β¬ΕCompleteΓ’β¬Β
Nice script otherwise!
hi
i need to developed a script for unix to make an autobackup for an Oracle database
do you have some advise?
I have space problem on my Hard Disk of Server RS6000 working on AIX and Oracle 7.3.4 and PLSQL 2.3.4
So I want to reduce the size of dump file created on the system by dropping a particular table which is large enough and would like to export it seperately.
Any one sending me the required script is welcome. Pl add comments with each command to make it understandable as data is live and precious.
Help required for export backup of whole data except one or two tables on IBM server RS6000
o/s is AIX, Oracle ver 7.3.4 and PLSQL 2.3.4
My host doesn’t give shell access to perform the script is there a php version of it ? Thank you.
why don’t you try tar the whole mysql folder.it work too.hehe..
fancy that, looking for a little script like this, and guess who comes up first on google. lol, seo is good, script kool script also. must give u a call nice pic of yourself and the little fella. talk soon
Great Script.
@sarahnovotny Actually ,this is very similar to the script that I wrote except mine pipes to the MySQL client: http://tinyurl.com/yo29sn
Actually, if you use this and your remote machine is compromised, all your backups are gone. Every backup should be done from your local machine and never the other way around.
I drag everything down using backuppc to a local drive here at home. You’re right, a backup on the server is no backup at all!
good stuff, just what I was looking for.. oddly can’t get phpMyAdmin to run.