Guidance:

• Problems facing

• Solution

• MySQL Monitoring Gateway Installation Configuration Process

• Environmental description

• Installation Configuration Triple

• Experience the effect

• Killer Function--MySQL Auto-discovery Auto-monitoring

1. Problems to be faced

How can you "simply", "efficiently", "and"cheaply"collect any monitoring items for all MySQL instances in a single machine multiple instance scenario?

2. Solutions

One possible solution is the Monitoring Gateway, which collects, aggregates, and exposes all MySQL monitors on the Restful WEB-API host.Why this scheme?This issue is discussed in detail in the dbm-agent documentation.Here's how monitoring gateways change

Collect the MGR role of this instance 172.16.192.100:3306 from the command line

curl http://172.16.192.100:8080/instances/3306/member_role
    
{
"member_role":  "PRIMARY"
}

The same is true if you like to use a browser

Most importantly, to achieve this effect, MySQL-DBA s have little work and no economic costs, and the time saved can make more sense.

3. MySQL Monitoring Gateway Installation and Configuration Process

(1) Environmental description

Take a real environment as an example to illustrate how to use it

1. Key information about the environment

Key information	Explain
Host IP	172.16.192.100
operating system	centos-7.6
Python version	3.6.0
MySQL Version	8.0.18

2. Two instances 3306 & 3308 are currently running on the host

ps -ef | grep mysqld
mysql33+  10913  1  0  13:21  ?  00:00:56  /usr/local/mysql-8.0.18-linux-glibc2.12-x86_64/bin/mysqld --defaults-file=/etc/my-3306.cnf
mysql33+  10995  1  0  13:25  ?  00:00:54  /usr/local/mysql-8.0.18-linux-glibc2.12-x86_64/bin/mysqld --defaults-file=/etc/my-3308.cnf

(2) Three steps of installation and configuration

1. Create a user on the MySQL instance through which the monitoring gateway connects to MySQL and collects monitoring items

create user monitor@'127.0.0.1' identified by  'dbma@0352';
grant replication client,process on *.* to monitor@'127.0.0.1';
grant select on performance_schema.* to monitor@'127.0.0.1';

2. Install the Monitoring Gateway Program (it was released as part of the dbm-agent package, so the real one to install is the dbm-agent)

sudo su
    
pip3 install dbm-agent # install
dbm-agent init # Initialize the dbm-agent, be sure to initialize it before using the dbm-agent

3. Start monitoring network

sudo su
    
dbm-monitor-gateway --bind-ip=172.16.192.100  --bind-port=8080 \
--monitor-user=monitor --monitor-password=dbma@0352 start
    
Successful start and log file save to '/usr/local/dbm-agent/logs/dbm-monitor-gateway.log'

4. Experience Effect

1. View a list of all monitored instances on the host

curl http://172.16.192.100:8080/instances
    
[3306,  3308]  # You can see that dbm-monitor-gateway returns a list of instances on the current host (172.16.192.100)

Effects on browsers

2**, Query a specific monitoring item for an instance (for example, com_select)**

curl http://172.16.192.100:8080/instances/3306/com_select
    
{
"com_select":  "1702"
}

Effects on browsers

3. Query all monitoring items for a given instance

curl http://172.16.192.100:8080/instances/3306/
    
{
"aborted_clients":  "5",
"aborted_connects":  "155",
"acl_cache_items_count":  "0",
"binlog_cache_disk_use":  "0",
"binlog_cache_use":  "0",
"binlog_stmt_cache_disk_use":  "0",
"binlog_stmt_cache_use":  "0",
"bytes_received":  "495062",
"bytes_sent":  "62005555",
....
....
....  # Too many monitors, only the first few are listed
}

Effects on browsers

5. Killer Function--MySQL Auto-discovery Auto-monitoring

The most commendable feature of a monitoring gateway is that it automatically discovers new MySQL instances on the host, and it automatically connects to the new instances and begins collecting their monitoring items.

Next, we will install another instance on the host to listen on 3307. You do not need to restart the monitoring gateway. There is a dedicated thread inside it to check for new instances.It is the mission and pursuit of DBM (dbm-center & dbm-agent) to save DBA as much as possible

dbma-cli-single-instance --max-mem=128  --port=3307 install

3307 was discovered a minute later

In addition, dbm-monitor-gateway is a full-featured monitoring tool with more and more complete documentation.

Recommended reading: https://github.com/Neeky/dbm-agent