1

I am pretty inexperienced with MariaDB, especially replication. However, being 'on call duty' I have an issue with this today.

it looks like my replica is falling behind while I am taking a mysqldump of the replica. Is this to be expected behavior or is there an issue with my backup command below?

mysqldump --defaults-file=$MYCNF -A --events --routines --master-data=2 | gzip -9 -c > ${BKPFILE}

It looks like we are catching up after the dump is finished.

dbdemon
  • 6,964
  • 4
  • 21
  • 40
vrms
  • 269
  • 1
  • 4
  • 12

1 Answers1

4

By default the --opt option is used by mysqldump unless --skip-opt is set. --opt contains the --lock-tables option.

That means that for each dumped database, the dump first locks all tables for read to ensure consistency. It was needed with MyISAM tables and was not changed yet afaik.

If all your tables are InnoDB, you should run the dump with --single-transaction option set instead. That will disable the locking and use transaction snapshot for consistency, allowing others to write to the tables as needed.

The possible downside is a potentially long running transaction making the undo-log grow big and slowing all operations down. But that should not be a problem unless your dump runs for more than an hour or the DB is very write-heavy - if the replica catches up without much trouble currently, it should be OK.

jkavalik
  • 5,249
  • 1
  • 15
  • 20