MySQL Enterprise Monitor

MySQL Enterprise Monitor is an Enterprise Edition feature

Architecture

With Agent
Agentless
For performance reasons, the agent-less installation is not recommended for monitoring large implementations. It is useful for testing, or very small-scale implementations. If an Agent monitors a MySQL instance remotely, it cannot monitor the host and can only collect data from the monitored MySQL instance. It is strongly recommended to install an agent on each host. 

MySQL Enterprise Manager Lifecycle

MySQL Enterprise Monitor 8.0 supports monitoring of MySQL 5.7.6+ and MySQL 8.0.11+

Service Manager Start / Stop / Status

/opt/mysql/enterprise/monitor/mysqlmonitorctl.sh (start|stop|status|restart)

/opt/mysql/enterprise/monitor/mysqlmonitorctl.sh (start|stop|status|restart) mysql

/opt/mysql/enterprise/monitor/mysqlmonitorctl.sh (start|stop|status|restart) tomcat

MySQL Enterprise Monitor Usage

MySQL Enterprise Monitor maintains a record, in the Inventory schema, of all assets detected. As a result, if the network topology changes frequently, the inventory and the MySQL Enterprise Monitor User Interface may contain many unused or obsolete assets. The Inventory page enables you to remove such assets, permanently. 

Version

The version of MySQL Enterprise Monitor is visible at the bottom right of all screens displayed via the web front-end.

Connect to MEM Database

In a default installation of MEM, mysql will not be on the PATH and the socket will be in a MEM specific location...

/opt/mysql/enterprise/monitor/mysql/bin/mysql -u myuser -S /opt/mysql/enterprise/monitor/mysql/tmp/mysql.sock -p

Useful Queries

Hostname, Start Time and Port of all database instances monitored by this instance of MySQL Enterprise Monitor...

SELECT UPPER(SUBSTRING_INDEX(e.hostname,'.',1)) AS Host,

       FROM_UNIXTIME(s.startTime/1000) AS startTime,

       n.port

  FROM mysqlserver s

  JOIN networking n

  JOIN environment e

 WHERE s.id=n.id

   AND n.id=e.id

 ORDER BY e.hostname;

Hostname and database size (assuming /mysql/data is dedicated to database files and /mysql/logs is dedicated to binary logs) of all database instances monitored by this instance of MySQL Enterprise Monitor....

SELECT UPPER(SUBSTRING_INDEX(h.hostname,'.',1)) AS Host,

       fs.usedBytesSum AS Size

  FROM host h

  LEFT JOIN (SELECT id AS fullid,

                    SUBSTRING_INDEX(fsi.id, '.', 1) AS id,

                    SUM(usedBytes) AS usedBytesSum

               FROM filesystem fsi

              WHERE (fsi.id LIKE '%mysql/data' OR fsi.id LIKE '%mysql/log')

              GROUP BY SUBSTRING_INDEX(fsi.id, '.', 1)) fs ON h.id=fs.id

 ORDER BY h.hostname;

Hostname and version of all database instances monitored by this instance of MySQL Enterprise Monitor....

SELECT UPPER(SUBSTRING_INDEX(h.hostname,'.',1)) AS Host,

       SUBSTRING_INDEX(m.version, '-', 1) AS VERSION,

       SUBSTRING(m.version, 8)

  FROM host h

  JOIN metadata m

  JOIN environment e

 WHERE e.id=m.id

   AND SUBSTRING_INDEX(e.hostname,'.',1)=SUBSTRING_INDEX(h.hostname,'.',1)

   AND FROM_UNIXTIME(m.timestamp/1000) > DATE_SUB(current_timestamp(), interval 1 DAY)

 ORDER BY h.hostname;

Combined...

SELECT UPPER(SUBSTRING_INDEX(h.hostname,'.',1)) AS Host,

       fs.usedBytesSum AS Size,

       FROM_UNIXTIME(s.startTime/1000) AS startTime,

       n.port,

       SUBSTRING_INDEX(m.version, '-', 1) AS VERSION,

       SUBSTRING(m.version, 8)

  FROM host h

  JOIN networking n

  JOIN environment e

  JOIN mysqlserver s

  JOIN metadata m

  LEFT JOIN (SELECT id AS fullid,

                    SUBSTRING_INDEX(fsi.id, '.', 1) AS id,

                    SUM(usedBytes) AS usedBytesSum

               FROM filesystem fsi

              WHERE (fsi.id LIKE '%mysql/data' OR fsi.id LIKE '%mysql/log')

              GROUP BY SUBSTRING_INDEX(fsi.id, '.', 1)) fs ON h.id=fs.id

 WHERE s.id=n.id

   AND n.id=e.id

   AND e.id=m.id

   AND SUBSTRING_INDEX(e.hostname,'.',1)=SUBSTRING_INDEX(h.hostname,'.',1)

   AND FROM_UNIXTIME(m.timestamp/1000) > DATE_SUB(current_timestamp(), interval 1 DAY)

 ORDER BY h.hostname;

Remove Monitored Instance

Configuration - MySQL Instances

Select the instance to be deleted and select the Delete Instances button near the top of the page.

Shutdown the Agent on the target server before continuing.

Bibliography