Laravel MySql DB Connection with SSH

Question

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

Dave · Accepted Answer · 2015-12-31 19:23:03Z

56

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 [email protected]

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

edited Dec 31, 2015 at 19:23

Dave

12.5k10 gold badges49 silver badges52 bronze badges

answered Aug 25, 2014 at 22:51

egekhter

2,2253 gold badges24 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Vexter Over a year ago

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 [email protected]

Alain1405 Over a year ago

Is there a way to automate the execution of the script? Or create an artisan command such like "artisan tunnel"?

jstudios Over a year ago

This worked for me, but I had to set the port separately with 'port' => '13306'. Using Laravel 5.1, thanks!

Ryan Over a year ago

Thank you! This answer got me on the right track. I finally figured it out here: stackoverflow.com/questions/464317/…

CJ Thompson Over a year ago

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)

|

jszobody · Accepted Answer · 2017-01-27 19:50:26Z

23

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.

edited Jan 27, 2017 at 19:50

jszobody

29k6 gold badges66 silver badges76 bronze badges

answered Jan 17, 2017 at 13:46

bubba

3,87223 silver badges25 bronze badges

2 Comments

Bagaskara Wisnu Gunawan Over a year ago

awesomely easy to use and works as expected, thanks for the package. 🎉

Mike Jan 3 at 12:09

No longer maintained and does not support Laravel 11

Park Keeper · Accepted Answer · 2021-02-26 02:23:29Z

4

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 [email protected]

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

answered Feb 26, 2021 at 2:23

Park Keeper

5905 silver badges6 bronze badges

1 Comment

Tharaka Dilshan Over a year ago

This is the clean answer when you have RDS databases (where your db is no on the ssh instance).

hamza ajaz · Accepted Answer · 2024-11-12 12:18:53Z

Here is the solution if you're using Laravel Sail/Docker with private key authorization to connect to a MySQL database over SSH.

FIRST STEP:

Run the below command:

ssh -i "YOUR_PUBLIC_KEY_PATH.pem" -N -L 13306:YOUR_DATABASE_HOST:YOUR_DATABASE_PORT SSH_USER@SERVER_ADDRESS

-N tells SSH not to execute remote commands.
-L forwards your local port which is 13306 to the mysql port
-i specifies the path of your private key.

Once you hit the command, you won't likely to get any response if the command is run successfully.

SECOND STEP

Update your laravel.test service in docker-compose.yml file to allow another port which is 13306

 ports:
       
        - "13306:13306"

Rebuild the containers

THIRD STEP:

Update the following entries in your .env file:

DB_HOST=host.docker.internal
DB_PORT=13306
DB_USERNAME="keep the same as your db username"
DB_PASSWORD="keep the same as your db password"

This setup will direct Laravel to connect to the forwarded local port 13306, allowing access to your remote database over SSH.

Collectives™ on Stack Overflow

Laravel MySql DB Connection with SSH

4 Answers 4

7 Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related