Script Sharing-mysql service + master-slave + alarm Notification monitoring

1. Configure MySQL local password-free login

Account password information will be recorded in the user's home directory ". mylogin.cnf" file, if you want to re-enter the password to log in, then delete this file.

2. Configure mail forwarding

We need to install the mail client software "MAILX" and then configure the corporate public mailbox for mail delivery.

3. Writing scripts

Writing ideas:

• Detects if the MySQL service is running, detects MySQL master-slave replication status if it is running, notifies the administrator if it is not running, and logs
• Detection of MySQL master-slave replication status, if abnormal alarm notify the administrator to handle, and log
• Scripts run in a dead loop, run every 10 seconds
• In order to prevent repeated email alert notification and logging during the loop, I use the temporary file to filter its duplicate write log and mail delivery through if judgment, log and send messages when the status switch occurs

The script reads as follows:

While:d o# configuration section [email protected] #设置管理员邮箱, multiple mailboxes log_path=/var/log/mysqlmonitor.log# set the log file path at a space interval mysql_port= 3306# set MySQL database port ip_device=ens160# set host NIC device name host_ipaddr= ' ifconfig $IP _device |awk ' nr==2{print $ ' local_date= ' Date +%y-%m-%d\ \%h:%m:%s ' TMP_FILE_PROCESS=/TMP/MYSQLPROCESS.TMPTMP_FILE_MASTER_AND_SLAVE=/TMP/MYSQLMS.TMPIF [!-E $TMP _file_process] | | [!-e tmp_file_master_and_slave]thentouch $TMP _file_processtouch $TMP _file_master_and_slavefimysqlmonitor_tmp_one= ' Cat $TMP _file_process |grep ' MySQL service is working properly! ' | Wc-l ' mysqlmonitor_tmp_two= ' cat $TMP _file_process |grep ' MySQL service is not running! ' | Wc-l ' mysqlmonitor_tmp_three= ' cat $TMP _file_master_and_slave |grep ' MySQL master copy works abnormally! ' | Wc-l ' mysqlmonitor_tmp_four= ' cat $TMP _file_master_and_slave |grep ' mysql master copy works fine! ' |  Wc-l ' mysql_active= ' netstat-lnupt |grep $MYSQL _port|wc-l ' if [$MYSQL _active! = 0]then If [$MYSQLMONITOR _tmp_one = 0] Then echo "$LOCAL _date $HOST _ipaddr mysql service is working!" >> $LOG _path Echo ' MySQL service is working! ' > $TMP _File_process for I in $ADMIN _mails do echo-e "MySQL service has resumed normal operation! The information is as follows: \ n occurs: $LOCAL _date \ nthe host: $HOST _ipaddr "|mail-s" host: $HOST _ipaddr the MySQL service has resumed functioning "$i done FISL Ave_io_running= ' mysql-e ' show slave status\g; "|grep" slave_io_running: "|awk-f ': ' {print $} ' slave_sql_running= '  Mysql-e "show slave status\g;" |grep "slave_sql_running:" |awk-f ': ' {print $} ' slave_sql_running_state= ' Mysql-e ' show Slave status\g; "|grep" slave_sql_running_state: "|awk-f ': ' {print $} ' if [$Slave _io_running = No] | |                [$Slave _sql_running = No] Then if [$MYSQLMONITOR _tmp_three = 0] Then echo "$LOCAL _date $HOST _ipaddr mysql master copy exception!" >> $LOG _path Echo ' MySQL master copy works abnormally! '                        > $TMP _file_master_and_slave for i in $ADMIN _mails do Echo-e "MySQL master copy works abnormally! Please deal with it in time! The information is as follows: \nio thread: $Slave _io_running \nsql Thread: $slave_sql_running \ n Error message: $Slave _sql_running_state \ n occurs: $LOCAL _date \ nthe host: $HOST _ipaddr "|mail-s" Host: $HOST _ipaddr  MySQL master-slave copy works abnormally "$i done fi else if [$MYSQLMONITOR _tmp_four = 0 ] then echo "$LOCAL _date $HOST _ipaddr mysql master copy normal!" >> $LOG _path Echo ' mysql master copy works fine! '                        > $TMP _file_master_and_slave for i in $ADMIN _mails do Echo-e "MySQL master copy work is back to normal! The information is as follows: \nio thread: $Slave _io_running \nsql thread: $Slave _sql_running \ n occurs: $LOCAL _date \ nthe host: $HOST _ipaddr "|mail-s" Host: $HOST _ ipaddr MySQL master-slave copy work has returned to normal "$i done fi fielse if [$MYSQLMONITOR _tmp_two = 0] Then echo "$LOCAL _date $HOST _ipaddr MySQL service is not running!" >> $LOG _path Echo ' MySQL service is not running! ' > $TMP _file_process for i in $ADMIN _mails do echo-e "MySQL service is not running! Please deal with it in time! The information is as follows: \ nOccurrence time: $LOCAL _date \ nthe host: $HOST _ipaddr "|mail-s" host: $HOST _ipaddr MySQL service is not running "$i done Fifisleep 10;don E

4. Run the script

Run as daemon.
[Email protected] ~]# nohup sh mysqlmonitor.sh >/dev/null &

5. Test Scripts

• Run the script
• Test whether the MySQL service is working properly, manually turn off the startup service, check for email notifications
• Test MySQL master-slave replication status, manually shut down start MySQL master and slave to see if the email notification was received

Script Sharing-mysql service + master-slave + alarm Notification monitoring