Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

Please run this query:

SELECT
    Data_BB / POWER(1024,1) Data_KB,
    Data_BB / POWER(1024,2) Data_MB,
    Data_BB / POWER(1024,3) Data_GB
FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')) A;

This will give you a ballpark figure. The column index_length is not used because mysqldump does not dump indexes, only data. Just to be safe, you should always gzip it immediately:

mysqldump --all-databases --routines --triggers | gzip > MySQLData.sql.gz

Give it a Try !!!

share|improve this answer
    
Thanks. In my case, the query returned about 10.4 GB and the actual file size (not gzipped) turned out to be about 10.8 GB. –  Micah - ps I love you Mar 20 '13 at 19:18
    
You are welcome –  RolandoMySQLDBA Mar 20 '13 at 19:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.