Date: Fri, 13 Jan 2006 09:50:03 -0500 From: "N.J. Thomas" <njt@ayvali.org> To: Hans Nieser <h.nieser@xs4all.nl> Cc: freebsd-questions@freebsd.org Subject: mysql backups (was Re: Remote backups, reading from and writing to the same file) Message-ID: <20060113145003.GJ2451@ayvali.org> In-Reply-To: <43C6E55A.8020500@xs4all.nl> References: <43C6E55A.8020500@xs4all.nl>
next in thread | previous in thread | raw e-mail | index | archive | help
* Hans Nieser <h.nieser@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@ayvali.org
Etiamsi occiderit me, in ipso sperabo
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20060113145003.GJ2451>