How to check MySQL database and table sizes

Databases, Linux / December 7, 2021 / ashishpsk / 0

To check the sizes of all of your databases, at the mysql> prompt type the following command:

SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;

To check the sizes of all of the tables in a specific database, at the mysql> prompt, type the following command. Replace database_name with the name of the database that you want to check:

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "database_name"
ORDER BY (data_length + index_length) DESC;

To View The MySQL/MariaDB Database Size In Linux:

Use the below command to get the database size in MB format:

# du -h --max-depth=1 /var/lib/mysql/

To View The MySQL/MariaDB Database Table Size In Linux:

Use the below command to get the database table size in MB format.

MySQL uses a number of different file formats for the storage of information.

If it’s MyISAM Storage Engine and it will create a following file when the new database is created.

MySQL/MariaDB creates files named Table1.MYD MySQL Data, Table1.MYI MySQL Index, and Table1.frm Format. These files will be in the directory.

# ls -ltrhS /var/lib/mysql/testdb