[Box Backup] backing up mysql database

Ben Summers boxbackup@fluffy.co.uk
Wed, 27 Oct 2004 09:06:30 +0100


On 27 Oct 2004, at 08:36, Martin Ebourne wrote:

> On Wed, 2004-10-27 at 06:16, Michel CERDINI wrote:
>>   Now I wanted to backup a big mysql database. If I put 
>> /var/lib/mysql in
>> the bbackupd.conf, the whole database will be backuped regularly, so 
>> it
>> will take a lot of place on the server, isn't it ? Or when I read 
>> "Only
>> changes within files are sent to the server, just like rsync", does 
>> it mean
>> that only changes within files are also STORED on the server side ?
>
> Yes, as of 0.8 only changes will be stored on the server, so storage
> requirements should not be too high.
>
> If it does work out that it is still taking up too much space then one
> possibility is to dump the database out to text regularly and back that
> up instead. Typically that will diff well and it'll be compressed on 
> the
> server too. Not sure how to do that with mysql, but pg_dump will do 
> that
> for PostgreSQL. I'm sure you'll be fine backing up the raw files 
> though.

I would worry that there are inconsistencies in these backed up raw 
files. Especially as the backup operation may take a while to upload 
the data over the net, increasing the chances of a problem 
dramatically. You might be able to fix the restored file, you might 
not.

It's better to backup a derived file, probably using mysqldump or 
something. At least you know it's consistent. You will get the benefits 
of upload by patch and storage by patch whichever way you choose.

Ben