connect remotely  via SSH using PDO MySQL

Victor Kimura
Victor Kimura used Ask the Experts™
on
Hi,

I'm trying to connect via SSH using PDO MySQL. With my shared bluehost account I connected via my SSH login username, pass and connect to the db box but I have a VPS with godaddy. I'm wondering how do I connect remotely to my db using SSH.

self::$instance = new PDO("mysql:host=173.201.24.210;port=2083;dbname=db_name", 'username', 'password');

Open in new window


To connect via the web browser to my PHPMyAdmin I connect via:
https://173.201.24.210:2083
That's my url for my cPanel for this domain.

I have a connection via SSH and the connection is remote through my MySQL IDE but I don't know how to do this using PDO.

For some reason I cannot do a regular connection like

self::$instance = new PDO("mysql:host=emailmarketingservicesco.com;dbname=db_name", 'username', 'password'');

Open in new window


I tried using doing a regular connection (i.e. non SSH) via my MySQL IDE and it cannot connect either. So I can't test the PDO via a regular connection (for now anyway).

The Remote MySQL IP has been whitelisted via cPanel already. It's a VPS host so I'm thinking perhaps there needs to be some additional setting in WHM but I'm not certain.

Thanks,
Victor
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Make sure that the mysql user you are trying to connect with has permission to the database from anywhere or the IP you are trying to access it from,  not just the local machine...

I am not sure how you do that with your setup. But if you can SSH to the machine that has the database you should be able to access MySQL and run a grant command to allow access to that database from your location.

So instead of

GRANT ALL ON db1.* TO 'username'@'localhost';

you would do

GRANT ALL ON db1.* TO 'jeffrey'@'youripaddress';


or maybe if you only need select permissions, just grant SELECT instead of all...



Victor KimuraSEO, Web Developer

Author

Commented:
Hi jrm213jrm213,

Thanks, I managed to connect remotely via regular (i.e. not SSH) MySQL connection username and pass using port 3306. It's not a secured connection and I have it working for both my MySQL IDE (dbforge) and via PDO PHP.

But the secure connection does not connect. It just hangs at this line:
self::$instance = new PDO("mysql:host=173.201.24.210;port=2083;dbname=db_name", 'username', 'password');

I'd like to learn how to do this with a secure connection with my PDO PHP code.

Thanks,
Victor
Commented:
Are you trying to do a tunnel? I guess that you cannot use PDO to create a tunnel.
If you wish to do a Secure Connection (is a different thing, not a SSH) PDO can work with SSL.
Top Expert 2015

Commented:
To tunnel any TCP application you need to forward local port (3306) to remote 127.0.0.1:3306 (-L 3306:127.0.0.1:3306) and connect DBconnection to localhost while SSH connection is active.

I tend to agree with NoiS opinion that you mistook SSH with SSL, the later you need to enable in mysqld.ini after generating SSL server key.
not sure if this affects you but apparently support for PDO connection with SSL was not available until PHP 5.3.6 which is the most-current stable release of PHP. It was only released a couple of months ago so it is possible you have an earlier version. I am unsure though if upgrading your PHP version would help with this particular issue or not.
Victor KimuraSEO, Web Developer

Author

Commented:
Hi fellas,

Okay. I guess I would need PHP 5.3.6 for PDO to work with SSL. Hmm...I'll look into upgrading later then.

So if I upgraded to  PHP 5.3.6 I can use SSL. Then in that case I would port the forward to remote 127.0.0.1:3306. What's the command or where is the configuation file for it? What would I change?

Do you know what I would look for int the mysqld.ini file?

OR perhaps I can use mysqli to connect via a tunnel using SSH? Just wondering what are the options.

Thanks,
Victor
from this comment on php.net: http://php.net/manual/en/ref.pdo-mysql.php#103501

Today's PHP snapshot now has SSL support for PDO. Follow the directions here ( http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html ) to set up MySQL and then use the following connection options:

<?php
$pdo = new PDO(
    'mysql:host=hostname;dbname=ssldb',
    'username',
    'password',
    array(
        PDO::MYSQL_ATTR_SSL_KEY    =>'/path/to/client-key.pem',
        PDO::MYSQL_ATTR_SSL_CERT=>'/path/to/client-cert.pem',
        PDO::MYSQL_ATTR_SSL_CA    =>'/path/to/ca-cert.pem'
    )
);
?>
Victor KimuraSEO, Web Developer

Author

Commented:
Thank you. That's really helpful. =)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial