Connection MySQL Local Database and Online Server

Question

Connection MySQL Local Database and Online Server

Navigation

#1 by (4 votes)

3

I am developing an application using Java Swing + MySQL database, however I am in analysis of making an online platform that talks to the local application, using the database to interconnect the data, because in reality the system itself they talked, but the banks.

I would like to know if MySQL has such a function and what should I look for to be able to do this interaction, I would also like to know if it is more common and correct to do using inter-bank communication or to do between systems?

mysql java

asked by anonymous 02.09.2014 / 18:47

1 answer

Dictionary in C always returns "word not found" SQL Group query with more than one distinguished attribute

score 4 · Accepted Answer

What you want is the Federated tables feature of MySQL. This feature is available from MySQL 5.

With it you can interconnect two different MySQL databases as follows:

Create the remote table

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=MyISAM

And in the local bank, recreate the same table structure by changing Engine to Federated and inserting the remote bank path in Connection

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

As an alternative to the Connection connection string, you can create a nickname for this server using the CREATE SERVER command:

CREATE SERVER remote
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL DEFAULT '',
    other  INT(20) NOT NULL DEFAULT '0',
    PRIMARY KEY  (id),
    INDEX name (name),
    INDEX other_key (other)
)
ENGINE=FEDERATED
CONNECTION='remote';

References

How to Use FEDERATED Tables

CREATE SERVER Syntax