Remove 'DEFINER' entries from database dump file


If we are importing the database dump which has DEFINER entries it causes errors while importing the database because DEFINER contains the database user and hostname of the database server. It has to match with the database server and database user who is importing it. If it does not match then it shows errors like "Error 1227 (42000): Access denied; you need (at least one of ) the SUPER privilege(s) for this operation"

Steps to remove DEFINER entries:

Unzip database dump file.

Change directory to the database dump file. Unzip the database dump.

unzip  or gunzip OPENSPECIMEN_26-03-2019.SQL.gz

Remove 'DEFINER' entries from the database dump.

sed -e 's/DEFINER=[^ ]* / /' OPENSPECIMEN_26-03-2019.SQL > OPENSPECIMEN_26-03-2019-NEW.SQL

To check all definer entries are removed open the newly generated dump file and search for DEFINER keyword into it.

If no entry is found for DEFINER, import newly generated database dump into the database.