I've configured mysql to use the innodb_file_per_table
option.
Everything works fine, I've been able to transfer data from another mysql server to the server with innodb_file_per_table
set. All records are accessible and the application has been working fine for a couple of days now.
However I cannot find the .ibd files. I've restarted mysql via /etc/init.d/mysql restart.
In the /var/lib/mysql/xxx (xxx being the catalog name) there is a .frm file, but not a .ibd file.
I cannot find the expected .ibd file anywhere.
The operating system used is Ubuntu Linux:
Linux 2.6.24-24-server #1 SMP Tue Aug 18 16:51:43 UTC 2009 x86_64 GNU/Linux
The interesting thing is that du and df show different informtions about occupied space – df says 34GB are occupied whereas du shows 19G; the difference of 15GB is roughly the data occupied by the .ibd file (as shown in mysql admin).
I've pasted the output of du + df as well as the /proc/xxx/fd contents for the mysqld process. Interestingly the fd shows "/var/lib/mysql/ibdata1 (deleted)".
]du --max-depth 1 -h / 12M ./etc 3,8M ./lib32 1,9G ./var 8,0K ./media 1,6G ./usr 159M ./tmp 125M ./boot 4,0K ./initrd 86M ./root 4,0K ./srv 196M ./opt 16K ./lost+found 15G ./jboss 12K ./mnt 708M ./lib 0 ./proc 48K ./home 48K ./dev 0 ./sys 6,7M ./sbin 4,6M ./bin 19G . ]df -h Filesystem Size Used Avail Use% Mounted on /dev/sda1 221G 34G 177G 16% / varrun 7,9G 260K 7,9G 1% /var/run varlock 7,9G 0 7,9G 0% /var/lock udev 7,9G 36K 7,9G 1% /dev devshm 7,9G 0 7,9G 0% /dev/shm ]l /proc/15215/fd total 0 /dev/null pipe:[31469149] socket:[31469165] /tmp/ibyTflWI (deleted) socket:[31469166] pipe:[31469149] /var/lib/mysql/ibdata1 (deleted) /tmp/ibYr0wC6 (deleted) /tmp/ibX4rGe0 (deleted) /tmp/ibjOaQQT (deleted) /tmp/ibbGtntN (deleted) /var/lib/mysql/ib_logfile0 (deleted) /var/lib/mysql/ib_logfile1 (deleted)
Here's the output of show table status:
mysql> show table status like '%xxx%'; +-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------------------+ | xxx | InnoDB | 10 | Compact | 7982574 | 1674 | 13369344000 | 0 | 81526784 | 0 | NULL | 2009-11-18 04:15:08 | NULL | NULL | utf8_general_ci | NULL | | InnoDB free: 4096 kB; | +-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------------------------------------------------------------------------------+ 1 row in set (0.21 sec)
and the mysql version: 5.0.51a-3ubuntu5.4
Best Answer
File-per-table only applies to tables created after the option was enabled -- existing tables aren't copied out to separate files. You'll need to create new tables, copy the records across, then drop the old table and rename the new one to the old name to get all of your tables into their own files.