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.<\/p>\n\n\n\n
Please find the bash script below which gives the alerts when replica lags or stops.<\/p>\n\n\n\n
#!\/bin\/bash\n#\n# Set the maximum number of seconds behind master that will be ignored. \n# If the slave is be more than maximumSecondsBehind, an email will be sent. \n#\nmaximumSecondsBehind=300\n#\n# Checking MySQL replication status...\n#\nmysql -u replicationStatusLocal -p'password' -e 'SHOW SLAVE STATUS \\G' | grep 'Running:\\|Master:\\|Error:' > replicationStatus.txt\n#\n# displaying results, just in case you want to see them \n#\necho \"Results:\"\ncat replicationStatus.txt\n#\n# checking parameters\n#\nslaveRunning=\"$(cat replicationStatus.txt | grep \"Slave_IO_Running: Yes\" | wc -l)\"\nslaveSQLRunning=\"$(cat replicationStatus.txt | grep \"Slave_SQL_Running: Yes\" | wc -l)\"\nsecondsBehind=\"$(cat replicationStatus.txt | grep \"Seconds_Behind_Master\" | tr -dc '0-9')\"\n#\n# Sending email if needed\n#\nif [[ $slaveRunning != 1 || $slaveSQLRunning != 1 || $secondsBehind -gt $maximumSecondsBehind ]]; then\n echo \"\"\n echo \"Sending email\"\n mail -s \"MyServer.com - replication issue found\" my@email.com < replicationStatus.txt \nelse\n echo \"\"\n echo \"Replication looks fine.\"\nfi\n<\/pre>\n\n\n\nYou 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\u00a0execution string. As the last step, you should adjust an email subject and address in mail\u00a0execution part.<\/p>\n","protected":false},"excerpt":{"rendered":"