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?

NinjaCat
  • 576
  • 1
  • 9
  • 21

1 Answers1

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 ...

Wouter
  • 131
  • 1
  • 8
Qiqi
  • 243
  • 2
  • 12
  • 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
  • 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 06 '21 at 02:32