1

I'm trying to log into MySQL on a remote server. I have ssh-ed into the server, and I am trying to access the server's local MySQL db.

I got the login credentials from the config file of a local application

mysql -u user -p

but when I login using the same password, I get 

ERROR 1045 (28000): Access denied for user 'user'@'localhost'

Details:

username: user
password: password
table   : table_name
host    : localhost

I have tried the same password a few times, but there must be something other than an incorrect password. I'm very confused, what am I doing wrong?

LSerni
  • 55,617
  • 10
  • 65
  • 107
Rob513
  • 47
  • 6
  • Do you have sudo access ? Do you own or administer the server? – Keith John Hutchison Apr 29 '15 at 21:26
  • Every time this has happened to me it resolved to an incorrect password. – Keith John Hutchison Apr 29 '15 at 21:27
  • Cool. this just happened to me. I'll explain how I resolved it. – Keith John Hutchison Apr 29 '15 at 21:28
  • @csmu i have used the same password in the config – Rob513 Apr 29 '15 at 21:28
  • The only other thing I can think of is user may be a reserved word. Try using a different user name. – Keith John Hutchison Apr 29 '15 at 21:41
  • 1
    What makes you think that the access is granted to user@localhost? Do you have an application on that server using localhost? Are you *sure* it isn't connecting to the IP address of the local network interface (i.e. whatever `hostname()` resolves to?) If you have root access to MySQL, try `select User, Password, Host from mysql.user [where User='youruser'];` to see what logins are actually granted. – LSerni Apr 29 '15 at 21:48
  • user what just an example, i dont want to write the real username – Rob513 Apr 29 '15 at 21:48
  • @lserni I have root to the site not to mysql, i have an application that is using that user and password to access the site's db – Rob513 Apr 29 '15 at 21:50
  • 1
    Just to check, try using the local interfaces' numeric IPs instead of localhost. You can get the list with the ifconfig command. – LSerni Apr 29 '15 at 21:54
  • 1
    Also try 127.0.0.1 and ::1 . You never know. – LSerni Apr 29 '15 at 21:55
  • You may want to try changing the application IP to 127.0.0.1 (after you have granted the appropriate privilege). Depending on various factors, connections through the loopback interface *might* show better performance (but it's *not* guaranteed and it might even make things worse; much depends on the actual network layer as well as application architecture). However, it's performance that would come for free, and well worth a try. – LSerni Apr 29 '15 at 22:41

1 Answers1

0

  1. get root privileges.

    sudo bash

  2. stop mysql server -- ubuntu example

    service mysql stop

  3. open mysql without checking passwords -- ubuntu example

    /usr/bin/mysqld_safe --skip-grant-tables &

  4. log in as root

    mysql --user=root mysql

  5. Enable privileges

    flush privileges;

  6. Change the password and grant any access you might need

    update user set Password=PASSWORD('password') WHERE User='username';

    GRANT ALL ON table_name.* TO 'username'@'localhost' ;

    flush privileges;

    exit ;

  7. Exit safe mode and restart

    kill cat /var/run/mysqld/mysqld.pid

    service mysql start

Keith John Hutchison
  • 4,955
  • 11
  • 46
  • 64
  • i can stop the server, its on the live site – Rob513 Apr 29 '15 at 21:44
  • can you log in as root? – Keith John Hutchison Apr 29 '15 at 21:50
  • It's unclear to me why'd you have root access to the site but not to mysql. As @lserni mentioned ... you have to find out the actual permissions the user has from the mysql.user table. You probably have to add privileges for that user, and that means if you don't have the root access for mysql then you'll either have to find it or stop the server and set it. – Keith John Hutchison Apr 29 '15 at 21:59
  • You need to find out what access privileges the user has. You'll probably need to be able to change / update the privileges so you are going to need super user access . The following question might help you http://stackoverflow.com/questions/10299148/mysql-error-1045-28000-access-denied-for-user-billlocalhost-using-passw?rq=1 – Keith John Hutchison Apr 29 '15 at 22:04