6.2 Creating MySQL User Accounts for the Monitor Agent

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.

  Note

  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.

  Important

  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.

Creating the Admin User

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.

  Note

  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.

  Note

  If you manage your General and Limited users manually, you must also grant this privilege to those users.

• DROP on the ndb_objects table, a requirement for MySQL Enterprise Monitor Agent 8.0.22 or higher.

For example, the following GRANT statement gives the agent the required rights:

GRANT SELECT, CREATE USER, REPLICATION CLIENT, SHOW DATABASES, SUPER, DROP, PROCESS
  ON *.*
  TO  'agent_user'@'localhost';

Note

When using Auto-Create Less Privileged Users, also add WITH GRANT OPTION to the above statement.

For security reasons, you might limit the CREATE, DROP, and INSERT privileges to the agent so that it can only create tables within the mysql database:

GRANT CREATE, DROP, 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'@'%';

Note

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.

Note

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.

Important

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.

Creating the Limited and General Users

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 6.7, “Configuring an Agent to Monitor a Remote MySQL Server”.