Geek asks and answers

I need to join a table from another database, and sometimes to another server

My project has its own database. In addition, I use a user table that resides in a different database. Two offices have their data on one server, but the third has its own user table on another server.

So, in many queries I need to join any some_db.users or other_server.some_db.users table

What solution would you recommend for this scenario?

I am using MySQL.

+5
source share
3 answers

In MySQL Federated Tables :

FEDERATED MySQL . FEDERATED () . .

, , FEDERATED. , sakila :

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL
    PRIMARY KEY  (id)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

FEDERATED :

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL
    PRIMARY KEY  (id)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user:fed_user@197.186.1.199:3306/sakila/test_table';

:

CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'

, , ENGINE FEDERATED.

:

show variables like '%federated%';

, FEDERATED .

federated_table localhost test_table .

JOIN localhost. localhost test, sakila.test_table, , , :

SELECT * FROM `federated_table` JOIN `test`;

federated_table test_table .

FEDERATED Storage Engine

; FEDERATED, MySQL, --federated.

:
, --skip-grant-tables.

db , :

110318 21:37:23 [ERROR] /usr/local/libexec/mysqld: unknown option '--federated'

, , , 5.x , . --skip-grant-tables --federated, --skip-grant-tables --federated.

: FEDERATED Storage Engine

+13

MySQL , ,

`database_name1`. `table_name1` JOIN `database_name2`.`table_name2`

, , , , , .

() . . MySQL GUI, SQLyog MySQL admin, .

, ....

0

More articles:


All Articles