Unable to export MySQL dump for a domain in Plesk: insufficient privileges to SHOW CREATE

Symptoms

• Unable to export MySQL dump in Plesk under Domains > example.com > Databases > database_name > Export with one of the following errors:

  PLESK_ERROR: Unable to export a dump of database_name:
  mysqldump: johndoe has insufficient privileges to SHOW CREATE PROCEDURE database_name!
  (“program ‘mysqldump’ finished with non-zero exit code: %d”, 2)

  ---

  PLESK_ERROR: Unable to export a dump of database_name:
  Unable to backup database ‘database_name’
  mysqldump: example has insufficient privileges to SHOW CREATE FUNCTION `cleanHtml`!
  (Error code 21)

Cause

Insufficient permissions for the database user.

Resolution

1. Connect to the server via SSH

2. Grant SELECT permission to the database user by using the following MySQL query:

   # plesk db “GRANT SELECT ON mysql.proc TO ‘johndoe'”

3. Export the database once again