31

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

2 Answers 2

39

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

7
  • The A in A;is no typo.
    – Björn
    Jan 22, 2021 at 17:45
  • 1
    @Björn The A is an alias for the subquery. Jan 22, 2021 at 18:03
  • Thank you for the explanation.
    – Björn
    Jan 24, 2021 at 17:00
  • Is there any way to exclude the index size from that count? I have rather large indices and they won't be taking room in the dump file. Edit: I guess it would be SUM(DATA_LENGTH)-SUM(INDEX_LENGTH) DATA_BB Nov 18, 2021 at 19:48
  • @JoeYahchouchi Please look carefully at my answer. I did exclude it already. Data pages and index pages are independent pages that coexist inside an .ibd file. Nov 18, 2021 at 23:54
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 and acknowledge you have read our privacy policy.

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