PHP Share 28: Troubleshoot problems with MySQL running

One: The method of killing MySQL connection:

Kill thread_id: Kills the current process, disconnects

Kill query thread_id: Only Kill a connection to the current SQL, and keep the connection open.

Several ways to kill MySQL connections in bulk:

1> generates the statement temporary file for the MySQL connection that needs to be processed by using the connection information in the Information_schema.processlist table, and then executes the instructions generated in the temporary file.

Mysql> Select Concat (' KILL ', id, '; ') from information_schema.processlist where user= ' root '; +---------------------- --+| Concat (' KILL ', id, '; ') |+------------------------+| KILL 3101;             | | KILL 2946;             | +------------------------+2 rows in Set (0.00 sec)

2> kill all current MySQL connections

Mysqladmin-uroot-p processlist|awk-f "|" ' {print $} ' |xargs-n 1 mysqladmin-uroot-p ki

Mysqladmin-uroot-p processlist|awk-f "|" ' {if ($ = = ' Mike ') print $ ' |xargs-n 1 mysqladmin-uroot-p Kill

3> via Shel Script

Kill the locked MySQL connection

For ID in ' mysqladmin processlist|grep-i Locked|awk ' {print $} ' do   mysqladmin kill ${id}done

II: MySQL server has gone away problem solving method

Reason one. MySQL service is down.

A simple way to determine if this is the cause is to go to the MySQL console and see how long MySQL is running

Mysql> show global status like ' uptime '; +---------------+---------+| variable_name | Value   |+---------------+---------+| Uptime        | 3414707 |+---------------+---------+

or check the MySQL error log to see if there is any reboot information.

If the uptime value is large, it indicates that the MySQL service has been running for a long time. Indicates that the service has not been restarted recently.
If the log does not have relevant information, also the table name MySQL service has not been restarted recently, you can continue to check the following several things.

Cause two. MySQL Connection timed out

That is, a MySQL long connection has not been initiated for a long time, reached the server side timeout, was forcibly shut down by the server.
After this connection initiated the query, it will error server has gone away
(Most PHP scripts fall into this category)

Mysql> show global variables like '%timeout '; +----------------------------+----------+| Variable_name              | Value    |+----------------------------+----------+| connect_timeout            | |       | delayed_insert_timeout     | |      | innodb_lock_wait_timeout | | | |       innodb_rollback_on_timeout | OFF      | | interactive_timeout |        28800 |    | lock_wait_timeout          | 31536000 | | net_read_timeout           | 30       | | | net_write_timeout          | |       | slave_net_timeout          | 3600     | | wait_timeout               | 28800    |+----------------------------+--- -------+10 rows in Setwait_timeout is 28,800 seconds, that is, the MySQL link is automatically closed after 28,800 seconds of no operation

Workaround:

1> modifying MY.CNF

Wait_timeout=2880000interactive_timeout = 2880000

If you cannot modify MY.CNF, you can set Client_interactive,mysql to 8 hours by default when you connect to the database.

2> Check the link status of MySQL to make it relink

function ping () {    if (!mysql_ping ($this->link)) {        mysql_close ($this->link);//Note: Be sure to perform a database shutdown first, which is the key        $this->connect ();    }}

The code that I need to call this function might be like this

Ping () This function first detects whether the data connection is normal, if it is closed, the entire MySQL instance of the current script is closed, and then reconnected.
After this processing, it is very effective to solve the problem of MySQL server has gone away, and it will not cause additional overhead to the system.

sql = "Set interactive_timeout=24*3600"; Mysql_real_query (...)

Cause three. MySQL Request link process is active kill

This is similar to the reason two, just one person is a MySQL own action

Mysql> show global status like ' Com_kill '; +---------------+-------+| variable_name | Value |+---------------+-------+| Com_kill      |    |+---------------+-------+

Cause four. Your SQL statement was too large.

This error can also occur when the result set of the query exceeds Max_allowed_packet. The positioning method is to play the relevant error statement.

Export to a file using select * into outfile to see if the file size exceeds max_allowed_packet, and if it is exceeded, you need to adjust the parameters or refine the statement.

Mysql> show global variables like ' max_allowed_packet '; +--------------------+---------+| Variable_name      | Value   |+--------------------+---------+| max_allowed_packet | 1048576 |+--------------------+---------+1 row in Set (0.00 sec)

Workaround:

1> Modifying parameters

Mysql> set global max_allowed_packet=1024*1024*16;mysql> show global variables like ' max_allowed_packet '; +------ --------------+----------+| Variable_name      | Value    |+--------------------+----------+| max_allowed_packet | 16777216 |+--------------------+----------+1 row In Set (0.00 sec)

2> Modify MY.CNF, increase the value of Max_allowed_packet can be

Under [mysqld] configuration:

Max_allowed_packet = 10M (You can also set the size you want)
The Max_allowed_packet parameter is used to control the maximum length of its communication buffer.

Reference:

MySQL Common statements: http://www.jb51.net/article/18667.htm

PHP Share 28: Troubleshoot problems with MySQL running