OT backup mysql databases

[Edwin Dicker]

----- Original Message ----- 
From: "Edwin Dicker" <fedora at dicker.nl>
To: "For users of Fedora Core releases" <fedora-list at redhat.com>
Sent: Wednesday, 27 October, 2004 23:16
Subject: Re: OT backup mysql databases


>
> ----- Original Message ----- 
> From: "Christopher Hicks" <chicks at chicks.net>
> To: "RedHat's Fedora List" <fedora-list at redhat.com>
> Sent: Wednesday, 27 October, 2004 23:07
> Subject: Re: OT backup mysql databases
>
>
>> On 27 Oct 2004, Edwin Dicker shaped electrons to convey:
>>> I have a few small mysql databases on my system. I backup my whole 
>>> system every week with a cpio backup to tape. Is a cpio backup good 
>>> enough for mysql databases? If I restore the /var/lib/mysql directory 
>>> structure, will i have full access again ? Do I need to stop the mysqld 
>>> service before creating the backup ?
>>
>> Running mysqldump on each tablespace (including the mysql tablespace) 
>> would be much cleaner and not require any shutdown of mysql.  The backups 
>> of the /var/lib/mysql directory may or may not be consistant depending on 
>> whether there's any update activity at that point.  (And mysql can delay 
>> updates so there may be update activity after there's noone connected for 
>> a short time.)
>>
>> -- 
>> </chris>

According to your tips I've decided to use mysqldump through the logrotate 
function. ( read a tip on the mysql developer forum )
This way it'll create a backup every night and keeps it 14 days.
This is how I did it:

[root at storm mysql]# cd /etc/logrotate.d
[root at storm logrotate.d]# cat databases
/var/backups/mysql/alldbs {
    rotate 14
    daily
    compress
    missingok
    sharedscripts
    postrotate
         mysqldump --opt --all-databases -u root --password="password" > 
/var/backups/mysql/alldbs
    endscript
}

My databases are not growing to many MBytes so there's no problem with 
diskspace :)

thanks for all your tips.
Edwin