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

Question

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

Navigation

#1 by (1 votes)

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.

mysql stored-procedure

asked by Aritzbn 24.04.2018 в 12:23

source

1 answer

"sun.net.www.protocol.ftp.FtpURLConnection can not be cast to java.net.HttpURLConnection" when connecting to an FTP server in Eclipse How can I send a PDF file to a printer by "LPR" from vb.net? [closed]

score 1 · Accepted Answer

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:password@hostname: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