MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:David Brodbeck Date:February 11 2004 7:27pm
Subject:RE: best-practices backups
View as plain text  
> > -----Original Message-----
> > From: Michael Collins [mailto:mcollins@stripped]

> > Is there any "best-practices" wisdom on what is the most preferable
> > method of backing up moderately (~10-20,000 record) MySQL 4
> > databases? A mysql dump to store records as text, the 
> format provided
> > by the BACKUP sql command, or some other method?

I think it depends on how long a backup window you can tolerate.

On our databases, I use mysqldump to dump to a text file.  The backup is
piped through gzip to reduce the size on disk.  This has the advantage of
being portable between architectures, robust, and human-readable.

I also run with update logging turned on, and turn over the logfiles after
each backup.  This way I can restore the database to any point in time by
restoring the next earliest backup, then running as much of the update log
as necessary to get to the desired point.  I use a script with the 'find'
command to weed out old backups and update logs older than a certain number
of weeks.  All of this is run nightly by cron entries.

The disadvantage of this method is that mysqldump can take a while to dump
large databases, and the tables are locked during the backup process.  If
this is a problem, you should probably investigate mysqlhotcopy.
Thread
myDomain-relay-bin file?Michael Collins5 Feb
  • best-practices backupsMichael Collins5 Feb
    • Re: best-practices backupsBrent Baisley6 Feb
    • RE: best-practices backupsMichael McTernan11 Feb
RE: best-practices backupsJeffrey Smelser11 Feb
RE: best-practices backupsDavid Brodbeck11 Feb
  • Re: best-practices backupsDavut Topcan11 Feb
  • RE: best-practices backupsMichael McTernan11 Feb
    • RE: best-practices backupsPaul Owen11 Feb
    • Second mysqld fails...Greg G12 Feb
      • Re: Second mysqld fails...vpendleton12 Feb
        • Re: Second mysqld fails...Greg G12 Feb
RE: best-practices backupsDavid Brodbeck11 Feb
Re: best-practices backupsTbird67ForSale14 Feb
Re: best-practices backupsTbird67ForSale14 Feb