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
You might also like
- MySQL Optimization by Jeremy Z …
MySQL Optimization by Jeremy Zawodny at Yahoo. He was instrumental- Searching with PHP and MySQL
Great article on searching MySQL using PHP! I didn't know- MySQL Control Center – very, v …
MySQL Control Center - very, very nice MySQL GUI .
If you like this post then please subscribe to my full RSS feed. You can also click here to subscribe by email. There are also my fabulous photos and funny videos to explore too!
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
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.
`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 numbersI 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.
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!
[...] Earlier this week, my WordPress dashboard pointed me to this simple MySQL backup script. I’m pretty new to BASH and working with the shell in general, but yet I managed to customize the script for a bit. [...]
[...] Más información en: Tamba Cron Job: Wordpress @ T2 Holy Shmoly: Simply Backup Mysql Wordpress Codex Restoring Wordpress Codex Backups [...]
Mysql Backup
# 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/fdomingos/backups/mySQL
export usr=
export pwd=
[...] There are oh-so-many of these on the web, so I thought I’d add another. I got inspirations from two sources, and here’s my version. [...]
Database
The database the development is mostly focusing on is MySQL and PostgreSQL (the latter being more complete, a little more complex). In addition, there are fully java based alternatives,…
[...] But it’s not my server and backups are important. I had been using Podz’s script for backing up on a cron job but forgot that the script only got tables that you told it to. Donncha’s script gets all the tables, but I like getting one file for the data. Also I want to be able to retrieve the file backup without using a password onto the system but make sure that the file can’t be used by other people. For me that means putting it on the web server but encrypting the backup so that only my private key can decrypt the data. [...]
[...] The only concern is that it’s not my server and backups are important. I had been using Tamba2’s script for backing up on a cron job but forgot that the script only got tables that you told it to. Donncha’s script gets all the tables, but I like getting one file for the data. Also I want to be able to retrieve the file backup without using a password onto the system but make sure that the file can’t be used by other people. For me that means putting it on the web server but encrypting the backup so that only my private key can decrypt the data. [...]
[...] Simple MySQL Backup at Holy Shmoly! a script to backup your mysql database from Donncha (tags: wordpress-resources backup) Share and Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages. [...]
[...] up the mySQL database on my Gentoo Linux server the other day, and I stumbled across some posts at http://ocaoimh.ie/2005/06/28/simple-mysql-backup/ (so credit where it is due!) that provided a good basis. Of course, I figured I should share the [...]
[...] use Backuppc to backup all my servers every night, and a simple MySQL backup script to dump the database [...]
[...] uso Backuppc per eseguire il backup dei miei server ogni notte e un semplice script di backup di MySQL per il backup [...]
[...] backups on the Codex, which makes a good starting point. I also found some old script examples here. Time to do a bit of modifying! I liked the original idea of having a 7-day rolling backup set [...]