Execute one MySQL query on multiple databases

Question

I have a simple question that keeps me up all night :( .

Suppose we have 2 databases on different servers. You can easily create 2 connections and manipulate data in both databases.

What if I want to execute one query on both databases?

Example :

INSERT INTO database1.table1 
VALUES ( 'one','two','three') 
WHERE database1.table1.something LIKE (SELECT something from database2.table2)

Sorry for my code, I'm not really experienced with SQL code.

Such attempts are possible when the databases are all accessible through the same connection.

But is it possible to achieve the same result if the databases are on separate servers (when you need two connections) ?

It would be so much easier for me if such a solution would exist :( .

best regards

Database engines are not really designed to do this sort of thing. — Amber
– Amber, Commented Aug 25, 2010 at 17:34
linked servers have been around for as long as I can remember - oracle for instance. (10 yrs++) however, i'd be using something like MS enterprise services (think MTS) for 2 phase commit en.wikipedia.org/wiki/Two-phase_commit_protocol (corba also applies if you're that way inclined) — Jon Black
– Jon Black, Commented Aug 25, 2010 at 18:18

ircmaxell · Accepted Answer · 2010-08-25 17:35:44Z

1

Well, it's possible over one connection (Well, one connection from the client to one of the servers. There'll be connections from the server to the other servers). Just use the FEDERATED storage engine. It'll work the way you want (but beware it'll likely be quite slow due to all the added parsing and network traffic)...

answered Aug 25, 2010 at 17:35

ircmaxell

165k36 gold badges269 silver badges316 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

OMG Ponies Over a year ago

+1: I was just going to post the same thing - Oracle and SQL Server have the functionality to connect to other databases (including on other vendors). PostgreSQL 8.4 added dblink, but only supports connecting to other PostgreSQL instances...

ircmaxell Over a year ago

Yeah, MySQL's only supports other MySQL servers. But I thought I saw somewhere a plugin engine that supports ODBC. I've found FederatedX, but that's not really much...

Ziga Petek Over a year ago

Thx for the answer, I think that might just work. I tried it and it works exactly like the way I hoped. The speed is ok, I need it only for scheduled tasks on small tables anyway. Thx again

Collectives™ on Stack Overflow

Execute one MySQL query on multiple databases

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related