MySQL Enterprise Monitor 4.0.13 Manual
MySQL Enterprise Monitor Agent requires a user configured within each MySQL instance that is being monitored with suitable privileges to collect information about the server, including variable names, replication, and storage engine status information.
The Agent requires the Admin
user, and can
optionally use General
or
Limited
users, or both, depending on the
system's security requirements. During the installation process,
you are prompted to create General and Limited users. You can
allow the agent to connect to the database using the Admin user
for all tasks but it is recommended to create the General or
Limited users for tasks which do not require root access to the
database. It is not necessary to create both users. It is possible
to create one or the other. The Agent uses the user with the
lowest, required privileges for the query and changes to a user
with higher privileges only if the query requires it.
Admin
: a user that has the
SUPER
privilege. The
SUPER
privilege is required to run certain
statements such as SHOW MASTER LOGS
or
SHOW ENGINE INNODB STATUS
, depending on the
version that is being monitored.
If monitoring MySQL Server 5.5, you must also grant
CREATE
and
INSERT
privileges on the
schema the inventory table is created on. The inventory
table stores unique identifiers for the MySQL instance, and
is created in the mysql
schema by
default.
The inventory table is not used in MySQL Server 5.6, or higher.
If you intend to automatically create the less-privileged
users, General and Limited, you must also grant the Admin user
CREATE USER
. You must also
grant SHOW VIEW
,
PROCESS
,
REPLICATION CLIENT
,
SELECT
and
SHOW DATABASES
privileges
globally, and UPDATE
on the
performance_schema.threads
table, with
GRANT OPTION
for all.
If you intend to install the sys schema from within MySQL Enterprise Monitor,
in addition to the privileges listed above, you must also
grant the Admin user CREATE
,
INSERT
,
CREATE ROUTINE
,
CREATE TEMPORARY TABLES
,
CREATE VIEW
, and
TRIGGER
.
General
: This optional user handles general
monitoring tasks that do not require
SUPER
level privileges. Lower
privileged users are used until higher privileges are
required. In which case, MEM temporarily logs in as the
SUPER
privileged user, and then falls back
to the general user.
If you are manually managing this user, grant at least the
EXECUTE
,
PROCESS
,
REPLICATION CLIENT
,
SELECT
, and
SHOW DATABASES
privileges
globally, and UPDATE
on the
performance_schema.threads
table. If you
intend to use EXPLAIN
on views, you must
also grant SHOW VIEW
.
If you are monitoring MySQL 5.1.63, or earlier, you must
grant the SUPER
privilege to
the General user. The agent requires this privilege to use
the SHOW BINARY LOGS
statement on the
monitored instance.
Limited
: This optional user is used for
statements that should be limited to a single connection.
Examples of these types of statements include getting database
metadata from INFORMATION_SCHEMA
tables, or
any custom SQL that is used to monitor application specific
statistics.
If you are manually managing this user, it should have at
least the EXECUTE
,
SELECT
and
SHOW DATABASES
privileges
globally, and UPDATE
on the
performance_schema.threads
table. If you
intend to use EXPLAIN
on views, you must
also grant SHOW VIEW
.
If you do not want to supply the root user information to the installer, create a user manually within your MySQL server and provide these credentials as the agent user/password combination during installation. The privileges required for this user account vary depending on the information you gather using the MySQL Enterprise Monitor Agent.
SHOW DATABASES
:
MySQL Enterprise Monitor Agent can gather inventory about the monitored
MySQL server.
REPLICATION CLIENT
:
MySQL Enterprise Monitor Agent can gather Replication master/slave status
data. This privilege is only needed if you use the MySQL
Replication Advisor Rules.
SELECT
: MySQL Enterprise Monitor Agent can
collect statistics for table objects.
SUPER
: MySQL Enterprise Monitor Agent can
execute SHOW ENGINE INNODB STATUS
to
collect data about InnoDB tables. This privilege is also
required to obtain replication information using
SHOW MASTER STATUS
and, if monitoring
MySQL 5.5 or earlier, to temporarily switch off replication
when populating the mysql.inventory
table
used to identify the MySQL instance. The
mysql.inventory
table is not created on
MySQL 5.6, or higher.
SELECT
is required to read
data on all objects from
INFORMATION_SCHEMA
. If it is not
granted, it is not possible for MySQL Enterprise Monitor to perform object
monitoring or statistics.
EXECUTE
: required to execute
helper functions.
PROCESS
: When monitoring a
MySQL server running MySQL 5.1.24 or above with
InnoDB
, the
PROCESS
privilege is required
to execute SHOW ENGINE INNODB STATUS
.
INSERT
: If you are monitoring
a MySQL 5.6, or higher, this privilege is not required.
Required to create the UUID used by the agent.
CREATE
: If you are monitoring
a MySQL 5.6, or higher, this privilege is not required. The
MySQL Enterprise Monitor Agent can create tables. During discovery, the
agent creates the table inventory
within
the mysql
database that stores the UUID
for the server. Without this table, the agent cannot
determine the UUID of the server, which it sends along with
other information to MySQL Enterprise Service Manager.
UPDATE
on the
performance_schema.threads
table. This is
done to prevent SQL Statement Generates Warnings
or Errors events which can be triggered by
EXPLAIN
plans run by the Query Analyzer.
These warnings are generated because the
Performance_Schema
captures only 1024
characters of each query. Granting this privilege enables
the connection to Performance_Schema
to
be dropped before the EXPLAIN
and
reconnected after the EXPLAIN
finishes.
If you manage your General and Limited users manually, you must also grant this privilege to those users.
For example, the following GRANT
statement gives the agent the required
SELECT
,
REPLICATION CLIENT
,
SHOW DATABASES
and
SUPER
rights:
GRANT SELECT, CREATE USER, REPLICATION CLIENT, SHOW DATABASES, SUPER, PROCESS ON *.* TO 'agent_user
'@'localhost
';
When using Auto-Create Less Privileged
Users, also add WITH GRANT
OPTION
to the above statement.
For security reasons, you might limit the
CREATE
and
INSERT
privileges to the agent so
that it can only create tables within the
mysql
database:
GRANT CREATE, INSERT ON mysql.* TO 'agent_user
'@'localhost
';
If the master, or slave, runs a version of MySQL earlier than
5.6, you must grant the SELECT
privilege on the mysql.inventory
table for
each user with replication privileges on the corresponding
replication master. This enables MySQL Enterprise Monitor Agent to read the
replication master UUID. For example:
GRANT SELECT ON mysql.inventory TO 'replicationuser
'@'%
' IDENTIFIED BY 'replication_password
';
Perform this step after running the agent
on the corresponding MySQL server to ensure that the
mysql.inventory
table is created correctly.
Start the agent, shut the agent down, run the above
GRANT
statement, then start the
agent.
If the agent cannot access the information from the table, a warning containing this information is written to the agent log.
You might disable logging for the grant statement to prevent
the grant information being replicated to the slaves. In this
case, execute the statement SET
SQL_LOG_BIN=0
before executing the above
GRANT
statement.
If running MySQL 5.6, or later, it is not necessary to grant
select on mysql.inventory
because the
master_uuid
is used from the slave status.
Therefore, there is no need to start and stop the agent, nor
to set SQL_LOG_BIN
to 0.
If the Admin user has the necessary privileges to create other users, you can check the Auto-Create Less Privileged Users check box, enter credentials for those users, and they are created for you.
If the Auto-Create Less Privileged Users box is unchecked and the credentials for the General and Limited users blank, the Agent only uses the Admin user for monitoring.
If the Auto-Create Less Privileged Users box is unchecked, you can enter credentials for the General and Limited users. If you define these users. you must create them on the monitored assets manually. The installer attempts to validate these users and displays a warning message if they are invalid. The installation process continues, and the Agent works properly, but you must create those users later.
In a typical configuration, the Agent runs on the same host as
the MySQL server it is monitoring, so the host name is often
localhost
. If the Agent is running on
a machine other than the monitored MySQL server(s), then change
localhost
to the appropriate value.
For more information about remote monitoring, see
Section 5.7, “Configuring an Agent to Monitor a Remote MySQL Server”.