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