Every dump file created using the mysqldump command includes a clause named DEFINER. This clause cannot be excluded from the dump as of now, and hence whenever you try to restore the dumps on a remote database server or database server you would get an error referring to DEFINERS.

That is because DEFINERS have the information of the user and his IP, who has created or executed the SP/View/function from his login, hence it is not necessary for the user to have access over all the database servers.

You can remove the DEFINERS from the mysqldump file manually, however you would not want to do it by looking for DEFINERS on each line and then removing it. You need some script or a command to do this task for you.

Follow the below steps to achieving a dump file without DEFINERS,

Firstly create a mysql dump file,

>mysqldump –u root –p mydatabase>mydatabase.sql

Enter Password:

This command will create your database dump file. The –p is for password, which you have to enter at the second line when prompted.

Now you need to remove the DEFINERS from the dump file which is created, here i.e. mydatabase.sql, to do it use the following command,

>perl -p -i.bak -e "s/DEFINER=\`\w.*\`@\`\d[0-3].*[0-3]\`//g" mydatabase.sql

This command will search for the DEFINER clause in the dump file and replace it with blank space. How is it doing?

-p ; This option places a loop around your script. It will automatically read a line from the diamond operator, execute the script, and then print $_.

-i.bak ; This option lets you edit files in-place and create a backup copy of the original dump.

-e ; This option lets you specify a single line of code on the command line. This line of code will be executed in lieu of a script file.

After –e we have specified what the command has to do,

Searches for the DEFINER= user@IP and replace it with blank space,

“s/DEFINER=\`\w.*\`@\`\d[0-3].*[0-3]\`//g” mydatabase.sql

\w. is the user

[0-3] is the range of the IP address

This command will firstly create a mydatabase.bak file and then go head and make the modifications to the mydatabase.sql file.

You can also run this command with the pipe option, i.e.

>mysqldump –u root –p mydatabase | perl –p –i.bak –e “s/DEFINER=\`\w.*\`@\`\d[0-3].*[0-3]\`//g" mydatabase.sql

I would recommend to create a dump and then make modifications.


About these ads