MySQL Process Management

Number of Connections

show status where variable_name = 'threads_connected';

Connections

show status where variable_name = 'threads_running';

Active Connections

SHOW VARIABLES LIKE "max_connections";

Connection Limit

SHOW VARIABLES LIKE "max_used_connections";

High Water Mark

Running Processes

show processlist;

or...

SELECT id,

       user,

       host,

       db,

       command,

       time,

       state,

       info

  FROM information_schema.processlist; 

From the Query (Q) screen...
d - change refresh intervale - explain a queryf - show full queryr - reverse sort orderi - toggle idle processesk - kill a query

Kill Processes

KILL 9999;

Where 9999 is the id from processlist
From the Query (Q) screen, type k. You will be prompted for a thread id (as shown in the ID column on the screen). Confirm kill by pressing y when prompted (anything other than y will cancel the kill action).

Kill All Processes Running for Longer than a Specified Time for a Specified User

SELECT CONCAT('KILL ',id,';')

  FROM information_schema.processlist

 WHERE user='myuser'

   AND time > 1800 

  INTO OUTFILE '/mysql/mysql-files/myfile.txt';

1800 seconds equates to 30 minutes

SOURCE /mysql/mysql-files/myfile.txt;

Kill All SELECT Queries for a Specified User

SELECT CONCAT('KILL ',id,';') 

  FROM information_schema.processlist 

 WHERE user='myuser' 

  AND info LIKE 'SELECT%'

 INTO OUTFILE '/mysql/mysql-files/myfile.txt'; 


SOURCE /mysql/mysql-files/myfile.txt;

Kill All Processes for a Specified User

SELECT CONCAT('KILL ',id,';') 

  FROM information_schema.processlist 

 WHERE user='myuser' 

 INTO OUTFILE '/mysql/mysql-files/myfile.txt'; 


SOURCE /mysql/mysql-files/myfile.txt;

For mariaDB only...

KILL USER myuser;

Python

import pymysql


connection = pymysql.connect(host='localhost',

                             user='myuser',

                             db='mydb',

                             cursorclass=pymysql.cursors.DictCursor)


with connection.cursor() as cursor:

    cursor.execute('SHOW PROCESSLIST')

    for item in cursor.fetchall():

        if item.get('Time') > 1800:

            _id = item.get('Id')

            print('kill %s' % item)

            cursor.execute('kill %s', _id)

    connection.close()

1800 seconds equates to 30 minutes

PHP

$result = mysql_query("SHOW FULL PROCESSLIST");

while ($row=mysql_fetch_array($result)) {

  $process_id=$row["Id"];

  if ($row["Time"] > 1800) {

    $sql="KILL $process_id";

    mysql_query($sql);

  }

}

1800 seconds equates to 30 minutes

TCP/IP or Socket

To determine if a connection is using a TCP/IP connection or a UNIX Socket use...

netstat -ln | grep 'mysql'

Useful Tables/Views