How does MySQL store data, can I detach a mysql Database?

Question

I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to the actual database size. They can compressed and considerably reduced in size, but things seems to be different in MySQL.

I look up the files of MySQL (InnoDB) where they are stored in /var/lib/mysql/ folder. There is files for each database (I can't share that) and there are these three files which seems to contain data

-rw-rw---- 1 mysql mysql 50M Feb 24 16:17 ibdata1
-rw-rw---- 1 mysql mysql 48M Feb 24 16:17 ib_logfile0
-rw-rw---- 1 mysql mysql 48M Feb 22 23:49 ib_logfile1

First I am not sure if all the database data is kept in one file, that ibdata1, second some of my database are large (1.4 GB one one of them) but these files are very small. I am wondering how data is maintained in MySQL.

Here is my query to find my database size Engine wise

SELECT Engine "DB Engine",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM information_schema.tables 
GROUP BY engine;

And the result

+--------------------+---------------+
| DB Engine          | DB Size in MB |
+--------------------+---------------+
| NULL               |          NULL |
| CSV                |           0.0 |
| InnoDB             |        3695.8 |
| MEMORY             |           0.0 |
| MyISAM             |        3120.5 |
| PERFORMANCE_SCHEMA |           0.0 |
+--------------------+---------------+

Obviously my database a lot larger than the datafiles that stores that data. On a side note, I also want to know if I can detach a database in mySQL just like in SQL Server. Part of the question is because I want to optimize my database, remove the ones that are no longer used. Also currently there is only 30GB on this drive and I want to free up some space too. Note I have additional drives on my Linux (centos) server as well (extra). Thanks

Answer 1

Simplified answer (does not include PARTITIONing, TRIGGERs, etc):

With innodb_file_per_table = 1:

ibdata1  -- some meta info
dbname/tblname.frm  -- schema for `tblname`
dbname/tblname.ibd  -- data and indexes for `tblname`
... (for each table in each database)

With innodb_file_per_table = 0:

ibdata1  -- meta info, table data, table indexes
dbname/tblname.frm  -- schema for `tblname`
... (for each table in each database)

MySQL 8.0 changes all that.

Answer 2

1.4GB is a tiny database.

To remove ones that are not of use any more, back them up with mysqldump and DROP DATABASE.

On files: - ib_log files are redologs - ibdata1 is a system tablespace.