The COPY FROM
will not violate integrity of database.
When a constraint fails, an error is throw, and none of data are copied (it is rolled back).
This behavior is unchangeable. You can do a script in a programming language to help you in a copy.
There are a few solutions. First, however, I'm not sure about the process in the first place. Is this a one time copy operation? A recurring copy operation? Do you want to migrate from MyISAM to InnoDB?
What is the main reason for your desire for a quick operation?
If you're looking for migration, then why don't you use an online table alter tool, such as oak-online-alter-table (disclaimer: I'm author of this tool) or pt-online-schema-change? Both will allow you to change your schema live and online with very little disturbance.
If you're looking to a copy+paste of your data, then I would suggest using chunking: copying the data in small packets. This way you don't get that huge lock and no funny timeouts. You can use either oak-chunk-update or pt-archiver for this. This may actually make the total runtime shorter because of reduces locking, but may also take longer. Also consider that it is not an atomic operation, and changes to original table while copying is made, may not get caught, so you may get an inconsistent copy.
Otherwise (or in addition) you can use all the usual tweaks, such as
SET GLOBAL innodb_flush_log_at_trx_commit := 2;
or set
[mysqld]
innodb_doublewrite = 0
or perhaps, depending on OS and disks,
[mysqld]
innodb_flush_method = O_DIRECT
Each of the above may reduce disk I/O access. First two will also make your server less crash safe. But if for limited time, this may be OK for you.
Best Answer
It just works, like it should. It doesn't lock the table, so start testing.