Fastest way to copy a large MySQL table?

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

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.

Off the three options listed above.

I would select the second option if you have a Unique constraint on at least one column, therefore not creating duplicate rows if the script has to be run multiple times to achieve its task in the event of server timeouts.

Otherwise your third option would be the way to go, while manually taking into account any server timeouts to determine your insert select limits.