Mysql – How to get a valuation about the size of thesql table dump

MySQL

Hi to everyone someone know how to get an estimate about the size of one mysql table?
I mean I am planning to make a backup of all the mysql's table on all my server
but I'd like to know how big should be every dump table without make the physical dump.
There is some command which permit to do this one?
I am using mysql 5.1 on debian 6
Cheers

Best Answer

As seen here, you can run these queries on your database (stole from comments on the page, then tweaked).

  • By database:

SELECT table_schema 'database', concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) size FROM information_schema.TABLES WHERE ENGINE=('MyISAM' || 'InnoDB' ) GROUP BY table_schema;

  • By table:

SELECT concat( table_schema, '.', table_name ) table_name, concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size FROM information_schema.TABLES ORDER BY ( data_length + index_length ) DESC;

Related Topic