On Tuesday, 18 July 2006 at 10:57:24 +0530, Kaushal Shriyan wrote: > Hi ALL > > I would like to know following 2 things. > > 1) if i would like to take mysql incremental backup weekly > > cp /var/lib/mysql/ /backup/mysqlbak/
Others have already explained why this won't work. None of them have
addressed the issue of incremental backups, though. To clarify: an
incremental backup is one which bases on a previous backup, so it only
contains data that has changed since that backup. None of the methods
described can do that. Neither will the first release of our new
online backup, though it's planned for later.
Currently you have the following option:
1. Ensure that you have the binlog enabled. See the chapter on
replication in the reference manual for details
(http://dev.mysql.com/doc/refman/5.0/en/replication.html). You
don't need to run a slave node, but you do need to create the
binlogs, which will be your incremental backup files.
2. Make a complete backup at some point. All incremental backups
require this step, of course. Currently mysqldump or mysqlhotcopy
are your choices, though of course you can also back up the
underlying files if you take the appropriate precautions to ensure
that they're consistent. Note the binlog position at the time of
the backup.
3. Back up the binlogs as they fill up.
4. To restore the database, first restore the complete backup. Then
use mysqlbinlog
(http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html) to
output the incremental part of the backup to a mysql client. Use
the --start-position option to mysqlbinlog to tell it where to
start.
5. Optionally, use the --stop-position argument to tell it where to
stop. This gives you the capability to recover to a point in time
(like just before that monster query went wrong).
For more information, see the Example Backup and Recovery Strategy at
http://dev.mysql.com/doc/refman/5.0/en/backup-strategy-example.html.
I *strongly* recommend trying this out with a dry run before
entrusting your database to it; it's quite possible to shoot yourself
in the foot.
Greg
--
Greg Lehey, Senior Software Engineer, Online Backup
MySQL AB, http://www.mysql.com/
Echunga, South Australia
Phone: +61-8-8388-8286 Mobile: +61-418-838-708
VoIP: sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED]
Diary http://www.lemis.com/grog/diary.html
Are you MySQL certified? http://www.mysql.com/certification/
pgpeCco6F3tqQ.pgp
Description: PGP signature
