maria-discuss team mailing list archive

[mariadb@xxxxxxxxxxxxxx]

Trying to get my head around how (if) the transportable tablespaces 
feature of InnoDB can be used in conjunction with cold DB backups. I'm 
strictly talking here about a DB where innodb_file_per_table=1.

Specifically, is "FLUSH TABLES FOR EXPORT" necessary when doing a cold 
backup of the DB (using innodb_fast_shutdown=0)? I.E., I originally 
envisioned something like this:

  1.) Save a copy of all CREATE TABLE statements for all tables
  2.) Shutdown the DB with innodb_fast_shutdown=0
  3.) Copy the full contents of the data dir, cnf, etc.

Now, my hope was that I would be able to not only restore the full DB as 
needed from the saved copy, but also be able to restore individual 
tables both to the original source DB as well as remote DBs, using the 
CREATE TABLE statement to create the table on the remote, ALTER TABLE 
... DISCARD TABLESPACE, copy the .ibd and .frm in, and then ALTER TABLE 
... IMPORT TABLESPACE. But trying this I get:

ERROR 1030 (HY000): Got error -1 from storage engine

I'm currently using MariaDB 5.5, thus I don't have support for FLUSH 
TABLES FOR EXPORT. But at this point I wasn't sure if I just need to 
upgrade for this to work or if I was completely barking up the wrong 
tree. If I did upgrade to 10.0 (or later), would my plan above work as 
described? Or would I also need to do the FLUSH TABLES FOR EXPORT 
(perhaps before step 1)?

Thanks,

Dan