Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Mar 17 '13 at 8:28

This question came from our site for professional and enthusiast programmers.

    
+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.) –  spencer7593 Mar 16 '13 at 23:56
    
Did you compile mysql yourself? –  Aaron Brown Mar 17 '13 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. –  pritam Mar 17 '13 at 13:21
    
good post. good finding. thank you! –  user23338 May 3 '13 at 7:02

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

share|improve this answer
    
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 Mar 17 '13 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. –  spencer7593 Mar 17 '13 at 3:26
    
Thanks! Additional info has been added to my original question. I'm still hunting for those damn .idb files. –  user2178198 Mar 17 '13 at 4:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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