How do I use SSH to access MySQL remotely?

September 1, 2014 82k views
oslerlabs
By:
oslerlabs

I am having difficulty accessing MySQL remotely.

I have tried accessing the database via MySQL Workbench (the "Standard TCP/IP over SSH" option) and phpMyAdmin. Both fail when I try to login.

I followed the 'how to secure MySQL' tutorial to set up the database and my sense is some of the options enabled through the tutorial block remote access.. but I'm not sure exactly what is going on.

I have gone through other tutorials here on the topic but none have overcome this problem.

I can login and access the database locally using putty.

My VPS is running ubuntu and have Java, Tomcat and MySQL installed. I use my server to run a mobile android app. I have also installed fail2ban and I have set up IP tables.

Thanks

1 comment
  • sierracircle September 1, 2014

    If you are not able to access via phpMyadmin, then there is an issue somewhere, as phpMyadmin only needs localhost access.

    However, if you can login using Putty..it should be working. I will do a quick walk through of Workbench connection using ssh:

    1. Database >Manage Connections > New Connection
    2. On the connection tab..choose Standard TCP/IP over SSH
    3. SSH Hostname will be your IP address of your droplet. If you have changed your default SSH port you need to add a colon and port like this: 172.33.44.66:2727 so 2727 being the alternative port..
    4. SSH Username is your droplet username, not the database username
    5. Password for SSH, or browse to your SSH pub key file on your computer
    6. MySQL hostname...should be 127.0.0.1 unless you changed that .
    7. MySQL Server port.should be 3306 unless you changed that
    8. Username is your database username
    9. Password for your database user
    10. press the "Test Connection" button...it will take a minute and then it should say "Connection Parameters are Correct"
Log In to Comment
4 Answers
asb MOD September 2, 2014

In the securing MySql tutorial, you set:

bind-address = 127.0.0.1

This restricts direct remote access to the database. Though sierracircle's solution of using SSH through Workbench should still work. phpMyAdmin should also work as it should be connecting locally.

You could also set up an SSH tunnel then access it as if it were local. On your local computer, run:

ssh root@162.243.1.37 -L 3306:127.0.0.1:3306 -N &

You can then connect locally through the tunnel with:

mysql -h 127.0.0.1 -P 3306 -u user -p
Reply
  • oslerlabs September 4, 2014

    Thanks for the comments sierracircle & asb.

    1) I am able to run the SSH tunnel code above then connect to the tunnel locally. A few second later I get: 

    mysql> ssh: connect to host 162.243.1.37 port 22: Connection timed out

    I'm not sure what to make of this.

    2) Regarding Workbench, I still get an authentication error, "Could not connect to SSH Tunnel," when I try to connect with workbench.

    I have root users and specific users for both the SSH connection and MySQL. I have tried different combinations of users with no luck.

    Could IP table block remote access?

    Ideally I would like to use Workbench for this project.

    Thanks so much

  • sierracircle September 4, 2014

    just to clarify, you say that: "I have tried accessing the database via MySQL Workbench (the "Standard TCP/IP over SSH" option) and phpMyAdmin. Both fail when I try to login."

    so I am understanding that you have phpMyadmin installed on your ubuntu server, but that it cannot access your MySQL database.
    In addition, you cannot access your MySQL database with Workbench, which is installed on your home computer.

    My thought is that there is an issue with the bind-address, as phpMyadmin installed on your server should be able to access your database without any problems.

    can you connect using the same code as above but with:
    ssh root@162.243.1.37 -L 3306:localhost:3306 -N &

  • oslerlabs September 5, 2014

    sierracircle - You are correct phpMyadmin on sever and Workbench on my desktop. Nether seems to work.

    ssh root@162.243.1.37 -L 3306:localhost:3306 -N &

    mysql -h localhost -P 3306 -u user -p

    Seem to work fine but the time out message mentions port 22:

    mysql> ssh: connect to host 162.243.1.37 port 22: Connection timed out

    22 is not my SSH port

    I have tried to run:

    ssh root@162.243.1.37 -L 2727:localhost:3306 -N &

    I was able to log into MySQL but I couldn't log in with Workbench (I also changed the MySQL Hostname to localhost as well).

    I imagine port 22 is the default but where do I change that to ensure the tunnel is set up correctly?

    And, if I can get the tunnel on the right port should I then be able to login with Workbench?

    Thanks so much!

  • sierracircle September 6, 2014

    I think to set an alternate port you do something like:

    ssh root@162.243.1.37 -p YOURPORTNUMBER

    or, from Workbench, You would set your SSHHostname like this:

    yourdomain.com:YOURPORTNUMBER

iamkingsleyf January 5, 2015

Same problem here

Reply
michaelf January 23, 2016
Reply
leineme April 3, 2016

hi, in my case i am trying to access mysql workbench remotly , the thing is , the two computers are binded using a home group , will that work ?

Reply
Have another answer? Share your knowledge.