Thursday, June 7, 2018

mysql with innodb_file_per_table - cannot see any .ibd files

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

No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...