Running ubuntu 10.10, MySQL, I imported a set of databases from a backup.

The import looks OK - I can query the databases and see data in them. The strange thing is that in the /var/lib/mysql/ folder there exists only *.frm files and not the *.ibd file to that I would expect. Along the same lines, the size of the files in each folder are way too small to contain all of the data.

Likewise, the /var/lib/mysql/ibdata1 file is way too small to contain the data.

Where can I look to figure out a) where the data actually is and b) where the ibd files are?

  • Did you check size of the whole folder? Use: sudo du -sh /var/lib/mysql/. – Khaled Jan 21 '11 at 11:34

You should look at your my.conf ( /etc/mysql/my.conf by default) , note that .sql are text files, which could be larger than binary form of the database sometimes - depends on the indexes and structure of the sql dump (number are much more effient in binary form than dumped for example). If you have phpmyadmin, you can see the configuration variables along with the file setup there for example. Otherwise "lsof" should give you the list of open files and you may check where the mysql writes (could be better seen through "iotop" also).

.idb files are not mandatory - they are created by table optimization/setup , for example by "mysqloptimize" command in ubuntu ...

  • Well, all of the .frm files are under 9K, which is insanely small to contain the data I can see when I query the database. I am looking in the right folder. – NinjaCat Jan 21 '11 at 10:57
  • .frm contain just structure,not the data, when you use innodb, the rest of the data are by default in ibdata and/or .idb files. If you used MEMORY storage engine, then your data would never hit the disk, but it's not the default setup at all... – Qiqi Jan 21 '11 at 17:17
  • OK... maybe I am on to something now. I have a mixture of INNODB and MyISAM databases. I ahve been backing them up via a cron job that runs mysqldump. If I want to restore them on another server, what do I do? It seems that I cannot just simply create the database and import the .sql file that the mysqdump creates. – NinjaCat Jan 22 '11 at 11:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.