MySQL via SSH for PHP

I was moving a site from one server to another which has a lot of database transactions, and I did not want to lose any. So at 2am I started the move, at this time the traffic is fairly quiet. Then I realised that neither hosts allowed remote access to MySQL without their super admin’s help. I didn’t like the idea of trying to sync the databases up after the fact so I created an SSH tunnel to the new DB. This meant I could have the old server’s PHP talking to the new server’s MySQL until the DNS propagation was complete.

To do so:

1. I exported a MySQL dump from the old DB server and upload it to the new server

2. In terminal (PuTtY for Windows fans) I SSH into the old server and from there created a port (3377) on the old server which pointed to the new server

[cc lang=”bash”]ssh -L 3377:127.0.0.1:3306 a_user@example.com[/cc]

3. I wanted to see if it worked so I run MySQL in a new terminal window (the existing terminal window needs to remain open, as the switch over of A-Record will not take long I am not worried)

[cc lang=”bash”]mysql -u dbuser -p -h 127.0.0.1 -P 3377[/cc]

4. MySQL on the old server was able to connect to the MySQL instance on the new server

5. Finally I needed PHP on the old server to connect to the MySQL on the new server while the propagation period is taking place

[cc lang=”PHP”]$db = mysqli_connect(‘127.0.0.1’, ‘dbuser’, ‘password’, ‘the_db’, 3377);[/cc]

And that is it!