Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

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.

share|improve this answer
2  
Alternatively, you can just ALTER TABLE [name] ENGINE=InnoDB. This will force a re-creation of the tablespace. –  Matt Solnit Nov 19 '09 at 21:39
    
Hello, thanks, but the creation (restore) of the transferred data happened after my.cnf was changed (mysql restart also happened before the import). Also, there is no ibdata1 file in the /var/lib/mysql directory. I'll try to add the output of show status to this question. –  Anonymous Nov 19 '09 at 21:53
    
I've tried to use ALTER TABLE to MyISAM and back to InnoDB; still no .ibd file. –  Anonymous Nov 19 '09 at 21:55
    
Can you run "SHOW VARIABLES LIKE '%innodb%'" and paste the output into your question? –  Matt Solnit Nov 19 '09 at 22:48
1  
Just to make sure the quoting is clear: SHOW VARIABLES LIKE '%innodb%' –  Matt Solnit Nov 19 '09 at 22:48

Your Answer

 
discard

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