Turn: the Final Solution to mysql lock table

Turn: most of the time to solve the mysql lock table! Lock the table accidentally! Here is the ultimate solution to the lock table! Case 1: mysqlshow? Processlist; if you see few SQL statements, mysqlkill? Thread_id; can solve the problem of kill the first lock table process ,? Still not improved .? Since it does not improve ,? Let's try to import all the lock tables.

Turn: most of the time to solve the mysql lock table! Lock the table accidentally! Here is the ultimate solution to the lock table! Case 1: mysqlshow? Processlist; if you see few SQL statements, mysqlkill? Thread_id; can solve the problem of kill the first lock table process ,? Still not improved .? Since it does not improve ,? Let's try to import all the lock tables.

Turn: the ultimate solution to mysql lock tables

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.

Kill the process of the first lock table ,? Still not improved .? Since it does not improve ,? Let's try to kill all the lock table processes, right ,? A 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 }''
Do?
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;
.....

Okay ,? Run the following command in the mysql shell ,? All the lock table processes can be killed.

Mysql> source? Kill_thread_id. SQL

Of course ,? It can also be done in one row.
For? Id? In? 'Mysqladmin? Processlist? |? Grep? -I? Locked? |? Awk? '{Print? $1 }''
Do
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? MySQL? +? The PHP mode often causes a large number of zombie processes in MySQL under high concurrency pressure, leading to service suspension. 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? "\""? "? -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 it in cron. The time is adjusted 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 }''?
Do?
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? Is it convenient!

Finally, paste a script in use.

?

12345

#!/bin/bashmysql -uroot -pxxxxx -e?"show processlist"?|grep?-i?"Lock"?|awk?'{print $1}'?>sqlid.sqlsed?-i?'s/^/kill\ /g'?sqlid.sqlsed?-i?'s/$/;/g'?sqlid.sqlmysql -uroot -pxxxxx -e?"source /home/hgz/sqlid.sql"

Sqlid. SQL file content:

1234567891011121314151617

kill 2;kill 1088;kill 1265;kill 1274;kill 1287;kill 1288;kill 1289;kill 1290;kill 1291;kill 1295;kill 1296;kill 1298;kill 1299;kill 1302;kill 1304;kill 1305;kill 1309;

?

Article