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?

Improve this question

1 Answer 1

Reset to default
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 :)

Improve this answer
2
  • 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
    Commented Oct 16, 2014 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)
    – user1822
    Commented Oct 16, 2014 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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