1

I have an issue with accessing MySQL using root and password. I am trying to connect the Server using PuTTY SSH using 

> mysql u root -p

For an unknown reason I can NOT access the Database and I am getting rejected with the following ERROR:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

The weird thing is that I am using the same password with PhpMyAdmin using Web and I can access everything. I need to run a script that will allow the SQL server

root@localhost user

run the query over the LAN and not the web.

Therefore I need the user to connect using command line SSH or something like that.

How can I fix that issue? the user rott and password WORKS just fine through PhpMyAdmin and not through SSH PuTTY, Why?

Is there something I need to do in order to fix this? I have been working with the Database for 2 years now using the web interface. Now I need to have it working using SSH (22).

Please assist. thanks in advance. Arye

Arye Guetta
  • 45
  • 1
  • 9
  • Does `mysql u root -p user` work ? – Michael Durrant Jun 16 '15 at 14:30
  • Also see https://dev.mysql.com/doc/refman/5.1/en/problems-connecting.html – Michael Durrant Jun 16 '15 at 14:34
  • What happens when you try `mysql -h 127.0.0.1 -u root -p` ? (when no host is specified or 'localhost' is used, then mysql will try to connect over a filesystem socket - and these usually have different authentication rules associated compared with network sockets) – symcbean Jun 16 '15 at 15:16

4 Answers4

0

It sounds like you need to update the your password for root on MySQL. Check here: MySQL - ERROR 1045 - Access denied

Community
  • 1
  • 1
Grant P
  • 85
  • 7
  • I am not sure I am following what you want me to do? the password is working on PhpMyAdmin and been like that for two years now. I CAN access the DB from the web BUT NOT from the SSH command line. – Arye Guetta Jun 16 '15 at 14:35
  • Since you are getting denied. You should make sure that your root credentials through MySQL are set up correctly. The link has some good information on checking that. – Grant P Jun 16 '15 at 14:39
  • Thanks. this is my work Server and I don't want to NOT be able to connect to SQL any longer. Whould that effect my access of root in the web? Since if I do that and I will lose the access to the server, then what would I do? – Arye Guetta Jun 16 '15 at 14:55
  • I am not sure if I understand what you mean. MySQL is just a program that runs on your server. Flushing/changing credentials for it will not affect your root credentials for other programs on your server or your access to your server as a whole. Just for security/data access reasons, MySQL maintains its own set of user credentials for people that access the DB. E.g. you may only want to give some non-root user readonly privileges. – Grant P Jun 16 '15 at 15:01
  • When you run `mysql u root -p`, you are running a script that is trying to automatically log you into MySQL using whatever password it has saved. That password is not necessarily the correct one which appears to be the case here. You say PhpMyAdmin is able to connect, so check what password you have saved for root there and then set the root password for MySQL to match. – Grant P Jun 16 '15 at 15:05
  • I am working with MySQL DB and creating Databases on a regular basis. I am not that expert with MySQl and I am doing a rutine work on the Server dealing the Databases. I just anted to be sure that I will able to log back to the server after I do the flushing. – Arye Guetta Jun 16 '15 at 15:13
  • Oh ok, yes don't worry. `FLUSH PRIVILEGES;` is a MySQL command that is meant to be run in MySQL and does not affect other parts of the server. – Grant P Jun 16 '15 at 15:17
  • So I can Run FLUSH PRIVILEGES; and it will not effect my login using root@localhost to the server? I do not want to be logged out from the server. just wanted to be sure that it is safe for me to use the FLUSH PRIVILEGES; command and it will not effect my root user at all and i will be able to continue working on my DB from PhpMyAdmin, correct? – Arye Guetta Jun 16 '15 at 15:27
  • As long as you are setting the credentials to match those on PhpMyAdmin, there should not be an issue. Flush seems like a terrible term but essentially it is meant to reload the the grant tables and clear any cached data on the server that may be lingering from multiple MySQL instances. It's not deleting your credentials. Just refreshing so that only the most up to date ones are used. – Grant P Jun 16 '15 at 15:36
  • Thanks. can't i clear cache on the server and restart it? that would be a better solution, No? By using this `free && sync && echo 3 > /proc/sys/vm/drop_caches && free` I can free the MySQL cache. No? – Arye Guetta Jun 16 '15 at 16:52
  • Thanks for helping out. I will try and hopefully it will work. The idea is to RESET the password with the same that I currently use, correct? that way I will be able to use the PhpMyAdmin as well. Is NOT there anything to do with SSH on my Server with that? Thank You. – Arye Guetta Jun 16 '15 at 17:30
  • As far as clearing server caches, I am unsure if that clears individual app data caches. And yes. You should try to reset using the password that you use with PhpMyAdmin otherwise you will have to reset that password as well. – Grant P Jun 16 '15 at 17:34
  • Thanks. that was FIXED Only when I changed from old password to new. in my.cnf file I have removed the flag for old_password=1 and Restart the service of MySQL. then I have created a NEW password (using the old one I had) and it was created with 41 (NEW) Hash length and not 16 (OLD).. then I did flush privileges as you recommended - and IT WORKS. Thank You! – Arye Guetta Jul 19 '15 at 09:07
0

Thank you all for your help. I have managed to overcome the issue: (see below)

That was FIXED Only when I changed from old password to new. in my.cnf file I have removed the flag for old_password=1 and Restart the service of MySQL.

Then I have created a NEW password (using the old one I had) and it was created with 41 (NEW) Hash length and not 16 (OLD)..

Then I did flush privileges as you recommended - and IT WORKS. Thank You!

Arye Guetta
  • 45
  • 1
  • 9
0
> mysql -h your_host_name -P your_port_number -u your_user_name -p

try with hostname option it should work!

Atul Baldaniya
  • 761
  • 8
  • 14
0

Ubuntu 18.04 running MySQL 5.7.28 creates a file /etc/mysql/debian.cnf. It contains some credentials and other settings. So, this debian-sys-maint user is by default a root equivalent. It is used by certain maintenance scripts on Debian systems, and as a side-effect, allows users with root access on the box to view the plaintext password in /etc/mysql/debian.cnf. If your SSH using a client fails after a fresh install through root try using the credentials set in this file.

mobby
  • 361
  • 4
  • 8