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, see
Section 24.3.9, “The INFORMATION_SCHEMA FILES Table”.
The INNODB_SYS_TABLESPACES
and
INNODB_SYS_DATAFILES
tables also
provide metadata about InnoDB
tablespaces,
but data is limited to file-per-table and general tablespaces.
This query retrieves metadata about the InnoDB
system tablespace from columns 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 copymysql> 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 copymysql> 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 InnoDB
temporary tablespaces. Temporary tablespace file names are
prefixed by ibtmp
.
Press CTRL+C to copymysql> 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, if separate undo tablespaces are configured.
Press CTRL+C to copymysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%undo%';