Mariadb restore take long time, dump data without sql

Question

I am trying to restore a mariadb database with size 320Gb that takes until now 10 days and it's not over yet.

To take the backup I use mysqldump command:

mysqldump --single-transaction -u maxscale -hx.x.x.x nextcloud | gzip > file.sql.gz

I am looking for the syntax for dumping all data in my mysql database whithout sql or some other backup method.

• danblack: mydumper as an option (that is getting some attention for MariaDB if there's a feature not dumping correctly that needs fixing). Or mariadb-backup (as a logical backup).
• danblack: Also large innodb_buffer_pool_size and innodb_log_file_size, or trade of durability with innodb_flush_log_at_trx_commit to reduce IO slowness noting a restore failure could need restarting the backup or carefully resuming from the right location.

Answer 1

If gzip is using 99% CPU, it is the slow part. Consider storing the uncompressed data (to speed things up).

If gzip is not the bottleneck, then the network probably is. In this case, move gzip to the other machine to cut down on the amount of data sent across the wire. (This requires a messier script; I'm getting rusty on such, so I won't try to spell it out.)

If you are running queries during the dump, this could be a problem.

• danblack: On CPU saturation, use a lower gzip compression level to trade slightly bigger size for less CPU. Or pgzip.