| MySQL 4 Database Size [message #3272] |
Fri, 11 July 2008 04:38  |
 ouranos21
Messages: 3
Registered: July 2008
Location: La Louviere.be |
Junior Member |
|
|
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.
|
|
|
|
|
| Re: MySQL 4 Database Size [message #3355 is a reply to message #3284 ] |
Thu, 31 July 2008 07:01  |
ankur02018
Messages: 8
Registered: July 2008
Location: India |
Junior Member |
|
|
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
|
|
|
| Re: MySQL 4 Database Size [message #3358 is a reply to message #3272 ] |
Thu, 31 July 2008 07:24 |
ouranos21
Messages: 3
Registered: July 2008
Location: La Louviere.be |
Junior Member |
|
|
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 $sumByDB
echo "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=password
done
And that works.
Regards.
Franck.
|
|
|
Members
Search
Help
Register
Login
Home
