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.