Shulou(Shulou.com)06/01 Report--

This article mainly introduces "the method of solving mysql table lock". In the daily operation, I believe that many people have doubts in solving the problem of mysql table lock. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "solving the method of mysql table lock". Next, please follow the editor to study!

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

Do

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

Do

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 "

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

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

At this point, the study of "how to solve the problem of mysql table locking" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.