How to Connect Remote Database With SSH Tunnel In Laravel?

Laravel provides a .env file for connecting different types of databases. In this tutorial, I will show you how to connect a remote MySQL database with an ssh tunnel in laravel application. I will show you step by step so that you can make it very easily without any confusion.

Sometimes we need to connect the server database to another server or in the local environment at that time we can use an ssh tunnel to connect the remote server database. ssh tunnel will allow connecting databases using the port. so let's follow bellow steps:

Step 1: Open SSH Tunnel

We can open the ssh tunnel using the ssh command below:

SYNTAX

ssh -N -L 13306:127.0.0.1:3306 [USER]@[SERVER_IP]

 

Now see the example code:

ssh -N -L 13306:127.0.0.1:3306 root@111.111.111

 

Example with SSH Key:

ssh -i ./path/to/id_rsa -N -L 13306:127.0.0.1:3306 root@111.111.111

 

Now we need to reliably keep an SSH tunnel open in the server. So run the below command:

ssh -f -N -L 13307:127.0.0.1:3306 user_name@server_ip

 

Step 2: Add MySQL Configuration

Next, we need to add another MySQL configuration to the database.php file. so let's add the server MySQL database username and password on the .env file and use it in the database.php file:

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your_db_name_here
DB_USERNAME=your_db_username_here
DB_PASSWORD=your_db_password_here

 

Now update the database.php file like:

config/database.php

'server_mysql' => [
    'driver' => 'mysql',
    'host' => env('SERVER_DB_HOST', '127.0.0.1'),
    'port' => env('SERVER_DB_PORT', '3306'),
    'database' => env('SERVER_DB_DATABASE', 'forge'),
    'username' => env('SERVER_DB_USERNAME', 'forge'),
    'password' => env('SERVER_DB_PASSWORD', ''),
],

 

Step 3: Fetch Remote Server Database

All are set to go now. Now we can fetch remote server data like:

routes/web.php

<?php

Route::get('/test', function () {
  
    $records = \DB::connection('server_mysql')
            ->table('products')
            ->get()
            ->toArray();
  
    dd($records);
});

 

Read also: How To Connect MySQL Database In PHP Laravel?

 

Conclusion

I have tried to discuss the clear concept of how to connect remote mysql database in laravel. Now we know laravel connect to remote database. Hope this how to connect remote mysql database in laravel tutorial will help you.