MySQL的小工具orzdba

orzdba是淘宝开源的一个方便监控MySQL的Perl脚本

下载地址：https://github.com/zhangchunsheng/orzdba

上传到本地服务器并解压

unzip orzdba-master.zip

放到 /usr/local/下

cp orzdba-master.zip /usr/local/orzdba

赋予orzdba脚本权限

cd /usr/local/orzdba
chmod +x orzdba

修改配置信息

vi orzdba
set nu
#在第54行修改端口号
my $port = 3306;       # -P
#在第55行修改
my $socket = '/tmp/mysql3306.sock' ;# -S #请确认sock路径
#在第160行修改
my $MYSQL = qq{mysql -s --skip-column-names -uroot -proot -P$port }; #请确认账号密码

配置完成

#查看Linux主机指标

1 [root@hostM1 orzdba]# pwd
2 /usr/local/orzdba
3 [root@hostM1 orzdba]# ./orzdba -sys -C 10 -i 1 -t -d sda
4
5 .=================================================.
6 | Welcome to use the orzdba tool ! |
7 | Yep...Chinese English~ |
8 '=============== Date : 2020-08-19 ==============='
9
10 HOST: hostM1 IP: 192.168.44.128
11
12 -------- -----load-avg---- ---cpu-usage--- ---swap--- -------------------------io-usage-----------------------
13 time | 1m 5m 15m |usr sys idl iow| si so| r/s w/s rkB/s wkB/s queue await svctm %util|
14 15:08:28| 0.17 0.09 0.06| 1 0 99 0| 0 0| 0.2 0.9 9.3 55.1 0.0 7.3 1.4 0.2|
15 15:08:29| 0.17 0.09 0.06| 0 1 99 0| 0 0| 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0|
16 15:08:30| 0.17 0.09 0.06| 1 1 98 0| 0 0| 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0|
17 15:08:31| 0.16 0.09 0.06| 1 1 98 0| 0 0| 0.0 1.0 0.0 4.0 0.0 1.0 1.0 0.1|
18 15:08:32| 0.16 0.09 0.06| 0 2 98 0| 0 0| 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0|
19 15:08:33| 0.16 0.09 0.06| 1 2 97 0| 0 0| 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0|
20 15:08:34| 0.16 0.09 0.06| 1 0 99 0| 0 0| 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0|
21 15:08:35| 0.16 0.09 0.06| 0 2 98 0| 0 0| 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0|
22 15:08:36| 0.15 0.08 0.06| 1 2 97 0| 0 0| 0.0 13.9 0.0 75.2 0.0 0.4 0.2 0.3|
23 15:08:37| 0.15 0.08 0.06| 0 1 99 0| 0 0| 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0|
24 15:08:38| 0.15 0.08 0.06| 1 1 98 0| 0 0| 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0|

-sys：打印系统信息，包括-t(打印当前时间)、-l(打印负载信息，分1分钟、5分钟、15分钟)、-c(打印cpu信息)、-s(打印交换分区信息)
-d：打印磁盘信息，需要指点磁盘设备名
-n：打印网络信息，接收和发送大小，需要指点网卡设备名

查看DISK（-d）和NET（-n）需要带具体的设备名（具体可以查看/proc/diskstats和/proc/net/dev中的设备或者可以取自iostat
和sar -n DEV）

Linux指标的数据都来自/proc目录下的相关系统元数据：
LOAD : /proc/loadavg
CPU : /proc/stat
SWAP : /proc/vmstat
DISK : /proc/diskstats
NET : /proc/net/dev

#查看MySQL响应时间(rt)

#通过调用tcprstat来监控MySQL的响应时间。

[root@hostM1 orzdba]# pwd
/usr/local/orzdba
[root@hostM1 orzdba]# ./orzdba -rt -C 10 -i 1 -t -d sda
.=================================================.
| Welcome to use the orzdba tool ! |
| Yep...Chinese English~ |
'=============== Date : 2020-08-19 ==============='
HOST: hostM1 IP: 192.168.44.128
mysql: [Warning] Using a password on the command line interface can be insecure.
DB : mycat|performance_schema|sys|testdb|zst
Var : mysql: [Warning] Using a password on the command line interface can be insecure.
binlog_format[ROW] max_binlog_cache_size[17179869184G] max_binlog_size[1G]
max_connect_errors[100] max_connections[1000] max_user_connections[0]
open_files_limit[164850] sync_binlog[1] table_definition_cache[4096]
table_open_cache[81920] thread_cache_size[64]
mysql: [Warning] Using a password on the command line interface can be insecure.
innodb_adaptive_flushing[ON] innodb_adaptive_hash_index[ON] innodb_buffer_pool_size[100M]
innodb_file_per_table[ON] innodb_flush_log_at_trx_commit[1] innodb_flush_method[O_DIRECT]
innodb_io_capacity[200] innodb_lock_wait_timeout[20] innodb_log_buffer_size[128M]
innodb_log_file_size[100M] innodb_log_files_in_group[3] innodb_max_dirty_pages_pct[75.000000]
innodb_open_files[65535] innodb_read_io_threads[4] innodb_thread_concurrency[0]
innodb_write_io_threads[4]
-------- -------------------------io-usage----------------------- --------tcprstat(us)--------
time | r/s w/s rkB/s wkB/s queue await svctm %util| count avg 95-avg 99-avg|
15:10:13| 0.2 0.9 9.3 55.0 0.0 7.3 1.4 0.2|mysql: [Warning] Using a password on the command line interface can be insecure.

#监控InnoDB的性能指标

[root@hostM1 orzdba]# pwd
/usr/local/orzdba
[root@hostM1 orzdba]# ./orzdba -innodb -C 10 -i 1
.=================================================.
| Welcome to use the orzdba tool ! |
| Yep...Chinese English~ |
'=============== Date : 2020-08-19 ==============='
HOST: hostM1 IP: 192.168.44.128
mysql: [Warning] Using a password on the command line interface can be insecure.
DB : mycat|performance_schema|sys|testdb|zst
Var : mysql: [Warning] Using a password on the command line interface can be insecure.
binlog_format[ROW] max_binlog_cache_size[17179869184G] max_binlog_size[1G]
max_connect_errors[100] max_connections[1000] max_user_connections[0]
open_files_limit[164850] sync_binlog[1] table_definition_cache[4096]
table_open_cache[81920] thread_cache_size[64]
mysql: [Warning] Using a password on the command line interface can be insecure.
innodb_adaptive_flushing[ON] innodb_adaptive_hash_index[ON] innodb_buffer_pool_size[100M]
innodb_file_per_table[ON] innodb_flush_log_at_trx_commit[1] innodb_flush_method[O_DIRECT]
innodb_io_capacity[200] innodb_lock_wait_timeout[20] innodb_log_buffer_size[128M]
innodb_log_file_size[100M] innodb_log_files_in_group[3] innodb_max_dirty_pages_pct[75.000000]
innodb_open_files[65535] 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|
15:12:02|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 0 0| 0 0 0 0| 0 0| 0 0 0 0 0 0|
15:12:03|mysql: [Warning] Using a password on the command line interface can be insecure.
354 6046 0 0| 0 0 0 0| 0 0|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 9 0 0 0|
15:12:04|mysql: [Warning] Using a password on the command line interface can be insecure.
354 6046 0 0| 0 0 0 0| 0 0|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 9 0 0 0|
15:12:05|mysql: [Warning] Using a password on the command line interface can be insecure.
354 6046 0 0| 0 0 0 0| 0 0|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 9 0 0 0|
15:12:06|mysql: [Warning] Using a password on the command line interface can be insecure.
354 6046 0 0| 0 0 0 0| 0 0|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 9 0 0 0|
15:12:07|mysql: [Warning] Using a password on the command line interface can be insecure.
354 6046 0 0| 0 0 0 0| 0 0|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 9 0 0 0|
15:12:08|mysql: [Warning] Using a password on the command line interface can be insecure.
354 6046 0 0| 0 0 0 0| 0 0|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 9 0 0 0|
15:12:09|mysql: [Warning] Using a password on the command line interface can be insecure.
354 6046 0 0| 0 0 0 0| 0 0|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 9 0 0 0|
15:12:10|mysql: [Warning] Using a password on the command line interface can be insecure.
354 6046 0 0| 0 0 0 0| 0 0|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 9 0 0 0|
15:12:12|mysql: [Warning] Using a password on the command line interface can be insecure.
354 6046 0 0| 0 0 0 0| 0 0|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 9 0 0 0|
15:12:13|mysql: [Warning] Using a password on the command line interface can be insecure.
354 6046 0 0| 0 0 0 0| 0 0|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 9 0 0 0|

#监控MySQL Server性能

[root@hostM1 orzdba]# pwd
/usr/local/orzdba
[root@hostM1 orzdba]# ./orzdba -mysql -C 10 -i 1
.=================================================.
| Welcome to use the orzdba tool ! |
| Yep...Chinese English~ |
'=============== Date : 2020-08-19 ==============='
HOST: hostM1 IP: 192.168.44.128
mysql: [Warning] Using a password on the command line interface can be insecure.
DB : mycat|performance_schema|sys|testdb|zst
Var : mysql: [Warning] Using a password on the command line interface can be insecure.
binlog_format[ROW] max_binlog_cache_size[17179869184G] max_binlog_size[1G]
max_connect_errors[100] max_connections[1000] max_user_connections[0]
open_files_limit[164850] sync_binlog[1] table_definition_cache[4096]
table_open_cache[81920] thread_cache_size[64]
mysql: [Warning] Using a password on the command line interface can be insecure.
innodb_adaptive_flushing[ON] innodb_adaptive_hash_index[ON] innodb_buffer_pool_size[100M]
innodb_file_per_table[ON] innodb_flush_log_at_trx_commit[1] innodb_flush_method[O_DIRECT]
innodb_io_capacity[200] innodb_lock_wait_timeout[20] innodb_log_buffer_size[128M]
innodb_log_file_size[100M] innodb_log_files_in_group[3] innodb_max_dirty_pages_pct[75.000000]
innodb_open_files[65535] 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|
15:13:28|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 0 0 0| 0 100.00| 0 0 0 0| 0 0|
15:13:29|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 0 2 0| 0 100.00| 2 12 0 0| 868 1k|
15:13:30|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 0 1 0| 0 100.00| 2 12 0 0| 850 1k|
15:13:31|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 0 1 0| 0 100.00| 2 12 0 0| 850 1k|
15:13:32|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 0 1 0| 0 100.00| 2 12 0 0| 850 1k|
15:13:33|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 0 1 0| 0 100.00| 2 12 0 0| 850 1k|
15:13:34|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 0 1 0| 0 100.00| 2 12 0 0| 850 1k|
15:13:35|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 0 1 0| 0 100.00| 2 12 0 0| 850 1k|
15:13:36|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 0 1 0| 0 100.00| 2 12 0 0| 850 1k|
15:13:37|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 0 1 0| 0 100.00| 2 12 0 0| 850 1k|
15:13:38|mysql: [Warning] Using a password on the command line interface can be insecure.
0 0 0 1 0| 0 100.00| 2 12 0 0| 850 1k|

转载链接：https://www.it610.com/article/1290531668926472192.htm