ITPub博客

首页 > 数据库 > MySQL > MySQL自用脚本/shell命令(不定期更新)

MySQL自用脚本/shell命令(不定期更新)

原创 MySQL 作者:wangwenan6 时间:2015-09-30 15:36:49 0 删除 编辑
批量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)
  3. mysql>source /tmp/a.txt;
  4. 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
  8. n=`mysqladmin processlist -u$USER -p$PASSWORD | grep -i sleep |wc -l`
  9. m=`mysqladmin processlist -u$USER -p$PASSWORD | grep -i Query |wc -l`
  10. if [ "$n" -gt $SLIMIT ]
  11. then
  12.     echo "sleep connection is too many, count:$n \n\n\n" >> /home/log/MySQL_Connection_Killer.log
  13.     mysqladmin processlist -u$USER -p$PASSWORD | grep -i sleep >> /home/log/MySQL_Connection_Killer.log
  14.     mysqladmin processlist -u$USER -p$PASSWORD | grep -i sleep |awk '{print $2}' | xargs -n 1 mysqladmin -u$USER -p$PASSWORD kill
  15.     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
  16. else
  17.     echo "MySQL Connection check is passed, sleep connection count:$n" >> /home/log/MySQL_Connection_Killer.log
  18. fi
  19. if [ "$m" -gt $QLIMIT ]
  20. then
  21.     echo "Query connection is too many, count:$m \n\n\n" >> /home/log/MySQL_Connection_Killer.log
  22.     mysqladmin processlist -u$USER -p$PASSWORD | grep -i Query >> /home/log/MySQL_Connection_Killer.log
  23.     mysqladmin processlist -u$USER -p$PASSWORD | grep -i Query |awk '{print $2}' | xargs -n 1 mysqladmin -u$USER -p$PASSWORD kill
  24.     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
  25. else
  26.     echo "MySQL Connection check is passed, Query connection count:$m" >> /home/log/MySQL_Connection_Killer.log
  27. 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-02-28

  • 博文量
    102
  • 访问量
    1350157