Can't connect to db after streaming a mysqldump over SSH
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.
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.