Shell script checks and monitors the CPU usage of mysql

When the site traffic is large, mysql is under a high pressure. when the CPU usage of mysql exceeds 300%, it cannot provide services, and it is almost stuck. at this time, the best way is to restart the mysql service. This is unpredictable. we don't know when mysql's usage reaches 300%, or we need to write a program to make regular judgments. After learning shell programming, I wrote the following script: when the site traffic is large, mysql is under great pressure and cannot provide services when the CPU usage of mysql exceeds 300%, it's almost stuck. The best way to do this is to restart the mysql service. This is unpredictable. we don't know when mysql's usage reaches 300%, or we need to write a program to make regular judgments.

I learned shell programming and wrote the following script:

#!/bin/bashcpu=`ps aux | grep 'mysqld$' | cut -d " " -f6 | cut -d. -f1`if [ $cpu -gt 300 ]then    service mysql restart && date >> /tmp/mysql.logfi

 

A little explanation. First, run the "ps aux" command to obtain the status information of all system processes, including CPU and memory, such:

Then, the information is transmitted to grep through the pipeline. $ indicates the end of the regular expression. the process ending with "mysqld" is actually mysql, here, the information of the mysql process is returned, one line, for example:

The next cut is the truncation string. we need to calculate the CPU usage. of course, we need to take the mysql CPU value. the cut command is separated by tab by default, however, the blank space in the string displayed by ps aux is a space rather than a tab. we need to use spaces to separate them. Some strings contain multiple spaces, here, The-f8 parameter intercepts 8th strings (some systems may have 6th strings). This string is the CPU usage of mysql!

Maybe you have doubts, since you have obtained the CPU usage of mysql, why is there a cut? Good question! I didn't think of this problem at the beginning. Because we need to compare the CPU usage, the string we obtain here is a floating point number (with a decimal point), but the shell programming does not support comparing floating point numbers. What should I do? Then compare the integer. Use "." to separate the floating point number. The first one is the integer part, so that we can obtain the integer part of mysql usage.

The following is a judgment. if it is greater than 300, the mysql service will be restarted. There is another command, which is a simple "log". The purpose is to record the restart time of mysql once the service is restarted, so that you can analyze the website pressure in the future.

The next step is to regularly execute this program. you can execute the program once every 5 or 10 minutes as needed. to edit the/etc/crontab file, add the following statement:

? */5 *   * * *   root    /home/ma6174/mysql_restart.sh