Databases linux MySQL networking OS php programming Quick Tips

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';

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/
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: 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 );