MySQL: Can’t find .ibd files after turning on innodb_file_per_table

innodbmy.cnfMySQL

Question:

This a question about MySQL. Let me give the specifics of my setup:

  • Mac OSX 10.6.8
  • MySQL 5.5.28
  • Workbench 5.2.44 for all administration
  • InnoDB engine exclusively

In order to manage limited hard drive space on my laptop, I'd like to turn on the innodb_file_per_table option so that each table gets its own .ibd file instead of all data going into the ibdata1 file. I researched various sources, including Stackoverflow, and they all suggest the same basic steps:

  1. export existing tables

  2. turn off MySQL server

  3. add innodb_file_per_table under the [mysqld] heading of the my.cnf file

  4. delete ibdata1, ib_logfile0, and ib_logfile1 – which for me are located in /usr/local/mysql/data

  5. restart MySQL server

  6. at this point, newly created tables or tables imported from the original dump should each get there own .ibd file.

I followed the steps and there appears to be no problem. Various checks that I did within Workbench all indicate that the innodb_file_per_table option is turned on. However, when I create new tables or import the old tables, I'm not seeing any new .ibd files, and it seems like ibdata1 is growing.

When I create/import a myschema, the following directory is created:

  • /usr/local/mysql/data/myschema

It is my assumption that if I create myschema.table1 that a the file table1.ibd will be added to the directory above, and that all the data associated with that table will be stored in this file. However, I can't check this assumption because in both Console and Finder, I get "permission denied" messages when I try to look inside the myschema directory. Moreover, in Finder, the folder icon has a red/white minus sign on it.

Resolution & Observations:

It turns out that my error was a simple one, and that the steps above to get innodb_file_per_table are correct. My issue was simply that I didn't have permissions to /usr/local/mysql/data/myschema – this was indicated by the red minus sign on the folder icon – which is easily resolved in Finder (for details just Google "folder with red minus sign mac").

Here are some things I learned along the way:

  1. Even with innodb_file_per_table turned on, ibdata1 does still grow (and doesn't shrink) as you add data, but at a much slower rate that the actual table. This makes sense because the Innodb engine still needs this file, even when innodb_file_per_table is turned on, so things should be written to it.

  2. My implementation works even though I don't include the line innodb_data_home_dir = /usr/local/mysql/data in the my.cnf file. However, I do have datadir = /usr/local/mysql/data – but this must have been a default since I didn't set it.

Best Answer

The .ibd files will be under the innodb_data_home_dir under a subdirectory, the subdirectory name matching the database name.

Given innodb_data_home_dir=/etc/local/mysql/data and innodb_file_per_table is set:

 SHOW VARIABLES LIKE 'innodb_data_home_dir'
 SHOW VARIABLES LIKE 'innodb_file_per_table'

then when table test.mytable is created, the .ibd file will be located in /etc/local/mysql/data/test/mytable.ibd

(It's possible that the ibdata1 file will grow to accommodate rollback for transactions. When a transaction is run, pre-change copies of blocks get recorded, in case you issue a ROLLBACK, and also for the benefit of concurrent transactions (with an appropriate isolation levels), that need to reference only committed changes, and not any uncommitted changes.)

Related Question