0

We face the same issue like this posting on dba.stackexchange.com Deadlock from mysqldump --single-transaction, is it possible?. We offer DBaaS and can't control the DDL statements of our customers.

mysqldump: Error 1213: Deadlock found when trying to get lock; try restarting transaction when dumping table `x` at row: x

After that error the backup fails.

This is the backup command

mysqldump --max_allowed_packet=1G -u root -pvubaluaGebetee1aichaingubaePho --opt --flush-logs --single-transaction  --all-databases | bzcat -zc > /var/lib/mysql/backup/$FILE`date +%Y%m%d-%H%M%S`.sql.bz2

The accepted solution recommends Setup Master/Slave Replication. We use already Galera multi master replication.

> select version();
+----------------------+
| version()            |
+----------------------+
| 5.5.41-MariaDB-wsrep |
+----------------------+
1 row in set (0.00 sec)

other ideas howto make sure the backup completes? I don't care if a few rows not in backup.

1

Option 1

Use a replication slave off the galera cluster exclusively for creating backups. You can stop slave to ensure that all DDL operations have finished before doing a dump. Parallel dumping operations like mydumper are also possible.

Don't forget to enable log-slave-updates on the galera member you use as a master.

Option 2

Desync a galera node before doing a backup from it.

  • thanks. Please provide more details howto do that? What config changes and CLI commands? – Ivanov Nov 1 '16 at 10:51
1

Consider using percona-xtrabackup -it's a non blocking method. Also consider using pbzip2 (parallel bzip2) in order to speed up the backup process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.