Remove DEFINER clause from MySQL Dumps up vote 6 down vote favorite 6 I have a MySQL dump of one of my databases.
In it, there are DEFINER clauses whi ch look like, "DEFINER=`root`@`localhost`" Namely, these DEFINER clauses are on my CREATE VIEW and CREATE PROCEDURE stateme nts. Is there a way to remove these DEFINER clauses from my dump file? mysql share|improve this question edited Oct 17 at 6:38 Broken Heart ? 8,85871845 asked Feb 25 '12 at 18:28 FastTrack 87311125 add comment 4 Answers activeoldestvotes up vote 4 down vote accepted Well, I don't think there is a way to ignore adding DEFINERs to the dump. But th ere are ways to remove them after the dump file is created. open the dump file in a text editor and replace all occurrences of DEFINER=root @ localhost with an empty string "" Try this - [Link] mp/ Search for DEFINER in [Link] [Link] Hope it helps! share|improve this answer answered Feb 25 '12 at 19:16 Abhay 3,5561512 Thanks for the response! So in my text editor, would I replace the entire 'DEFIN ER=root @ localhost' string with ""? Or replace 'root @ localhost'? FastTrack F eb 25 '12 at 23:02 1 @FastTrack, please remove the entire string. Abhay Feb 26 '12 at 7:53 Worked! Thank you! add comment FastTrack Feb 27 '12 at 3:05
up vote 3 down vote As per the other's recommendations, here is an example of how to remove the DEFI NER from the dump, after the dump has finished: mysqldump -u user --password='password' -h localhost database | grep -v "50013 D EFINER" > [Link] share|improve this answer answered May 9 at 16:29 Jonathan 1,236924 add comment up vote 1 down vote A quick way to do this is to pipe the output of mysqldump through sed to remove the DEFINER statements wrapped in conditional comments. I use this to remove DEF INER from CREATE TRIGGER statements, but you can tweak the condition comment ver sion number in the regex to suit your purposes. mysqldump -u user --password='password' -h localhost database | \ sed 's/\/\*!50017 DEFINER=`.*`@`.*`\*\///' share|improve this answer edited Jul 17 at 14:32 answered Jul 17 at 14:09 Paul Dixon 104k13167238 A slightly more readable sed statement: sed "s/DEFINER(.*?)FUNCTION/FUNCTION/" manu08 Sep 11 at 20:05 add comment up vote 0 down vote Replace all of your definer users with CURRENT_USER. In my gradle script that cr eates the backup, my replace script looks like: [Link](file: "$buildDir/sql/[Link]", match: "`${[Link] _db_username}`@`%`", replace: "CURRENT_USER", byline: true); which really is just calling ANT. Then you won't have to worry about it. share|improve this answer answered Dec 29 '12 at 8:59 Ryan Shillington 37248 add comment Your Answer Sign up or login Sign Sign Sign Post up up up as using Google using Facebook using Stack Exchange a guest
Name Email