Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

MySQL 4 Database Size

ouranos21ouranos21 EntrantInactive User Role Beginner

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.



  • erkuleserkules Contributor Inactive User Role Beginner
    show table status:
  • ouranos21ouranos21 Entrant Inactive User Role Beginner
    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 ?
  • ankur02018ankur02018 Entrant Inactive User Role Beginner
    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
  • ouranos21ouranos21 Entrant Inactive User Role Beginner
    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</pre>

    And that works.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.