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

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

点击(此处)折叠或打开

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

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

点击(此处)折叠或打开

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

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

点击(此处)折叠或打开

1. #!/bin/bash
2. #Author Wang.wenan @ 2015/09/30
3. #Edit @ 2015/10/09
4. USER=root
5. PASSWORD=PWD
6. SLIMIT=10000
7. QLIMIT=10000
9. n=`mysqladmin processlist -u$USER -p$PASSWORD | grep -i sleep |wc -l`
10. m=`mysqladmin processlist -u$USER -p$PASSWORD | grep -i Query |wc -l`
12. if [ "$n" -gt $SLIMIT ]
13. then
14.     echo "sleep connection is too many, count:$n \n\n\n" >> /home/log/MySQL_Connection_Killer.log
15.     mysqladmin processlist -u$USER -p$PASSWORD | grep -i sleep >> /home/log/MySQL_Connection_Killer.log
16.     mysqladmin processlist -u$USER -p$PASSWORD | grep -i sleep |awk '{print $2}' | xargs -n 1 mysqladmin -u$USER -p$PASSWORD kill
17.     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
18. else
19.     echo "MySQL Connection check is passed, sleep connection count:$n" >> /home/log/MySQL_Connection_Killer.log
20. fi
22. if [ "$m" -gt $QLIMIT ]
23. then
24.     echo "Query connection is too many, count:$m \n\n\n" >> /home/log/MySQL_Connection_Killer.log
25.     mysqladmin processlist -u$USER -p$PASSWORD | grep -i Query >> /home/log/MySQL_Connection_Killer.log
26.     mysqladmin processlist -u$USER -p$PASSWORD | grep -i Query |awk '{print $2}' | xargs -n 1 mysqladmin -u$USER -p$PASSWORD kill
27.     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
28. else
29.     echo "MySQL Connection check is passed, Query connection count:$m" >> /home/log/MySQL_Connection_Killer.log
30. fi

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

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

点击(此处)折叠或打开

1. 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)){\
2. print "----------|---------|--- MySQL Command Status --|----- Innodb row operation -----|-- Buffer Pool Read --";\
3. print "---Time---|---QPS---|select insert update delete|  read  inserted updated deleted|   logical    physical";\
4. }\
5. else if ($2 ~ /Queries/){queries=$3;}\
6. else if ($2 ~ /Com_select /){com_select=$3;}\
7. else if ($2 ~ /Com_insert /){com_insert=$3;}\
8. else if ($2 ~ /Com_update /){com_update=$3;}\
9. else if ($2 ~ /Com_delete /){com_delete=$3;}\
10. else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\
11. else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\
12. else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\
13. else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\
14. else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\
15. else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\
16. else if ($2 ~ /Uptime / && count >= 2){\
17. printf(" %s |%9d",strftime("%H:%M:%S"),queries);\
18. printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);\
19. printf("|%8d %6d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
20. printf("|%10d %11d\n",innodb_lor,innodb_phr);\
21. }}';

效果图


shell，查询指定SQL的trace信息

点击(此处)折叠或打开

1. 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

#查看表空间的使用情况

点击(此处)折叠或打开

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

在mysql client显示具体的信息

点击(此处)折叠或打开

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

在mysql client使用tab自动完成

点击(此处)折叠或打开

1. [mysql] auto-rehash

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

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

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

点击(此处)折叠或打开

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

效果如图:


解析所有的.frm文件

点击(此处)折叠或打开

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

效果与上图类似

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29510932/viewspace-1812475/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29510932/viewspace-1812475/