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
$host=”localhost”;
$user=”dbuser”;
$password=”dbpassword”;
$date=date(”Ymd-Hi”);
$path=’/raid/.mysql_backups/’;
#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.

 

backing up linenine

Ξ May 24th, 2006 | → Comments Off | ∇ backup |

linenine uses a program called rsnapshot to backup things from the non-raid raptor to the raid5 array. currently, the only things that are backed up are:

/home/
/etc/
/usr/local/
/var/www/
/usr/portage/
/usr/src/linux/
/raid/.mysql_backups/

(let me know if you think of something else that should be backed up)

It does a full backup once a week, and incremental ones throughout the week, saving only the last 7 days. Then it does a full backup once a month, and incremental ones every week, and saves 4 weeks. It also does a full backup once a month, and keeps 3 months of those.I may just change that to keep a full month’s worth of daily (incremental) backups, and then just the monthly ones on top of that though. we’ll see.

 

  • Rumours and Lies