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:
export existing tables
turn off MySQL server
[mysqld]heading of the my.cnf file
ib_logfile1- which for me are located in
restart MySQL server
at this point, newly created tables or tables imported from the original dump should each get there own
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:
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:
ibdata1does 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_tableis turned on, so things should be written to it.
My implementation works even though I don't include the line
innodb_data_home_dir = /usr/local/mysql/datain the
my.cnffile. However, I do have
datadir = /usr/local/mysql/data- but this must have been a default since I didn't set it.