Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 621
  • Last Modified:

MySQL Over SSH

Dear Experts,

I would like to use my locally install MySQL Query Browser to access a remote mysql server securely.  How would I go about doing this?
0
narmi2
Asked:
narmi2
  • 13
  • 10
  • 4
  • +4
14 Solutions
 
Meir RivkinFull stack Software EngineerCommented:
ru using MySql workbench?
0
 
narmi2Author Commented:
No, just mysql query browser and mysql administrator
0
 
Meir RivkinFull stack Software EngineerCommented:
enter the connection instance name, the port should be entered by default, and as Server Host enter the ip of the remote server.

Untitled.jpg
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Meir RivkinFull stack Software EngineerCommented:
if it fails try to ping the remote server.
Untitled.jpg
0
 
torimarCommented:
Please check out this nifty way of SSH tunneling to a MySQL server:
http://www.howtogeek.com/howto/ubuntu/access-your-mysql-server-remotely-over-ssh/

I admit I haven't ever tested it myself, and there are passages in that guide which, I guess, need to be read a couple of times over before getting them to work, but I'm almost certain that this is the only method that fulfils all your requirements: a) remote MySQL access, b) SSH and c) MySQL Query Browser.
0
 
jeff_01Commented:
torimar has the solution. This maps a local port on your pc to your mysql server using ssh. Then you connect to the localhost or 127.0.0.1 instead of your remote server and you will have access via the ssh tunnel.

0
 
jeff_01Commented:
I would however suggest

ssh -N -L 3306:localhost:3306 username@remoteserver

-N instructs ssh to run silently without creating a remote shell.

0
 
narmi2Author Commented:
So if my remote server is

xx.xxx.xxx.xx and the remote user is yyy

would I use the following?

ssh -N -L 3306:localhost:3306 yyy@xx.xxx.xxx.xx

to create the tunnel, then use mysql query browser to log into localhost on port 3306?
0
 
torimarCommented:
Yes, so it seems to me.
0
 
narmi2Author Commented:
As this is using ssh, do I just need port 22 the ssh port open on the remote and local machines?  I am assuming I do not need port 3306 open in this case?
0
 
torimarCommented:
Ideally, yes. Still, I'd first test the procedure with the port open on both sides. If it works fine, close the port first on the remote machine, then on the local one, and test after each step.
0
 
narmi2Author Commented:
Thanks.

And the ports, as this is going to be a one way connection, i.e. I will always be using the local machine to connect to the remote machine, I will never use the remote machine to connect to the local machine.  So on the remote firewall, do I need to open the ssh port as TCP and on the local machine firewall does the ssh port need to be opened as UDP?

I am new to this UDP/TCP stuff.  On both firewalls, each port can be opened as TCP, UDP or BOTH.
0
 
torimarCommented:
Remotely administrated servers can only be administered via SSH. If that isn't properly configured, you can't access them in a secure way.
That's why SSH usually is already configured correctly by default, no user needs to open the SSH port. So just leave the SSH settings as they are (provided you have already had successful connections via SSH to that server).
TCP and UDP are transmission protocols, which means they work like languages: unless they are the same on both sides, communication will not function. You cannot use TCP for the incoming and UDP for the outgoing traffic of one and the same connection; that would be like talking Chinese to an Indian. You may very well have varying ports on both sides of the transfer, but not varying protocols. SSH is TCP on both sides.

But before dealing with firewalls, please first check whether this works. Improperly configured firewalls can ruin your day in server administration.
0
 
narmi2Author Commented:
I can ssh into the server like so

ssh username@remote_server

I ran the following command from the local machine

ssh -L 666:localhost:3306 username@remote_server

It asked me for the remote servers password, which I entered in.  I then opened mysql query browser on the local machine, and tried connecting to localhost on port 666.  But it does not seem to work.  What am I doing wrong?
0
 
arnoldCommented:
Check /etc/ssh/sshd_config to make sure that port forwarding is allowed as it might not be.
0
 
narmi2Author Commented:
>> Check /etc/ssh/sshd_config to make sure that port forwarding is allowed as it might not be.

On the local machine or the remote machine?
0
 
arnoldCommented:
I think you have the setup of the tunnel wrong.
ssh -L remote_server_port:localhost:localhost_port username@remote_server

Presumably you have a local mysql instance running that prevents you from using the example.

Check the /var/log/messages to see whether you have an entry dealing with port 3306 already being in use when you run the ssh connection.
make sure the ssh -L is present in the process list "ps -ef | grep ssh"

ssh -L 3306:localhost:666 username@remote_server
0
 
arnoldCommented:
The portforwarding needs to be allowed on the remote machine.
0
 
narmi2Author Commented:
Yes I already have a localhost on my local machine.

I already have mysql on the local machine

ps -ef | grep ssh

returns the following

narmi2     8143  8039  0 15:09 pts/0    00:00:00 grep ssh

Which username and password do I type into mysql query browser?

The username and password to connect to mysql on the remote machine, or the ssh username and password of the remote machine?
0
 
