Before setting up an agent to monitor a MySQL server you need to ensure that there is a user account for the agent on that server.
The privileges required for this user account vary depending on the information you wish to gather using the MySQL Enterprise Monitor Agent. The following privileges allow the Monitor Agent to perform its assigned duties without limitation:
SHOW DATABASES
: Allows the MySQL Enterprise Monitor Agent
to gather inventory about the monitored MySQL server.
REPLICATION CLIENT
: Allows the
MySQL Enterprise Monitor Agent to gather Replication master/slave status data.
This privilege is only needed if the MySQL Replication Advisor
Rules are employed.
SELECT
: Allows the MySQL Enterprise Monitor Agent to
collect statistics for table objects.
SUPER
: Allows the MySQL Enterprise Monitor Agent to execute
SHOW ENGINE INNODB STATUS
in order to
collect data about InnoDB tables.
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
: Required to create the UUID
required by the agent.
CREATE
: Allows the MySQL Enterprise Monitor Agent to create
tables. During discovery, the agent creates the table
inventory
within the
mysql
database that is used to the UUID for
the server. Without this table, the agent cannot determine the
UUID of the server and therefore use this when sending
information to MySQL Enterprise Service Manager.
For example, the following GRANT
statement will
give the agent the required SELECT
,
REPLICATION CLIENT
, SHOW
DATABASES
and SUPER
rights:
GRANT SELECT, REPLICATION CLIENT, SHOW DATABASES, SUPER, PROCESS ON *.* TO 'mysqluser
'@'localhost
' IDENTIFIED BY 'agent_password
';
For security reasons, you may wish to 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 'mysqluser
'@'localhost
' IDENTIFIED BY 'agent_password
';
To enable replication discovery to work, you should also grant the
SELECT
privilege on the
mysql.inventory
table for each user with
replication privileges on the corresponding replication master.
This is required to let the MySQL Enterprise Monitor Agent read the replication
master UUID. For example:
GRANT SELECT ON mysql.inventory TO 'replicationuser
'@'%
' IDENTIFIED BY 'replication_password
';
You should perform this step after after
having run the agent on the corresponding MySQL server to ensure
that the mysql.inventory
table has been
correctly created. You can do this by running the agent,
shutting the agent down, running the above
GRANT
statement, and then restarting the
agent.
If the agent is unable to access the information from the table then a warning containing this information will be written to the agent log.
You may want to disable logging for the grant statement to
prevent the grant information being replicated to the slaves. If
this is the case, execute the statement SET
SQL_LOG_BIN=0
before you execute the above
GRANT
statement.
In a typical configuration, the agent runs on the same machine as
the MySQL server it is monitoring so the host name will be
localhost
. However, this will change if
the agent is running on a machine other than the one that hosts
the monitored MySQL server. In this case, change
localhost
to the appropriate value. For
more information about remote monitoring see
Section 2.3.6.4, “Configuring an Agent to Monitor a Remote MySQL Server”.