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] IDENTIFIED BY 'database_pass';

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/ [email protected]
cat /tmp/ >> /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: [email protected] sleep 9999

To use this from PHP you would simply do this:

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

One response to “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!