2

What I'm trying to do is generate a full dump of my database using mysql shell, like so:

util.dumpSchemas(["my_db"], "outdir", {threads:88, bytesPerChunk: "256M"})

And then rsync it to a remote nfs share that contains a previous version of the dump. I'd like to minimise the transferred data, so I was hoping mysql shell's dump utility would generate file chunks for old unchanged data in a repeatable manner (i.e. the files would checksum the same). However this has proved not to be the case, and in fact virtually all the files checksum differently after minor table updates.

The mysql docs say that the primary key is used to order & chunk the data, so I optimistically thought that any tables with autoincrementing primary keys should dump in a deterministic way. But that appears not to be the case as the produced files don't checksum the same. Maybe it's ordering rows in reverse?

In any case I would love suggestions for how to do this.

Improve this question
9
  • 1
    Are you writing to the same NFS directory? If you have inserted/deleted as little as a single row I think that would throw off all the "chunks" after that.
    – Rick James
    Aug 16, 2022 at 0:20
  • 88 CPU threads? That seems useless -- the process is mostly I/O-bound. Unless you have RAID striping, 3 might be a practical limit.
    – Rick James
    Aug 16, 2022 at 0:21
  • @RickJames That would suggest it's dumping newest rows first. Do you know if that's actually the case, or just speculating? I'm looking for a way to dump oldest rows first, as the vast majority of the chunks would then be unchanged between db dumps
    – Minsc
    Aug 16, 2022 at 0:27
  • The number of threads isn't a factor -- I've tried various values and doesn't make a difference to the repeatability of the dump
    – Minsc
    Aug 16, 2022 at 0:29
  • DId a different number of threads lead to slower or faster?
    – Rick James
    Aug 16, 2022 at 4:15

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.