# MySQL 4 Database Size

**URL:** <https://forums.percona.com/t/mysql-4-database-size/838>\
**Category:** Other MySQL® Questions\
**Created:** [July 11, 2008, 3:08am UTC](https://forums.percona.com/t/mysql-4-database-size/838 "2008-07-11T03:08:08Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![ouranos21](https://avatars.discourse-cdn.com/v4/letter/o/65b543/32.png) [@ouranos21](https://forums.percona.com/u/ouranos21)\
**Post date:** [July 11, 2008, 3:08am UTC](https://forums.percona.com/t/mysql-4-database-size/838/1 "2008-07-11T03:08:08Z")

</div>

Hi,

I’m facing a problem:

Well, in MySQL 5 it’s easy to query the Information\_Schema Database to retrieve the size of all the batabases of your MySQL5 instance.

What about MySQL 4 ?

I have still several MySQL4 instances containing informations that needs to be centralized. It’s 3 days i’m seeking through the web but I cannot find how to retrieve the datafile sizes of all my databases running on my MySQL4 instances using ONLY SQL. I’m turning crazy.

I can only SQL, and my boss doesn’t want me to create a bash file to do that job.

Please help me.

Regards.  
Franck.

---

<div class="post-metadata">

**Author:** ![erkules](https://avatars.discourse-cdn.com/v4/letter/e/db5fbb/32.png) [@erkules](https://forums.percona.com/u/erkules)\
**Post date:** [July 11, 2008, 2:23pm UTC](https://forums.percona.com/t/mysql-4-database-size/838/2 "2008-07-11T14:23:51Z")

</div>

show table status:

---

<div class="post-metadata">

**Author:** ![ouranos21](https://avatars.discourse-cdn.com/v4/letter/o/65b543/32.png) [@ouranos21](https://forums.percona.com/u/ouranos21)\
**Post date:** [July 14, 2008, 1:41am UTC](https://forums.percona.com/t/mysql-4-database-size/838/3 "2008-07-14T01:41:18Z")

</div>

Mmmh, not really, I should have given you more informations:

With that command I have too much informations. I don’t care about the name of the database, its size, row format, row count, average row size etc… I just want a unique information when I query the database: its size (index+datas).

Under a Unix based system, I would use the Grep command, but it has to be only SQL command.

Is it possible ?

---

<div class="post-metadata">

**Author:** ![ankur02018](https://avatars.discourse-cdn.com/v4/letter/a/838e76/32.png) [@ankur02018](https://forums.percona.com/u/ankur02018)\
**Post date:** [July 31, 2008, 5:31am UTC](https://forums.percona.com/t/mysql-4-database-size/838/4 "2008-07-31T05:31:40Z")

</div>

you can try this query

go to mysql prompt

SELECT s.schema\_name, CONCAT(IFNULL(ROUND((SUM(t.data\_length)+SUM(t.index\_length)) /1024/1024,2),0.00), “Mb”) total\_size, CONCAT(IFNULL(ROUND(((SUM(t.data\_length)+SUM(t.index\_length) )-SUM(t.data\_free))/1024/1024,2),0.00), “Mb”) data\_used, CONCAT(IFNULL(ROUND(SUM(data\_free)/1024/1024,2),0.00),“Mb”) data\_free, IFNULL(ROUND((((SUM(t.data\_length)+SUM(t.index\_length))-SUM( t.data\_free))/((SUM(t.data\_length)+SUM(t.index\_length)))\*100 ),2),0) pct\_used, COUNT(table\_name) total\_tables FROM INFORMATION\_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION\_SCHEMA.TABLES t ON s.schema\_name = t.table\_schema WHERE t.engine = “INNODB” GROUP BY s.schema\_name ORDER BY pct\_used DESC\G;

for InnoDB engine

---

<div class="post-metadata">

**Author:** ![ouranos21](https://avatars.discourse-cdn.com/v4/letter/o/65b543/32.png) [@ouranos21](https://forums.percona.com/u/ouranos21)\
**Post date:** [July 31, 2008, 5:54am UTC](https://forums.percona.com/t/mysql-4-database-size/838/5 "2008-07-31T05:54:49Z")

</div>

No. There’s no Information\_Schema in MYSQL4 so you can’t query it.

But I’ve found a solution using the shell (it’s quite impossible without it because of No Information\_Schema).

Here’s the code:

for i in `echo "SHOW DATABASES;" | mysql_path -S mysql_db_socket --user=Username --password=password --disable-column-names`do sumByDB=0 echo $i ------------------ for s in `echo "SHOW TABLE STATUS FROM $i;" | mysql_path -S mysql_db_socket--user=Username --password=password --disable-column-names |awk '{print $6,$8}'` do sumByDB=`expr $sumByDB + $s` done echo size is $sumByDBecho “insert into MYDB.MYTABLE (DATABASE\_NAME, DATAFILE\_NAME,DB\_SIZE, CHECK\_DATE)select ‘$i’, ‘engine’, ‘$i’,$sumByDB/1024/1024,0 , sysdate()” | mysql\_path -S mysql\_db\_socket --user=Username --password=passworddone

And that works.

Regards.  
Franck.