SEO

Hi,

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

After shutting down mysql, we could:

# Move the old files out of the way so we can archive them later
mv ~/mysql/data/mydb/XYZ.frm XYZ_old.frm
mv ~/mysql/data/mydb/XYZ.MYD XYZ_old.MYD
mv ~/mysql/data/mydb/XYZ.MYI XYZ_old.MYI

# Copy empty ones in their place so we can start our process
cp ~/mysql/data/mydb/XYZ_emptyShell.frm XYZ.frm
cp ~/mysql/data/mydb/XYZ_emptyShell.MYD XYZ.MYD
cp ~/mysql/data/mydb/XYZ_emptyShell.MYI XYZ.MYI

Start mysqld.

Assume XYZ_emptyShell.* are valid mysql files representing an XYZ
table with 0 rows.

Would mysql complain if we do this to it?

yankeerivera@yahoo.com wrote:

> 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:
<cut>...</cut>
> Assume XYZ_emptyShell.* are valid mysql files representing an XYZ
> table with 0 rows.

If you don't need the old data, that would work, but why not just mirror the
hard drive, that way you always have a backup.

--

//Aho

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/

>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