Im trying to restore the database dump which has the DEFINER=test@%.

Backup file is 50GB.

OS - CentOS 7.5

While restoring this, I got the below error.

ERROR 1227 (42000) at line 30908: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

The line 30908 is,

CREATE DEFINER=`test`@`%` PROCEDURE `myprod`(IN id INT)

So I tried to remove the definer using perl.

perl -p -i.bak -e "s/DEFINER=\`\w.*\`@\`\d[0-3].*[0-3]\`//g" my_dump_file.sql

Its executed, but after that also, I got the same issue and its not changed in file.

Then I tried,

sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' < my_dump_file.sql > my_dump_file.new

This is also executed, and tried to restore my_dump_file.new but still same issue on the same line.

Then finally I tried to create an user on the MySQL.

create user `test`@`%` identified by 'test';
grant all on *.* to `test`@`%`;
flush privileges;

Again, Im getting the same issue.

Can anyone help me to fix this?

1 Answer 1

sed -r -e 's/DEFINER\s*=\s*\S+//'


-r : better syntax

\s : any whitespace (eg, tabs, too)

\S : non-space

(I tested on Ubuntu, not CentOS.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.