1

I have a 3-node Galera MariaDB cluster and I want to have a supplementary backup using mysqldump for restore of individual tables in the event of any user errors. Currently Node1 is being used by all applications while node2 and node3 are just kept in sync. I want to run mysqldump from idle Node3. Should I not use --flush-logs? Also should I use --master-data option?

I ran mysqldump backup in a pre-prod cluster (same setup as production) from an idle node Node3 with these options

But as soon as I ran mysqldump, the data in few tables (checked only few at random) and they were not in sync with other nodes. But in few minutes it came back in sync with other nodes.

mysqldump -u root -pPassword --host=localhost --all-databases --flush-logs --events --routines --single-transaction --master-data=2 --include-master-host-port

My question is:

a) Should I avoid using --flush-logs option in my mysqldump? --Is it the cause for the current node going out of sync? b) Should I even include --master-data option in the mysqldump command?

user10523182
  • 13
  • 3

1 Answers1

1
  1. Take node3 out of the cluster.
  2. Do whatever dump you like (mysqldump, copy disk, xtrabackup, etc)
  3. Put back into the cluster -- it will repair itself to get back in sync.
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I have single mariadb pod in simplex conf. I understand that backup & restore will work when i try to backup from 1 simple simplex mariadb to 1 simple simplex mariadb. But i want to take backup from 1 simple simplex mariadb and restore it to 3 node galera mariadb. i tried mysqldump but vain. Is there any procedure to do so? Kindly let me know. – kish Oct 30 '19 at 06:56
  • @kish - Elaborate on the steps. Was the node already in the Cluster? Was the new server empty, and you were freshly loading both MariaDB and your data? Or what? But, please start your own Question. – Rick James Oct 31 '19 at 05:46
  • please find the question https://stackoverflow.com/questions/58620045/taking-backup-from-one-simple-simplex-confg-mariadb-pod-and-restoring-it-to-3-no Initially only one server and then i will install three nodes(galera mode). – kish Nov 03 '19 at 13:02
  • This does not answer the question. – Birkhoff Lee Feb 08 '22 at 08:34