Home > Mobile >  mysql restore command and granting privilige
mysql restore command and granting privilige

Time:10-27

I am having issue by restoring my MySQL table. When I try to write the below command, I am getting the following error → ERROR 1044 (42000): Access denied for user 'moodle'@'localhost' to database 'moodle'

 root@ubuntu:/var/www# mysql -u moodle -p moodle < ./backup/moodle.sql
 ERROR 1044 (42000) at line 45: Access denied for user 'moodle'@'localhost' to database 'moodle'

The user "moodle" has already all privileges on the db.

mysql> SHOW GRANTS FOR 'moodle'@'localhost';

| GRANT USAGE ON *.* TO `moodle`@`localhost`                                                                                          
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON `moodle`.* TO `moodle`@`localhost` |

Thanks for any assistance and support in advance

CodePudding user response:

As per the comment discussion above, the issue was:

  • The dump file contains LOCK TABLES statements.

  • The user moodle@localhost did not have privilege to execute LOCK TABLES statements during restore.

  • Once the root user was used to grant the right privilege to moodle@localhost, the restore succeeded.

  • An alternative could have been to omit the LOCK TABLES statements from the dump file.

  • Related