MySQL database dump causes out of space error

[Daniel-Doggy]

Hello everyone,

On my DirectAdmin server I have a really large database.
There are two tables in particular that causes problems for me as with these tables are that they are 50 GB each when stored uncompressed. (And the server only has 150 GB of storage.)
So in the database these two tables are configured to be a compressed tables and only take up 8 GB combined. (Data has a lot of replica data so highly compressible.)

Now the two tables do not really create a problem as the database normally only takes up 8GB total due to these tables being configured to be compressed.
But when I update MySQL via CustomBuilds I start to run into the out of space error as CustomBuilds dumps the databases raw and not compressed.

In an ideal world I would like to continue to use the dump before update setting as I think it is a good safety to have. (And also since it is turned on by default.)
But I have no idea how to configure CustomBuilds to dump the databases as a compressed dump.
Does anyone know if this is possible and if so how?

Many thanks in advance

 

[romans]

DA CustomBuild has the option to create GZIP-ed backup of MySQL DB:

da build set mysql_backup_gzip yes

But I am not sure if it helps in your case and if the dump is created first and only then gzipped or no.
Also you can skip creating the backup of MySQL DBs using such command:

da build set mysql_backup no

and then run update MySQL via CustomBuilds

 

[Daniel-Doggy]

Thanks for the info.

da build set mysql_backup_gzip yes

This setting would be what I am looking for but I do have one question:
Does this setting compress each table after dumping it or does it only compress after dumping everything?

If it compresses after each table dump then that would 100% solve my problem as I do have 60GB free and thus should be enough for the database dump.
But if it only compresses after dumping everything then I still would need 110 GB even when the server only has a 100GB disk.

EDIT:
The only reason why this is a big problem for me is:
If the data is stored uncompressed then the two tables take 50 GB each (For a total of 100GB)
But if stored compressed then they only take 4GB each (total of 8 GB)
So storing 1 table uncompressed temporary is possible but not both at the same time.

 

[sysdev]

If you use innodb, check out percona xtrabackup (e.g. it's incremental stuff) https://docs.percona.com/percona-xtrabackup/9.7/
This can speed up backup/restore times very much, mostly.

If you need the mysqldump tool (but with big db's you shouldn't do that unless you accept the time a 50GB restore can take), pipe it through zstd. This prevents the file from being made on disk and directly compresses it.

 

[romans]

With "mysql_backup_gzip" option - the dump should created and compressed "on-the-fly" entirely in-memory using a standard Unix/Linux pipe (|). The only file that "touches" the disk should be the final one - compressed .sql.gz file.