When I run mysqldump, I get an error:

mysqldump: Got error: 1449: The user specified as a definer ('root'@'foobar') does not exist when using LOCK TABLES

This makes sense because foobar is a legacy machine that no longer exists.

How do I change the definer of all my tables to 'root'@'localhost'?

  • 4
    Do you have Views? Tables don't have definers I'm sure... Also see dba.stackexchange.com/q/4129/630
    – gbn
    Dec 16, 2011 at 6:18
  • 1
    @gbn +1 you were right - they were views - thx for the link, but I couldn't get it working quite right. However, I was able to modify the views in SQLyog so I could do the dump.
    – kfmfe04
    Dec 16, 2011 at 12:58

4 Answers 4


What I think is that the database you are trying to dump contains procedures/methods that were defined by a user while logged in as root@'foobar'.

Now the solution is that you have to replace the definer's for that procedures/methods

then you can generate the dump without the error.

you can do this like ..

 UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='root@foobar'

Be careful, because this will change all the definers for all databases.

Try it....!

UPDATE on 9th Feb 2012

As I saw the link given by @gbn which is an answer given by @Rolando that can also be the Case. Please visit the link

EDIT by @RolandoMySQLDBA 2011-12-16 11:20 EDT

While risky, this answer is good. Just to clarify: You can specify the database in your query like this:

 UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='root@foobar' AND db='whateverdbyouwant';
  • 7
    This answer works for changing the definers of procedures and functions but does not change the definer of a View. Sep 25, 2017 at 11:04

Easier to use the --single-transaction switch:

mysqldump --single-transaction -u username -p db > db.sql
  • 1
    Sorry, how this addresses the problem desribed in the original question? Looks like you answered the wrong question... Nov 22, 2012 at 20:06
  • 6
    The structure of the answer is wrong, because --single-transaction needs to be prior to the database name, but the answer is actually correct. Specifying this option works around the issue by changing the locking behavior of mysqldump, so the error in the original question no longer occurs. Nov 23, 2012 at 3:41

The quickest solution would just be to re-create the definer so it does exist, as long as it doesn't create any conflicts with existing users.

CREATE USER 'root'@'foobar';

  • What if the user was 'root'@'%'? I simply deny such thing on a server we're responsible for. Sep 21, 2016 at 10:04
  • @AttilaFulop Yes, security definitely should be a consideration, but I said this was the "quickest" solution, not the perfect one. Once the import is complete the user could be deleted to mitigate risks of having extra users.
    – ColinM
    Sep 22, 2016 at 16:40
  • You're right in that. I just wanted to draw attention to this particular aspect as well. My workmates would have added 'root'@'%' and leave it there as long as Chuck Norris is alive ie. forever ;) Sep 23, 2016 at 6:15

Export all the views of the database <DB>:

    information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql


    information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql

Edit views.sql and recreate them:

cat views.sql | mysql <DB>

Specify -u and -p switches if necessary.


Not the answer you're looking for? Browse other questions tagged or ask your own question.