MySQL is a Relational Database Management System, widely used as a database system for Linux systems. This article will help you to calculate the size of tables and database in MySQL or MariaDB servers though SQL queries. MySQL stored all the information related to tables in a database in the information_schema database. We will use the information_schema table to find tables and databases size.
How to find each data base size ?
Check ALL Databases Size in MySQL
Using mysql query
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM
information_schema.TABLES GROUP BY table_schema;
Sample output:
mysql> SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM
information_schema.TABLES GROUP BY table_schema
-> ;
+--------------------+------------+
| Database | Size (MB) |
+--------------------+------------+
| information_schema | 0.00878906 |
| mylabdb | 0.00111008 |
| mysql | 0.68704987 |
| performance_schema | 0.00000000 |
+--------------------+------------+
mysql> SELECT
-> table_schema 'Database Name',
-> SUM(data_length + index_length) 'Size in Bytes',
-> ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) 'Size in MiB'
-> FROM information_schema.tables
-> GROUP BY table_schema;
Check Single Table Size in MySQL Database
To find out the size of a single MySQL database called mylabdb (which displays the size of all tables in it) use the
following mysql query.
mysql> SELECT table_name AS "Table Name",ROUND(((data_length + index_length) / 1024 / 1024),
2) AS "Size in (MB)" FROM information_schema.TABLES WHERE table_schema = "mylabdb"
ORDER
BY (data_length + index_length) DESC;
Finally, to find out the actual size of all MySQL database files on the disk (filesystem), run the
du command below.
sudo du -h /var/lib/mysql
