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"
Change directory to the database dump file. Unzip the database dump.
unzip OPENSPECIMEN_26-03-2019.zip or gunzip OPENSPECIMEN_26-03-2019.SQL.gz |
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.