30

I have a couple of remote databases I would like to access, but they are sitting on a server accessible only through SSH with a key.

In Sequel Pro, I connect to this remote DB something like this: enter image description here

How would I configure my Laravel app to connect to such a DB?

'mysql_EC2' => array(
        'driver'    => 'mysql',
        'host'      => '54.111.222.333',
        'database' => 'remote_db',
        'username' => 'ubuntu',
        'password' => 'xxxxxxxxxxxxxxxxxxxx',
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
    ),
egekhter
  • 2,067
  • 2
  • 23
  • 32

3 Answers3

51

Here's a workable solution of working with a database hosted on an EC2 instance via SSH w/ a key.

First, setup a corresponding connection in your database config:

'mysql_EC2' => array(
        'driver'    => 'mysql',
        'host'      => '127.0.0.1:13306',
        'database' => 'EC2_website',
        'username' => 'root',
        'password' => 'xxxxxxxxxxxxxxxx',
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
    ),

Second, establish a tunnel:

ssh -i ~/dev/awskey.pem -N -L 13306:127.0.0.1:3306 ubuntu@54.111.222.333

(we pass in the SSH key to the i parameter and establish an SSH connection, binding to port 13306)

Third, use the DB how you normally would in a Laravel App:

$users = DB::connection('mysql_EC2')
        ->table('users')
        ->get();

var_dump($users);
Dave
  • 12,117
  • 10
  • 46
  • 52
egekhter
  • 2,067
  • 2
  • 23
  • 32
  • 1
    The tunnel command for some reason didn't work for me with the -N option. Also there's a type there for the local host. This command (-L joined with remote port or not, doesn't matter) worked for me: `ssh -i PATH_TO_YOUR_KEY -L13306:127.0.0.1:3306 remoteuser@111.222.111.222` – Vexter Dec 10 '14 at 10:12
  • 1
    Is there a way to automate the execution of the script? Or create an artisan command such like "artisan tunnel"? – Alain1405 Feb 11 '15 at 10:44
  • This worked for me, but I had to set the port separately with `'port' => '13306'`. Using Laravel 5.1, thanks! – jstudios Jul 20 '15 at 15:46
  • 1
    Thank you! This answer got me on the right track. I finally figured it out here: http://stackoverflow.com/questions/464317/connect-to-a-mysql-server-over-ssh-in-php#comment65229024_16138417 – Ryan Aug 13 '16 at 19:59
  • Saved me so much frustration. just a note to anyone else reading this in the future, you can also use this for postgres using the same method (with the appropriate port, of course) – CJ Thompson Jan 27 '17 at 23:42
  • When you say, "Second, establish a tunnel," do you mean that I have to run that command separately and manually from the console? Is there anyway to automate that? Thanks. – HartleySan Jan 24 '19 at 18:55
24

I wrote a Laravel Package to handle for us. stechstudio/laravel-ssh-tunnel

composer require stechstudio/laravel-ssh-tunnel

register the TunnelerServiceProvider::class and set up the configuration in your .env.

jszobody
  • 28,495
  • 6
  • 61
  • 72
bubba
  • 3,839
  • 21
  • 25
3

In case the ssh server is just the tunnel and the db is not located there. You can use this.

ssh -i ~/path/your-key.pem -N -L 13306:your-db-hostname.com:3306 your-ssh-tunnel-username@your-tunnel-hostname.com

And set the db hostname in your laravel .env or database config file to 127.0.0.1 port 13306

Park Keeper
  • 520
  • 4
  • 6