NYCPHP Meetup

[Daniel J Cain Jr.]

On Fri, 2004-02-27 at 11:15, Carlos A Hoyos wrote:
> . The only catch I can remember: mysql has to be stopped
> before copying the files. Attempting to copy data directory with mysql
> running gives corrupt files.
This is kinda OT from the original question of 'moving' the files.  Even
though this is a backup solution it should be a short hop to a moving
mechanism.

A solution I have found that works very well for backing up LIVE MySQL
databases without having to take the DB offline is as follows.

-Take the list of DB's you want backed up/migrated.
	-For each table within each DB
		-- Lock that table
		-- Flush that table
		-- rsync that table to your backup location (filesystem|server)
		-- UnLock that table
	-Next Table
-Next DB

You lock the table to make sure it doesn't change during your backup.

You flush the table to make sure the table on the file system is
complete and doesn't have some parts of it only in memory(otherwise you
end up backing a corrupted table file).

Use rsync to copy only the file deltas (live vs. last backup copy) to
minimize the time the table must be locked.  The other option is to copy
over the whole file, but for a lot of my tables this takes way too long
and is redundant in my opinion.  The first rsync you won't see a speed
boost, without the destination file everything must be moved.  But the
subsequent ones have the potential to be MUCH faster.

I believe MySQL data files cannot be copied from one platform to
another, like Windows to UNIX without possible file corruption. 
Something to do with the format of the files on one system vs another. 
But from box 1:OS A to box 2:OS A you should not have a problem.

At the time I implemented this backup scheme I could not find a free
utility to backup a live MySQL DB WITHOUT having to copy the entire file
every time.  See below as to why it was necessary to find a better
method.

To give you an idea of the performance boost you can get.

A full copy of the DBs and all their tables was taking approximately 45
minutes to complete.  It was also locking everything in the beginning
and unlocking everything at the end.  Which made the site useless during
that time, which sucked a lot because site traffic is pretty constant
and very large.

The new solution takes less than 2 minutes almost all the time (never
more than 4 minutes in my case).

Hope this helps some folks out there in their travels through cyberspace
:)

-dan