29

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

35

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 !!!

3
  • The A in A;is no typo.
    – Björn
    Jan 22 at 17:45
  • 1
    @Björn The A is an alias for the subquery. Jan 22 at 18:03
  • Thank you for the explanation.
    – Björn
    Jan 24 at 17:00
1

Based on the other answer, if you want a one liner to determine the size in bytes to use in a shell script, you can use this:

mysqlSizeBytes="$(mysql --database=information_schema --skip-column-names --silent --execute "SELECT Data_BB FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')) A;")"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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