17.15.8 Retrieving InnoDB 表pace 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”.
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.
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.
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
.
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.
mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES
WHERE FILE_NAME LIKE '%undo%';