Check data file location and tablespaces in MariaDB or MySQL

Check size and location of data files or tablespaces present in MySQL or MariaDB

Check innodb_file_per_table parameter.
innodb_file_per_table=ON, InnoDB uses one tablespace file per table.
innodb_file_per_table=OFF, InnoDB stores all tables in the InnoDB system tablespace.

show variables like 'innodb_file_per_table'

Check the location of datafile/tablespace present in MySQL or MariaDB:


-- On MariaDB
show variables like 'datadir'
Variable_name|Value                              |
-------------+-----------------------------------+
datadir      |C:\Program Files\MariaDB 10.6\data\|

--On MySQL
show variables like 'datadir'
Variable_name|Value                                      |
-------------+-------------------------------------------+
datadir      |C:\ProgramData\MySQL\MySQL Server 8.0\Data\|

Check files and tablespace for MySQL:

select * from information_schema.FILES;

Check files and tablespace detail for MariaDB:

select * from information_schema.INNODB_SYS_TABLESPACES;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.