The Ultimate Guide to High Availability with MariaDB

Comments - Questions about backup database using idb files

4 years, 11 months ago Marko Mäkelä
Re: Questions about backup database using idb files [Answer]

The .ibd files never store table ID. Index pages do store index ID and transaction ID, and every page contains a tablespace ID and a log sequence number (LSN). All these will be rewritten by ALTER TABLE…IMPORT TABLESPACE.

I would like to improve this:

  • MDEV-11658 Simpler, faster IMPORT of InnoDB tables
  • MDEV-15225 Can't import .ibd file with temporal type format differing from mysql56_temporal_format

The reason why arbitrarily copying files while InnoDB is running is not safe is that the InnoDB buffer pool basically acts as a write-back cache. Also, plain FLUSH TABLES or FLUSH TABLES…WITH READ LOCK has no effect on InnoDB, because InnoDB can perform some writes in the background, on files that have not been touched by SQL for quite a while. These background writes will be synchronized and suppressed by FLUSH TABLES…FOR EXPORT.

MySQL 5.7 implemented CREATE TABLESPACE for InnoDB, but MariaDB did not follow with that. It is hard to design and implement all aspects of that in a consistent way. For example, you cannot export and import such shared tablespaces in MySQL.

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.