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%?
Answer :
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;