0

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.

2
  • 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
    Dec 16, 2022 at 4:03
  • 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.
    – danblack
    Apr 7 at 2:17

1 Answer 1

0

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.

1
  • 1
    On CPU saturation, use a lower gzip compression level to trade slightly bigger size for less CPU. Or pgzip.
    – danblack
    Dec 16, 2022 at 4:00

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.