Remote table in a database

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

is it possible to create remote virtual tables in mysql? By that I mean
if it is possible to define a table, which actually resides on a remote
mysql server, but which can be accessed and addressed as if the table
would be on the local database.

My intention is to move one table to a remote host, but to be able to
address the table as if it was still on the same machine.

So for example:

SELECT m.header, m.body, m.from, FROM users u, messages m WHERE

would still work, even though the table "users" resides in the database
"moon" on a different host, than the table "message", which resides on
the localhost in the database "alltables".

The problem I'm trying to solve is: I have to webapplications, which
both have a table, which stores login information. Those tables have to
be combined into one and have to be put on one of the hosts only.

One of the application will access the remote user table for logins.
Since many of the SELECTS of this application contain joins like the
example above I'm looking for a way in which I do not have to modify
the selects but can move the login table to a different machine.

Any suggestions?

Re: Remote table in a database

Quoted text here. Click to load it

I don't think this is supported.  MySQL permits joins between tables in
different databases, but as far as I know they both have to be under the
control of the same MySQL instance.  That is, the following works:

select ... from db1.table1, db2.table2 where ...

But only if databases db1 and db2 are both on the same host (and under the
same MySQL instance, if you are running multiple MySQL daemons on the same

There doesn't seem to be any syntax like "servername.databasename.tablename"
or anything.  The USE command in the mysql command-line tool doesn't support
a server name argument, either.

One possibility you might look into is to use MySQL's replication feature to
copy the login table from the remote host to the local host, and just treat
it as read-only on the local host.  You can even put it in a separate
database on the local host if it makes it easier to set up the replication.

Bill K.

Re: Remote table in a database

Quoted text here. Click to load it

Yes.  Look at the FEDERATED storage engine.  I think you have to
have at least MySQL 5, it needs to be compiled in and it isn't
by default, and it's limited by things like no transactions.
I seem to recall reading that it pretty much had to fetch the
entire table every time.

Quoted text here. Click to load it

You may pay a big performance penalty for that.

                    Gordon L. Burditt

Re: Remote table in a database

Gordon Burditt schrieb:

Quoted text here. Click to load it

I'm using 3.2:(

Site Timeline