MySQL – Monitor & kill sleep processes

1. Monitor:

mysql -uroot -ppassword databaseName -e “show full processlist” | grep -v Sleep
mysql -uroot -ppassword databaseName -e “show full processlist” | grep -v Sleep | sort -k6rn >sort.tmp //sort the SQL queries
mysql -uroot -ppassword databaseName -e “show global status like ‘%tmp%'” //IOWait is high, check the disk tmp table tmp, Sending Data、statistics will be the points to check out

2. Kill mysql:

ps -ef|grep mysql
———————————————–
root 3649 1 0 17:44 pts/1 00:00:00 /bin/sh ./bin/mysqld_safe –no-defaults
root 3663 3649 0 17:44 pts/1 00:00:06
/usr/local/mysql/bin/mysqld –no-defaults –basedir=/usr/local/mysql
–datadir=/usr/local/mysql/data –user=mysql
–pid-file=/usr/local/mysql/data/localhost.localdomain.pid
–skip-locking
root 3664 3663 0 17:44 pts/1 00:00:00
/usr/local/mysql/bin/mysqld –no-defaults –basedir=/usr/local/mysql
–datadir=/usr/local/mysql/data –user=mysql
–pid-file=/usr/local/mysql/data/localhost.localdomain.pid
–skip-locking
root 3665 3664 0 17:44 pts/1 00:00:00
/usr/local/mysql/bin/mysqld –no-defaults –basedir=/usr/local/mysql
–datadir=/usr/local/mysql/data –user=mysql
–pid-file=/usr/local/mysql/data/localhost.localdomain.pid
–skip-locking
root 3666 3664 0 17:44 pts/1 00:00:00
/usr/local/mysql/bin/mysqld –no-defaults –basedir=/usr/local/mysql
–datadir=/usr/local/mysql/data –user=mysql
–pid-file=/usr/local/mysql/data/localhost.localdomain.pid
–skip-locking
root 3667 3664 0 17:44 pts/1 00:00:00
/usr/local/mysql/bin/mysqld –no-defaults –basedir=/usr/local/mysql
–datadir=/usr/local/mysql/data –user=mysql
–pid-file=/usr/local/mysql/data/localhost.localdomain.pid
–skip-locking
root 3668 3664 0 17:44 pts/1 00:00:00
/usr/local/mysql/bin/mysqld –no-defaults –basedir=/usr/local/mysql
–datadir=/usr/local/mysql/data –user=mysql
–pid-file=/usr/local/mysql/data/localhost.localdomain.pid
–skip-locking
root 3669 3664 0 17:45 pts/1 00:00:02
/usr/local/mysql/bin/mysqld –no-defaults –basedir=/usr/local/mysql
–datadir=/usr/local/mysql/data –user=mysql
–pid-file=/usr/local/mysql/data/localhost.localdomain.pid
–skip-locking
root 3670 3664 0 17:45 pts/1 00:00:01
/usr/local/mysql/bin/mysqld –no-defaults –basedir=/usr/local/mysql
–datadir=/usr/local/mysql/data –user=mysql
–pid-file=/usr/local/mysql/data/localhost.localdomain.pid
–skip-locking
root 3673 3664 0 17:45 pts/1 00:00:00
/usr/local/mysql/bin/mysqld –no-defaults –basedir=/usr/local/mysql
–datadir=/usr/local/mysql/data –user=mysql
–pid-file=/usr/local/mysql/data/localhost.localdomain.pid
–skip-locking
root 3674 3664 0 17:45 pts/1 00:00:00
/usr/local/mysql/bin/mysqld –no-defaults –basedir=/usr/local/mysql
–datadir=/usr/local/mysql/data –user=mysql
–pid-file=/usr/local/mysql/data/localhost.localdomain.pid
–skip-locking
killall -9 mysql
ps -ef | grep mysql | kill -9 $2
ps -ef | grep mysql | killall -9 $2
killall mysqld

3. kill sleep

#!/bin/sh
while :
do
n=`/usr/bin/mysqladmin processlist -uroot -ppasswprd | grep -i sleep | wc -l`
date=`date +%Y%m%d\[%H:%M:%S]`
echo $n

if [ "$n" -gt 10 ]
then
for i in `/usr/bin/mysqladmin processlist -uroot -ppasswprd | grep -i sleep | awk '{print $2}'`
do
/usr/bin/mysqladmin kill $i
done
echo "sleep is too many i killed it" >> /tmp/sleep.log
echo "$date : $n" >> /tmp/sleep.log
fi
sleep 5
done

4. MySQL Replication:
4.1 Check Master-Slave:

mysql> Show slave status \G;
mysql> Show master status;
mysql> reset slave;
mysql> set global sql_slave_skip_counter=1;
//remember to Slave first:mysql> stop slave; then restart Slave:mysql> start slave;)
mysql>change master to master_host=IP,
master_user=’replication userName’,
master_password=’replication Passwrod’,
master_log_file=’log-bin.000001′,
master_log_pos=0;
//-F will refresh Master Log, it usually work with Change Master, but this command will lock your table
mysqldump –database DATABASEName -uUserName -pPassWord –lock-all-tables -F >DATAyyyymmdd.sql
mysqldump -d DATABASEName -uUserName -pPassWord > DATAyyyymmdd.structure
// structure only without data
mysqldump -t DATABASEName -uUserName -pPassWord > DATAyyyymmdd.data
// data only without table structure queries
mysqlbinlog binlogFileName –start-position=
mysql> grant replication slave on *.* to username@IP identified by ‘ passwd';
$ tcpdump -A “dst port 3306″ //check port 3306

作者:Buro#79xxd 出处:http://www.cnblogs.com/buro79xxd/

About 智足者富

http://chenpeng.info

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>