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

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.

Answer 2

Hello, same problem. I solved it that way:

mysqldump -uuser1 -ppassword1 database1 > backup.sql

sed '/^\/\*\!50013/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 3

Run mysqldump with the option "--skip-triggers"

Answer 4

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.