Cross server Database operations

To perform cross-server database operations in Laravel, you can configure multiple database connections in your config/database.php file and then use these connections in your models or queries. Here’s a step-by-step guide to achieve this:

1. Configure Multiple Database Connections:

   Open your config/database.php file and add a new connection for the remote database. For example:

   'connections' => [
       'mysql' => [
           'driver' => 'mysql',
           'host' => env('DB_HOST', '127.0.0.1'),
           'port' => env('DB_PORT', '3306'),
           'database' => env('DB_DATABASE', 'forge'),
           'username' => env('DB_USERNAME', 'forge'),
           'password' => env('DB_PASSWORD', ''),
           'unix_socket' => env('DB_SOCKET', ''),
           'charset' => 'utf8mb4',
           'collation' => 'utf8mb4_unicode_ci',
           'prefix' => '',
           'strict' => true,
           'engine' => null,
       ],
   
       'remote_mysql' => [
           'driver' => 'mysql',
           'host' => env('REMOTE_DB_HOST', 'remote_host'),
           'port' => env('REMOTE_DB_PORT', '3306'),
           'database' => env('REMOTE_DB_DATABASE', 'remote_db'),
           'username' => env('REMOTE_DB_USERNAME', 'remote_user'),
           'password' => env('REMOTE_DB_PASSWORD', 'remote_password'),
           'unix_socket' => env('REMOTE_DB_SOCKET', ''),
           'charset' => 'utf8mb4',
           'collation' => 'utf8mb4_unicode_ci',
           'prefix' => '',
           'strict' => true,
           'engine' => null,
       ],
   ],
   

   Make sure to add the corresponding environment variables in your .env file:

   REMOTE_DB_HOST=remote_host
   REMOTE_DB_PORT=3306
   REMOTE_DB_DATABASE=remote_db
   REMOTE_DB_USERNAME=remote_user
   REMOTE_DB_PASSWORD=remote_password
   

2. Using the Remote Connection:

   You can use the remote connection in your models or directly in your queries. Here’s an example of how to insert records into a table on the remote server:

   use Illuminate\Support\Facades\DB;
   
   class SomeController extends Controller
   {
       public function insertIntoRemoteDatabase()
       {
           $data = [
               'column1' => 'value1',
               'column2' => 'value2',
               // Add other columns and values as needed
           ];
   
           DB::connection('remote_mysql')->table('your_table')->insert($data);
   
           return response()->json(['message' => 'Data inserted successfully']);
       }
   }
   

3. Using Eloquent with Remote Connection:

   If you prefer using Eloquent, you can define a model that uses the remote connection:

   namespace App\Models;
   
   use Illuminate\Database\Eloquent\Model;
   
   class RemoteModel extends Model
   {
       protected $connection = 'remote_mysql';
       protected $table = 'your_table';
   
       // Define other model properties and methods as needed
   }
   

   Then, you can use this model to insert records:

   use App\Models\RemoteModel;
   
   class SomeController extends Controller
   {
       public function insertIntoRemoteDatabase()
       {
           $data = [
               'column1' => 'value1',
               'column2' => 'value2',
               // Add other columns and values as needed
           ];
   
           RemoteModel::create($data);
   
           return response()->json(['message' => 'Data inserted successfully']);
       }
   }
   

By following these steps, you can securely and efficiently perform cross-server database operations in your Laravel application.