How to get a valuation about the size of mysql table dump

Question

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

Answer 1

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;

Answer 2

Since a mysqldump is a logical representation of the database, you will only find actual data. Indexes are never stored in a mysqldump, but the table definition will dictate how to populate indexes.

It is hard to pickoff an exact number for a mysqldump because of what is written to a mysqldump:

• Extended INSERT commands with dozens,hundreds, or thousands of rows
  • dozens or hundreds of parentheses and commas per INSERT
• Table creation commands
• Directives for
  • Disabling Foreign Keys and Unique Keys for Duration of mysqldump
  • Recording Log File and Position of the mysqldump
  • Disabling/Enabling Indexes (MyISAM only)
  • Locking a Whole Table for Writes
  • Saving/Restoring Global Variables

What you can gauge as a factor to go by is just the sum of the data_lengths:

SELECT SUM(data_length)/POWER(1024,2) TotalDB_MB
FROM information_schema.tables;

To see the sum by database and grand total:

SELECT table_schema,SUM(data_length)/POWER(1024,2) DB_MB
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')
GROUP BY table_schema
WITH ROLLUP;

To see every table and databases subtotaled:

SELECT table_schema,table_name,SUM(data_length)/POWER(1024,2) DB_MB
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')
GROUP BY table_schema,table_name
WITH ROLLUP;

You can use the following

• POWER(1024,1) reports in KB
• POWER(1024,2) reports in MB
• POWER(1024,3) reports in GB
• POWER(1024,4) reports in TB
• POWER(1024,5) reports in PB
  • That's PetaBytes
  • If you have to use this one, I charge reasonable consulting rates

You could use this Formula

• NOC = Number of Columns Per Table
• NumCommas = NOC - 1
• NumQuotes = NumCharacterColumnsInTable X 2
• NumRows = Number of Rows Per Table
• NumParens = NumRows X 2
• CharactersForSQLPerTable = NumRows X (NumCommas + NumQuotes) + NumParens

Final Formula for Approximate Size of a mysqldump

• CharactersForSQLPerTable X NumberOfTables
• + Characters for "INSERT INTO tblname VALUES "
• + TotalDB_MB