22

I want to check if innodb_file_per_table is set (i.e .ibd is created) for each database tables using MYSQL-5.5 query.

Any way to do it?

Deepak Ingole
  • 14,912
  • 10
  • 47
  • 79

2 Answers2

64
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)
Igor Tverdovskiy
  • 847
  • 1
  • 7
  • 5
  • 8
    This just tells you if the setting is enabled. Any tables that were created before enabling file_per_table will still be in the global tablespace. My interpretation of the question is they want to know which tables actually do have their own file (they were created after enabling file_per_table). – antriver Sep 04 '16 at 15:12
23
SELECT NAME, SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES;

The SPACE will be 0 for the global tablespace (ibdata1) and some greater number for file-per-table tablespaces.

See http://dev.mysql.com/doc/refman/5.6/en/innodb-sys-tables-table.html

Since you are using MySQL 5.5, the above solution will not work. In MySQL 5.5, you can use this:

SELECT DISTINCT TABLE_NAME, SPACE FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE_LRU
WHERE TABLE_NAME IS NOT NULL AND TABLE_NAME NOT LIKE 'SYS%';

Again, SPACE is 0 for the global tablespace, and greater integers for file-per-table.

With the caveat that it only reports on tables that have pages in the buffer pool. If you haven't queried a table, it will have no pages in the buffer pool LRU, and the query will not report any result for that table.

The only other solution is to use ls in the data directory. :-)

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828