MySQL Privileges

Regardless of whether you're running a single MySQL server or a cluster of multiple servers, one thing you are always interested in is squeezing the maximum performance out of your system. Monyog developers were well aware of this, and so they provided a fairly comprehensive list of performance variables that you can monitor in real time to check the health and performance of your MySQL server.

Most of Monyog functionalities do not require any privilege at all for the MySQL user, Monyog uses for its connection. The users will only need to be created without any specific global or object privileges. In terms of MySQL GRANT syntax this is equivalent to the statement "GRANT USAGE ON *.* to 'user'@'host'".

However, to fully enable all Monyog functionalities a little more privileges are required. Those additional privileges are:

  • Security counters: Monyog user will need SELECT privilege to the `mysql`.`user` table. Additionally, if the server was started with the 'skip_show_databases' option, you must explicitly grant SHOW DATABASES privilege for Monyog security counters to be functional.
  • Replication counters: To retrieve replication metrics from a replicating slave user must have the REPLICATION_CLIENT privilege.  

SUPER privilege will work too. Also  note that when registering servers there is an option (Is this a Replication Slave?) to include replication information. This must be 'Yes' when registering the server (default value is 'No'). If the option is set to 'No', Monyog will not attempt to retrieve that information. When performing 'Test Connection' from the server registration page Monyog will display the error message:

"Access denied. You need the SUPER/REPLICATION CLIENT privilege for retrieving REPLICATION details!"

(This can be ignored if the MySQL server is not a REPLICATION slave or if you don't want to monitor REPLICATION.)

  • InnoDB deadlock monitoring: Depending on server version SUPER or PROCESS privilege is required. MySQL changed from SUPER to PROCESS in 5.1.24. With older 5.1 versions as well as 5.0 versions SUPER is required. From 5.1.24 onwards PROCESS.
  • Processlist feature: The PROCESS privilege is required if server processes for all users to be displayed (and without that the 'processlist' feature is not very useful). Monyog user also requires the SUPER privilege if it should be possible for Monyog to kill running processes. The SUPER privilege is a strong privilege, and it will probably depend on your environment if you want to enable this or not. Also to use the Processlist EXPLAIN option the user will need SELECT and SHOW VIEW (for performing EXPLAIN on view structures) privilege to the objects accessed by the statements you want to explain. Granting global SELECT privilege to the user is the simplest way to handle this requirement.

If some required privilege is not enabled, Monyog will display "N/A" for the counter affected and the Monyog log will record a MySQL server error for every attempt to retrieve those counters from the server. Be aware that the log may grow considerably if this is the case!  

  • Performance Schema based Sniffer:  Performance schema requires SELECT, DROP and UPDATE privileges. SELECT privilege is required to read performance schema tables. DROP privileges is required to truncate the Performance schema table. And UPDATE privileges is required to enable statement digest and statement history long in performance schema if not enabled.
  • Log retrieval: For retrieval of log information when stored in a table (supported from MySQL v5.1) user must also have SELECT privilege to the log tables.
  • Flush status: To execute FLUSH STATUS the user must have RELOAD privilege.

In Summary

To fully enable Monyog you can do this,

"GRANT SELECT, RELOAD, PROCESS, SUPER on *.* to 'monyoguser'@'host'"

But if you want to avoid the SUPER privilege then,

"GRANT SELECT, RELOAD, PROCESS on *.* to 'monyoguser'@'host'" is an option too (and only few Monyog features will be disabled or reduced).

Still need help? Contact Us Contact Us