MySQL Replication Deadlock Recovery

Submit review Recommend Print Contact Owner Visit

Rating

1 vote

Favoured:

Current Version

1.0

Last Release Date

2012-02-06

Owner

MosheKaplan

Website

top-performance.blogspot.com

Download URL

www.brightaqua.com/MySQL-Replication-DeadLock-Recovery.sh

License

BSD

Hits

76409

Files:

File	Description
MySQL-Replication-DeadLock-Recovery.sh	MySQL Replication Deadlock Recovery Plugin

Network Monitoring Software - Download Nagios XI

Log Management Software - Nagios Log Server - Download

Netflow Analysis Software - Nagios Network Analyzer - Download

A shell script (/bin/sh) that connects to a specified MySQL slave database, check the replication status for Deadlock errors (Error 'Deadlock found when trying to get lock: try restarting transaction') + Automatically recover from the state by performing "SLAVE STOP; SLAVE START;"

Required parameters: host, user, password.
If no Deadlock is detected, "OK" is returned
O/w, a message is printed and Warning state is returned.

Suggestions are welcome,
Moshe Kaplan

The best practice to handle deadlocks (after eliminating them from the code) is recovering from them.
Unfortunately, MySQL Replication does not recover automatically from these cases.
Therefore, we decided to implement this Nagios plugin. The plugin detects the deadlock and recover from it by restart the slave ("STOP SLAVE; START SLAVE;").

Fast, Simple and very effective.

Reviews (1)

MySQL mysqld_slave-skip-errors Option

byhasegeli, February 7, 2012

MySQL mysqld_slave-skip-errors option [1] is a better solution.

[1] http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_slave-skip-errors

Nagios, the Nagios logo, and Nagios graphics are the servicemarks, trademarks, or registered trademarks owned by Nagios Enterprises. All other servicemarks and trademarks are the property of their respective owner. The files and information on this site are the property of their respective owner(s). Nagios Enterprises makes no claims or warranties as to the fitness of any file or information on this website, for any purpose whatsoever. In fact, we officially disclaim all liability. We do, however, think these community contributions are pretty damn cool. Website Copyright © 2009-2023 Nagios Enterprises, LLC. All rights reserved.