List sizes of MySQL databases

Do you wonder which databases are actually taking up how much space but only have one huge ibdata1 in your /var/lib/mysql and the directories inside your mysql data directory don't represent the actual database sizes? This is for you!

Run from a mysql root console:

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

This will get you a result like the following:

+----------------------+--------------+
| Database name        | Size (MB)    |
+----------------------+--------------+
| information_schema   |   0.00781250 | 
| mysql                |   0.61204815 |
| project1_development | 281.59375000 |
| project1_test        |   7.29687500 |
| project2_development |   2.65625000 |
| project2_test        |   0.15625000 |
+----------------------+--------------+

forums.mysql.com

Arne Hartherz

makandra.com

Say thanks49

Last edit

2014-12-18

Arne Hartherz

Keywords

show, calculate, space, consumption

License

Source code in this card is licensed under the MIT License.

PostgreSQL: Show size of all databases

Use this:

SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024 AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;

Want to see [database sizes in MySQL](https://makandracards.com/makandra/1447-...

Show the character set and the collation of your MySQL tables

To show the collation of your tables you have to login to the MySQL console and execute SHOW TABLE STATUS FROM database;

mysql> SHOW TABLE STATUS FROM test;
+-------------+--------+---------+------------+------+----------------+--------...

MySQL / MariaDB: Show disk usage of tables and columns

You can find out about disk space usage of all tables within your database by running this:

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

Show and change MySQL default character set

To show the MySQL default character set you have to login to the MySQL console and execute SHOW VARIABLES LIKE 'char%';

mysql> SHOW VARIABLES LIKE  'char%';
+--------------------------+----------------------------+
| Variable_name  ...

netstat: How to show listening ports

Sometimes it's necessary for you to check which ports are in use on your local machine and which process is using it. To list this information you can use the following command (which is pretty easy to memorize for Germans 🌷):

sudo netstat -t...

PostgreSQL: How to show table sizes

When you have a large PG database, you may want to find out which tables are consuming the most disk space.
You can easily check this using the following SQL statement from the PostgreSQL wiki.

SELECT nspname || '.' || relname AS "relation",

...

Posted by Arne Hartherz to makandra dev (2011-08-10 13:54)

List sizes of MySQL databases

Related cards: