Table of Contents
問題描述
當我執行以下指令時，想要將 mydatabase 這個資料庫備份出來時
<span class="line"><span style="color: #D8DEE9FF">mysqldump </span><span style="color: #81A1C1">-</span><span style="color: #D8DEE9FF">u myaccount </span><span style="color: #81A1C1">-</span><span style="color: #D8DEE9FF">p mydatabase </span><span style="color: #81A1C1">></span><span style="color: #D8DEE9FF"> backup.sql </span></span>出現了以下錯誤訊息
<span class="line"><span style="color: #D8DEE9FF">mysqldump: Error: </span><span style="color: #ECEFF4">'</span><span style="color: #A3BE8C">Access denied; you need (at least one of) the PROCESS privilege(s) for this operation</span><span style="color: #ECEFF4">'</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">when</span><span style="color: #D8DEE9FF"> trying </span><span style="color: #81A1C1">to</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">dump</span><span style="color: #D8DEE9FF"> tablespaces</span></span>
<span class="line"></span>原因與處理方式
這是因為 myaccount 這個用戶缺少了 PROCESS 這個權限。
請以 root 權限登入 mysql，並執行
<span class="line"><span style="color: #81A1C1">GRANT</span><span style="color: #D8DEE9FF"> PROCESS </span><span style="color: #81A1C1">ON</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">*</span><span style="color: #D8DEE9FF">.</span><span style="color: #81A1C1">*</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">TO</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">'</span><span style="color: #A3BE8C">myaccount</span><span style="color: #ECEFF4">'</span><span style="color: #D8DEE9FF">@</span><span style="color: #ECEFF4">'</span><span style="color: #A3BE8C">localhost</span><span style="color: #ECEFF4">'</span><span style="color: #D8DEE9FF">;</span></span>這樣就可以啦。
注意事項
PROCESS 是個靜態、全域的權限，不能指定給單一的資料庫
如果你想執行以下的指令，想要讓 myaccount 只能處理 mydatabase 這個資料庫，是不行滴
<span class="line"><span style="color: #D8DEE9FF"># 這樣執行，會有錯誤</span></span>
<span class="line"><span style="color: #81A1C1">GRANT</span><span style="color: #D8DEE9FF"> PROCESS </span><span style="color: #81A1C1">ON</span><span style="color: #D8DEE9FF"> mydatabase.</span><span style="color: #81A1C1">*</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">TO</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">'</span><span style="color: #A3BE8C">myaccount</span><span style="color: #ECEFF4">'</span><span style="color: #D8DEE9FF">@</span><span style="color: #ECEFF4">'</span><span style="color: #A3BE8C">localhost</span><span style="color: #ECEFF4">'</span><span style="color: #D8DEE9FF">;</span></span>
<span class="line"></span>
<span class="line"><span style="color: #D8DEE9FF"># 錯誤訊息如下：</span></span>
<span class="line"><span style="color: #D8DEE9FF"># ERROR </span><span style="color: #B48EAD">1221</span><span style="color: #D8DEE9FF"> (HY000): Incorrect usage of DB </span><span style="color: #81A1C1">GRANT</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">and</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">GLOBAL</span><span style="color: #D8DEE9FF"> PRIVILEGES</span></span>
<span class="line"></span>
<span class="line"></span>出現錯誤訊息：You are not allowed to create a user with GRANT
這很可能是因為帳號與主機 (Host) 的組合出錯了，請以 root 權限登入 mysql，執行
<span class="line"><span style="color: #81A1C1">select</span><span style="color: #D8DEE9FF"> User, Host </span><span style="color: #81A1C1">From</span><span style="color: #D8DEE9FF"> mysql.user;</span></span>檢查一下 Host 欄位，是 localhost 還是 %，然後再試試。
通常改為以下 SQL 指令，也就是把 localhost 換成 % ，就可以正常運作了。
<span class="line"><span style="color: #81A1C1">GRANT</span><span style="color: #D8DEE9FF"> PROCESS </span><span style="color: #81A1C1">ON</span><span style="color: #D8DEE9FF"> mydatabase.</span><span style="color: #81A1C1">*</span><span style="color: #D8DEE9FF"> </span><span style="color: #81A1C1">TO</span><span style="color: #D8DEE9FF"> </span><span style="color: #ECEFF4">'</span><span style="color: #A3BE8C">myaccount</span><span style="color: #ECEFF4">'</span><span style="color: #D8DEE9FF">@</span><span style="color: #ECEFF4">'</span><span style="color: #A3BE8C">%</span><span style="color: #ECEFF4">'</span><span style="color: #D8DEE9FF">;</span></span>
<span class="line"></span>參考資料
https://dev.mysql.com/doc/refman/5.7/en/grant.html
https://stackoverflow.com/questions/47460670/cannot-grant-privileges-to-a-user-using-root-on-mysql
Comments