SSH port forwarding/tunneling for MySQL connection

Create an 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

Add MySQL permissions in the remote MySQL database to allow user connections from localhost.

USE mysql;
GRANT ALL ON db.* TO [email protected].0.0.1 IDENTIFIED BY 'database_pass';
FLUSH PRIVILEGES;

Now, on the local server (as root) create an RSA key pair to avoide the need for passwords for remote_user. (Simply hit enter for each question encountered.)

ssh-keygen -t rsa

Now transfer the public key file to the remote server from your local server.

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 the local server, create an SSH tunnel to the remote MySQL database server using the following command.

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

To use this from PHP you would simply do this:

$remote_server_mysql = mysql_connect( "127.0.0.1", "database_user", "database_pass" );
mysql_select_db( "database", $remote_server_mysql );

Published by

Adam

Application Architect from Florida

One thought on “SSH port forwarding/tunneling for MySQL connection”

  1. Great post. I’m going to try to use this info to make a tunnel to my host’s DB from a Local MySql GUI. If it works, I’ll let you know. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *