The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

Questions about backup database using idb files

hello i have three questions about database backup using idb files. suppose i do not want to backup table structure because i have the DDL used to create the database but i want to backup only data and indexes (idb files), 1- suppose i used flush table for export and OS copy to backup idb files then i created database tables on other server using exactly the same DDL and used discard tablespace and import tablespace to restore, will i have problems related to difference in table id between source and destination? 2- if i did not execute flush for export and copied idb files while server is running what would happen? - i can not copy idb files while server is running (OS will prevent me because files are open in another process) - copied files may be inconsistent and unusable if they have been copied while a transaction is running. - copied files will be consistent and usable but data in transactions that where running during copy operation and not commited yet will be lost. - copied files will be consistent and usable but unknown amount of data will be lost 3- Can i use one tablespace for eg two tables (not all tables in one tablespace, not file per table)?

thanks for reply.

Answer Answered by Marko Mäkelä in this comment.

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.