2

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 May 18 '15 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+... – Kovu May 18 '15 at 10:50
  • What's the bandwidth between the servers? If low, compress the file(s) during the transmission. – Rick James May 18 '15 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 May 19 '15 at 1:57
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

  • /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 !!!

  • Leave out the copy of the .MYI file to save half the transfer time, at the cost of an unknown REPAIR time. – Rick James May 18 '15 at 19:01
  • @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. – RolandoMySQLDBA May 18 '15 at 19:03
  • Subtle diff.... (Next time suggest using different string lengths; that would have jarred me into noticing.) – Rick James May 18 '15 at 19:05
  • 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 – Kovu May 22 '15 at 9:23
  • 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/…) – RolandoMySQLDBA May 22 '15 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.