[Box Backup] Backup Ninja

[Per Thomsen]

On 10/31/06 8:05 AM, Richard Hurt wrote:
> I was just thinking about how to back up a live MySQL database using
> Box Backup.  The only difference is that I would like to back up the
> database live using lazy mode, not dump it once a day.  Any
> clues/hints from anyone?
If you want to back up a live mySQL database, there are a couple of
options (with caveats):

    * You can't count on the datafiles being consistent during backup,
      so if you snapshot your database every hour or so (mysqlhotcopy
      does a good job for me), you can have bbackupd pick these files up
      whenever it runs. This is what I do (although I only run
      mysqlhotcopy once a day), and it works well for me.
    * If you're using mySQL >=5.0 and you use InnoDB datafiles, you can
      use the binary log to do the backups. The binary log is a
      transaction log, that you can play back after a point-in-time
      datafile recovery. More about the Binary log here:
      http://dev.mysql.com/doc/refman/5.0/en/binary-log.html ... I have
      not tried this, so I don't know which logs to back up, how the log
      archiving works, etc. Also make sure you have a recent
      point-in-time snapshot to start your transaction playback with.
      Otherwise, you could be replaying these logs for days, depending
      on your transaction volume.

HTH.

Thanks,
Per

-- 
Per Reedtz Thomsen | Reedtz Consulting, LLC | F: 209 883 4119
V: 209 883 4102    |   pthomsen@reedtz.com  | C: 209 996 9561
GPG ID: 1209784F   |  Yahoo! Chat: pthomsen | AIM: pthomsen