MySQL轻量级监测工具—doDBA

avatar 2022年8月2日18:13:17 评论 424 次浏览

一、什么是doDBA

doDBA工具是基于控制台的远程系统监视器。在远程系统上不需要特殊的软件。它从Linux和MySQL收集实时性能数据。并且可以生成一个执行文件来帮助您分析MySQL数据库。该程序是免费软件。doDBA是用go编写的。

二、下载与部署#

1.软件下载

#下载软件
[root@www.wulaoer.org ~]# wget https://raw.githubusercontent.com/dblucyne/dodba_tools/master/doDBA --no-check-certificate
#下载配置文件
[root@www.wulaoer.org ~]# wget https://raw.githubusercontent.com/dblucyne/dodba_tools/master/doDBA.conf --no-check-certificate

下载解压完成即可直接使用，不依赖任何环境。

使用帮助

[root@www.wulaoer.org ~]# ./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@www.wulaoer.org ~]# vim doDBA.conf
{
"Host":"192.168.0.35",
"Huser": "root",
"Hport": "22",
"Hpwd":  "123456",
"Muser": "dodba",
"Mpwd":  "dodba",
"Mport":"3306"
}

3.启动监控

[root@www.wulaoer.org ~]# ./doDBA -h 192.168.0.35 -myall -rds

建议将doDBA放在一台主机上去监控其他的MySQL

[root@www.wulaoer.org ~]# ./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@www.wulaoer.org ~]# ./doDBA -c doDBA.conf -mysql -log #启动监控
[root@www.wulaoer.org ~]# ls #生成的日志
192168035_dodba_mysql_20200729.log
[root@www.wulaoer.org ~]# 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@www.wulaoer.org ~]# ./doDBA -c doDBA.conf -innodb -log
[root@www.wulaoer.org ~]# ls
192168035_dodba_innodb_20200729.log
[root@www.wulaoer.org ~]# 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@www.wulaoer.org ~]# ./doDBA -c doDBA.conf -myall -rds -log
[root@www.wulaoer.org ~]# ls
192168035_dodba_myall_20200729.log
[root@www.wulaoer.org ~]# 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@www.wulaoer.org ~]# ./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
avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: