Delete and Recreate Indexes in MySQL to reduce table size for copying the db to a new server? 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%?
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 !!!