When your are trying to import your data into RDS MySQL, it may prompt with following error message. “ERROR 1227 (42000) at line xxx: Access denied; you need (at least one of) the SUPER privilege(s) for this operation”
This can be fixed by removing the DEFINER from MySQL dump. You can use following simple command to fix this issue.
1 |
perl -pe 's/\sDEFINER=`[^`]+`@`[^`]+`//' < your_db_dump.sql fixed-your_db_dump.sql |
there are alternative solution which provided by AWS premium support knowledge base , But that does not work for me. You can try that out if I above mentioned work around does not work.
Let’s look at why there is limited permission on RDS MySQL .
As you might be aware, AWS RDS (Relational Database Service) is a managed serviceĀ and hence in order to guarantee the stability of RDS instance, the permissions of master user (root user in RDS) are not same as root user in native mysql.
RDS Master user has the following permission:
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* WITH GRANT OPTION, REPLICATION SLAVE (Only For Amazon RDS MySQL versions 5.6 and 5.7, Amazon RDS MariaDB)
So if you need more permission other than above, you have to request / inform it to AWS support team. they will do the necessary arrangement.
If you have any questions please do comment below. š
have nice time !!
3 comments
Mark Harrison
October 28, 2019 at 5:19 pm (UTC 5.5) Link to this comment
I think there should be a > before the fixed-your_db_dump.sql in your code line.
priya
September 4, 2020 at 6:12 pm (UTC 5.5) Link to this comment
where?
priya
September 4, 2020 at 6:17 pm (UTC 5.5) Link to this comment
This can be fixed by removing the DEFINER from MySQL dump. You can use following simple command to fix this issue.
how to fix can you explain me.