Search code examples
mysqlputtyremote-serverportforwarding

Can't connect to remote mysql (with PuTTY and tunnel)

I'm trying to use mysql on a remote Debian server. I have configured PuTTy client with a tunnel:

  • In Session , Host name is the remote IP address , and port is 22
  • In SSH/Tunnels I have source port 3306 and Destination: 127.0.0.1:3306

I click open, type the user name and password to log in to the server.

But at this point I can't use mysql. If I try this with no password:

mysql -u root -p

I get access denied for user 'root'@localhost' (using password: NO)

I am not sure if this is because there really is some password needed or if there is something wrong with the port forwarding - how can I know?

I tried going in to the my.cnf file and change the bind address 127.0.0.1 to the remote address instead, but there was no change. Maybe mysql needs to be restarted for that, but I get access denied when trying to restart it (the same mysql error message).

I can see in the puTTY event log that it says "local port 3306 forwarding to 127.0.0.1:3306". A bit further down it says "Opening connection to 127.0.0.1:3306 for forwarding from 127.0.0.1:55271" - I don't know what that's about?

How do I know if the issue is with the password or if it's with the "@localhost" part, were there is something wrong with the port forwarding?

Solution

  • You have either forgotten what your root password was, or you never set it up .. Either way you're going to need to set / reset it.

    If you have access to ssh (which your post suggests you do), and you have sudo access .. This should be a snap. Issue the following commands:

    Stop your MySQl process .. Most Debian systems will be 

    sudo service mysql stop

    Then start MySQL safely without password:

    sudo mysqld_safe --skip-grant-tables &

    Then log in as root

    mysql -u root

    Set the password for root

    use mysql;
update user set password=PASSWORD("PASSWORD") where User='root';
flush privileges;
quit

    Then restart mysql

    sudo service mysql restart

    At this point, you should be prompted for a password when you issue the command:

    mysql -u root -p