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?

Answer 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

Answer 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.