Procedure for viewing the number of MySQL connections

The following articles mainly describe how to correctly check the number of MySQL connections and parse the actual application code for viewing the number of MySQL connections, the following describes the specific operation plan for the number of MySQL connections. I hope this will help you in your future studies.

 
 

  
  
MySQLadmin -uiseeuon -pmtcera2009 processlist -v 
 
 

When the CPU usage is high, which statements are being executed and then indexed are added to those statements will usually be downgraded.

1. view the details of all current connections:

 
 

  
  
MySQLadmin -uroot -proot processlist  
  
  
D:\MySQL\bin>MySQLadmin -uroot -proot processlist  
  
  
+-----+------+----------------+---------+---------+------+-------+------------------+  
  
  
| Id | User | Host | db | Command | Time | State | Info |  
  
  
+-----+------+----------------+---------+---------+------+-------+------------------+  
  
  
| 591 | root | localhost:3544 | bbs | Sleep | 25 | | |  
  
  
| 701 | root | localhost:3761 | | uery | 0 | | show processlist |  
  
  
+-----+------+----------------+---------+---------+------+-------+------------------+ 
 
 

2. view only the current MySQL connections (Threads is the number of connections .):

 
 

  
  
MySQLadmin -uroot -proot status  
  
  
D:\MySQL\bin>MySQLadmin -uroot -proot status  
  
  
Uptime: 2102 Threads: 3 Questions: 15531 Slow queries: 0 Opens: 0 Flush tab  
  
  
les: 1 Open tables: 61 Queries per second avg: 7.389 
 
 

3. Modify the maximum number of MySQL connections:

Open my. ini and modify max_connections = 100 (100 by default ).

Command: show processlist;

If it is a root account, you can see the current connection of all users. For other common accounts, you can only view the connections you are using.
 

How processlist; only the first 100 items are listed. If you want to list them all, use show full processlist;

4. MySQLadmin extended-status

It turns out that someone wrote a script to monitor the server on his blog. when something went wrong, he sent an email and sent an article about text message alerts. This was amazing. After a while, I learned more and more knowledge.

In retrospect, it is actually quite simple. The basic idea is to use shell to write commands to detect many types of servers, such as MySQL, apache, and nginx .)

If the returned status is disconnected, an email is sent for an alarm if it fails. Finally, add the script to the cron program to run it regularly, so that you can check the health status of the number of MySQL connections. Below is what I wrote

A simple script:

 
 

  
  
# test.sh  
  
  
#!/bin/bash   
 
 

# Specify the shell interpreter to be called

 
 

  
  
/usr/bin/MySQLadmin -uroot ping &>/dev/null 
 
 

# This statement uses the MySQLadmin command. The ping parameter checks the health status of the MySQL server. If the MySQL connection process fails, an error is returned. &>/dev/null redirects the error, send error message

Do not output on the screen

If [-z $? ] # The key lies in $? This variable indicates the exit status after the execution of the previous command. If it is 0, it indicates that the operation is successful, and others # non-0

The parameter indicates a failure. In addition, this is the if syntax in shell. The-z parameter determines whether the condition is 0,

Then

# Do nothing, $? If the value is 0, the execution is successful and the MySQL process is healthy.

 
 

  
  
else  
  
  
mail -s "MySQL server cracked down" jackywdx@163.com  
  
  
fi  
 
 

This is the simplest detection script. Don't forget to run chmod + x test. sh to make it have the execution attributes. There are still many ways to determine whether the MySQL connection process is healthy, such as using ps aux

When running v, pay attention to the fact that a mail server must be started on the local machine, such as sendmail or qmail. If it is not installed, you can set up the sendmail server here, sendmail application topic on mail server-IT168 server Channel
The following is to put the script into cron and let it be executed regularly. The command is as follows:

Crontab-e press enter to go to a text editing page and put the following

 
 

  
  
*/5 * * * * /root/test.sh &>/dev/null 
 
 

Then exit and save. If you do not exit, press shift and then press Z again to continue _^)

The preceding command enables cron to run the test. sh script every five minutes. Check the specific parameters on your own. This is just a very simple monitoring script. More specifically, if a process is found to have crashed, you need to go to the log file to find the error information, which is a little more troublesome, use sed or awk to extract error information and try again later.