Copying a table to another MySQL database

Unfortunately, in MySQL there are no decent export tools similar to Data Pump in Oracle. Luckily one can copy either the entire database data folder or only the required individual tables. The text is adapted from the MySQL manual.

1. In the source database, first generate the CREATE statement for the target table so that an identical table can be created in the target database. Then execute an SQL command:

   FLUSH TABLES TEMP FOR EXPORT;

   A TEMP.cfg file is created in the MySQL data directory. This file has to be copied to the target machine. I first copy it to /tmp, so that it becomes more accessible.

   sudo cp TEMP.{ibd,cfg} /tmp
   cd /tmp
   chmod 644 TEMP.*
   

   Then execute an SQL command. The previously created TEMP.cfg disappears.

   UNLOCK TABLES;

2. In the target database, execute SQL to create an identical table using the DDL from the source database and discard its tablespace:

   CREATE TABLE `TEMP` (
     `INTERVENTION_LIBELLE` varchar(93) NOT NULL,
     ...
     KEY `INTERVENTION_LIBELLE_idx` (`INTERVENTION_LIBELLE`),
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

   ALTER TABLE TEMP DISCARD TABLESPACE;

   Copy the files from the source machine to the local MySQL data folder:

   cd /data1/mysql/axya/
   scp test@source-machine:/tmp/TEMP.{ibd,cfg} .
   chmod 640 TEMP.*
   chown mysql:mysql TEMP.*
   

   Execute the last SQL command after which the data becomes usable.

   ALTER TABLE TEMP IMPORT TABLESPACE;