jump to navigation

How to List the Size of All Databases in MySQL (using SQL or the terminal) November 22, 2012

Posted by Tournas Dimitrios in Linux, Mysql.

One of the most important task when dealing with server administration is to monitor disk usage . Even though  hard-disks are very cheap nowadays  , we have to make an estimation for what our  future needs will be and act upon . Especially if we host our server on the Cloud , a careful estimation has to be done .  For instance , Amazon charges a few cents / Gb per month and that might not seen very important ,  but on a long term basis that could save some bucks from our pocket . This article is a short how-to  , it will show how to find the size of our Databases via SQL and from the terminal .

When hosting websites on the Cloud (Amazon) , I always use an extra disk (EBS = Elastic Block Store)  to store all database files on a separate disk . Even if Amazon’s EC2-machine could be crash for some reason my data won’t be lost . One more benefit from using an separate disk is that it’s convenient when I take “snapshots” (back-ups) . Storing “snapshots” on  S3 is an extra security measure . Hope it’s clearly now that each and every GB of disk-storage counts .

The commands are :

mysql> select table_schema "Data Base Name " , sum(data_length + index_length) / 1024 "Data Base Size in KB" from information_schema.TABLES group by table_schema ;
| Data Base Name     | Data Base Size in KB |
| information_schema |               8.0000 |
| mysql              |             637.4639 |
| world              |             514.1318 |
3 rows in set (0.00 sec)

mysql> exit
[root@aws-server]# grep datadir /etc/my.cnf
[root@aws-server]# ls -l /var/lib/mysql
lrwxrwxrwx 1 root root 14 Nov  8 10:51 /var/lib/mysql -> /mnt/ebs/mysql
[root@aws-server]# du -h /mnt/ebs/mysql/{mysql,world}
992K    /mnt/ebs/mysql/mysql
612K    /mnt/ebs/mysql/world
[root@aws-server]# ls -lh /mnt/ebs/mysql/ibdata1
-rw-rw---- 1 mysql mysql 10M Nov 21 23:46 /mnt/ebs/mysql/ibdata1
[root@aws-server]# df -h /mnt/ebs
Filesystem            Size  Used Avail Use% Mounted on
/dev/xvdq            1008M   57M  901M   6% /mnt/ebs

Same commands taken in a screenshot :


It’s clearly seen ,  the database files have only 6% (six percent) occupations of the total disk-space .  One important note , the SQL query return a slight different result than the results taken from the terminal . This is due the actual file-size of Mysql data-files vs space occupied on the disk ( block-size , meta-data … ) .



1. linardo - November 30, 2012

thank you so much for your dedication, it makes us readers come to your website everyday.

2. canoa - December 10, 2012

thank you for searching and sharing information. an excellent blog and an excellent page.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s