Fastest way to copy a large MySQL table?

Question

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

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

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

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

EDIT: I will not 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 whole copy / conversion copy process without worrying about manually dropping any tables. Therefore, it would be helpful if you could answer which of the above options is better.

Answer 1

Turn off the three options listed above.

I would choose the second option if you have a Unique constraint on at least one column, so don't create duplicate rows if the script needs to be executed multiple times in order to complete its task in the server timeout event.

Otherwise, your third option will be a transition method, while manually taking into account any server timeouts to determine insertion limits.

Answer 2

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

Answer 3

If possible, the fastest way would be to use the database offline and simply copy the data files to disk.

Because of this, it has some requirements:

• You can stop the database when copying.
• you use a storage engine that stores each table in separate files, MyISAM does this.
• you have privileged access to the database server (root login or similar)

And, I see, you edited your post, then I think that this DBA-from-hell approach is not an option ... but still it is fast!

Answer 4

Use stored procedure

Option two should be the fastest, but it will be a very long transaction. You should look at creating a stored procedure that executes the copy. This way you can offload some of the parsing / data processing from the MySQL engine.

Answer 5

The best way I find so far is to create files as dump files (.txt) using outfile for text and then using infile in mysql to get the same data in the database