mysql backup setup

Ξ September 19th, 2006 | → Comments Off | ∇ backup, mysql |

I promised I’d write about my MySQL backup script, so here it is.You’ll need this file:

# mysql database backup
# written by logikal, Sep 18, 2006
## No copyright. Do as you like.
## Dumps each mysql database in a mysql server
# to its own timestamped file. feel free to
# edit $date to match your date conventions
#Must be absolute path, including trailing slash
$db = mysql_connect(”$host”,”$user”,”$password”);
$result = mysql_query(”SHOW DATABASES”);
while($row = mysql_fetch_array($result))
{ echo “$row[0]–>$path$date-$row[0].sql.gz” ); }

This has GAPING_SECURITY_HOLE written all over it

Now, this script isn’t really safe to run in a browser, so I hope you’ve got CLI-PHP installed. Also, this script is meant to run as a user who has at least read access to everything in mysql. I suggest making a ‘backup’ user to do this, so you don’t have your root database user/pass sitting in a file somewhere. Even with that, I still suggest chmodding this 700, and storing all the backups in a 700 folder that you own.
Now, you could just cron this to run every so often, but then you’ll end up with a horribly bloated $path. You need to do some pruning of old files. This is how I did it:

#!/bin/shcd /raid/.mysql_backups/find . *.sql.gz -atime +9 -exec rm {} \;
/usr/bin/php /raid/.mysql_backups/mysql_backup.php

Quick and dirty, but it does the job. the find line does this: find everything that ends in *.sql.gz, and is older than 9 days, and delete it. I chose 9 days, because of my rsnapshot setupInstant mysql backup system. just cron it up, and you’re set.


  • Rumours and Lies