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'?
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';
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';
Export all the views of the database <DB>:
mysql -BNe "SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = '<DB>' AND TABLE_TYPE = 'VIEW'" \
information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql
or:
mysql -BNe "SELECT TABLE_NAME FROM VIEWS WHERE TABLE_SCHEMA = '<DB>'" \
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.
