CODEWITHSUNDEEP
mysqlwindows
Sharathkumar Vattikunta
Sharathkumar Vattikunta

Reputation: 361

mysqldump: Got error: 1449:

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

kindly give the solution on above error.

Upvotes: 36

Views: 61928

Answers (6)

venkey
venkey

Reputation: 82

I faced the same problem after I copied all the views and tables from another host.

It worked after I used the query from this answer to change all definers in my database.

Copied here for convenience:

  SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW ", 
                table_name, 
                " AS ", 
                view_definition, ";") 
    FROM information_schema.views 
    WHERE table_schema='your-database-name';

Upvotes: 5

y durga prasad
y durga prasad

Reputation: 1202

mysqldump -u user -ppassword --lock-tables=false --default-auth=mysql_native_password dbname > dump.sql

Upvotes: 2

nitin kumar
nitin kumar

Reputation: 652

The most updated answer mentions using --single-transaction make sure that when you are restoring that backup you will face that error again so the best solution is to alter the definers, if user is missing add definer for the any admin user which is being used currently.

Upvotes: 0

prashant thakre
prashant thakre

Reputation: 5147

Its better to use first mysqldump with --single-transaction, like:

mysqldump --single-transaction -u root -p mydb > mydb.sql

If above not working try below one.

You have to replace the definer's for that procedures/methods, and then you can generate the dump without error.

You can do this like:

UPDATE `mysql`.`proc` p SET definer = 'root@localhost' WHERE definer='[email protected]'

3rd party edit

For mysql 8.0 the table proc does no longer exist. Try

 SELECT * FROM information_schema.routines;

Upvotes: 104

Vittee Criss
Vittee Criss

Reputation: 1

I had a similar problem, the problem was that wanted to migrate a database from one instance to another but in the dump also were procedures that referred to other databases so I marked that mistake and corrected by modifying the procedures or failing to remove some.

Upvotes: 0

thomas
thomas

Reputation: 1

try this:

mysqldump -h hostname -u thomas -p -x dbname >  xxx_2015_03_25.sql

Upvotes: -6

Related Questions