Is it possible for a stored procedure to work with two databases?

4

I've been searching but I have not found exactly what I'm looking for.

I have two databases, let's call them Base1 and base 2 and a stored procedure that executes in base 1 , this base has a series of mirror tables that look at base 2 . They are copies and redundant information.

base 1 is in say that ip 10.1.1.1 and base two in ip 10.2.2.2 .

What I want is for my stored procedure to do something like this:

BEGIN
DECLARE valor int Default 0;

Select count(*) into valor
From base2.tabla

Insert into base1.tabla
values(valor)

END

To give an example.

In this link puts something like this

I wonder if I could do something kind

FROM  ip:3306.base2.tabla

Something that would allow me to get data of base 1 and put them in base 2 to eliminate that redundancy of data.

    
asked by Aritzbn 24.04.2018 в 12:23
source

1 answer

1

According to the official documentation, a form that could answer your question is the use FEDERATED storage engine :

  

The FEDERATED storage engine lets you access data from a remote MySQL   database without using replication or cluster technology. Querying a   local FEDERATED table automatically pulls the data from the remote   (federated) tables. No data is stored on the local tables.

That is:

  

The FEDERATED storage engine allows you to access the data   from a remote MySQL database without the need to use   replication technology or cluster. The query of a local table   FEDERATED automatically extracts data from remote tables   (federated). No data is stored in the local tables.

How to use it?

  • In the first place, this functionality is not enabled by default, to do it you have to start the server with the --federated parameter. We can verify if we have the "engine" doing: show variables like '%federated%'; .

  • Suppose now that on our remote server we have the following table:

    CREATE TABLE mitabla (
        id     INT(15) NOT NULL AUTO_INCREMENT,
        Texto  VARCHAR(100) NOT NULL
        PRIMARY KEY  (id)
    )
    ENGINE=MyISAM
    DEFAULT CHARSET=latin1;
    

    It's an example, the design can obviously be any other. On our server we will create a "federated" table identical in structure to the previous one

    CREATE TABLE base1_mitabla (
        id     INT(15) NOT NULL AUTO_INCREMENT,
        Texto  VARCHAR(100) NOT NULL
        PRIMARY KEY  (id)
    )
    ENGINE=FEDERATED
    DEFAULT CHARSET=latin1
    CONNECTION='mysql://username:[email protected]:port/database/tablename';
    

    Obviously replace the values of CONNECTION with those that apply in your case.

  • If everything went as expected, we already have available in our current database a table base1_mitabla that we can use as if it were local, even as part of a JOIN . You should repeat the process with any other remote table, for example base2_mitabla (maybe the name of the table should contain the name of the host too)

  • Some important considerations

    • Any optimization by existence of indexes is done on the remote server, which is good, because then all the rows of the table between servers do not "travel", however, if the SELECT requires a TABLE SCAN , this will mean that the remote server will send the entire table to the local.
    • The remote server must be a MySQL
    • The remote table must exist before creating the "federated"
    • This model does not support transactions

    Related links:

    answered by 24.04.2018 / 20:09
    source