shell 解决同步mysql slave 锁表问题
简明说下我的环境： 这是台 mysql SLAVE 服务器，主的不和他同一机房 ，主的是写的数据库 ，这是读的mysql 一又抓数据的 同步进程就锁，导致下面的select 进程全部锁掉 导致数据库 负载超级从而服务器就死机。因为我这是做架构调整方案前的一些弊端问题，这是架构调整前的访问关系！为了临时解决就写了一个简单的脚本分享给大家不要见笑： [root@c3 shell]# cat slav.sh #!/bin/bash State=`/usr/local/mysql-5.0.89/bin/mysql -u root -padmin#1 -e "show processlist" |grep system |awk '{print$7}'|sed -n '2'p` NU=`/usr/local/mysql-5.0.89/bin/mysql -u root -padmin#1 -e "show processlist" |grep -i Locked |wc -l` status1=`/usr/local/mysql-5.0.89/bin/mysql -u root -padmin#1 -e "show slave status\G;" |grep Running |awk '{print$2}' | sed -n '2'p` sleepst=`/usr/local/mysql-5.0.89/bin/mysql -u root -padmin#1 -e "show processlist" |grep -i sleep |wc -l` sort=`/usr/local/mysql-5.0.89/bin/mysql -u root -padmin#1 -e "show processlist" |grep -i sorting |wc -l` if [ "$State" = "Locked" ]; then /usr/local/mysql-5.0.89/bin/mysql -u root -padmin#1 -e "slave stop;"&& echo " `date` comm is Locked" sleep 60s elif [ "$NU" -eq 0 -a $status1 = No -a $sleepst -lt 10 -a $sort -lt 10 ]; then /usr/local/mysql-5.0.89/bin/mysql -u root -padmin#1 -e "slave start;" && echo " `date` slave start" elif [ $sleepst -gt 15 ]; then for id in `/usr/local/mysql-5.0.89/bin/mysqladmin -u root -padmin#1 processlist |grep -i sleep | awk '{print $2}' ` do /usr/local/mysql-5.0.89/bin/mysqladmin -u root -padmin#1 kill ${id} && echo " `date` kill sleep " done elif [ "$NU" -gt 3 ] ; then echo " `date` table locked status " else echo "slave is operating normally!" fi -- 本脚本比较山寨，新手而已切莫见怪！ 本文转自 houzaicunsky 51CTO博客，原文链接:http://blog.51cto.com/hzcsky/506427
