Shell script for kill deadlock process in MySQL 02/24 Update SLTechnology News&Howtos

Shell script for kill deadlock process in MySQL

2025-02-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article mainly talks about "the shell script of kill deadlock process in MySQL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the shell script of the kill zombie process in MySQL.

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 million = "root") print "'" $mysql_exec ""'- e "" / "kill", $2 "/"-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 chmod 0 mysqld_kill_sleep.sh,chmod u+rx mysqld_kill_sleep.sh, and then use the root account to run in cron, and adjust the time by yourself.

Displays 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 you confirm that there is no problem, change the last cat to sh.

I rewrote the script above:

#! / 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

A lot of times! Lock the watch if you are not careful! Here is the ultimate way to solve the problem of table lock!

Case one

Mysql > show processlist

See sql statement

If there are few words

Mysql > kill thread_id

It can be solved.

The process of kill losing the first table lock is still not improved. Since there is no improvement, let's find a way to kill all the locking 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 looks like this.

Kill 66402982

Kill 66402983

Kill 66402986

Kill 66402991

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

Mysql > source kill_thread_id. Sql

Of course, it can also be done one by one.

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

Mysqladmin kill ${id}

Done

Case two

If a large number of operations can be generated through a series of select statements, then these results can theoretically be processed in batches.

However, mysql does not provide the ability to analyze the result set like eval. So you can only save the select results to a temporary file now, and then execute the instructions 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 three

The mode of MySQL + PHP often leads to a large number of dead processes in MySQL under high concurrency pressure, resulting in service hanging. In order to kill these processes automatically, a script is created and automatically executed by crontab in the background of the server. It is found that after doing so, it really alleviates the problem very well. Send out this script and Share.

According to their own actual needs, some changes have been made:

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 million = "root") print "'" $mysql_exec ""'- e ""\ "kill", $2 "\"- uroot"- p"\" $mysql_pwd "\";"}'$mysql_timeout_log > $mysql_kill_timeout_sh "