Delete and Recreate Indexes in MySQL to reduce table size for copying the db to a new server?

Question

I have to move a given database of 100gb from server a to server b and later this year to server c with a copy for testing to server d.

So all servers are Unix (mostly Debian) with MySQL. The databases are MyISAM.

My biggest table, which holds 99% off all data, is 47gb of data (MID) with additional 50gb of indexes (MIY).

My idea is to:

1) Tar the database files
2) copy to new server
3) un-tar it
4) copy it to new mysql folder

My question:

Can I delete the index-file and recreate the indexes on the new server to reduce the copy and tar-time by 50%?

• Masoud: I would recommend Logical back up instead. File copy will cause problem if certain circumstances. So i suggest using mysqldump
• PassionateDeveloper: Hey, I tried mysqldump, it crash's allready on a smaller table (17GB) becuase of a connection timeout after 3 hours, I dont want to know how it should work on 100gb+...
• Rick James: What's the bandwidth between the servers? If low, compress the file(s) during the transmission.
• Michael - sqlbot: If mysqldump isn't working, then you are doing something wrong, and you should consider posting a new question, to figure out why, assuming there is not already an answer here that addresses the problem. It is the standard tool for this task, and is by far the safest approach.

Answer 1

You asked

Can I delete the index-file and recreate the indexes on the new server to reduce the copy and tar-time by 50%?

Yes, you can. You copy just the .MYD into the tar file. You will need a blank .MYI file.

SUGGESTION

Suppose you are moving mydata.mytable. You will have

• /var/lib/mysql/mydata/mytable.frm
• /var/lib/mysql/mydata/mytable.MYD
• /var/lib/mysql/mydata/mytable.MYI

STEP 01 : Make Copy of Table Structure

CREATE DATABASE IF NOT EXISTS myjunk;
CREATE TABLE myjunk.mytable LIKE mydata.mytable;

STEP 02 : Copy Real Data and Blank Index File

• COPY S1:/var/lib/mysql/mydata/mytable.frm -> S2:/var/lib/mysql/mydata/mytable.frm
• COPY S1:/var/lib/mysql/mydata/mytable.MYD -> S2:/var/lib/mysql/mydata/mytable.MYD
• COPY S1:/var/lib/mysql/myjunk/mytable.MYI -> S2:/var/lib/mysql/mydata/mytable.MYI

STEP 03 : Rebuild Index File on Target Server

REPAIR TABLE mydata.mytable;

GIVE IT A TRY !!!

• Rick James: Leave out the copy of the .MYI file to save half the transfer time, at the cost of an unknown REPAIR time.
• RolandoMySQLDBA: @RickJames Please look at the thrid line in Step 02. He has to copy the blank .MYI. That way, Step 03 rebuild indexes starting the index structure only. I have done this before.
• Rick James: Subtle diff.... (Next time suggest using different string lengths; that would have jarred me into noticing.)
• PassionateDeveloper: Hey, I tried it, but it doesn't work that well... in fact the repait table needs around 4 hours on ym 50gb data while the copy of the index is only 2 hours... thx anyway
• RolandoMySQLDBA: If your .MYI is larger than your .MYD, you may want to make sure you don't have needless indexes. Please look into using pt-duplicate-key-checker (percona.com/doc/percona-toolkit/2.1/…)