Any way to check if innodb_file_per_table is set in MYSQL 5.5 per table?

Question

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?

Studer

Answer 1

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. :-)

Annunciation

getting error ERROR 1109 (42S02): Unknown table 'INNODB_SYS_TABLES' in information_schema – Studer

It was introduced in MySQL 5.6 and in Percona Server 5.1. – Annunciation

Hey I want solution in MySQL 5.5 – Studer

it's SELECT NAME, SPACE FROM INFORMATION_SCHEMA.INNODB_TABLES; in MySQL 8 – Erose

Answer 2

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

S

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). – Carma