mysqlsshlaravel-4

Laravel MySql DB Connection with SSH


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'    => '',
    ),

Solution

  • 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);