Im trying to restore the database dump which has the DEFINER=
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?