O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

How to do it...

The outline is: you create the table on the destination with the same table definition and execute the DISCARD TABLESPACE command on the destination. Execute FLUSH TABLES FOR EXPORT on the source, which ensures that changes to the named table have been flushed to disk, and so a binary table copy can be made while the instance is running. After that statement, the table is locked and does not accept any writes; however, reads can happen. You can copy the .ibd file of that table to the destination, execute UNLOCK tables on source, and finally execute the IMPORT TABLESPACE command, which accepts the copied .ibd file.

For example, you want to copy the events_history table in a test database from one server (source) to another ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required