0

Where does mysql store data? I found out (by using mysql>SELECT @@datadir ) that it's in var/lib/mysql - but that can't be it. I have a quite big database (4 GB) called 'bot', but all the files in the 'bot' subdirectory (var/lib/mysql/bot) have only 280KB. Where is the rest?

One more thing - 99% of the database's size is a text column in one of the tables - I know that mysql stores it in separate files, but does it create one file per record?

2

If you're using InnoDB and have a reasonably default configuration, tablespace data is in the ibdata files, you should have at least ibdata1 by default.

You may want to read up about its configuration:

https://dev.mysql.com/doc/refman/5.7/en/innodb-configuration.html

Specifically splitting it so that each table gets its own ibdata file:

https://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html

And there are some related questions:

Ibdata usage and Recommendations?

If you're not using InnoDB, disregard that :)

  • Thanks! Yes, it's innodb. That's actually quite scary that all the databases are stored in a single file... I'll certainly set innodb-file-per-table to true. Thanks again. – Konrad Oct 16 '14 at 12:03
  • @Konrad: that's actually pretty common. Oracle and SQL Server also store everything in a single file (although you can configure them differently) – a_horse_with_no_name Oct 16 '14 at 12:11

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.