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
