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>
- 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”