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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
up vote 11 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 Bolen Mar 20 '13 at 19:18
    
You are welcome – RolandoMySQLDBA Mar 20 '13 at 19:19
    
@RolandoMySQLDBA is there a way of getting an approximation of the gzipped size? – KalenGi Mar 1 at 1:22
    
@KalenGi Not easily, no. Compression ratio is a function of both the algorithm and the data being compressed. For example, the text "123123123..." repeating for 5GB is going to have a much higher compression ratio than 5GB of images. – christianbundy Jun 9 at 19:10

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.