Description:
While this may be used infrequently if you want to copy tables from one system to another or want to rebuild an instance cleanly (to recover ibdata1 file space etc) the current FLUSH TABLE <table> FOR EXPORT generates a .cfg file but does not generate the corresponding .sql file with the table definition, yet this information is always needed.
worklog WL#5522: InnoDB: Transportable Single-Table Tablespaces seen on https://dev.mysql.com/worklog/ suggests as a high level architecture that the SQL table definition is needed but the current documentation for exporting or importing of tablespaces requires this to be handled manually, something that's tedious if you have multiple tablespaces to copy or rebuild.
How to repeat:
* Read the worklog, https://dev.mysql.com/worklog/
* Read the tablespace export and import documentation, here https://dev.mysql.com/doc/refman/8.0/en/innodb-table-import.html and https://dev.mysql.com/doc/refman/8.0/en/flush.html
Suggested fix:
Improve the current implementation to:
- on export (FLUSH TABLE .... FOR EXPORT): save .sql files (SHOW CREATE TABLE output ready to be read to be executed) for each table in the same directory the .cfg file is created, overwrite any file that's present if needed
- on import (using existing syntax ALTER TABLE <name> IMPORT TABLESPACE): if the .sql file is there re-create the table definition based on the .sql file and then directly read the .cfg .ibd files and import as normal.
This simplifies the rebuild process of an instance and removes tedious steps of exporting the SQL prior to reimporting it again.
