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%?

I would recommend Logical back up instead. File copy will cause problem if certain circumstances. So i suggest using mysqldump — Masoud
– Masoud, Commented May 18, 2015 at 10:28
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+... — PassionateDeveloper
– PassionateDeveloper, Commented May 18, 2015 at 10:50
What's the bandwidth between the servers? If low, compress the file(s) during the transmission. — Rick James
– Rick James, Commented May 18, 2015 at 19:02
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. — Michael - sqlbot
– Michael - sqlbot, Commented May 19, 2015 at 1:57

RolandoMySQLDBA · Accepted Answer · 2015-05-18 19:24:39Z

2

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

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;

answered May 18, 2015 at 17:38

RolandoMySQLDBA

186k34 gold badges327 silver badges543 bronze badges