mo_lock.sh:
++++++++++++++++++++++++++++
source ~/.bash_profile
NM=`mysql -u root -p"Xjsd.6392" -e "select count(1) from information_schema.processlist where state like '%lock%'" 2>/dev/null |awk NR==2`
echo $NM
if [ $NM -gt 0 ]; then
echo $(date +"%Y-%m-%d %H:%M:%S") >> /tmp/lock_info.log
mysql -u root -p"Xjsd.6392" -e "select user, host, info from information_schema.processlist where state like '%lock%'" 2>/dev/null >> /tmp/lock_info.log
mysql -u root -p"Xjsd.6392" -e "SELECT concat('KILL ',id,';') FROM information_schema.processlist where state like '%lock%' and user='AppUser'" 2>/dev/null >/tmp/kill_lock.sql
else
exit
fi
+++++++++++++++++++++++++
mo_session:
++++++++++++++++++++++++++
#!/bin/bash
# 设置数据库连接信息
user="root"
password="rootroot"
host="localhost"
database="cxs"
# 设置输出文件
output_file="mysql_session_monitor.log"
# 循环监控
while true; do
# 获取当前时间
current_time=$(date "+%Y-%m-%d %H:%M:%S")
# 获取MySQL session信息
session_info=$(mysql -u$user -p$password -h$host -e "SHOW FULL PROCESSLIST;" $database)
# 将监控信息及时间写入文件
echo "[$current_time] MySQL Session Info:" >> $output_file
echo "$session_info" >> $output_file
echo "----------------------------------------" >> $output_file
# 等待一段时间后再次监控
sleep 60
done
++++++++++++++++++++++++++++
常用:
mysql -u root -p"Xjsd.6392" -e "select user, host, info from information_schema.processlist where state like '%lock%'" >> /tmp/lock_info.log 2>/dev/null
SELECT concat('KILL ',id,';') FROM information_schema.processlist where state like '%lock%' INTO OUTFILE '/data/lock.txt';
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;