discuss

discuss@lists.mariadb.org

February 2019

12 participants
11 discussions

[Maria-discuss] InnoDB, Cold Physical Backups, and Transportable Tablespaces
by mariadb＠Biblestuph.com 06 Feb '19

06 Feb '19

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

1 0