Re: OT backup mysql databases

["Edwin Dicker" <fedora dicker nl>]

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


> ----- Original Message ----- 
> From: "Christopher Hicks" <chicks chicks net>
> To: "RedHat's Fedora List" <fedora-list 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 storm mysql]# cd /etc/logrotate.d
[root 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