Galera backup failing with error "mysqldump: Error 1213: Deadlock found when trying to get lock; try restarting transaction when dumping table"

Question

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.

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

Answer 2

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