Extract table data from one bbdd to another

Question

Extract table data from one bbdd to another

Navigation

#1 by (2 votes)

0

I need to extract some data from some tables and be able to save them in other tables within a different database. And this must happen every so often to update the records of the destination table (it's the one I should use).

The truth is that I do not know how I should proceed in this case. I made the web with Laravel and it uses a Mysql database with the necessary structure for its operation. The thing is that this database is filled with data from another external database.

I was thinking of an api to extract the data and to do the updates to each table manually with laravel, but it is what has occurred to me.

What is the correct way to proceed? The way I came up with is right or wrong?

mysql laravel

asked by kmilo93sd 07.03.2018 в 06:16

source

1 answer

add text to the beginning of my text file how to change the image of a carousel

score 2 · Answer 1

Well this is possible in laravel.

1º You must configure your file in config > database.php

'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,
     ],

    'yoursql'  => [
         'driver'     => 'mysql',
         'host' => env('YOURSQL_HOST', '127.0.0.1'),
         'port' => env('YOURSQL_PORT', '3306'),
         'database' => env('YOURSQL_DATABASE', 'forge'),
         'username' => env('YOURSQL_USERNAME', 'forge'),
         'password' => env('YOURSQL_PASSWORD', ''),
         'unix_socket' => env('YOURSQL_SOCKET', ''),
         'charset' => 'utf8mb4',
         'collation' => 'utf8mb4_unicode_ci',
         'prefix' => '',
         'strict' => true,
         'engine' => null,
     ],
],

Once both databases are configured, if you notice they have 2 different names.

-mysql
-yoursql

Remember to configure in the ENV file

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=base_de_datos_principal
DB_USERNAME=usuario_base_datos_principal
DB_PASSWORD=pass_usuario_base_datos_principal


YOURSQL_CONNECTION=mysql
YOURSQL_HOST=127.0.0.1
YOURSQL_PORT=3306
YOURSQL_DATABASE=base_de_datos_secundaria
YOURSQL_USERNAME=usuario_base_datos_secundaria
YOURSQL_PASSWORD=pass_usuario_base_datos_secundaria

Once the databases have been configured, you should bear in mind that:

-When you do not specify to laravel which connection you should use, by default you will use the first "mysql" database

Now you just have to specify the connection ::connection('yoursql')->where()....

Also, if a model were to use practically only the 2nd database, it is more practical to indicate to the model which connection should be made, than to specify the database in each query.

This you can do in the model

<?php namespace App;

use Illuminate\Database\Eloquent\Model;

class TUMODELO extends Model {

    // Conexion que va a usar este modelo
    protected $connection = 'yoursql';

    ...

}

Now, simply specify the connection to extract the data.

And then you specify the connection to upload the data. "voila", multiple connection.