How can I determine the size of a dump file prior to dumping?

Question

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

Answer 1

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

Answer 2

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;")"