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

转载于:https://www.cnblogs.com/buro79xxd/archive/2009/11/23/1682567.html