Shell Script for MySQL Backup
I’ve finally gotten around to writing a shell script to back up my databases. It’s not particularly complicated, but it works, backing up, timestamping, and gzipping each database.
I wanted it to timestamp each file, which seems an obvious requirement. So I set the filename:
filename=database_backup_`date +%Y-%d-%m-%H%M`.sql
and then use the mysqldump command to dump the contents of the required database:
mysqldump -uuser-ppassdatabase_name > path/$filename
Next I zip it:
gzip -f9 path/$filename -o path/$filename.gz
That’s the easy part done. Next, I want to keep it around only if it’s different from the last backup, and I also want to copy any new version to a file called database_backup_latest.sql.gz. The best way I can think of to see if the database has changed since the previous backup is to check the file size of the new data dump against the file size of the previous one.
First, I have to make sure to check file size only if an old file exists:
oldfilesize=0;
if [ -r database_backup_latest.sql.gz ]; then
oldfilesize=`gzip -l database_backup_latest.sql.gz | grep sql | awk '{print $2}'`;
fi
There are probably better ways to get the file size. I tried using cut but you need to know exactly how many spaces separate the file sizes returned by gzip -l, and that changes depending on the file sizes themselves.
Next, get the size of the new file and check it against the old one:
newfilesize=`gzip -l $filename.gz | grep sql | awk '{print $2}'`
if [ $oldfilesize != $newfilesize ]; then
cp path/$filename.gz path/database_backup_latest.sql.gz
else
rm $filename.gz
fi
That snippet copies the new file over the one previously marked as “latest”—unless the new file isn’t different in size, in which case it doesn’t copy it and instead deletes it.
The full script:
#!/bin/sh filename=database_backup_`date +%Y-%d-%m-%H%M`.sql mysqldump -uuser-ppassdatabase_name > path/$filename gzip -f9 path/$filename -o path/$filename.gz oldfilesize=0; if [ -r database_backup_latest.sql.gz ]; then oldfilesize=`gzip -l database_backup_latest.sql.gz | grep sql | awk '{print $2}'`; fi newfilesize=`gzip -l $filename.gz | grep sql | awk '{print $2}'` if [ $oldfilesize != $newfilesize ]; then cp path/$filename.gz path/database_backup_latest.sql.gz else rm $filename.gz fi