需求描述
1 、查找超过300s的慢查询会话。
2、kill掉的历史会话保留在文件中
解决方案
#!/bin/bash
# 脚本参数定义
MYSQL_USER="root"
MYSQL_PASSWORD="######"
MYSQL_HOST="192.168.209.x"
MYSQL_PORT="3307"
SLOW_QUERY_TIME=3
SLEEP_TIME=10
OUTPUT_FILE="kill_select_slow_slave.txt"
# 无限循环，每隔 10 秒查询一次
while true; do
# 查询执行时间超过 300 秒的 SELECT 会话
slow_queries=$(mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD} -h${MYSQL_HOST} -P${MYSQL_PORT} -N -e "SELECT b.ID, b.USER, b.HOST, b.DB, b.COMMAND, TIME_TO_SEC(b.TIME), b.INFO FROM information_schema.PROCESSLIST AS b JOIN information_schema.INNODB_TRX AS a ON a.TRX_MYSQL_THREAD_ID = b.ID WHERE b.COMMAND = 'Query' AND b.INFO LIKE 'SELECT%' AND TIME_TO_SEC(b.TIME) > ${SLOW_QUERY_TIME};")
# 遍历查询到的慢查询会话并 kill 掉
while IFS= read -r line; do
if [ ! -z "$line" ]; then
session_id=$(echo $line | awk '{print $1}')
slow_query=$(echo $line | cut -d ' ' -f 7-)
# kill 慢查询会话
mysql -u${MYSQL_USER} -p${MYSQL_PASSWORD} -h${MYSQL_HOST} -P${MYSQL_PORT} -e "KILL $session_id;"
# 将被 kill 的慢 SQL 及会话记录到文件中
echo "$(date) - Killed session: $session_id - SQL: $slow_query" >> "$OUTPUT_FILE"
fi
done <<< "$slow_queries"
# 等待 10 秒后再次查询
sleep $SLEEP_TIME
done