Calculate size of the database in MySQL
Learn how to calculate the size of each database in MySQL. In MySQL the table data size and index length are stored in a table called TABLES in the database called information_schema.
You can see all the details when you run desc on information_schema.TABLES.
desc information_schema.TABLES; +-----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) unsigned | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) unsigned | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_FREE | bigint(21) unsigned | YES | | NULL | | | AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(32) | YES | | NULL | | | CHECKSUM | bigint(21) unsigned | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(2048) | NO | | | | +-----------------+---------------------+------+-----+---------+-------+ 21 rows in set (0.01 sec)
The fields DATA_LENGTH and INDEX_LENGTH stores the size of the data and index respectively. If you can sum these you would get the size of the database in bytes.
The following query would return the size in MB. Easy, right?
SELECT table_schema AS "DB", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema
mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 353922
Server version: 5.5.64-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SELECT table_schema AS "DB",
-> ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
-> FROM information_schema.TABLES
-> GROUP BY table_schema
-> ;
+--------------------+-----------+
| DB | Size (MB) |
+--------------------+-----------+
| information_schema | 0.07 |
| mysql | 0.63 |
| performance_schema | 0.00 |
| clientdata | 355.61 |
| smartapp | 0.28 |
+--------------------+-----------+
