批量kill MySQL中的sleep状态的连接
方法1. 基于MySQL本身的查询和kill指令（感谢飞飞哥友情赞助）

点击(此处)折叠或打开

mysql>select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';
Query OK, 2 rows affected (0.00 sec)
mysql>source /tmp/a.txt;
Query OK, 0 rows affected (0.00 sec)

方法2. 使用管道命令（修正）

点击(此处)折叠或打开

mysqladmin -uroot -pPWD processlist | grep -i sleep | awk '{print $2}' | xargs -n 1 mysqladmin -uroot -pPWD processlist kill

简易延伸(修正)
使用Linux的定时任务来定时检测MySQL的连接数，并根据一定的阈值来批量kill sleep连接或者Query连接（防止数据库连接被爆）
类似脚本，在使用前，无论如何请在测试环境先进行详细验证之后再慎重使用......

点击(此处)折叠或打开

#!/bin/bash
#Author Wang.wenan @ 2015/09/30
#Edit @ 2015/10/09
USER=root
PASSWORD=PWD
SLIMIT=10000
QLIMIT=10000
n=`mysqladmin processlist -u$USER -p$PASSWORD | grep -i sleep |wc -l`
m=`mysqladmin processlist -u$USER -p$PASSWORD | grep -i Query |wc -l`
if [ "$n" -gt $SLIMIT ]
then
echo "sleep connection is too many, count:$n \n\n\n" >> /home/log/MySQL_Connection_Killer.log
mysqladmin processlist -u$USER -p$PASSWORD | grep -i sleep >> /home/log/MySQL_Connection_Killer.log
mysqladmin processlist -u$USER -p$PASSWORD | grep -i sleep |awk '{print $2}' | xargs -n 1 mysqladmin -u$USER -p$PASSWORD kill
echo "sleep is too many, so killed them all at $(date -d today +%Y-%m-%d\ %H:%M:%S)" >> /home/log/MySQL_Connection_Killer.log
else
echo "MySQL Connection check is passed, sleep connection count:$n" >> /home/log/MySQL_Connection_Killer.log
fi
if [ "$m" -gt $QLIMIT ]
then
echo "Query connection is too many, count:$m \n\n\n" >> /home/log/MySQL_Connection_Killer.log
mysqladmin processlist -u$USER -p$PASSWORD | grep -i Query >> /home/log/MySQL_Connection_Killer.log
mysqladmin processlist -u$USER -p$PASSWORD | grep -i Query |awk '{print $2}' | xargs -n 1 mysqladmin -u$USER -p$PASSWORD kill
echo "Query is too many, so killed them all at $(date -d today +%Y-%m-%d\ %H:%M:%S)" >> /home/log/MySQL_Connection_Killer.log
else
echo "MySQL Connection check is passed, Query connection count:$m" >> /home/log/MySQL_Connection_Killer.log
fi

使用crontab，随意设置时间间隔

查看MySQL数据库状态（小改）
转载自http://www.orczhou.com/index.php/2014/03/some-tricky-about-mysqladmin-extended-status/
PS：如果标题格式和下面的数据没有对齐，在红线标出的print语句添加空格修改就好

点击(此处)折叠或打开

mysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 extended-status |awk -F"|" "BEGIN{ count=0; }"'{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){\
print "----------|---------|--- MySQL Command Status --|----- Innodb row operation -----|-- Buffer Pool Read --";\
print "---Time---|---QPS---|select insert update delete| read inserted updated deleted| logical physical";\
}\
else if ($2 ~ /Queries/){queries=$3;}\
else if ($2 ~ /Com_select /){com_select=$3;}\
else if ($2 ~ /Com_insert /){com_insert=$3;}\
else if ($2 ~ /Com_update /){com_update=$3;}\
else if ($2 ~ /Com_delete /){com_delete=$3;}\
else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
else if ($2 ~ /Uptime / && count >= 2){\
printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
printf("|%8d %6d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
printf("|%10d %11d\n",innodb_lor,innodb_phr);\
}}';

效果图

shell，查询指定SQL的trace信息

点击(此处)折叠或打开

mysql -uUSER -pPWD -hHOST -e "use ins_tc_prd;set session optimizer_trace='enabled=on';set optimizer_trace_max_mem_size = 1638400;explain SQL;select * from information_schema.optimizer_trace\G;" > trace_log.log

#查看表空间的使用情况

点击(此处)折叠或打开

select table_name,
(data_length+ index_length) /1024/1024 as total_mb,
table_rows
from information_schema.tables
where table_schema= 'zabbix' ;//不写就是全部

在mysql client显示具体的信息

点击(此处)折叠或打开

[Client] prompt='\u@\h(\d) \\r:\m:\\s:>'

在mysql client使用tab自动完成

点击(此处)折叠或打开

[mysql] auto-rehash

避免脚本中总是出现密码insecure的小技巧

在脚本中添加 export MYSQL_PWD=xxxxxxxx, 然后在mysql的连接命令中去掉-p选项, 在脚本中就不会出现讨厌的提示信息了~~\(≧▽≦)/~

通过.frm文件来获取表结构
需要安装mysql-utilities; 3306是mysql实例的监听端口, 3310是一个空端口

点击(此处)折叠或打开

mysqlfrm --user=root --basedir=/usr/ --server=root:root@localhost:3306 /home/mysql/data/sakila/actor.frm --port=3310

效果如图:

解析所有的.frm文件

点击(此处)折叠或打开

mysqlfrm --diagnostic /home/mysql/data/sakila

效果与上图类似

ITPub博客

MySQL自用脚本/shell命令（不定期更新）

最新文章