Right now the procedure for a partial backup, and restore on another server, using transportable tablespaces, is:
- take backup with mariabackup
- take schema-only backup with `mysqldump --no-data`
- prepare backup with `mariabackup --prepare --export`
- create tables on target system, using the schema-only dump, or parts of it
- DISCARD newly created tables
- transfer .ibd and .cfg files from backup to correct location
- IMPORT transferred files
This approach only works when the newly created table is exactly the same as on the original server. There are several cases though where that may not be the case:
- table was created without explicit ROW_FORMAT, and the two servers having different innodb_default_row_format settings
- different mysql56_temporal_types setting on both servers
- table was originally created on a server version that did not have subsecond support, and contains a TIME, DATETIME or TIMESTAMP column
In all such cases IMPORT will not work, and the list may not even be complete ...
So my request is for a way to make sure a table can be created in exactly the same way as on the original server, without having to manually check for differences not covered by SHOW CREATE TABLE output.
Maybe adding a new SHOW FULL CREATE TABLE command, or an option to mysqldump that makes it gather additional information from the INFORMATION_SCHEMA to rewrite SHOW CREATE output by adding further create options.
Or have mariabackup itself create appropriate CREATE statement .sql files along with the .cfg files?