[DB][MySQL] 建立只能用來Monitor各DB的帳號

Posted On 2018-09-10

[DB][MySQL] 建立只能用來Monitor各DB的帳號

DB Version: MySQL 5.7.23

其實跟一般建帳號的方式沒什麼兩樣，差別在於權限的給予方式不太一樣而已，以下記錄過程

CREATE USER 'monitor_User_Remote'@'xxx.xxx.xxx.xxx' IDENTIFIED BY '<YOUR PASSWORD>';
GRANT SELECT, SHOW DATABASES, PROCESS ON *.* TO 'monitor_User_Remote'@'XXX.XXX.XXX.XXX';

FLUSH PRIVILEGES;

主要就是給予的權限是Select, Show Databases, Process這三樣權限，這樣這個帳號才有辦法監控到全DB的執行Process，另外根據官方文件，為了避免發生SQL Statement Generates Warnings or Errors的錯誤，所以還需要給這個帳號另一個權限

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.

GRANT UPDATE ON performance_schema.threads TO 'monitor_User_Remote'@'XXX.XXX.XXX.XXX';
FLUSH PRIVILEGES;

這些權限都設定完成後，這個帳號就可以監控全DB的運作情況了，以上

Reference

5.2 Creating MySQL User Accounts for the Monitor Agent