cloudnine
By:
cloudnine

Can't connect to db after streaming a mysqldump over SSH

October 26, 2017 186 views
MySQL Ubuntu

I streamed a mysqldump over SSH from one remote server (say, Server A) to another (say, Server B) and the import went through fine and the db on Server B was correctly updated. This is on Ubuntu 14.04 and the MySQL version is 5.6.37 or so.

However, when I access the application on the new server (the server to which the db was imported), I get the following error:

Access denied for user ''@'localhost' (using password: NO)

I checked that the db user for the db in question on Server B had full privileges for the db and I was also able to connect to the db as that user on command line. I also verified that the application's db config file had the correct db details, so, this has been very puzzling to me.

The only thing I can think of is that, both servers had a .my.cnf file in the home directory of the root user, but on Server B, I put in just the password for the mysql root user and left out the 'username=root' part, as per the MySQL documentation.

Going by the fact that the error message doesn't specify a user, it seems to me that the import got created without a specific user (because of the absence of the 'username=root' part in .my.cnf) and hence it refuses to connect as the other db user that has full privileges. I am not sure if this sounds like a logical explanation, though.

I tried reassigning all privileges to the db user for this particular db and also ran the flush privileges command, but nothing seems to help. Can anyone here think of an explanation or solution to this issue?

I haven't changed MySQL's root password on the destination server (Server B) or the origin server (Server A), either.

Thanks in advance.

1 Answer
  • Thanks for the link. I saw similar suggestions when searching for a solution.

    I am curious to know, though, why I need to reset the root password when I didn't change it in the first place, as I mentioned in my post. Also, if this were the case for mysqldump based exports and imports that use the mysql root user name and password, wouldn't this be mentioned in the docs and tutorials?

    Just trying to understand the logic behind changing the root password as a solution to this problem.

    • If you didn't change it, then it remains the same. Add the user and password to your command.

      • Thanks, I tried the instructions in your link but it didn't work for me, unfortunately.

        • "It didn't work for me" is not useful information in the slightest.

          • Well, I am not sure what you are expecting. Your replies were cryptic one-liners, too. And, you expect me to describe what I tried and how it failed and why it failed? Nice.

            You tried to be helpful, no doubt, and I thanked you for that, despite having seen the same links when I was searching for a solution. I only went ahead with your suggestions because I thought you might have experienced the same issue.

          • Yes, if you want help, describing what you tried and how it failed would be nice.

          • It has to work both ways. If you expect people to describe what they tried and how it failed, you need to be more detailed and helpful in your replies, too. Replies such as yours would be down voted on StackExchange.

Have another answer? Share your knowledge.