Is it really true that XtraBackup requires the original table definition to be created before it can restore the table files using “IMPORT TABLESPACE”? If so that seems like a big oversight. This functionality should be built into XtraBackup. Imagine this scenario:
- DBA has a 500 GB MySQL database on a production server. The server is a standalone server (no replication).
- An application or user (or careless DBA) accidentally drops a very important 1 GB table that is the key to the whole application.
- DBA tries to restore the table from the XtraBackup backup, but doesn’t have the definition of the table to create the empty table first.
- DBA gets fired because the entire 500 GB (the whole server) needs to be restored in order to restore the innodb table dictionary stuff.
Am I missing something here? Seems like as part of the backup process the XtraBackup software should perform some sort of “mysqldump --no-data” on the backed up databases and store that away for later table creation use if needed. Better yet, I think the table definitions can be retrieved from the backup files using the minimal innodb engine that is used to apply the logs during the backup process.