How can I use mysqldump to replicate views between accounts?

Question

I'm using mysqldump to replicate a database between accounts on a particular machine. Everything works just great, except when we get to our defined views. Because the dump includes a line like the following ...

/*!50013 DEFINER=`user_a`@`localhost` SQL SECURITY DEFINER */

... when loading the dump into mysql on user_b we receive an error:

ERROR 1227 (42000) at line 657: Access denied; you need the SUPER privilege for this operation

Needless to say, I don't have SUPER privilege on this mysql instance. Is there a way to convince mysqldump to dump the views in a user-agnostic way? I can't find anything in the manual on this point. Do I have to actually parse the dumpfile to replace the usernames? Or am I missing something?

Solution 1

same problem. I solved it that way:

mysqldump -uuser1 -ppassword1 database1 > backup.sql

sed '/^\/\*\!50013 DEFINER/d' backup.sql > backup_without_50013.sql

mysql -u user2 -ppassword2 -D database2 < backup_without_50013.sql

The interesting thing is the sed command which, here, removes all lines beginning with /*!50013.

Heidy

Solution 2

You will need to process the backup file and change the DEFINER:

DEFINER=user_a@localhost

I like to change it to :

DEFINER=CURRENT_USER

It will then be automatically associated to the account that loads it.

Solution 3

The SQL SECURITY clauses determine which MySQL account to use when checking access privileges for the view (as you have probably figured out).

When you create a view you can define a couple of options for security for that view. You can read more here, but essentially by default access is restricted to the 'definer' of the view, i.e. the user who created it.

Comments

[Adam Bellaire]

Thanks, Max. I checked it out, and it doesn't seem that tinkering with these options affects the way mysqldump produces output. If it would simply omit the DEFINER line, I would be golden. :)

[Adam Bellaire]

Thanks, I actually ended up doing the same thing, except using grep -v instead of sed, but the result was the same. :)

[Adam Bellaire]

Interesting idea, but this doesn't do anything to affect the @DEFINER lines in the dump output. As long as I'm asserting a definer other than the logged-in MySQL user, I get the security error.

[Thomas Foster]

thanks for the tip! I use (I need to escape the `): sed -ri s/DEFINER=`[^`]+`@`%`/DEFINER=CURRENT_USER/g file.sql

[Matt]

This! And with a little sed magic from @brunoqc it's just perfect