0

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?

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

1 Answer 1

Reset to default
0

You should look at your my.conf ( /etc/mysql/my.conf by default), note that .sql are text files, which could be larger than the binary form of the database sometimes - depends on the indexes and structure of the SQL dump (numbers are much more efficient 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 ...

Improve this answer
4
  • 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, 2011 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, 2011 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, 2011 at 11:10
  • 10 years late, but since this was uselessly necroed: innodb uses $table.ibd in the per-database subdirectory IF your configuration sets innodb_file_per_table=1 and otherwise it uses a single ibdata1 file in the datadir (not in the per-database subdirectory)
    – dave_thompson_085
    Oct 6, 2021 at 2:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .