MySQL轻量级监测工具—doDBA
doDBA的下载与部署
一、什么是doDBA
doDBA工具是基于控制台的远程系统监视器。在远程系统上不需要特殊的软件。它从Linux和MySQL收集实时性能数据。并且可以生成一个执行文件来帮助您分析MySQL数据库。该程序是免费软件。doDBA是用go编写的。
二、下载与部署
1.软件下载
#下载软件
[root@mysql8 ~]# wget https://raw.githubusercontent.com/dblucyne/dodba_tools/master/doDBA --no-check-certificate
#下载配置文件
[root@mysql8 ~]# wget https://raw.githubusercontent.com/dblucyne/dodba_tools/master/doDBA.conf --no-check-certificate下载解压完成即可直接使用，不依赖任何环境。
使用帮助
[root@mysql8 ~]# ./doDBA -help
doDBA tools 1.0 - Copyright (c) 2016, dblucyne WeChat:doDBA
The doDBA tools is a console-based remote system monitor.
that does not require special software on the remote system.
it collects real-time performance data from linux and MySQL.
And can generate a doing file to help you analyze the MySQL database.
This program is free software.doDBA is written in go.
Usage: doDBA [OPTIONS]
-help
Display this help. 显示此帮助
-c string
configuration file. (default "doDBA.conf") 使用什么配置文件（默认就是上面下载的那个）
-h string
Connect to host/IP. 连接目标主机
-sys
Print system info. 打印系统信息
-myall
Print system and mysql info. 打印系统和MySQL信息
-mysql
Print mysql info.只打印MySQL信息
-innodb
Print innodb info.打印innodb信息
-mytop
Print mysql prcesslist info , like top.打印MySQL processlist，类似top
-i duration
refresh interval in seconds. (default 1s)刷新间隔
-t int
mysql doing on Threads_running. (default 50)当MySQL Threads_running到达阈值时会输出 show processlist和showengine innodb status到dodba.log中 (默认50)
-hP string
Connect host port. (default "22")指定主机端口
-hp string
Connect host password.主机密码
-hu string
Connect host user. (default current user)连接用户
-mP string
Connect mysql port. (default "3306")MySQL端口
-mp string
Connect mysql password.MySQL密码
-mu string
Connect mysql user.MySQL用户
-rds
Ignore system info.忽略linux信息
-log
Print to file by day.按照日期输出到日志文件
-nocolor
Print to nocolor.没有颜色输出2.配置文件解析
配置文件信息：
[root@mysql8 ~]# vim doDBA.conf
{
"Host":"192.168.0.35",
"Huser": "root",
"Hport": "22",
"Hpwd": "123456",
"Muser": "dodba",
"Mpwd": "dodba",
"Mport":"3306"
}3.启动监控
[root@mysql8 ~]# ./doDBA -h 192.168.0.35 -myall -rds建议将doDBA放在一台主机上去监控其他的MySQL
[root@mysql8 ~]# ./doDBA -h 192.168.0.35 -myall -rds
DoDBA tools on host 192.168.0.35
---------+----load--avg----+-----cpu-usage-----+--swap--+----net----+-------mysql-status-------+-slow---th---+---bytes---
time | 1m 5m 10m| usr sys iow ide| si so| recv send| QPS TPS ins upd del| sql run con| recv send
---------+-----------------+-------------------+--------+-----------+--------------------------+-------------+-----------
19:48:33 | 0.00 0.00 0.00| 0.0 0.0 0.0 0.0| 0 0| 0K 0K| 0 0 0 0 0| 0 2 1| 0K 8K
19:48:34 | 0.00 0.00 0.00| 0.0 0.0 0.0 0.0| 0 0| 0K 0K| 0 0 0 0 0| 0 2 1| 0K 8K
19:48:35 | 0.00 0.00 0.00| 0.0 0.0 0.0 0.0| 0 0| 0K 0K| 0 0 0 0 0| 0 2 1| 0K 8K
...三、doDBA可选数据源
一个 doDBA 进程同时只能配置一个数据源参数，配置2个及以上时，只能生效一个，参数有优先级的区别:mysql > innodb > myall > sys
1.mysql
[root@mysql8 ~]# ./doDBA -c doDBA.conf -mysql -log #启动监控
[root@mysql8 ~]# ls #生成的日志
192168035_dodba_mysql_20200729.log
[root@mysql8 ~]# cat 192168035_dodba_mysql_20200729.log
DoDBA tools on host 192.168.0.35
---------+-------mysql-status-------+-----threads-----+-----slow-----+---bytes---+---------locks----------
time | QPS TPS ins upd del| run con cre cac| sql tmp Dtmp| recv send| lockI lockW openT openF
---------+--------------------------+-----------------+--------------+-----------+------------------------
10:20:36 | 0 0 0 0 0| 2 1 0 2| 0 1 0| 0K 8K| 1 0 102 3
10:20:37 | 0 0 0 0 0| 2 1 0 2| 0 1 0| 0K 8K| 1 0 102 3
10:20:38 | 0 0 0 0 0| 2 1 0 2| 0 1 0| 0K 8K| 1 0 102 3
10:20:39 | 0 0 0 0 0| 2 1 0 2| 0 1 0| 0K 8K| 1 0 102 3
10:20:40 | 0 0 0 0 0| 2 1 0 2| 0 1 0| 0K 8K| 1 0 102 3
10:20:41 | 0 0 0 0 0| 2 1 0 2| 0 1 0| 0K 8K| 1 0 102 3
....结果解析：
- mysql-status
- qps —— Com_select
- tps —— Com_insert + Com_update + Com_delete
- ins —— Com_insert
- upd —— Com_update
- del —— Com_delete
- threads
- run —— Threads_running
- con —— Threads_connected
- cre —— Threads_created
- cac —— Threads_cached
- slow
- sql —— Slow_queries
- tmp —— Created_tmp_tables
- dtmp —— Created_tmp_disk_tables
- bytes
- recv —— Bytes_received
- send —— Bytes_sent
- locks
- lockI —— Table_locks_immediate
- lockW —— Table_locks_waited
- openT —— Open_tables
- openF —— Open_files
与上述信息对应的查询语句。
show global status where Variable_name in ( "Com_select", "Com_insert", "Com_update", "Com_delete", "Innodb_buffer_pool_read_requests", "Innodb_buffer_pool_reads", "Innodb_rows_inserted", "Innodb_rows_updated", "Innodb_rows_deleted", "Innodb_rows_read", "Threads_running", "Threads_connected", "Threads_cached", "Threads_created", "Bytes_received", "Bytes_sent", "Innodb_buffer_pool_pages_data", "Innodb_buffer_pool_pages_free", "Innodb_buffer_pool_pages_dirty", "Innodb_buffer_pool_pages_flushed", "Innodb_data_reads", "Innodb_data_writes", "Innodb_data_read", "Innodb_data_written", "Innodb_os_log_fsyncs", "Innodb_os_log_written", "Slow_queries", "Created_tmp_disk_tables", "Created_tmp_tables", "Open_tables", "Open_files", "Table_locks_immediate", "Table_locks_waited" );
--
mysql> show global status
-> where
-> Variable_name in (
-> "Com_select",
-> "Com_insert",
-> "Com_update",
-> "Com_delete",
-> "Innodb_buffer_pool_read_requests",
-> "Innodb_buffer_pool_reads",
-> "Innodb_rows_inserted",
-> "Innodb_rows_updated",
-> "Innodb_rows_deleted",
-> "Innodb_rows_read",
-> "Threads_running",
-> "Threads_connected",
-> "Threads_cached",
-> "Threads_created",
-> "Bytes_received",
-> "Bytes_sent",
-> "Innodb_buffer_pool_pages_data",
-> "Innodb_buffer_pool_pages_free",
-> "Innodb_buffer_pool_pages_dirty",
-> "Innodb_buffer_pool_pages_flushed",
-> "Innodb_data_reads",
-> "Innodb_data_writes",
-> "Innodb_data_read",
-> "Innodb_data_written",
-> "Innodb_os_log_fsyncs",
-> "Innodb_os_log_written",
-> "Slow_queries",
-> "Created_tmp_disk_tables",
-> "Created_tmp_tables",
-> "Open_tables",
-> "Open_files",
-> "Table_locks_immediate",
-> "Table_locks_waited"
-> );
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Bytes_received | 887352 |
| Bytes_sent | 9682273 |
| Com_delete | 0 |
| Com_insert | 99999 |
| Com_select | 14 |
| Com_update | 0 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 1105 |
| Innodb_buffer_pool_pages_data | 1859 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 1407 |
| Innodb_buffer_pool_pages_free | 194741 |
| Innodb_buffer_pool_read_requests | 927502 |
| Innodb_buffer_pool_reads | 1062 |
| Innodb_data_read | 17470464 |
| Innodb_data_reads | 1089 |
| Innodb_data_writes | 217436 |
| Innodb_data_written | 156094464 |
| Innodb_os_log_fsyncs | 161731 |
| Innodb_os_log_written | 111969792 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 100017 |
| Innodb_rows_read | 5015 |
| Innodb_rows_updated | 314 |
| Open_files | 3 |
| Open_tables | 103 |
| Slow_queries | 0 |
| Table_locks_immediate | 1101 |
| Table_locks_waited | 0 |
| Threads_cached | 2 |
| Threads_connected | 1 |
| Threads_created | 3 |
| Threads_running | 2 |
+----------------------------------+-----------+
33 rows in set (0.00 sec)2.innodb
[root@mysql8 ~]# ./doDBA -c doDBA.conf -innodb -log
[root@mysql8 ~]# ls
192168035_dodba_innodb_20200729.log
[root@mysql8 ~]# cat 192168035_dodba_innodb_20200729.log
DoDBA tools on host 192.168.0.35
---------+------innodb--rows-----+---------innodb--pages--------+-------innodb--data-------+--innodb-log--
time | read ins upd del| data free dirty flush|reads writes read written|fsyncs written
---------+-----------------------+------------------------------+--------------------------+--------------
10:28:38 | 0 0 0 0| 1859 194741 0 0| 0 0 0K 0K| 0 0K
10:28:39 | 0 0 0 0| 1859 194741 0 0| 0 0 0K 0K| 0 0K
10:28:40 | 0 0 0 0| 1859 194741 0 0| 0 0 0K 0K| 0 0K
10:28:41 | 0 0 0 0| 1859 194741 0 0| 0 0 0K 0K| 0 0K
10:28:42 | 0 0 0 0| 1859 194741 0 0| 0 0 0K 0K| 0 0K
10:28:43 | 0 0 0 0| 1859 194741 0 0| 0 0 0K 0K| 0 0K
....结果解析：
- innodb--rows
- read —— Innodb_rows_read
- ins —— Innodb_rows_inserted
- upd —— Innodb_rows_updated
- del —— Innodb_rows_deleted
- innodb--pages
- data —— Innodb_buffer_pool_pages_data
- free —— Innodb_buffer_pool_pages_free
- dirty —— Innodb_buffer_pool_pages_dirty
- flush —— Innodb_buffer_pool_pages_flushed
- innodb--data
- reads —— Innodb_data_reads
- writes —— Innodb_data_writes
- read —— Innodb_data_read
- written —— Innodb_data_written
- innodb-log
- fsyncs —— Innodb_os_log_fsyncs
- written —— Innodb_os_log_written
与上述信息对应的查询语句。
show global status where Variable_name in ( "Com_select", "Com_insert", "Com_update", "Com_delete", "Innodb_buffer_pool_read_requests", "Innodb_buffer_pool_reads", "Innodb_rows_inserted", "Innodb_rows_updated", "Innodb_rows_deleted", "Innodb_rows_read", "Threads_running", "Threads_connected", "Threads_cached", "Threads_created", "Bytes_received", "Bytes_sent", "Innodb_buffer_pool_pages_data", "Innodb_buffer_pool_pages_free", "Innodb_buffer_pool_pages_dirty", "Innodb_buffer_pool_pages_flushed", "Innodb_data_reads", "Innodb_data_writes", "Innodb_data_read", "Innodb_data_written", "Innodb_os_log_fsyncs", "Innodb_os_log_written", "Slow_queries", "Created_tmp_disk_tables", "Created_tmp_tables", "Open_tables", "Open_files", "Table_locks_immediate", "Table_locks_waited" );
--
mysql> show global status
-> where
-> Variable_name in (
-> "Com_select",
-> "Com_insert",
-> "Com_update",
-> "Com_delete",
-> "Innodb_buffer_pool_read_requests",
-> "Innodb_buffer_pool_reads",
-> "Innodb_rows_inserted",
-> "Innodb_rows_updated",
-> "Innodb_rows_deleted",
-> "Innodb_rows_read",
-> "Threads_running",
-> "Threads_connected",
-> "Threads_cached",
-> "Threads_created",
-> "Bytes_received",
-> "Bytes_sent",
-> "Innodb_buffer_pool_pages_data",
-> "Innodb_buffer_pool_pages_free",
-> "Innodb_buffer_pool_pages_dirty",
-> "Innodb_buffer_pool_pages_flushed",
-> "Innodb_data_reads",
-> "Innodb_data_writes",
-> "Innodb_data_read",
-> "Innodb_data_written",
-> "Innodb_os_log_fsyncs",
-> "Innodb_os_log_written",
-> "Slow_queries",
-> "Created_tmp_disk_tables",
-> "Created_tmp_tables",
-> "Open_tables",
-> "Open_files",
-> "Table_locks_immediate",
-> "Table_locks_waited"
-> );
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Bytes_received | 905503 |
| Bytes_sent | 9864544 |
| Com_delete | 0 |
| Com_insert | 99999 |
| Com_select | 16 |
| Com_update | 0 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 1127 |
| Innodb_buffer_pool_pages_data | 1859 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 1407 |
| Innodb_buffer_pool_pages_free | 194741 |
| Innodb_buffer_pool_read_requests | 927502 |
| Innodb_buffer_pool_reads | 1062 |
| Innodb_data_read | 17470464 |
| Innodb_data_reads | 1089 |
| Innodb_data_writes | 217436 |
| Innodb_data_written | 156094464 |
| Innodb_os_log_fsyncs | 161731 |
| Innodb_os_log_written | 111969792 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 100017 |
| Innodb_rows_read | 5015 |
| Innodb_rows_updated | 314 |
| Open_files | 3 |
| Open_tables | 105 |
| Slow_queries | 0 |
| Table_locks_immediate | 1123 |
| Table_locks_waited | 0 |
| Threads_cached | 2 |
| Threads_connected | 1 |
| Threads_created | 3 |
| Threads_running | 2 |
+----------------------------------+-----------+
33 rows in set (0.01 sec)3.myall
[root@mysql8 ~]# ./doDBA -c doDBA.conf -myall -rds -log
[root@mysql8 ~]# ls
192168035_dodba_myall_20200729.log
[root@mysql8 ~]# cat 192168035_dodba_myall_20200729.log
DoDBA tools on host 192.168.0.35
---------+----load--avg----+-----cpu-usage-----+--swap--+----net----+-------mysql-status-------+-slow---th---+---bytes---
time | 1m 5m 10m| usr sys iow ide| si so| recv send| QPS TPS ins upd del| sql run con| recv send
---------+-----------------+-------------------+--------+-----------+--------------------------+-------------+-----------
10:36:05 | 0.00 0.00 0.00| 0.0 0.0 0.0 0.0| 0 0| 0K 0K| 0 0 0 0 0| 0 2 1| 0K 8K
10:36:06 | 0.00 0.00 0.00| 0.0 0.0 0.0 0.0| 0 0| 0K 0K| 0 0 0 0 0| 0 2 1| 0K 8K
10:36:07 | 0.00 0.00 0.00| 0.0 0.0 0.0 0.0| 0 0| 0K 0K| 0 0 0 0 0| 0 2 1| 0K 8K
10:36:08 | 0.00 0.00 0.00| 0.0 0.0 0.0 0.0| 0 0| 0K 0K| 0 0 0 0 0| 0 2 1| 0K 8K
10:36:09 | 0.00 0.00 0.00| 0.0 0.0 0.0 0.0| 0 0| 0K 0K| 0 0 0 0 0| 0 2 1| 0K 8K
...与上述信息对应的查询语句
show global status where Variable_name in ( "Com_select", "Com_insert", "Com_update", "Com_delete", "Innodb_buffer_pool_read_requests", "Innodb_buffer_pool_reads", "Innodb_rows_inserted", "Innodb_rows_updated", "Innodb_rows_deleted", "Innodb_rows_read", "Threads_running", "Threads_connected", "Threads_cached", "Threads_created", "Bytes_received", "Bytes_sent", "Innodb_buffer_pool_pages_data", "Innodb_buffer_pool_pages_free", "Innodb_buffer_pool_pages_dirty", "Innodb_buffer_pool_pages_flushed", "Innodb_data_reads", "Innodb_data_writes", "Innodb_data_read", "Innodb_data_written", "Innodb_os_log_fsyncs", "Innodb_os_log_written", "Slow_queries", "Created_tmp_disk_tables", "Created_tmp_tables", "Open_tables", "Open_files", "Table_locks_immediate", "Table_locks_waited" );
--
mysql> show global status
-> where
-> Variable_name in (
-> "Com_select",
-> "Com_insert",
-> "Com_update",
-> "Com_delete",
-> "Innodb_buffer_pool_read_requests",
-> "Innodb_buffer_pool_reads",
-> "Innodb_rows_inserted",
-> "Innodb_rows_updated",
-> "Innodb_rows_deleted",
-> "Innodb_rows_read",
-> "Threads_running",
-> "Threads_connected",
-> "Threads_cached",
-> "Threads_created",
-> "Bytes_received",
-> "Bytes_sent",
-> "Innodb_buffer_pool_pages_data",
-> "Innodb_buffer_pool_pages_free",
-> "Innodb_buffer_pool_pages_dirty",
-> "Innodb_buffer_pool_pages_flushed",
-> "Innodb_data_reads",
-> "Innodb_data_writes",
-> "Innodb_data_read",
-> "Innodb_data_written",
-> "Innodb_os_log_fsyncs",
-> "Innodb_os_log_written",
-> "Slow_queries",
-> "Created_tmp_disk_tables",
-> "Created_tmp_tables",
-> "Open_tables",
-> "Open_files",
-> "Table_locks_immediate",
-> "Table_locks_waited"
-> );
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| Bytes_received | 932721 |
| Bytes_sent | 10141874 |
| Com_delete | 0 |
| Com_insert | 99999 |
| Com_select | 20 |
| Com_update | 0 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 1160 |
| Innodb_buffer_pool_pages_data | 1859 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 1407 |
| Innodb_buffer_pool_pages_free | 194741 |
| Innodb_buffer_pool_read_requests | 927502 |
| Innodb_buffer_pool_reads | 1062 |
| Innodb_data_read | 17470464 |
| Innodb_data_reads | 1089 |
| Innodb_data_writes | 217436 |
| Innodb_data_written | 156094464 |
| Innodb_os_log_fsyncs | 161731 |
| Innodb_os_log_written | 111969792 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 100017 |
| Innodb_rows_read | 5015 |
| Innodb_rows_updated | 314 |
| Open_files | 3 |
| Open_tables | 109 |
| Slow_queries | 0 |
| Table_locks_immediate | 1156 |
| Table_locks_waited | 0 |
| Threads_cached | 2 |
| Threads_connected | 1 |
| Threads_created | 3 |
| Threads_running | 2 |
+----------------------------------+-----------+
33 rows in set (0.01 sec)4.mytop
类似于top动态刷新，所以无法写入日志
[root@mysql8 ~]# ./doDBA -c doDBA.conf -mytop -rds
DoDBA tools -10:41:23 on host 192.168.0.35 UP Load: 0.00 0.00 0.00
CPU: 0.00% user, 0.00% sys, 0.00% nice, 0.00% iowait, 0.00% idle
Mem: 0K total, 0K used, 0K free, 0K buffers
Swap: 0K total, 0K used, 0K free, 0K Cached
MySQL: 0 QPS, 0 TPS, 0 slowSQL, 2 run
ID USER IP/HOST DB Time State SQL
-- ---- ------- -- ---- ----- ---
4 event_scheduler localhost 56010 Waiting on emp
27 dodba 192.168.0.35 0 starting show full processlist
