How to get the all the database and table sizes of MySQL or MariaDB?

If you are working with MySQL or MariaDB web server, sooner or later you will be asked what is the size of database or tell me the size of all the database and table sizes.

You can get the a particular databases tables and indexes size using below mentioned SQL query.

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
    AND table_name = "$TABLE_NAME";

To get the size of all the databases and tables in MySQL or MariaDB use below query.

SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

This is how you can get all the tables and databases sizes in MySQL. You can later pivot the records to get the cumulative sizes of all the databases in MS Excel or Libreoffice.

Living in permanent beta mode: Learning, Improving & evolving. SPECIALTIES: Web Application Development, Digital Media, E-Commerce Solutions, SEO, CRM Solutions, Open Source Technologies, System Administration ( Linux ), VOIP Solutions, Cloud Computing, Web Security.

Leave a reply:

Your email address will not be published.

Site Footer