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.

  • +1. Welcome to StackOverflow! It seems you have tried several things, and documented what you have done. You've narrowed down your problem to a specific issue. Kudos. (Debugging issues like this can be difficult.) Commented Mar 16, 2013 at 23:56
  • Did you compile mysql yourself? Commented Mar 17, 2013 at 11:57
  • Aaron - I did not compile MySQL (way above my computing pay grade). And let me add that I don't get an error when I try to access the schema directories, I just get a message that I don't have permission to look inside. Commented Mar 17, 2013 at 13:21

1 Answer 1


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

  • Thanks very much for your quick response! I'd like to give you some additional information but I don't know if it's best to do it in a comment, by editing the original message, or by posting an answer. It would be helpful to have the typesetting functionality of the question/answer posts. Please advise, again :).
    – user2178198
    Commented Mar 17, 2013 at 0:05
  • If it pertains to the original question, additional information, clarificaction, etc., I think it best to edit your question, and add an Addendum or Followup or Additional Info. (Not everyone reads the comments, and if you think it would be helpful to future readers, I think that's the best place to put the information. Commented Mar 17, 2013 at 3:26
  • Thanks! Additional info has been added to my original question. I'm still hunting for those damn .idb files.
    – user2178198
    Commented Mar 17, 2013 at 4:16
  • I got blank result running this query SHOW VARIABLES LIKE 'innodb_data_home_dir' Commented Apr 14, 2016 at 4:53
  • and what if innodb_data_home_dir is empty and innodb_file_per_table is on? Commented Jul 15, 2019 at 19:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.