Database restoration from backup fails when using AWS RDS (or when...)

[random_internet_guy]

Issue

Database restoration from backup fails when:

Using a remote database && database admin user registered with Plesk is not a super user

This is a specific annoyance for any providers using Amazon RDS to host their database(s). Amazon RDS does not grant a super user account to the client, only an account with the following privileges:

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER,SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, EVENT, TRIGGER, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EXECUTE​

Moreover, it is not necessarily best practice to register a MySQL super user with Plesk even if possible.

Root Cause

The restoration script attempts to grant super privileges to a temporary user for database import/export. Excerpt from deployer.log:

INFO: Executing utility: /usr/bin/mysql --no-defaults -u [remote_rds_admin] -h [remote_rds_server] -P 3306 -e GRANT\ ALL\ ON\ \*.\*\ TO\ \`backup_64G9Dqz\`@\`%\`\;\ FLUSH\ PRIVILEGES\;

This fails unless executed by a super user. [remote_rd_admin] is not a super user.

Suggest Fix

Change statement to GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, FILE, INDEX, ALTER, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, TRIGGER, CREATE ROUTINE, DELETE, EVENT, ALTER ROUTINE ON *.*

or just use the administrative user registered with Plesk for backup operations?

(caveat: I am not a DBA and am not sure if those are the correct grants for dump/import)

Anyways, thanks for the great product!