473,320 Members | 1,988 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Remote table in a database

Hi,

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, m.to FROM users u, messages m WHERE
u.login=m.login;

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?
Fritz

Feb 7 '06 #1
3 2273
<fr*********@web.de> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
The problem I'm trying to solve is: I have [two] 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.
. . . 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.


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
host).

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.

Regards,
Bill K.
Feb 7 '06 #2
>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.
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.
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.


You may pay a big performance penalty for that.

Gordon L. Burditt
Feb 8 '06 #3

Gordon Burditt schrieb:
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.


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.
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.


You may pay a big performance penalty for that.

Gordon L. Burditt


I'm using 3.2:(

Feb 8 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: iqbal | last post by:
Hi all, We have an application through which we are bulk inserting rows into a view. The definition of the view is such that it selects columns from a table on a remote server. I have added the...
3
by: Terry | last post by:
Id like to connect to a table in a different database in the same instance. Create database db1 Create database db2 Connect to db1.... Create table test1 ( Name varchar(10), age INT)
2
by: Douglas Harber | last post by:
If I have DB2 8.1 (FP5, I believe, but not relevant to my question...I hope) installed on my desktop, do I have what I need to connect to a remote DB2 server (also running 8.1 FP5) from my desktop...
4
by: banz | last post by:
Hello I have a problem to resolve: I wrote a Perlscript which caches data from a server (local on my machine) I would like to have a other connection to a remote server but I don't know how to...
6
by: Access Newbie | last post by:
I'm using Access 2000 and I'm trying to create a pass-through query to append all the data from a local table to a remote table. I'm using the SQL query editor in MS Access to create the query (I...
4
by: GAVO-UK | last post by:
Hello everyone. Using Access 2003 and MSDE I just started experimenting using Access as the front end for SQL using *.adp, and would like to connect the front end to the back end remotely,...
0
by: Bennett Haselton | last post by:
In Visual Studio .Net 2002 Server Explorer, if I add a new connection under "Data Connections", I can specify a remote server name and the username and password that I want to use, but then when...
2
by: Jeff | last post by:
I have an ASP.NET web page accessing a SQL database. I've used VS to build the app and stored it in the eNPTest02 directory of my localhost on my development machine. The database is on the web. ...
3
by: Pakna | last post by:
Hello, I have what may be a beginner's question regarding DB2. How does one access a remote table on a remote database via SQL? What is the command string, is there an equivalent of Oracle...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.