The following exerpt came out of the book "High Performance MySQL, Second Edition".
This is an excellent book and I would recommend it to anyone.
The short answer is:
With your table size and conditions, no matter what method you choose, I think you're potentially in for a long wait.
Table Conversions
There are several ways to convert a table from one storage engine to another, each
with advantages and disadvantages.
ALTER TABLE
mysql> ALTER TABLE mytable ENGINE = Falcon;
This syntax works for all storage engines, but there’s a catch: it can take a lot of time.
MySQL will perform a row-by-row copy of your old table into a new table. During
that time, you’ll probably be using all of the server’s disk I/O capacity, and the original
table will be read-locked while the conversion runs.
Dump and import
To gain more control over the conversion process, you might choose to first dump
the table to a text file using the mysqldump utility. Once you’ve dumped the table,
you can simply edit the dump file to adjust the CREATE TABLE statement it contains. Be
sure to change the table name as well as its type, because you can’t have two tables
with the same name in the same database even if they are of different types—and
mysqldump defaults to writing a DROP TABLE command before the CREATE TABLE, so you
might lose your data if you are not careful!
CREATE and SELECT
The third conversion technique is a compromise between the first mechanism’s
speed and the safety of the second. Rather than dumping the entire table or converting
it all at once, create the new table and use MySQL’s INSERT ... SELECT syntax to
populate it, as follows:
mysql> CREATE TABLE innodb_table LIKE myisam_table;
mysql> ALTER TABLE innodb_table ENGINE=InnoDB;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table;
That works well if you don’t have much data, but if you do, it’s often more efficient
to populate the table incrementally, committing the transaction between each chunk
so the undo logs don’t grow huge. Assuming that id is the primary key, run this
query repeatedly (using larger values of x and y each time) until you’ve copied all the
data to the new table:
mysql> START TRANSACTION;
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table
-> WHERE id BETWEEN x AND y;
mysql> COMMIT;
After doing so, you’ll be left with the original table, which you can drop when you’re
done with it, and the new table, which is now fully populated. Be careful to lock the
original table if needed to prevent getting an inconsistent copy of the data!
Best Answer
Copying ibdata files around is fraught with danger. Important bits of metadata can be stored in other files such as ib_logfile0 and ib_logfile1 (this is not a comprehensive list) and the settings on the new server (such as the size of those two logfiles) needs to be exactly the same as the old server. If you get any of this stuff wrong or miss copying one of the files, the new server will not start and you will have to deal with MySQL's helpful error messages in the error log.
Xtrabackup handles all of this for you. After you use it to dump your data and "prepare" it, you can do a straight file copy to the new server and start it up with the new ibdata file. The "prepare" step takes significant time.
However, since you mentioned that the database you are moving is only 100GB of the 250GB ibdata file, I suspect it will be quicker using the mysqldump method simply because it only has to transfer 100GB of data instead of 250GB.
You can pipe the output of mysqldump straight into mysql to avoid having to save the dump to disk like this:
mysqldump -uusername -pmypassword MyDB | mysql -h server2 -uusername -pmypassword MyDBThe mysql client has a -C option that enables compression if both ends support it. Just put it in before the -h option. The transfer over the network is likely to be the slowest part of the whole operation. If your server doesn't support compression, you could do the transfer via ssh with the -C option instead:
mysqldump -uusername -pmypassword MyDB | ssh -C server2 "mysql -uusername -pmypassword MyDB"Last thought: The time it has taken me to write this is probably longer than both methods put together unless you have a 10MB/s network. Get it started. :-)