arnoldCommented:
Disregard my comment on the port:host:port.
0
 
arnoldCommented:
On which system are you running ps -ef | grep ssh?

In your /etc/ssh/sshd_config on the server to which you are connecting, you need an entry
AllowTcpForwarding yes
or
#AllowTcpForwarding yes (means this is the default behavior)

The other problem is that you should not use ports on the local side below 1024 (i.e. you may get an error dealing with reserved ports.)
see if running the following from the local system where the query browser is installed to the remote
ssh -L 5424:localhost:3306 username@remote_system
You have a new shell to the remote server that has the tunnel.
0
 
narmi2Author Commented:
From the remote server:

[narmi2@remote_server ~]$ ps -ef | grep ssh
root      2017     1  0 Dec16 ?        00:00:00 /usr/sbin/sshd
root     17035  2017  0 15:40 ?        00:00:00 sshd: narmi2 [priv]
narmi2 17039 17035  0 15:40 ?        00:00:00 sshd: narmi2@pts/0
narmi2 17057 17040  0 15:40 pts/0    00:00:00 grep ssh
[narmi2@remote_server ~]$

Yes, I have the following on the remote server

#AllowTcpForwarding yes

I tried

ssh -L 5424:localhost:3306 remote_server_user@remote_server

and yes, it gives me a new shell to the remote server.

When when I open up mysql query browser on the local machine and type

server host: localhost
port: 3306
username: mysql_username_on_remote_server
password: mysql_password_on_remote_server

I get an error message

Access denied for user mysql_username_on_remote_server@localhost.

I tried pinging and it pings fine.
0
 
arnoldCommented:
You need to use the 5424 port to connect to the remote server and not the 3306 which is your local mysql server setup.

Try the following using a local terminal window after you ssh -L :

telnet localhost 5424

See if you get the mysql prompt.
0
 
narmi2Author Commented:
I get the following

narmi2@local_machine:~$ telnet localhost 5424
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
4
5.0.77ýýcCfYh~dY,oFN5[KGb7,t'Connection closed by foreign host.
narmi2@local_machine:~$
0
 
arnoldCommented:
This proves that the tunnel is established.

You should be able to establish a mysql session. One other thing you have to check is whether the username/password you use when trying to login into the remote mysql has the login rights from localhost.

connect to the remote server's mysql session and check whether the user you use has the rights to login from localhost or anywhere.
select host,name,password  from mysql.user where user='remote_username'
If the host is anything other than %,localhost means that your login attempt is being rejected.
You would need to grant the 'remote_username' the right to login from localhost or (%) anywhere.
If that is clear, you would need to see whether the user is restricted to a specific set of databases.

select db,user from mysql.db;

select host,db from mysql.host;
0
 
narmi2Author Commented:
I'm getting the following:

mysql> select host,user,password  from mysql.user where user='remote_username'
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *DDCFCFAB8ACBA3ADB5C2B44A358FBF00130CAF32 |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select db,user from mysql.db;
Empty set (0.00 sec)

mysql> select host,db from mysql.host;
Empty set (0.00 sec)

mysql>
0
 
arnoldCommented:
You are using root and password to login via the tunnel
i.e. host localhost port 5424 username root password <password>
What if any error are you getting from the connection attempt??
0
 
narmi2Author Commented:
Could not connect to host 'localhost'.
MySQL Error Nr. 1045
Access denied for user 'root'@'localhost' (using password: YES)

Click the 'Ping' button to see if there is a networking problem.
0
 
dapaintballer331Commented:
Install mySQL (mysql client) on your own computer.
Read this: http://dev.mysql.com/doc/refman/5.1/en/mysql.html

It's simple, you change localhost sockets to remote sockets, and connect.

If you're being rejected, read my comment here: http://www.experts-exchange.com/Database/MySQL/Q_25006091.html
0
 
jazzIIIloveCommented:
Install sqlyog, you won't regret;)

Best regards.
0
 
narmi2Author Commented:
I will not be able to continue this question until next week as I do not have enough resources as the moment.
0
 
narmi2Author Commented:
So should it be this:

ssh -N -L 3306:localhost:3306 username@remoteserver

or this?

ssh -N -L 3306:localhost:22 username@remoteserver
0
 
arnoldCommented:
neighter.  You can not reference an existing used port as the local port on which the tunnel will bind.

i.e.
ssh -N -L "local port to which the connection will be made":localhost:"the remote port of the service to which the tunnel will be established"
in your case, ssh -N -L 6542:localhost:3306 username@remoteserver
The local connection to port 6542 will get you to the mysql service on the remoteserver.
0
 
arnoldCommented:
Could you make sure that you can connect to the TCP port 3306 on the remote server while on the remote server?
Look in /etc/my.cnf to make sure that you have not changed the port on which mysql is running from 3306 to something else.

The other issue you may have is that your portforwarding might be disablem within the sshd_config file which will prevent the establishment of the tunnel. (PermitTunnel yes)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 13
  • 10
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now