#/bin/sh
6E) T;R(@ LBG`DYR@
#检测mysql server是否正常提供服务
zKP[]S- mysqladmin -u sky -ppwd -h localhost ping
s&ox%L4 ? /!Fv/ #获取mysql当前的几个状态值
+DSbr5"VlB mysqladmin -u sky -ppwd -h localhost status
aN"YEL>w ;,U@zB;\%( #获取数据库当前的连接信息
bP$e1I3` mysqladmin -u sky -ppwd -h localhost processlist
fAStM: <6]Hj2 #获取当前数据库的连接数
}%wP^6G*x\ mysql -u root -p123456 -BNe "select host,count(host) from processlist group by host;" information_schema
0 D4 4 d3E N0e+^ #显示mysql的uptime
=KR^0<2r mysql -e"SHOW STATUS LIKE '%uptime%'"|awk '/ptime/{ calc = $NF / 3600;print $(NF-1), calc"Hour" }'
q#Otp\f (Ajhf}zJ #查看数据库的大小
u@Gum|_=N mysql -u root -p123456-e 'select table_schema,round(sum(data_length+index_length)/1024/1024,4) from information_schema.tables group by table_schema;'
oBai9 [+ "c~``i\G #查看某个表的列信息
hTAc}'^$ mysql -u <user> --password=<password> -e "SHOW COLUMNS FROM <table>" <database> | awk '{print $1}' | tr "\n" "," | sed 's/,$//g'
w2 (}pz: 3|e~YmZx #执行mysql脚本
n>^9+Rx|i mysql -u user-name -p password < script.sql
;,/G*`81B 8oHIXnK #mysql dump数据导出
oMn'{+(w mysqldump -uroot -T/tmp/mysqldump test test_outfile --fields-enclosed-by=\" --fields-terminated-by=,
Rp
zuSh Qc9[/4R> #mysql数据导入
bMqS:+ mysqlimport --user=name --password=pwd test --fields-enclosed-by=\" --fields-terminated-by=, /tmp/test_outfile.txt
D_f:D^ LOAD DATA INFILE '/tmp/test_outfile.txt' INTO TABLE test_outfile FIELDS TERMINATED BY '"' ENCLOSED BY ',';
Exi#@- Hb!A\;> #mysql进程监控
}/ xdHt ps -ef | grep "mysqld_safe" | grep -v "grep"
%&O'>L ps -ef | grep "mysqld" | grep -v "mysqld_safe"| grep -v "grep"
8r.MODZG/ 9mv6 f`J[u!Ja #查看当前数据库的状态
\Zk<|T61$ mysql -u root -p123456 -e 'show status'
,(yaWd6 ciPaCrV Bm^8"SSN #mysqlcheck 工具程序可以检查(check),修 复( repair),分 析( analyze)和优化(optimize)MySQL Server 中的表
1 rmN) mysqlcheck -u root -p123456 --all-databases
^\PNjj*C i # f{L; #mysql qps查询 QPS = Questions(or Queries) / Seconds
-uK@2}NZ
mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Questions"'
'^t(=02J mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Queries"'
%t-}dC& /%^^hr #mysql Key Buffer 命中率 key_buffer_read_hits = (1 - Key_reads / Key_read_requests) * 100% key_buffer_write_hits= (1 - Key_writes / Key_write_requests) * 100%
EO.}{1m=hx mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Key%"'
Q"{Q]IT 5NYYrA8,^ #mysql Innodb Buffer 命中率 innodb_buffer_read_hits=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%
I*
JSb9r mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Innodb_buffer_pool_read%"'
U[l{cRT
|G>Lud #mysql Query Cache 命中率 Query_cache_hits= (Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100%
2k""/xMF' mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Qcache%"'
+QFY.>KH GI*2*m!u #mysql Table Cache 状态量
_M}}H3 mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Open%"'
q1o)l c`G~.paY| #mysql Thread Cache 命中率 Thread_cache_hits = (1 - Threads_created / Connections) * 100% 正常来说,Thread Cache 命中率要在 90% 以上才算比较合理。
%L;'C
v mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Thread%"'
nHi6$}
I Jx1JtnyP@ #mysql 锁定状态:锁定状态包括表锁和行锁两种,我们可以通过系统状态变量获得锁定总次数,锁定造成其他线程等待的次数,以及锁定等待时间信息
6P*2Kg` mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "%lock%"'
IGtqY8 v/lQ5R1 #mysql 复制延时量 在slave节点执行
T8rf+B/.L mysql -u root -p123456 -e 'SHOW SLAVE STATUS'
,=kQJ| G_a//[p #mysql Tmp table 状况 Tmp Table 的状况主要是用于监控 MySQL 使用临时表的量是否过多,是否有临时表过大而不得不从内存中换出到磁盘文件上
)Dq/fW mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Created_tmp%"'
GiN\@F! [3QKBV1\ #mysql Binlog Cache 使用状况:Binlog Cache 用于存放还未写入磁盘的 Binlog 信 息 。
^yB>0/{)z mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Binlog_cache%"'
\hwz;V.J" 4h[^!up.7 #mysql nnodb_log_waits 量:Innodb_log_waits 状态变量直接反应出 Innodb Log Buffer 空间不足造成等待的次数
Q@lJ| mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Innodb_log_waits'
wN
![SM/+ Wg+fT{[f| D'823,-). ]+B#SIC; 整理出来的常用的shell脚本会放到
https://github.com/zhwj184/shell-work 这,主要包括。
"K8<X shell-work
[=Yfdh
M8S shell常用分析命令和脚本
X>#!s Lt mysql监控脚本
F*_mHYa; cookielog分析脚本
0r+%5}|-K 线上java进程信息dump和机器信息dump脚本
/L1qdkG cpu监控相关#/bin/sh
dB`YvKr# 46dh@&U #检测mysql server是否正常提供服务
5UwaBPj4 mysqladmin -u sky -ppwd -h localhost ping
(,E.1j]ji G!54 e #获取mysql当前的几个状态值
?hS n) mysqladmin -u sky -ppwd -h localhost status
JeJc(e J}coWjw`q #获取数据库当前的连接信息
.!
LOhZ
mysqladmin -u sky -ppwd -h localhost processlist
%cD7}o:u :Q\b$=,: #获取当前数据库的连接数
?Ee HeN_ mysql -u root -p123456 -BNe "select host,count(host) from processlist group by host;" information_schema
gUCv#: p3*}! ez4 #显示mysql的uptime
aF$HF;-y mysql -e"SHOW STATUS LIKE '%uptime%'"|awk '/ptime/{ calc = $NF / 3600;print $(NF-1), calc"Hour" }'
Da)[mxJ g[j"]~ #查看数据库的大小
16 o3ER mysql -u root -p123456-e 'select table_schema,round(sum(data_length+index_length)/1024/1024,4) from information_schema.tables group by table_schema;'
OFCOMM <UG}P \N #查看某个表的列信息
7vV3"uns mysql -u <user> --password=<password> -e "SHOW COLUMNS FROM <table>" <database> | awk '{print $1}' | tr "\n" "," | sed 's/,$//g'
B5!$5Qc HW{osav9 #执行mysql脚本
dapQ5JT/ mysql -u user-name -p password < script.sql
G7`7e@{ u
Y/Q]NT #mysql dump数据导出
_ EHr?b2 mysqldump -uroot -T/tmp/mysqldump test test_outfile --fields-enclosed-by=\" --fields-terminated-by=,
.-[UHO05^8 ]E88zWDY` #mysql数据导入
3t(nV4uDF mysqlimport --user=name --password=pwd test --fields-enclosed-by=\" --fields-terminated-by=, /tmp/test_outfile.txt
) ]U-7 LOAD DATA INFILE '/tmp/test_outfile.txt' INTO TABLE test_outfile FIELDS TERMINATED BY '"' ENCLOSED BY ',';
R\MM2_I J(h=@cw #mysql进程监控
4)-LlYS_d< ps -ef | grep "mysqld_safe" | grep -v "grep"
>"B95$x5 ps -ef | grep "mysqld" | grep -v "mysqld_safe"| grep -v "grep"
wnP#.[,V MMD4b}p ;_,= #查看当前数据库的状态
a{QHv0goG mysql -u root -p123456 -e 'show status'
!t{3IE vN-#Ej.
u Xp{+){Iu #mysqlcheck 工具程序可以检查(check),修 复( repair),分 析( analyze)和优化(optimize)MySQL Server 中的表
+%=Ao6/# mysqlcheck -u root -p123456 --all-databases
( N~[sf?& Ni'vz7j #mysql qps查询 QPS = Questions(or Queries) / Seconds
OOGqtA; mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Questions"'
d3+pS\&IX? mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Queries"'
C#`eN{%.YT zJOjc/\
#mysql Key Buffer 命中率 key_buffer_read_hits = (1 - Key_reads / Key_read_requests) * 100% key_buffer_write_hits= (1 - Key_writes / Key_write_requests) * 100%
GNA:|x mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Key%"'
bBc<yaN ><viJ$i #mysql Innodb Buffer 命中率 innodb_buffer_read_hits=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%
aIpDf|~ mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Innodb_buffer_pool_read%"'
'`#2'MXG )dbi #mysql Query Cache 命中率 Query_cache_hits= (Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100%
5*xk8* mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Qcache%"'
mEmznA XL>cTM #mysql Table Cache 状态量
AqZ{x9g! mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Open%"'
~&4Hc%*IB %g{m12 #mysql Thread Cache 命中率 Thread_cache_hits = (1 - Threads_created / Connections) * 100% 正常来说,Thread Cache 命中率要在 90% 以上才算比较合理。
1hw1AJ}(F mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Thread%"'
^&MMtWR Fd":\7p #mysql 锁定状态:锁定状态包括表锁和行锁两种,我们可以通过系统状态变量获得锁定总次数,锁定造成其他线程等待的次数,以及锁定等待时间信息
d&4]?8}=. mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "%lock%"'
q{HfT
d iY1%"x #mysql 复制延时量 在slave节点执行
fpJM)HU mysql -u root -p123456 -e 'SHOW SLAVE STATUS'
v*SSc5gFG 2`tdH|Z` #mysql Tmp table 状况 Tmp Table 的状况主要是用于监控 MySQL 使用临时表的量是否过多,是否有临时表过大而不得不从内存中换出到磁盘文件上
/_5I}{ mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Created_tmp%"'
R5c
Ya #;sUAR?] #mysql Binlog Cache 使用状况:Binlog Cache 用于存放还未写入磁盘的 Binlog 信 息 。
mD{<Lp= mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Binlog_cache%"'
-B-?z?+(O 4nQk*:p(X #mysql nnodb_log_waits 量:Innodb_log_waits 状态变量直接反应出 Innodb Log Buffer 空间不足造成等待的次数
aT1T.3 a mysql -u root -p123456 -e 'SHOW /*!50000 GLOBAL */ STATUS LIKE "Innodb_log_waits'
~G|un}g= <NZ^*] 2\h}6DGx2 Oa~|a7 `o 整理出来的常用的shell脚本会放到
https://github.com/zhwj184/shell-work 这,主要包括。
,3l=44* shell-work
]L#6'|W shell常用分析命令和脚本
_i{$5JJ+K2 mysql监控脚本
P=PcO> cookielog分析脚本
[lZo'o 线上java进程信息dump和机器信息dump脚本
Q;{[U!\: cpu监控相关
sf )ojq6s dish 磁盘管理
d#P3
< c&r