MYSQL lock table Solution

MYSQL lock table Solution

This article describes how to solve the MYSQL lock table problem. We will share this with you for your reference. The details are as follows:

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/bashmysql - u root - e " show processlist " | grep - i " Locked " >> locked_log . txtfor line in ` cat locked_log.txt | awk '{print $1 }' `doecho " kill $line ; " >> kill_thread_id . sqldone

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}'`domysqladmin 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

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/shmysql_pwd = "root Password" mysqladmin_exec = "/usr/local/bin/mysqladmin" mysql_exec = "/usr/local/bin/mysql" mysql_timeout_dir = "/tmp" expiration = "$ mysql_timeout_dir/mysql_timeout.log" placement = "$ mysql_timeout_dir/expiration" placement = "$ response/expiration" $ mysqladmin_exec-uroot-p "$ mysql_pwd" processlist | awk print {$12, $2, $4} '| grep- V Time | grep-v '| sort-rn> $ mysql_timeout_logawk' {if ($1> 30 & $3! = "Root") print "'" $ mysql_exec ""'-e "" \ "kill ", $2 "\" "-uroot" "-p" "\" '"$ mysql_pwd ""'""\"""; "} '$ mysql_timeout_log> $ mysql_kill_timeout_shecho" check start .... ">>$ mysql_kill_timeout_logecho 'date'> $ mysql_kill_timeout_logcat $ 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/bashmysql_pwd = "password" mysql_exec = "/usr/local/mysql/bin/mysql" mysql_timeout_dir = "/tmp" delimiter = "$ mysql_timeout_dir/delimiter" delimiter = "$ mysql_timeout_dir/logs "$ mysql_exec-uroot-p $ mysql_pwd-e" show processlist "| grep-I" Locked ">$ mysql_kill_timeout_logchmod 777 $ define line in '$ mysql_kill_timeout_log | awk' {print $1} ''doecho "$ mysql_exec-uroot-p $ mysql_pwd-e \" kill $ line \ ">>>$ timeout 777 $ mysql_kill_timeout_shcat $ mysql_kill_timeout_sh