Fastest way to copy a large MySQL table?

Question

What's the best way to copy a large MySQL table in terms of speed and memory use?

Option 1. Using PHP, select X rows from old table and insert them into the new table. Proceed to next iteration of select/insert until all entries are copied over.

Option 2. Use MySQL INSERT INTO ... SELECT without row limits.

Option 3. Use MySQL INSERT INTO ... SELECT with a limited number of rows copied over per run.

EDIT: I am not going to use mysqldump. The purpose of my question is to find the best way to write a database conversion program. Some tables have changed, some have not. I need to automate the entire copy over / conversion procedure without worrying about manually dumping any tables. So it would be helpful if you could answer which of the above options is best.

Answer 1

There is a program that was written specifically for this task called mysqldump.

Answer 2

mysqldump is a great tool in terms of simplicity and careful handling of all types of data, but it is not as fast as load data infile

If you're copying on the same database, I like this version of Option 2:

a) CREATE TABLE foo_new LIKE foo;

b) INSERT INTO foo_new SELECT * FROM foo;

I've got lots of tables with hundreds of millions of rows (like 1/2B) AND InnoDB AND several keys AND constraints. They take many many hours to read from a MySQL dump, but only an hour or so by load data infile. It is correct that copying the raw files with the DB offline is even faster. It is also correct that non-ASCII characters, binary data, and NULLs need to be handled carefully in CSV (or tab-delimited files), but fortunately, I've pretty much got numbers and text :-). I might take the time to see how long the above steps a) and b) take, but I think they are slower than the load data infile... which is probably because of transactions.