mysql backups (was Re: Remote backups, reading from and writing to the same file)

[N.J. Thomas]

* Hans Nieser <h.nieser at xs4all.nl> [2006-01-13 00:25:14 +0100]:
> Among the things being backed up are my mysql database tables. This
> made me wonder wether the backup could possibly get borked when mysql
> writes to any of the mysql tables while tar is reading from them.

Yes. While MySQL is writing to the the database, it will put the files
on the disk in an inconsistent state. If you happen to copy those files
while they are in that state, MySQL will see a corrupted database.

> Do I really have to use MySQL's tools to do a proper SQL dump or stop
> MySQL (and any other services that may write to files included in my
> backup) before doing a backup? Do any of the more involved
> remote-backup solutions have ways of working around this? Or is it
> simply not possible to write to a file while it is being read?

Here are some methods that people use that I am aware of:

    - Turn off the MySQL db the entire time you are backing up. No new
      software/hardware needed, but you incur db downtime.

    - Use replication: have a slave that is a copy of the master,
      whenever you want to back up, break the replication for a little
      while, copy the slave, and then resume the replication. No
      downtime, but you will need another box for this, so you have the
      cost of new hardware.

    - Use OS snapshotting. On Linux systems with LVM, it is possible
      to take an exact "snapshot" of the filesystem at any point in time
      without too much disk usage (assuming the lifetime that the
      snapshot exists is relatively short).

      So what you do in this case is write a script that tells MySQL to
      write lock the entire database and flush the cache, this takes a
      second or two and will bring the db files on disk to a consistent
      state. You then take a snapshot of the filesystem, and immediately
      resume MySQL when you have done that. Now, you just backup off of
      the snapshot, destroying it when you are done.

      No new hardware, but you will need a snapshot capable filesystem
      and write the script to do this. I'm not sure exactly what
      snapshotting features FreeBSD has...perhaps someone else could
      fill in this information. Also, you will have a short period of
      downtime during which the MySQL db is write locked. This may or
      may not be acceptable for you.

hth,
Thomas

-- 
N.J. Thomas
njt at ayvali.org
Etiamsi occiderit me, in ipso sperabo