Mysql Local Database and Online Server Connection

Question

I’m developing an application using Java Swing + Mysql database, but I’m analyzing making an online platform that talks to the local application, using the database to interconnect the data, because in reality the system itself did not talk and the banks.

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

Answer 1

What you want is the resource of Federated tables mysql. This feature is available from Mysql 5.

With it it is possible to 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 at the local bank, recreate the same table structure, changing the Engine for Federated and inserting the remote bank path into 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 string of Connection, it is possible to create a nickname for that server via the command CREATE SERVER:

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