+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: SSH tunnel for MySQL on remote server (/showthread.php?tid=67398)
SSH tunnel for MySQL on remote server - skunkbad - 02-18-2017
Setting up an encrypted connection to MySQL on a remote server has not been possible, so I've spent many hours trying different "solutions", and I'm trying to get one working so I can test it out. Unfortunately, it's from some really old PHP, so I did my best to clean it up, but never could get it to work. Right now it's hanging at the call to stream_socket_accept on line 59. Anyone can help? I'm going to sleep, but will be back in the morning.
https://gist.github.com/skunkbad/a07e86248e2bce80cf3092cbe652c829
RE: SSH tunnel for MySQL on remote server - Diederik - 02-18-2017
I would use ssh -L on the php server to port fordward a local port through the ssh tunnel.
RE: SSH tunnel for MySQL on remote server - skunkbad - 02-18-2017
(02-18-2017, 03:48 AM)Diederik Wrote: I would use ssh -L on the php server to port fordward a local port through the ssh tunnel.
I've been trying that, with limited success, and not sure what's going on.
Code:
ssh -L 3307:123.123.123.123:3306 -p 2233 [email protected] -N3307 because this local machine already has MySQL running on 3306.
example.com only allows SSH connections on port 2233.
I can do passwordless SSH connection just fine if I'm not using -L, and assuming it's making the connection with -L.
My CI database connection using 127.0.0.1 and port 3307 appears to not be tunneling, because on the local machine if I stop MySQL then my connection errors indicate the tunnel was not working.
If I can get this to work, I want to be able to start and stop the tunnel when I'm done, so I thought I'd run the ssh command through PHP's shell_exec, so I could get the PID. Then I could kill the PID when I'm done. Right now, I can't even get it to work by putting the command in the terminal.
RE: SSH tunnel for MySQL on remote server - Diederik - 02-18-2017
Sounds like steange behaviour indeed. You could turn up the LogLevel ssh setting to. Port forwarding should be pretty straight forward.
RE: SSH tunnel for MySQL on remote server - skunkbad - 02-18-2017
(02-18-2017, 12:32 PM)Diederik Wrote: Sounds like steange behaviour indeed. You could turn up the LogLevel ssh setting to. Port forwarding should be pretty straight forward.
Yes. I've spent WAY too long trying to make something work ...
Unfortunately, the remote server is a server at a hosting company, and I think they're sick of me bugging them. They stopped responding a few hours ago, so I've been on my own.
Forgot to mention, I have SQLyog, and am tunneling through it just fine. It's doing exactly what I need to do. So it can be done! Frustration is increasing!
RE: SSH tunnel for MySQL on remote server - skunkbad - 02-19-2017
OK, so I got it working, but found a better way, so using shell_exec might be for convenience at some point. Here is what needed to be done:
TO USE SHELL_EXEC
1) Use sudo and an editor like nano to edit the file /etc/passwd. Duplicate the entry for www-data and make the two entries look like this:
Code:
#www-data:x:33:33:www-data:/var/www:/usr/sbin/nologin
www-data:x:33:33:www-data:/var/www:/bin/bash2) Now become the root user momentarily then switch to www-data:
Code:
$ sudo su
[password]
# become the www-data user
# su www-data
# generate an SSH key for www-data (don't add a password!)
$ ssh-keygen -t rsa ...
# add the key to other server
$ cat /var/www/.ssh/id_rsa.pub | ssh [email protected] 'cat >> .ssh/authorized_keys'
# check the connection
$ ssh [email protected]
# ... exit
$ exit
# exit3) Re-edit /etc/passwd so that www-data has /usr/sbin/nologin as its default shell once more.
THE BETTER WAY
The better way to handle all this, if you have the appropriate user account or know somebody who does, is to use autossh. Autossh will keep the ssh connection up permanently, and if goes down it will just put it right back up. You can even have autossh start at system boot.
Starting autossh once will look like this in the terminal:
Code:
autossh -M 0 -o "ServerAliveInterval 30" -o "ServerAliveCountMax 3" -o ExitOnForwardFailure=yes -f -N -p 2233 -L 3307:127.0.0.1:3306 [email protected] &After starting autossh, in this example I would just set up a CI DB connection that points to 127.0.0.1 port 3307. It totally works, and I can switch between a local DB and one on the remote server anytime I want.