Yearout’s Portal Project

Connecting to MySQL

November 27, 2008 at 4:05 pm (Technical solution)

Ok so I am following chapter 14 of

“How to Do Everything: Facebook Applications (How to Do Everything Series)”
Jesse Feiler;

and attempting to connect my SQL database using the MySQL GUI tools (here).

Joyent does not allow remote connections to SQL except via a secure tunnel. This is done via http://discuss.joyent.com/viewtopic.php?id=383

(Courtesy of Mamash)

This is a solution to the dynamic-IP-yet-desperately-need-remote-access-to-MySQL problem. Works pretty fast (much faster than via phpMyAdmin) and is quite secure, too.

1. Get putty;
2. Run putty;
3. Enter the hostname that you use for normal SFTP access;
4. Select ‘SSH’ as Protocol;
5. Go to ‘Connection’ and enter your TXD username (one that has SFTP rights);
6. Go to Tunnels and enter 3306 as ‘Source port’ and ’127.0.0.1:3306′ as ‘Destination’, then click on ‘Add’;
7. Go back to ‘Session’, enter any name in ‘Saved Sessions’ and click on ‘Save’;
8. Click on Open;
9. The terminal window appears – enter your user password and press Enter;
10. Load up your favorite MySQL administration program, connect to 127.0.0.1 at port 3306 (the default) with your usual MySQL login. Enjoy!

Notes:

1. You don’t need shell access for this, it works fine with the ‘scponly’ wrapper that everybody’s using here for SFTP.
2. The session can be later reloaded by double clicking on its name under ‘Saved sessions’. It can also be called as a command line argument – see help for Putty.
3. If you already have MySQL installed on you localhost, choose any other port as ‘Source port’, but don’t forget to specify it in your MySQL front-end.
4. I’m sure this can be replicated on Macs, but I don’t have one. Anybody?

I can connect successfully to the database with the query browser but get the following error using MYSQL administrator:

“”Either the server service or the configuration file could not be found. Startup variables and service section are therefore disabled.”

There are discussions on MySQL forums here (here) that might help if you are running this locally but as mine is not this is not much help!

Never mind I have proven that we can read data via a SQL query remotely and I can execute SQL directly on the server via the web admin console via Joyent.

Next stop: working out how to remotely connect to the database programmatically and manipulate the data