How to calculate the MySQL database size


As i know, there are two ways to calculate MySQL database size.

1. SQL script

Sum up the data_length + index_length is equal to the total table size.

  1. data_length – store the real data.
  2. index_length – store the table index.

Here’s the SQL script to list out the entire databases size

SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 
"Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;

Another SQL script to list out one database size, and each tables size in detail

SELECT TABLE_NAME, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "schema_name";

2. Locate the MySQL stored data

Access this article to find out where does MySQL database saved the data.

Windows
Locate the MySQL ibdata1 file, right click on the file and click the properties, see the size? 🙂

Linux
Locate the MySQL ibdata1 file

mkyong@myserver:/var/lib/mysql$ ls -lh
total 1.5G
drwx------ 2 mysql mysql 4.0K 2009-08-26 13:36 mydatabase
-rw-r--r-- 1 root  root     0 2009-08-19 09:39 debian-5.0.flag
-rw-rw---- 1 mysql mysql 1.5G 2009-08-27 17:32 ibdata1
-rw-rw---- 1 mysql mysql 5.0M 2009-08-27 17:32 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M 2009-08-27 17:32 ib_logfile1
drwxr-xr-x 2 mysql root  4.0K 2009-08-19 11:19 mysql
-rw------- 1 root  root     6 2009-08-19 09:39 mysql_upgrade_info

Copy from http://www.mkyong.com/mysql/how-to-calculate-the-mysql-database-size/
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s