Categories
Linux WordPress

Simple MySQL Backup

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

Comments

comments

By Donncha

Donncha Ó Caoimh is a software developer at Automattic and WordPress plugin developer. He posts photos at In Photos and can also be found on Google+ and Twitter.

45 replies on “Simple MySQL Backup”

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/.

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?”

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 date on 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 date on 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.

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 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.

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?

[…] 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. […]

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

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

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.

Leave a Reply to Fredrik Cancel reply