Documentation Home
MySQL 9.2 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.5Mb
PDF (A4) - 40.6Mb
Man Pages (TGZ) - 259.6Kb
Man Pages (Zip) - 366.8Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


MySQL 9.2 Reference Manual  /  ...  /  Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES

17.15.8 Retrieving InnoDB Tablespace Metadata from INFORMATION_SCHEMA.FILES

The Information Schema FILES table provides metadata about all InnoDB tablespace types including file-per-table tablespaces, general tablespaces, the system tablespace, temporary table tablespaces, and undo tablespaces (if present).

This section provides InnoDB-specific usage examples. For more information about data provided by the Information Schema FILES table, see Section 28.3.15, “The INFORMATION_SCHEMA FILES Table”.

Note

The INNODB_TABLESPACES and INNODB_DATAFILES tables also provide metadata about InnoDB tablespaces, but data is limited to file-per-table, general, and undo tablespaces.

This query retrieves metadata about the InnoDB system tablespace from fields of the Information Schema FILES table that are pertinent to InnoDB tablespaces. FILES columns that are not relevant to InnoDB always return NULL, and are excluded from the query.

Press CTRL+C to copy
mysql> SELECT FILE_ID, FILE_NAME, FILE_TYPE, TABLESPACE_NAME, FREE_EXTENTS, -> TOTAL_EXTENTS, EXTENT_SIZE, INITIAL_SIZE, MAXIMUM_SIZE, AUTOEXTEND_SIZE, DATA_FREE, STATUS ENGINE -> FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME LIKE 'innodb_system' \G *************************** 1. row *************************** FILE_ID: 0 FILE_NAME: ./ibdata1 FILE_TYPE: TABLESPACE TABLESPACE_NAME: innodb_system FREE_EXTENTS: 0 TOTAL_EXTENTS: 12 EXTENT_SIZE: 1048576 INITIAL_SIZE: 12582912 MAXIMUM_SIZE: NULL AUTOEXTEND_SIZE: 67108864 DATA_FREE: 4194304 ENGINE: NORMAL

This query retrieves the FILE_ID (equivalent to the space ID) and the FILE_NAME (which includes path information) for InnoDB file-per-table and general tablespaces. File-per-table and general tablespaces have a .ibd file extension.

Press CTRL+C to copy
mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES -> WHERE FILE_NAME LIKE '%.ibd%' ORDER BY FILE_ID; +---------+---------------------------------------+ | FILE_ID | FILE_NAME | +---------+---------------------------------------+ | 2 | ./mysql/plugin.ibd | | 3 | ./mysql/servers.ibd | | 4 | ./mysql/help_topic.ibd | | 5 | ./mysql/help_category.ibd | | 6 | ./mysql/help_relation.ibd | | 7 | ./mysql/help_keyword.ibd | | 8 | ./mysql/time_zone_name.ibd | | 9 | ./mysql/time_zone.ibd | | 10 | ./mysql/time_zone_transition.ibd | | 11 | ./mysql/time_zone_transition_type.ibd | | 12 | ./mysql/time_zone_leap_second.ibd | | 13 | ./mysql/innodb_table_stats.ibd | | 14 | ./mysql/innodb_index_stats.ibd | | 15 | ./mysql/slave_relay_log_info.ibd | | 16 | ./mysql/slave_master_info.ibd | | 17 | ./mysql/slave_worker_info.ibd | | 18 | ./mysql/gtid_executed.ibd | | 19 | ./mysql/server_cost.ibd | | 20 | ./mysql/engine_cost.ibd | | 21 | ./sys/sys_config.ibd | | 23 | ./test/t1.ibd | | 26 | /home/user/test/test/t2.ibd | +---------+---------------------------------------+

This query retrieves the FILE_ID and FILE_NAME for the InnoDB global temporary tablespace. Global temporary tablespace file names are prefixed by ibtmp.

Press CTRL+C to copy
mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%ibtmp%'; +---------+-----------+ | FILE_ID | FILE_NAME | +---------+-----------+ | 22 | ./ibtmp1 | +---------+-----------+

Similarly, InnoDB undo tablespace file names are prefixed by undo. The following query returns the FILE_ID and FILE_NAME for InnoDB undo tablespaces.

Press CTRL+C to copy
mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%undo%';