Access denied in mysqldump after ip address change

This one was a real hard nut to crack. My workstation ip address has changed and I could no longer connect to my MySQL server remotely. So far nothing new or interesting. I went to cPanel and added my new ip address to “Remote MySQL” – a list of ip addresses that are allowed to access the database server remotely. Being a cautious person, I also removed my old ip address while I was there.

So now I could connect remotely both from my IDE (via JDBC) and from my local mysql client but to my surprise, I was still getting access denied from mysqldump. Mhm…

mysqldump: Got error: 1045: Access denied for user 'myusername'@'' (using password: YES) when using LOCK TABLES

I verified that myusername indeed had permissions for locking tables. And I was able to make an alternate dump with Adminer and load the dump into my local database. (There was just one unrelated error with an empty view definition.)

To cut the story short, the problem was in the view. It was defined remotely from my old ip address and it stuck with the view in DEFINER. For the view to work, its definer must have access to the database – which in my case it didn’t because I had already revoked access to my old ip address. So now I couldn’t see the view definition, or use it, getting access denied for the old ip address.

It dawned on me that the “unrelated error” in my Adminer dump probably wasn’t that unrelated. Adminer defines dummy tables for views before dropping them and replacing them with a real view. And the error was triggered when it attempted to create a table with no attributes.

Obviously, it failed to get a definition of the view defined from the old address and thus it couldn’t get its attributes either.

It’s really unfortunate that I was getting access denied for my new ip address when dumping with mysqldump while in fact, access was being denied to the old ip address. The whole thing with DEFINER and its ip address seems rather silly, if you ask me.

Anyway, the solution was to recreate the faulty view from my new ip address (I actually used Adminer to create it from localhost) and all was good then.

One Response to “Access denied in mysqldump after ip address change”

  1. Novoj says:

    You have similar problems when migrating MySQL dump to another machine – form example dump from production server to your developer machine for debugging purposes.

    I’ve made a simple (Linux) based utility that will automatically recreate all views in certain database with redefining definer. More details here:

    Maybe it’ll be of some use to you.


Leave a Reply