<\/p>\n
As a Database Administrator, Monitoring of database is the most important part. As all we know Prevention is better than cure,<\/p>\n
So. I am in this article comes with Replication Monitoring.<\/p>\n
In replication monitoring, mainly we need to look into three things “sql_thread_running”, “io_thread_running” and the lag between master and slave.<\/p>\n
The below script will help you in replication monitoring.<\/p>\n
#!\/bin\/bash\r\n#change this in order to indicate which slave you are monitoring now\r\nserver_name=\"server1@example.com\"\r\n#email of the database administrator to recieve notification\r\nadmin_email='dba@example.com'\r\nadmin_email1='manager@example.com'\r\n# Add mysql credentials in the following commands.\r\n\r\nsql_thread_running=$(mysql -uuser -ppassword -e \"show slave status\\G\" | awk -F\":\" '\/Slave_SQL_Running\/ { print $2 }' | tr -d ' ')\r\nio_thread_running=$(mysql -uuser -ppassword -e \"show slave status\\G\" | awk -F\":\" '\/Slave_IO_Running\/ { print $2 }' | tr -d ' ')\r\nseconds_late=$(mysql -uuser -ppassword -e \"show slave status\\G\" | awk -F\":\" '\/Seconds_Behind_Master\/ { print $2 }' | tr -d ' ')\r\nseconds_late=$(($seconds_late+0))\r\n\r\nif [ \"$sql_thread_running\" = \"No\" ] || [ \"$io_thread_running\" = \"No\" ] || [ $seconds_late -gt 3600 ]; then\r\n\r\nlog_file=\"\/tmp\/log_slave_status_$(date +%m-%d-%Y-%H:%M)\"\r\necho \"Replication is stopped between master and (Slave) on $(date +%m-%d-%Y-%H:%M)\" >> $log_file\r\nif [ \"$sql_thread_running\" = \"No\" ]; then\r\necho \"-SQL Thread not running\" >> $log_file\r\nfi\r\n\r\nif [ \"$io_thread_running\" = \"No\" ]; then\r\necho \"-IO thread not running\" >> $log_file\r\nfi\r\n\r\nif [ $seconds_late -gt 3600 ]; then #formattting how the latency of the slave behind master should be displayed\r\ndisplay_late=\"$seconds_late seconds\"\r\nif [ $seconds_late -gt 60 ]; then\r\ndisplay_late=\"$display_late = $(($seconds_late\/60)) minutes\"\r\nfi\r\n\r\nif [ $seconds_late -gt 3600 ]; then\r\ndisplay_late=\"$display_late = $(($seconds_late\/3600)) hours\"\r\nfi\r\n\r\necho \"slave is behind master by $display_late\" >> $log_file\r\nfi\r\n\r\n#echo send alerts to the admin\r\nmail -s \"[Replication stop ALERT] Replication is stopped between master and Slave on $(date +%m-%d-%Y-%H:%M)\" $admin_email $admin_email1 < $log_file\r\necho \"Slave not running, alerts sent to the admins...\"\r\nelse\r\necho \"slave is running normally, no problem detected :)\"\r\nfi\r\n\r\n<\/pre>\nThank You.<\/p>\n\n
<\/div>\n