MySQL Cross Server Select Query

by

how about using federated tables on one of the servers?
create the federated tables based on the remote tables you will use in the query and just run your query as if your database was all local. example below from MySQL site

The procedure for using FEDERATED tables is very simple. Normally, you have two servers running, either both on the same host or on different hosts. (It is possible for a FEDERATED table to use another table that is managed by the same server, although there is little point in doing so.)

First, you must have a table on the remote server that you want to access by using a FEDERATED table. Suppose that the remote table is in the federated database and is defined like this:

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 
CHARSET=latin1;

The example uses a MyISAM table, but the table could use any storage engine.

Next, create a FEDERATED table on the local server for accessing the remote table:

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
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';

(Before MySQL 5.0.13, use COMMENT rather than CONNECTION.)

More Related Contents:

  1. ERROR: Loading local data is disabled – this must be enabled on both the client and server sides
  2. MySQL – Get row number on select
  3. How to use GROUP_CONCAT in a CONCAT in MySQL
  4. MySQL query / clause execution order
  5. How can I tell when a MySQL table was last updated?
  6. Operand Should Contain 1 Column – MySQL NOT IN
  7. Error Code: 1005. Can’t create table ‘…’ (errno: 150)
  8. Does adding ‘LIMIT 1’ to MySQL queries make them faster when you know there will only be 1 result?
  9. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  10. How to use LOAD_FILE to load a file into a MySQL blob?
  11. LOAD DATA INFILE easily convert YYYYMMDD to YYYY-MM-DD?
  12. MyIsam engine transaction support
  13. SQL Query to get column values that correspond with MAX value of another column?
  14. MySQL : transaction within a stored procedure
  15. When to use SELECT … FOR UPDATE?
  16. BULK INSERT in MYSQL
  17. Setting column values as column names in the SQL query result
  18. MYSQL sum() for distinct rows
  19. Implementing a hierarchical data structure in a database
  20. MySQL date format
  21. How to get current date & time in MySQL?
  22. Laravel join with 3 Tables
  23. phpMyAdmin – can’t connect – invalid setings – ever since I added a root password – locked out
  24. How do I split the output from mysqldump into smaller files?
  25. How to regex in a MySQL query
  26. How to remove duplicate entries from a mysql db?
  27. MySQL Group By Hours
  28. Syntax error on MERGE statement
  29. Strip last two characters of a column in MySQL
  30. mysql double-quoted table names

Leave a Comment