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.
Best Answer
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.