##########################################################################
# mysql db & host performance remote monitor for oracle dba
#
# date:2019-03014
#
# tools :Mydba.exe
#
###########################################################################
#目前暂时不支持 操作系统层面: 特定磁盘设备IO 监控,特定网络设备流量监控 【-d -n option 暂时无法使用】
#目前暂时不支持 mysql 数据库响应时间监控(基于tcp rrt 时间实现) 【 -rt 暂时无法使用】
###使用说明
操作系统ip和mysql 用户名,密码,端口 为必填项
###eg all:
工具需要运行在 windows 命令行界面中
mydba.exe -ip 10.80.18.241 -u root -p lixora -P 22 -muser root -mpwd mysql -MP 3309 -mysql [-sys|-innodb|-i 2]
mydba.exe -ip 10.80.18.243 -u root -p lixora -P 22 -muser root -mpwd mysql -MP 3309 -innodb
mydba.exe -ip 192.168.1.99 -u root -p oracle -P 22 -muser root -mpwd mysql -MP 3306 -innodb [-sys|-mysql|-i 2]
###帮助文档输出:
mydba.ex -h
usage: mysql_dba.py [-h] [-i INTERVAL] [-t] [-sys] [-l] [-c] [-d DISK]
[-n NET] [-s] [-com] [-innodb_rows] [-innodb_pages]
[-innodb_data] [-innodb_log] [-innodb_status] [-innodb]
[-T] [-B] [-rt] [-hit] [-mysql] [-muser MUSER]
[-mpwd MPWD] [-MP MP] [-ip HOST] [-user USER] [-pwd PWD]
[-P P] [-C COUNT] [-L LOGFILE] [-logfile_by_day] [-lazy]
Created By Huanglj@zjhcsoft.com ~
optional arguments:
-h, --help show this help message and exit
-i INTERVAL, --interval INTERVAL
Time(second) Interval.
-t, --time Print The Current Time.
-sys print SysInfo (include -l,-c,-s).
-l, --load Print Load Info.
-c, --cpu Print Cpu Info.
-d DISK, --disk DISK Print Disk Info.
-n NET, --net NET Print Net Info.Time.
-s, --swap Print The Swap Info.
-com print mysql status.
-innodb_rows Print Innodb Rows Status.
-innodb_pages Print Innodb Buffer Pool Pages Status.
-innodb_data Print Innodb Data Status.
-innodb_log Print Innodb Log Status.
-innodb_status Print Innodb Status from Command: "Show Engine Innodb
Status".
-innodb Print Innodb Info.
-T, --threads Print Threads Status.
-B, --bytes Print Bytes Status.
-rt Print MySQL DB RT.
-hit Print Innodb Hit%
-mysql print SysInfo (Print MySQLInfo (include
-t,-com,-hit,-T,-B).
-muser MUSER User name to use for mysql connection(default root).
-mpwd MPWD User pwd to use for mysql connection(default mysql).
-MP MP Port number to use for mysql connection(default 3306).
-ip HOST, --host HOST
root host IP.
-user USER root host user.
-pwd PWD root host user pwd.
-P P root host passport.
-C COUNT, --count COUNT
Times.
-L LOGFILE, --logfile LOGFILE
ath of logfile.
-logfile_by_day one day a logfile.
-lazy Print Info (include -t,-l,-c,-s,-m,-hit).
#########
输出显示<1>:Mysql server 全状态信息:
#########
mydba.exe -ip 10.80.18.241 -u root -p lixora -P 22 -muser root -mpwd mysql -MP 3309 -mysql
.===============================================================.
| Welcome to use the orzdba tool ! |
| Yep...easy mysql dba life~ |
| Created by Huanglinjie lixora@foxmail.com~ |
*====================== Date: 2019-03-14 =======================*
HOST: LIXORA-PC IP: 127.0.0.1
Ver : 5.7.16-log MySQL Community Server (GPL)
DB : ('information_schema',)
Var : binlog_format[ ROW] max_connect_errors[ 100] max_connections[ 300]
max_user_connections[ 0] open_files_limit[ 5000] sync_binlog[ 1]
table_definition_cache[ 1400] table_open_cache[ 2000] thread_cache_size[ 9]
innodb_adaptive_flushing[ ON] innodb_adaptive_hash_index[ ON] innodb_buffer_pool_size[ 128M]
innodb_file_per_table[ ON] innodb_flush_log_at_trx_commit[ 1] innodb_flush_method[ ]
innodb_io_capacity[ 200] innodb_lock_wait_timeout[ 50] innodb_log_buffer_size[ 16M]
innodb_log_file_size[ 48M] innodb_log_files_in_group[ 2] innodb_max_dirty_pages_pct[ 75.000000]
innodb_open_files[ 2000] innodb_read_io_threads[ 4] innodb_thread_concurrency[ 0]
innodb_write_io_threads[ 4]
-------- -----------------------QPS-----TPS ---------Hit%----- --------threads------ --------bytes----
time | ins upd del sel iud| lor hit | run con cre cac| recv send|
13:37:00| 0 0 0 0 0 | 0 100.00 | 0 0 0 0 | 0 0 |
13:37:01| 1 6 2 4 9 | 228 100.00 | 2 33 0 8 | 12k 5k |
13:37:02| 1 6 2 4 9 | 89 100.00 | 2 33 0 8 | 12k 5k |
13:37:04| 1 6 2 4 9 | 89 100.00 | 2 33 0 8 | 13k 5k |
13:37:05| 1 6 2 4 9 | 96 100.00 | 2 33 0 8 | 12k 5k |
13:37:06| 1 7 3 5 11 | 128 100.00 | 2 33 0 8 | 15k 13k |
13:37:07| 3 11 4 6 18 | 152 100.00 | 3 33 0 8 | 23k 7k |
#########
输出显示<2>:Innodb engine 性能状态信息
#########
mydba.exe -ip 10.80.18.241 -u root -p lixora -P 22 -muser root -mpwd mysql -MP 3309 -innodb
.===============================================================.
| Welcome to use the orzdba tool ! |
| Yep...easy mysql dba life~ |
| Created by Huanglinjie lixora@foxmail.com~ |
*====================== Date: 2019-03-14 =======================*
HOST: LIXORA-PC IP: 127.0.0.1
Ver : 5.7.16-log MySQL Community Server (GPL)
DB : ('information_schema',)
Var : binlog_format[ ROW] max_connect_errors[ 100] max_connections[ 300]
max_user_connections[ 0] open_files_limit[ 5000] sync_binlog[ 1]
table_definition_cache[ 1400] table_open_cache[ 2000] thread_cache_size[ 9]
innodb_adaptive_flushing[ ON] innodb_adaptive_hash_index[ ON] innodb_buffer_pool_size[ 128M]
innodb_file_per_table[ ON] innodb_flush_log_at_trx_commit[ 1] innodb_flush_method[ ]
innodb_io_capacity[ 200] innodb_lock_wait_timeout[ 50] innodb_log_buffer_size[ 16M]
innodb_log_file_size[ 48M] innodb_log_files_in_group[ 2] innodb_max_dirty_pages_pct[ 75.000000]
innodb_open_files[ 2000] innodb_read_io_threads[ 4] innodb_thread_concurrency[ 0]
innodb_write_io_threads[ 4]
-------- ----innodb bp pages status---- -------innodb data status------- ----innodb log---- his --log(byte)-- read ---query---
time | data free dirty flush| reads writes read written| fsyncs written| list uflush uckpt view inside que|
13:37:46| 0 0 0 0 | 0 0 0 0 | 0 0 | 0 0.0b 0.0b 0 0 0 |
13:37:47| 8052 100 288 0 | 0 3 0 3k | 3 3k | 1309 0.0b 3.0m 0 0 0 |
13:37:49| 8052 100 288 0 | 0 3 0 2k | 3 2k | 1311 0.0b 3.0m 0 0 0 |
13:37:50| 8052 100 289 0 | 0 3 0 3k | 3 3k | 1316 0.0b 3.0m 1 0 0 |
13:37:51| 8052 100 289 0 | 0 7 0 16k | 7 16k | 1279 322.0b 3.0m 0 0 0 |
13:37:52| 8052 100 290 0 | 0 5 0 16k | 5 16k | 1283 105.0b 3.0m 0 0 0 |
13:37:53| 8052 100 290 0 | 0 3 0 2k | 3 2k | 1285 106.0b 3.0m 0 0 0 |
C:\Users\lixora>
#########
输出显示<3>:操作系统负载信息
#########
mydba.exe -ip 10.80.18.241 -u root -p lixora -P 22 -muser root -mpwd mysql -MP 3309 -innodb
.===============================================================.
| Welcome to use the orzdba tool ! |
| Yep...easy mysql dba life~ |
| Created by Huanglinjie lixora@foxmail.com~ |
*====================== Date: 2019-03-14 =======================*
HOST: WIN-PVSUTX24A95 IP: 127.0.0.1
-------- -------load-avg------ ------cpu-usage----- -----swap-----
time | 1m 5m 15m | usr sys idl iow| si so |
13:39:09| 0.09 0.12 0.11 | 1 1 96 0 | 0 0 |
13:39:12| 0.09 0.12 0.10 | 1 1 94 2 | 0 0 |
13:39:14| 0.09 0.12 0.10 | 2 1 95 0 | 0 0 |
13:39:17| 0.08 0.11 0.10 | 2 1 92 3 | 0 0 |
13:39:19| 0.08 0.11 0.10 | 2 2 92 2 | 0 0 |
13:39:21| 0.07 0.11 0.10 | 1 1 94 1 | 0 0 |
#########
输出显示<4>:输出日志记录到文件保存
#########
mydba.exe -ip 10.80.18.241 -u root -p lixora -P 22 -muser root -mpwd mysql -MP 3309 -lazy -L lixora.log
--------write log begin--------------------------
logfile:lixora.log
##如果需要连接记录每天的日志,并且把每天的日志归档到1个带日期属性的文件中可以使用 -logfile_by_day 选项:
mydba.exe -ip 10.80.18.241 -u root -p lixora -P 22 -muser root -mpwd mysql -MP 3309 -lazy -L lixora.log -logfile_by_day
--------write log begin--------------------------
logfile:lixora.log.20190314
.===============================================================.
| Welcome to use the orzdba tool ! |
| Yep...easy mysql dba life~ |
| Created by Huanglinjie lixora@foxmail.com~ |
*====================== Date: 2019-03-14 =======================*
HOST: LIXORA-PC IP: 127.0.0.1
Ver : 5.7.16-log MySQL Community Server (GPL)
DB : ('information_schema',)
Var : binlog_format[ ROW] max_connect_errors[ 100] max_connections[ 300]
max_user_connections[ 0] open_files_limit[ 5000] sync_binlog[ 1]
table_definition_cache[ 1400] table_open_cache[ 2000] thread_cache_size[ 9]
innodb_adaptive_flushing[ ON] innodb_adaptive_hash_index[ ON] innodb_buffer_pool_size[ 128M]
innodb_file_per_table[ ON] innodb_flush_log_at_trx_commit[ 1] innodb_flush_method[ ]
innodb_io_capacity[ 200] innodb_lock_wait_timeout[ 50] innodb_log_buffer_size[ 16M]
innodb_log_file_size[ 48M] innodb_log_files_in_group[ 2] innodb_max_dirty_pages_pct[ 75.000000]
innodb_open_files[ 2000] innodb_read_io_threads[ 4] innodb_thread_concurrency[ 0]
innodb_write_io_threads[ 4]
-------- -------load-avg------ ------cpu-usage----- -----swap----- -----------------------QPS-----TPS ---------Hit%-----
time | 1m 5m 15m | usr sys idl iow| si so | ins upd del sel iud| lor hit |
11:18:26| 0.10 0.26 0.85 | 1 1 96 0 | 0 0 | 0 0 0 0 0 | 0 100.00 |
11:18:28| 0.10 0.26 0.85 | 1 1 94 2 | 0 0 | 3 18 26 20 47 | 3212 100.00 |
11:18:31| 0.09 0.25 0.84 | 2 2 94 1 | 0 0 | 2 13 4 8 19 | 339 100.00 |
11:18:33| 0.09 0.25 0.84 | 1 1 94 2 | 0 0 | 3 18 7 11 28 | 280 100.00 |
11:18:36| 0.08 0.25 0.84 | 7 2 87 2 | 0 0 | 2 13 4 10 19 | 204 100.00 |
11:18:38| 0.08 0.25 0.84 | 1 1 94 2 | 0 0 | 3 18 7 12 28 | 424 100.00 |
11:18:41| 0.07 0.24 0.83 | 2 2 93 1 | 0 0 | 2 13 4 9 19 | 338 100.00 |
11:18:43| 0.07 0.24 0.83 | 2 1 94 1 | 0 0 | 3 18 7 12 28 | 285 100.00 |
11:18:46| 0.07 0.24 0.83 | 2 2 93 1 | 0 0 | 2 13 4 9 19 | 2850 100.00 |
11:18:48| 0.07 0.24 0.83 | 1 1 95 1 | 0 0 | 2 12 4 8 18 | 185 100.00 |
11:18:50| 0.06 0.23 0.82 | 2 2 93 2 | 0 0 | 3 18 7 12 28 | 570 100.00 |
11:18:53| 0.06 0.23 0.82 | 2 2 93 1 | 0 0 | 2 13 4 9 19 | 200 100.00 |
############
下载地址:
链接:https://pan.baidu.com/s/1j1-Q6d5sXMxXLL0HkI62Pw
提取码:q0ed