yankeerivera@yahoo.com wrote:
>
> 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.

You can make a copy of the MyISAM table files *with running mysqld*
provided that

a) the table has been flushed to disk and
b) the table is not written to while you copy files

both can be accomplished by FLUSH TABLES WITH READ LOCK. Another way
would be LOCK TABLES ... READ followed by FLUSH TABLE ...
After you finished copying, just UNLOCK TABLES.

During copying, either all tables or at least the table in question
would be locked, allowing read only operation - all writes would have
to wait until you unlock the table(s). This is not perfect, but
probably much better than mysqld being completely offline.


> What if we just renamed the files, put empty ones in their place and
> restarted mysqld? That is:

<cut>

This could be done with running mysqld as well

1. create empty copy of your table (CREATE TABLE ... LIKE ...)
2. rename tables, effectively swapping old and new table
(this is an atomic operation, even for multiple renames)
3. flush the old table
4. copy the files
5. drop the old table


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/