Error Dumping Database on MySQL Database Definer does not Exist

Posted on

This is an article describing error shown or generated in the dumping process of a database in MySQL Database Server :

[root@hostname ]# mysqldump -uroot -p database_name > /var/databasename-01102016.sql
Enter password:
mysqldump: Got error: 1449: The user specified as a definer ('user'@'localhost') does not exist when using LOCK TABLES
[root@hostname ]# 

The above output is an example of the error generated from mysqldump’s execution command :

The user specified as a definer('user'@'localhost') does not exist when using LOCK TABLES

So, to be able to solve the problem arises with the execution of mysqldump command, below are the solution steps  used to try :

1. Try to execute ‘mysqldump’ command using the user which has already specified that it does not exist as a definer :

mysqldump -uuser_name -p database_name > /path_of_dump_database_file/dump_database_file_name.sql

This is an output of the command pattern’s execution :

[root@hostname ]# mysqldump -uuser -p database_name > /var/database_dump_file-01102016.sql
Enter password:
mysqldump: Got error: 1045: Access denied for user 'user'@'localhost' (using password: YES) when trying to connect
[root@hostname ]# 

2. Failing to execute the ‘mysqldump’ tool shown before, try to access  MySQL Command Line Console with the following command :

[root@hostname ]# mysql -uuser -p
Enter password:
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)
[root@hostname html]# 

3. Try to recreate the user which is stated as does not exist as a definer by accessing MySQL command console with root account :

mysql -uroot -p

This is the command’s execution of the above command pattern :

[root@hostname ]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
  1. Successfully logged in to MySQL Command Console, create the intended user which is not exist as a definer.
mysql> create user 'user'@'%' identified by 'user_password';
Query OK, 0 rows affected (0,01 sec)

mysql> quit
Bye
[root@hostname ]#

5. Re-execute the ‘mysqldump’ command by typing it in the command line :

[root@hostname ]#
mysqldump -uroot -p database_name > /var/database_dump_file-01102016.sql
Enter password:
[root@hostname ]# 

Based on the re-execution of the command, it has been successfully executed.

One thought on “Error Dumping Database on MySQL Database Definer does not Exist

Leave a Reply