The solution to the problem of Mysql lock table _mysql

This article illustrates the solution to the problem of MySQL lock table. Share to everyone for your reference, specific as follows:

A lot of times! Just accidentally lock the watch! Here is the ultimate way to solve the lock table!

Case A

Mysql>show processlist;

Refer to SQL statements

Less often.

Mysql>kill thread_id;

It's going to work out.

The process of killing the first lock table is still not improving. Now that we don't improve, we'll try to kill the process of locking all the tables, and the simple script is as follows.

#!/bin/bash
mysql-u root-e "Show Processlist" | Grep-i "Locked" >> locked_log. TXT in
' cat Locked_log.txt | awk ' {print '} ' do
echo kill $line; ' >> kill_thread_id. SQL Done

Now Kill_thread_id.sql's content looks like this.

Kill 66402982;
Kill 66402983;
Kill 66402986;
Kill 66402991;
.....

Well, we execute it in the MySQL shell, and we can kill all the lock-table processes.

MySQL > Source kill_thread_id. Sql

Of course, you can take care of it.

For ID in ' Mysqladmin processlist | Grep-i Locked | awk ' {print '} ' does
mysqladmin kill ${id} done

Case Two

If large quantities of operations can be generated through a series of SELECT statements, the results can be dealt with in batches in theory.

But MySQL does not provide the ability to parse the result set such as Eval. So you can only save the select results to a temporary file, 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

MySQL + PHP mode in the large concurrent pressure will often lead to a large number of MySQL zombie process, leading to service death. In order to automatically kill these processes, a script is placed in the server background through the crontab automatic execution. After discovering this, it really eased the problem. Send this script out to share with everyone.

According to their actual needs, made a number of changes:

Shell script: mysqld_kill_sleep.sh

#!/bin/sh
mysql_pwd= "Root's 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 $, $, $} ' | Grep-v Tim e | Grep-v ' | ' |  Sort-rn > $mysql _timeout_log
awk ' {if ($1>30 && $3!= "root") print "" "$mysql _exec" "' E" "\" "Kill", $ "\" "-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, then use the root account to run in cron, time to adjust their own.

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

For more information on MySQL-related content readers can view the site topics: "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia" and "Php+mysql Database Operation Tutorial"

I hope this article will help you with MySQL database program design.