Facebook
Twitterrandom_internet_guy
New Pleskian
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:
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!
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!
