Check MySQL database size

How to check MySQL database size in your server? 
Actually MySQL already provide the information, what you need to do is just do a simple query to retrieve the data.

Below is the query :

View Code MYSQL
SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
WHERE table_schema = '<DATABASE NAME>'
GROUP BY table_schema ;

6 Comments to “Check MySQL database size”

  1. Er Crini 4 May 2009 at 4:42 pm #

    Thanks dude, I’ve been locking for this one for a while. I’m impressed that everyone’s recommendation to know the size of a MySQL DB is “Install phpmyadmin and…”

    Now, I get the results just fine, except for the Free Space, that one is allways zero. Do you know what that could mean?

    Reply
  2. nione 30 June 2009 at 9:39 am #

    I trying this gives me the error:

    1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no GROUP columns is illegal if there is no GROUP BY clause

    Reply
  3. h2Guru 28 August 2009 at 2:51 pm #

    Hi nione,

    Thanks for your comments…
    I had amend the GROUP BY command in the query.
    It should be work now.

    Reply
  4. Anish Sneh 16 March 2012 at 1:09 am #

    Thanks mate, great help…

    — Anish Sneh

    Reply
  5. mactyr 20 July 2012 at 6:49 am #

    Just used this and it doesn’t seem to report free space correctly. When I look in the actual information_schema.TABLES table I see that all the tables report the same amount of free space, which is presumably the free space on disk (or whatever mysql thinks is available to it?) Summing the data_free entries would seem to over-report the free space by counting it multiple times. I suppose this could be dependent on MySQL version/platform/storage type; I’m not sure.

    Maybe just do “max( data_free )” rather than “sum( data_free )”?

    Reply
  6. afi 2 March 2013 at 7:03 am #

    hello, how can i used this query please?

    thank you for your time

    Reply

Leave a Reply