[Box Backup] Box saved my a**/day

Per Thomsen boxbackup@fluffy.co.uk
Sun, 12 Feb 2006 11:42:47 -0800


On 2/11/06 5:50 AM, Jamie Webb wrote:
> On Sat, Feb 11, 2006 at 01:14:23PM +0100, Stefan Norlin wrote:
>   
>> Yesterday (friday) a colleague of mine discovered he had
>> overwritten a customer database (in MySQL) with a month
>> old test data. Disregading from the fact that this should not
>> be able to happen we were happy (as happy as can be in
>> that situation) to discover that the files (around 50 MyISAM
>> file) were relatively easy to recover via bbackupquery since
>> old revisions of files are kept in the store. The latest versions
>> were not usable but a generation back was.
>>     
>
> Be wary of relying on direct backups of databases (for any DBMS). If
> it's being modified as the backup is done, there's no guarantee (even
> with journalling) that the results will be usable.
>
> For MySQL, the simplest procedure is to run the following in a cron
> job, and then back that file up instead.
>
> mysqldump -u backup -A --opt > /some/path/backup.sql
>
> Where backup is a user with select, reload, and lock tables rights on
> all databases. There are more efficient/secure solutions that are more
> complex.
>   
I use mysqlhotcopy. It claims to be faster than mysqldump, but only
works with MyISAM files.

A simple script:

# This script creates hot backups of the MySQL databases,
# and then kicks of a snapshot backup.
#

/usr/bin/mysqlhotcopy --quiet --allowold db01 /home/mysqlbackups
/usr/bin/mysqlhotcopy --quiet --allowold db02 /home/mysqlbackups
/usr/bin/mysqlhotcopy --quiet --allowold db03 /home/mysqlbackups

/usr/local/bin/bbackupctl -q sync 

Thanks,
Per

-- 
Per Reedtz Thomsen | Reedtz Consulting, LLC | F: 209 883 4119
V: 209 883 4102    |   pthomsen@reedtz.com  | C: 209 996 9561
GPG ID: 1209784F   |  Yahoo! Chat: pthomsen | AIM: pthomsen