MySQL kill dead process script

Last Update:2013-11-25 Source: Internet

Author: User

Tags mysql manual

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

The MySQL + PHP mode often causes a large number of zombie processes in MySQL under high concurrency pressure, resulting in service downtime. In order to automatically kill these processes, a script is created and automatically executed on the backend of the server using crontab. After this is found, it is indeed a good relief of this problem. Send this script to Share it with everyone.

According to your actual needs, make some modifications:

SHELL script: mysqld_kill_sleep.sh

#! /Bin/sh

Mysql_pwd = "root Password"

Mysqladmin_exec = "/usr/local/bin/mysqladmin"

Mysql_exec = "/usr/local/bin/mysql"

Mysql_timeout_dir = "/tmp"

Mysql_timeout_log = "$ mysql_timeout_dir/mysql_timeout.log"

Mysql_kill_timeout_sh = "$ mysql_timeout_dir/mysql_kill_timeout.sh"

Mysql_kill_timeout_log = "$ mysql_timeout_dir/mysql_kill_timeout.log"

$ Mysqladmin_exec-uroot-p "$ mysql_pwd" processlist | awk '{print $12, $2, $4} '| grep-v Time | grep-v' | sort-rn> $ mysql_timeout_log

Awk '{if ($1> 30 & $3! = "Root") print "'" $ mysql_exec ""'-e ""/"kill ", $2 route /" "-uroot" "-p" "/" '"$ mysql_pwd" "'"/""; "} '$ mysql_timeout_log> $ mysql_kill_timeout_sh

Echo "check start..."> $ mysql_kill_timeout_log

Echo 'date'> $ mysql_kill_timeout_log

Cat $ mysql_kill_timeout_sh

Write this to mysqld_kill_sleep.sh. Then, run chmod 0 mysqld_kill_sleep.sh, chmod u + rx mysqld_kill_sleep.sh, and use the root account to run the command in cron. Adjust the time by yourself.

Displayed after execution:

Www #./mysqld_kill_sleep.sh

/Usr/local/bin/mysql-e "kill 27549"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27750"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27840"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27867"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27899"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27901"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27758"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27875"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27697"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27888"-uroot-p "mysql root Password ";

/Usr/local/bin/mysql-e "kill 27861"-uroot-p "mysql root Password ";

If the problem persists, change the last cat to sh.

I have rewritten the above script:

#! /Bin/bash

Mysql_pwd = "password"

Mysql_exec = "/usr/local/mysql/bin/mysql"

Mysql_timeout_dir = "/tmp"

Mysql_kill_timeout_sh = "$ mysql_timeout_dir/mysql_kill_timeout.sh"

Mysql_kill_timeout_log = "$ mysql_timeout_dir/mysql_kill_timeout.log"

$ Mysql_exec-uroot-p $ mysql_pwd-e "show processlist" | grep-I "Locked" >>$ mysql_kill_timeout_log

Chmod 777 $ mysql_kill_timeout_log

For line in '$ mysql_kill_timeout_log | awk' {print $1 }''

Echo "$ mysql_exec-uroot-p $ mysql_pwd-e/" kill $ line/"> $ mysql_kill_timeout_sh

Done

Chmod 777 $ mysql_kill_timeout_sh

Cat $ mysql_kill_timeout_sh

========================================================== ==========================================

Many times! Lock the table accidentally! Here is the ultimate solution to the lock table!

Case 1

Mysql> show processlist;

See SQL statements

Rarely

Mysql> kill thread_id;

You can solve the problem.

The process of kill the first lock table is still not improved. Since it does not improve, we can try to kill all the lock table processes. The simple script is as follows.

#! /Bin/bash

Mysql-u root-e "show processlist" | grep-I "Locked"> locked_log. txt

For line in 'cat locked_log.txt | awk '{print $1 }''

Echo "kill $ line;"> kill_thread_id. SQL

Done

Now the content of kill_thread_id. SQL is like this

Kill 66402982;

Kill 66402983;

Kill 66402986;

Kill 66402991;

.....

Well, we can execute it in the mysql shell to kill all the lock table processes.

Mysql> source kill_thread_id. SQL

Of course, you can do it in one line.

For id in 'mysqladmin processlist | grep-I locked | awk '{print $1 }''

Mysqladmin kill $ {id}

Done

Case 2

If a large number of operations can be produced through a series of select statements, the results can be processed in batches theoretically.

However, mysql does not provide the eval function for analyzing the result set. Therefore, you can only save the select result to a temporary file and then execute the commands in the temporary file.

The specific process is as follows:

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)

Mysql> SELECT concat ('Kill ', id,'; ') FROM information_schema.processlist WHERE user = 'root' INTO outfile'/tmp/a.txt ';

Query OK, 2 rows affected (0.00 sec)

Mysql> source/tmp/a.txt;

Query OK, 0 rows affected (0.00 sec)

Case 3

According to your actual needs, make some modifications:

SHELL script: mysqld_kill_sleep.sh

#! /Bin/sh

Mysql_pwd = "root Password"

Mysqladmin_exec = "/usr/local/bin/mysqladmin"

Mysql_exec = "/usr/local/bin/mysql"

Mysql_timeout_dir = "/tmp"

Mysql_timeout_log = "$ mysql_timeout_dir/mysql_timeout.log"

Mysql_kill_timeout_sh = "$ mysql_timeout_dir/mysql_kill_timeout.sh"

Mysql_kill_timeout_log = "$ mysql_timeout_dir/mysql_kill_timeout.log"

$ Mysqladmin_exec-uroot-p "$ mysql_pwd" processlist | awk '{print $12, $2, $4} '| grep-v Time | grep-v' | sort-rn> $ mysql_timeout_log

Awk '{if ($1> 30 & $3! = "Root") print "'" $ mysql_exec ""'-e "" \ "kill ", $2 "\" "-uroot" "-p" "\" '"$ mysql_pwd ""'""\"""; "} '$ mysql_timeout_log> $ mysql_kill_timeout_sh