[HOWTO] Port Forwarding/Tunneling MySQL thru SSH

[asterisk]

First, one creates a shell account on the remote MySQL database server.


useradd -s /bin/false remote_user
mkdir /home/remote_user/.ssh
touch /home/remote_user/.ssh/authorized_keys
chown -R remote_user:remote_user /home/remote_user/.ssh
chmod 755 /home/remote_user/.ssh
chmod 600 /home/remote_user/.ssh/authorized_keys


Then one updates MySQL on the remote MySQL database server to accept connections from localhost.


USE mysql;
GRANT ALL ON db.* TO database_user@127.0.0.1 IDENTIFIED BY 'database_pass';
FLUSH PRIVILEGES;


On one's local server as root, one then creates an RSA key pair to avoid the need for passwords for remote_user.


ssh-keygen -t rsa


Tap enter upon each question encountered. Then transfer the public key file to the remote server from your local server still.


scp /var/root/.ssh/id_rsa.pub root@remote_server.com:/tmp/local_server.local_rsa.pub
ssh remote_server.com
cat /tmp/local_server.local_rsa.pub >> /home/remote_user/.ssh/authorized_keys


On one's local server, one then creates a SSH tunnel to the remote MySQL database server using the following command. Port 6603 on one's local server is forwarded to Port 3306 on the remote server.


ssh -fNg -L 6603:127.0.0.1:3306 remote_user@remote_server.com


Then, one writes the appropriate code to connect the local MySQL client to the port to be forwarded, in this case 6603, for the web application in a language of their choice. In PHP this would look something like the following.


<?php
...
$remote_server_mysql = mysql_connect( "127.0.0.1:6603", "database_user", "database_pass" );
mysql_select_db( "database", $remote_server_mysql );
...
?>

[vps-vince]

[plum in mouth] One is most impressed [/plum out of mouth] :D

Well done and thanks for taking the time.

- Vince

[nadzri]

Well done and thanks for taking the time.
Yes, thanks very much asterisk! :)