MySQL replication status alerts with bash script
In theory, MySQL replication should work without problems even if the servers are restarted, databases created and removed, data flowing in and out… Sometimes, however, there are errors that are breaking the replication process. It is good to know once such situation appears.
Understanding the basics of replication status messages
The main method to check the replication status is to execute
SHOW SLAVE STATUS \G on the replication slave server. The “\G” switch changes the result displaying layout – from the horizontal table to the vertical one (there is one row only, so it is much easier to review this way).
The main elements of the output we should check are Slave_IO_Running, Slave_SQL_Running, and Seconds_Behind_Master. The first two should read “Yes” and the third one should be a reasonable value (in seconds). If Slave_IO_Running or Slave_SQL_Running are not set to “Yes”, you can review values in Last_Error, Last_IO_Error, and Last_SQL_Error. This should give you the clue on what is going on.
When talking of Seconds_Behind_Master – this value may vary. For most of the small installations, it should be almost always zero or near zero. For bigger servers or a significant amount of data being inserted or adjusted, it can reach higher values. The number is provided in seconds. In most cases, I’m using 300 as the border value between “OK” state and “something wrong”. We will use this in the script below.
If your MySQL server is allowing you to connect from the command line without a password, you can skip this step (but it is still recommended from the security point of view). For remote servers or the servers that require a password to connect from localhost, you should create a separate user only for this purpose. Such a user will be granted only necessary privileges.
To create such user execute the commands in MySQL:
CREATE USER 'replicationStatus'@'localhost' IDENTIFIED BY 'password'; GRANT SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicationStatus'@'localhost';
The new user will be able to check the status of the replication, show database names but will not be able to retrieve or remove the data. Of course, in the example above you should replace the password with something more complicated.
Checking replication status
Here is the script:
#!/bin/bash # # Set the maximum number of seconds behind master that will be ignored. # If the slave is be more than maximumSecondsBehind, an email will be sent. # maximumSecondsBehind=300 # # Checking MySQL replication status... # mysql -u replicationStatusLocal -p'password' -e 'SHOW SLAVE STATUS \G' | grep 'Running:\|Master:\|Error:' > replicationStatus.txt # # displaying results, just in case you want to see them # echo "Results:" cat replicationStatus.txt # # checking parameters # slaveRunning="$(cat replicationStatus.txt | grep "Slave_IO_Running: Yes" | wc -l)" slaveSQLRunning="$(cat replicationStatus.txt | grep "Slave_SQL_Running: Yes" | wc -l)" secondsBehind="$(cat replicationStatus.txt | grep "Seconds_Behind_Master" | tr -dc '0-9')" # # Sending email if needed # if [[ $slaveRunning != 1 || $slaveSQLRunning != 1 || $secondsBehind -gt $maximumSecondsBehind ]]; then echo "" echo "Sending email" mail -s "MyServer.com - replication issue found" email@example.com < replicationStatus.txt else echo "" echo "Replication looks fine." fi
You can adjust the maximumSecondsBehind to the value of your choice. If you will notice that the alert is triggered too frequently, you can change this value to a higher number. You should also set the proper password in the MySQL execution string. As the last step, you should adjust an email subject and address in mail execution part.
The results of the status check are saved to the text file because I’m using it also for other reports. This allows me also to easily manipulate the values and check them using simple grep. If you need a more complex report or other values to be checked, you can simply change the grep filter on the MySQL execution string and the processing of the values.
25 Replies to “MySQL replication status alerts with bash script”
$secondsBehind > $maximumSecondsBehind
$secondsBehind -gt $maximumSecondsBehind
Interesting thought. Why do you suggest such a change? I used double brackets [[ ]] on purpose so I can use > sign safely. Maybe there is something I’m not aware of, can you explain it to me?
Hi dulare, first: great post! many thanks.
Secondly: the > does not work to me, be it does the -gt
Daniel, thank you for the comment. I will adjust the code above to use -gt instead of >
Thanks alot. The script worked for me.
Good to know, thank you for the information!
Worked for me.. Thaks!
I’m glad that it worked 🙂
thanx..simple and sweet worked like a charm..
I’m happy to hear that!
working for me just not able to execute using crontab.. did all the environment settings but no luck;
Thanks for the script;
Is there any particular error we may refer to?
I am not getting emails
notification.sh: 25: [[: not found
notification.sh: 25: 0: not found
notification.sh: 25: -gt: not found
Can you please paste the contents of your “notification.sh” file EXCLUDING sensitive information such as password? It looks like there is an error in the script.
# Set the maximum number of seconds behind master that will be ignored.
# If the slave is be more than maximumSecondsBehind, an email will be sent.
# Checking MySQL replication status…
mysql -u root -p’*****’ -e ‘SHOW SLAVE STATUS FOR CHANNEL “source_1″\G’ | grep ‘Running:\|Master:\| Last_SQL_Error:’ > replicationStatus.txt
# displaying results, just in case you want to see them
# checking parameters
slaveRunning=”$(cat replicationStatus.txt | grep “Slave_IO_Running: Yes” | wc -l)”
slaveSQLRunning=”$(cat replicationStatus.txt | grep “Slave_SQL_Running: Yes” | wc -l)”
secondsBehind=”$(cat replicationStatus.txt | grep “Seconds_Behind_Master” | tr -dc ‘0-9’)”
# Sending email if needed
if [[ $slaveRunning != 1 || $slaveSQLRunning != 1 || $secondsBehind -gt $maximumSecondsBehind ]]; then
echo “Sending email”
mail -s “MyServer.com – replication issue found” firstname.lastname@example.org < replicationStatus.txt
echo "Replication looks fine."
It looks like you are using various types of apostrophes and quotes.
Can you please change all apostrophes to ‘ and quotes to ” and check again?
I have simply copied your content and changed the values. Nothing else I did. Please help
share your email address . I will share the file with you.
I sent you an email, please take a look also at spam folder.
I am getting this after your suggestion to
change all apostrophes to ‘ and quotes to ”
grep: Last_SQL_Error:‘: No such file or directory
mysql: [Warning] Using a password on the command line interface can be insecure.
grep: Yes”: No such file or directory
grep: Yes”: No such file or directory
notification.sh: 25: [[: not found
notification.sh: 25: ”0”: not found
notification.sh: 25: ””: not found
”Replication looks fine.”
Thanks for helping me out. Code is perfect.. however the issue was :
– using sh instead of bash
– mail engine was not installed
Sorry for trouble.
I’m glad that it worked 🙂 no need to be sorry, we are all learning 🙂
Worked like a charm. Thank You
FYI – a new (10.5.9 birthversion) SLAVE MONITOR privilege allows SHOW SLAVE STATUS, SHOW RELAYLOG EVENTS
Comments are closed.