Removing DEFINER from MySQL < 5.7 dump files

Posted on by

Problem

You are using the dump files from MySQL (earlier version than 5.7 – that has -skip-definer option in mysqlpump), that outputs the DEFINER keyword in Functions and Views, which causes en error when trying to import in a different database that the users do not exist.

Solution

Use the following in your database dump to remove these keywords:

For FUNCTIONS

sed -i 's/DEFINER=\S*\sFUNCTION/FUNCTION/' mysql_dump

For VIEWS

sed -i '/50013\sDEFINER/d' mysql_dump