从INFORMATION_SCHEMA.FILES中获取InnoDB表空间元数据
INFORMATION_SCHEMA。FILES表提供了所有InnoDB表空间类型的元数据，包括file-per-table表空间、通用表空间、system表空间、临时表空间和undo表空间(如果存在)。
注意:
INNODB_SYS_TABLESPACES和INNODB_SYS_DATAFILES表也提供了关于InnoDB表空间的元数据，但数据仅限于file-per-table和通用表空间。
该查询从INFORMATION_SCHEMA.FILES表的字段中检索有关InnoDB系统表空间的元数据。与InnoDB无关的字段总是返回NULL，并且被排除在查询之外。
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: 2
TOTAL_EXTENTS: 74
EXTENT_SIZE: 1048576
INITIAL_SIZE: 77594624
MAXIMUM_SIZE: NULL
AUTOEXTEND_SIZE: 67108864
DATA_FREE: 37748736
ENGINE: NORMAL
1 row in set (0.01 sec)
对于InnoDB file-per-table和通用表空间，下面这个查询检索FILE_ID(等价于space ID)和FILE_NAME(包含路径信息)。file-per-table和通用表空间的文件扩展名是.ibd。
mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%.ibd%' ORDER BY FILE_ID; +---------+----------------------------------------------------------+ | FILE_ID | FILE_NAME | +---------+----------------------------------------------------------+ | 5 | ./mysql/plugin.ibd | | 6 | ./mysql/servers.ibd | | 7 | ./mysql/help_topic.ibd | | 8 | ./mysql/help_category.ibd | | 9 | ./mysql/help_relation.ibd | | 10 | ./mysql/help_keyword.ibd | | 11 | ./mysql/time_zone_name.ibd | | 12 | ./mysql/time_zone.ibd | | 13 | ./mysql/time_zone_transition.ibd | | 14 | ./mysql/time_zone_transition_type.ibd | | 15 | ./mysql/time_zone_leap_second.ibd | | 16 | ./mysql/innodb_table_stats.ibd | | 17 | ./mysql/innodb_index_stats.ibd | | 18 | ./mysql/slave_relay_log_info.ibd | | 19 | ./mysql/slave_master_info.ibd | | 20 | ./mysql/slave_worker_info.ibd | | 21 | ./mysql/gtid_executed.ibd | | 22 | ./mysql/server_cost.ibd | | 23 | ./mysql/engine_cost.ibd | | 24 | ./sys/sys_config.ibd | | 37 | ./ts1.ibd | | 38 | ./ts2.ibd | | 51 | ./test/user.ibd | | 57 | ./mysql/test_index_1.ibd | | 58 | ./test/test_index_1.ibd | | 59 | ./employees/employees.ibd | | 60 | ./employees/departments.ibd | | 61 | ./employees/dept_manager.ibd | | 62 | ./employees/dept_emp.ibd | | 63 | ./employees/titles.ibd | | 64 | ./employees/salaries.ibd | | 262 | ./test/bmsql_config.ibd | | 272 | ./test/bmsql_warehouse.ibd | | 279 | ./test/bmsql_item.ibd | | 280 | ./test/bmsql_district.ibd | | 281 | ./test/bmsql_customer.ibd | | 283 | ./test/bmsql_history.ibd | | 284 | ./test/bmsql_new_order.ibd | | 285 | ./test/bmsql_oorder.ibd | | 287 | ./test/bmsql_order_line.ibd | | 289 | ./test/bmsql_stock.ibd | | 293 | ./ts01.ibd | | 294 | ./ts02.ibd | | 414 | ./employees/t.ibd | | 415 | ./employees/c.ibd | | 420 | ./test/ts03.ibd | | 423 | ./test/abc.ibd | | 425 | ./test/t3.ibd | | 426 | ./test/t4.ibd | | 427 | ./test/t7.ibd | | 428 | ./test/t8.ibd | | 472 | ./undo/opening_lines.ibd | | 473 | ./undo/FTS_00000000000001d9_BEING_DELETED.ibd | | 474 | ./undo/FTS_00000000000001d9_BEING_DELETED_CACHE.ibd | | 475 | ./undo/FTS_00000000000001d9_CONFIG.ibd | | 476 | ./undo/FTS_00000000000001d9_DELETED.ibd | | 477 | ./undo/FTS_00000000000001d9_DELETED_CACHE.ibd | | 478 | ./undo/FTS_00000000000001d9_00000000000002da_INDEX_1.ibd | | 479 | ./undo/FTS_00000000000001d9_00000000000002da_INDEX_2.ibd | | 480 | ./undo/FTS_00000000000001d9_00000000000002da_INDEX_3.ibd | | 481 | ./undo/FTS_00000000000001d9_00000000000002da_INDEX_4.ibd | | 482 | ./undo/FTS_00000000000001d9_00000000000002da_INDEX_5.ibd | | 483 | ./undo/FTS_00000000000001d9_00000000000002da_INDEX_6.ibd | | 484 | ./undo/t1.ibd | | 485 | ./ts3.ibd | | 487 | ./undo/big_table.ibd | | 489 | ./undo/key_block_size_4.ibd | | 490 | ./ts4.ibd | | 491 | ./ts5.ibd | | 492 | ./ts6.ibd | | 494 | ./undo/t7.ibd | | 497 | ./undo/t8.ibd | | 498 | ./undo/employees.ibd | | 499 | ./undo/t9.ibd | | 500 | ./undo/t10.ibd | | 501 | ./undo/t11.ibd | | 503 | ./undo/t12.ibd | | 506 | ./undo/t13.ibd | | 507 | ./undo/t14.ibd | | 509 | ./test/parent.ibd | | 510 | ./test/child.ibd | | 523 | ./test/articles.ibd | | 524 | ./test/FTS_000000000000020c_BEING_DELETED.ibd | | 525 | ./test/FTS_000000000000020c_BEING_DELETED_CACHE.ibd | | 526 | ./test/FTS_000000000000020c_CONFIG.ibd | | 527 | ./test/FTS_000000000000020c_DELETED.ibd | | 528 | ./test/FTS_000000000000020c_DELETED_CACHE.ibd | | 529 | ./test/FTS_000000000000020c_0000000000000310_INDEX_1.ibd | | 530 | ./test/FTS_000000000000020c_0000000000000310_INDEX_2.ibd | | 531 | ./test/FTS_000000000000020c_0000000000000310_INDEX_3.ibd | | 532 | ./test/FTS_000000000000020c_0000000000000310_INDEX_4.ibd | | 533 | ./test/FTS_000000000000020c_0000000000000310_INDEX_5.ibd | | 534 | ./test/FTS_000000000000020c_0000000000000310_INDEX_6.ibd | | 535 | ./test/t1.ibd | +---------+----------------------------------------------------------+ 94 rows in set (0.00 sec)
下面这个查询获取了InnoDB临时表空间的FILE_ID和FILE_NAME。临时表空间文件名以ibtmp为前缀。
mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%ibtmp%'; +---------+-----------+ | FILE_ID | FILE_NAME | +---------+-----------+ | 537 | ./ibtmp1 | +---------+-----------+ 1 row in set (0.01 sec)
类似地，InnoDB的undo表空间文件名以undo为前缀。如果配置了单独的undo表空间，下面的查询返回了InnoDB undo表空间的FILE_ID和FILE_NAME。
mysql> SELECT FILE_ID, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%undo%'; +---------+----------------------------------------------------------+ | FILE_ID | FILE_NAME | +---------+----------------------------------------------------------+ | 1 | /mysqldata/mysql/undo/undo001 | | 2 | /mysqldata/mysql/undo/undo002 | | 3 | /mysqldata/mysql/undo/undo003 | +---------+----------------------------------------------------------+ 3 rows in set (0.00 sec)