>I would apreciate your input on the following solution to the problem
>below.
>
>We have a nightly job that takes too long to run. This is mostly
>because we have to save our mysql data files (*.MYD and *.MYI) to
>another location.
>
>While this takes place, the mysqld process has to be down and we
>experience down time. We need to do this at 12AM EST which is a
>problem for Tokyo since it is smack during their busy hours.
>
>What if we just renamed the files, put empty ones in their place and
>restarted mysqld? That is:
>
>we have these files which are 8GB in total:
>
>~/mysql/data/mydb/XYZ.frm
>~/mysql/data/mydb/XYZ.MYD
>~/mysql/data/mydb/XYZ.MYI

I'd like to suggest this procedure:
1. LOCK TABLES
2. Rename table XYZ to ZYX (using ALTER TABLE).
Make sure that no other SQL references table ZYX.
3. Create a new table XYZ using the same schema with CREATE TABLE.
4. FLUSH TABLES
5. UNLOCK TABLES
6. Copy ZYX files at your leisure, hoping it takes less than 24 hours to
do the copy.
7. Delete the ZYX files when the copy is done.

Advantages: no mysqld downtime. Only a very brief time (Steps 2-5
ought to execute in under a second) when table XYZ doesn't exist,
and you're locked during that time. Established connections don't
have to be dropped. If you could keep references to table XYZ from
happening in some other way, you could omit steps 1 and 5.


I'll suggest an alternative to steps 6 and 7:

6. mysqldump table ZYX and copy it elsewhere
7. DROP TABLE ZYX