mysqldump: Got error: 1449:

Question

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.

Answer 1

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;

Answer 2

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

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

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

Answer 3

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.

Answer 4

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.

Answer 5

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