Mysql 性能监控

杀掉mysql所有连接
mysqladmin -uroot -h127.0.0.1 -p123456  processlist |awk -F "|" '{print $2}' | xargs -n 1 mysqladmin -uroot -p123456 kill
 
杀掉joe用户连接
mysqladmin -uroot -h127.0.0.1 -p123456 processlist | awk -F "|" '{if($3 == "joe")print $2}'|xargs -n 1 mysqladmin -uroot -p123456 kill
show engine innodb status;
 
每隔一秒查询一次     grep  -r 递归  -E 正则
mysqladmin -p123456 extended-status -ri1|grep -r -E 'Queries|Threads_connected|Threads_running'
 
查看queries
mysqladmin -uroot -p123456 ext|awk '/Queries/{printf("%d",$4)}'
 
查看Queries   Threads_connected   Threads_running
mysqladmin -uroot -p123456 ext|awk '/Queries/{printf("%d ",$4)}/Threads_connected/{printf("%d ",$4)}/Threads_running/{printf("%d\n",$4)}'
 
查看MySQL实时运行状态 http://www.jb51.net/article/53900.htm
 
mysqladmin -P3306 -uroot -ppassword -h127.0.0.1 -r -i 1 ext |\
awk -F"|" \
"BEGIN{ count=0; }"\
'{ if($2 ~ /Variable_name/ && ++count == 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("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\
  printf("|%10d %11d\n",innodb_lor,innodb_phr);\
}}'
 
执行结果
----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --
---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical
 16:38:56 |     3003|   694      1      0      0| 38940        1       0       0|    115619           0
 16:38:57 |      100|    27      0      0      0|    29        0       0       0|        53           0
  
  
mysql -e 不登陆执行命令
mysql -p123456 -e "use owl-test;show tables"
如果explain出来的type列的值为index，则说明Mysql使用了索引扫描来排序

转载于:https://my.oschina.net/direnjie/blog/595542