[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 theperformance_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 thePerformance_Schema
captures only 1024 characters of each query. Granting this privilege enables the connection toPerformance_Schema
to be dropped before theEXPLAIN
and reconnected after theEXPLAIN
finishes.
GRANT UPDATE ON performance_schema.threads TO 'monitor_User_Remote'@'XXX.XXX.XXX.XXX';
FLUSH PRIVILEGES;
這些權限都設定完成後,這個帳號就可以監控全DB的運作情況了,以上