Where/How are (My)SQL Databases Stored on Webservers?

Question

Just curious as to where and how SQL databases are stored on web servers. My particular flavour of SQL is MySQL if it makes a difference, does it?

Answer 1

For non-trivial web-sites, the SQL databases, MySQL or otherwise, are generally stored on a separate server dedicated as a DB server.

Answer 2

It depends on the distro and the storage mechanism.

MyISAM databases are often stored in one file per table under /var/lib/mysql/[databasename]

All InnoDB databases are stored in the same file by default under, for example, /var/lib/mysql.

An optimized installation may have the database files live in a special filesystem optimized for speed or reliability, or both.

Answer 3

For MySQL the data is stored in /var/lib/mysql or something similar path and depending on the database format you are using, with MyISAM the database information will be stored in a directory of its same name in .frm, .MYI, and .MYD files and with InnoDB the database information will be stored in the ibdata1 and ib_logfiles.

Answer 4

For MySQL you can use SHOW VARIABLES to find out where the data lives on the file system:

mysql> show variables like "datadir";
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

Within that directory there will be one subdirectory for each database.

Within each database directory, you'll find the files used to store the table data and indexes. The precise structure of these depends on the storage engine used (typically MyISAM or InnoDB for MySQL).