Mysqldump: access denied for user ... when using LOCK TABLES


#1

Trying to do a simple backup of the mysql db with mysqldump. Have logged onto the DH server (berlin) via ssh, and entering:

I get the following error (after several lines have been written to the output file):

Have googled around + have tried omitting --opt and adding --skip-add-locks - still get the same error.

Any ideas / pointers much appreciated.


#2

First double check the command syntax, variable spelling and spacing.

On fail: grant an additional user access to the database and test it’s ability to dump - Panel > Goodies > MySQL Databases

On fail: message Support.


#3

try to export the database from phpMyAdmin via DH panel --> Goodies --> Manage MySQL --> phpMyAdmin


#4

Thanks for your help guys. Got it figured out.

I’d created a view in the mysql db via a mysql client running on my machine. The effect of this was to set the definer of the view to be mydbusername@mycurrentipaddr. Hence trying to mysqldump from the dreamhost server was authenticating via user/pass but failing due to the ip associated with the view.

I dropped the view, and recreated it from mysql client on the dreamhost server - this set the definer to be mydbusername@69.163.128.0/255.255.128.0, which presumably covers the DH IP range.

After that, mysqldump worked fine.