MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
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%';