fetching externally hosted db's table from within the local mysql server Let's say we have a mysql database on localhost with table A and table B.

Is it possible to fetch details from a 3rd party mysql server to which we have login info and which allows external access?

Let's say there are 3 tables in the database we can connect to - table A2, B2 and C2. 

How do you fetch the rows from table C2 and make it VIRTUALY accessible (as table C) from within the original mysql server (just like A and B), just like if it was hosted on it?
I normally would not recommend this but here it goes...

If the tables on the remote DB Server are MyISAM, try using the http://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html">FEDERATED Stroage Engine. First starters, find out if the FEDERATED Storage Engine is Enabled. This what I get running SHOW ENGINES; in MySQL 5.5.12 for Windows:

mysql&gt; show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)


In MySQL 5.5.12 for Windows it is not enabled

So, I added this to my.ini

[mysqld]
federated


I then restarted MySQL on my machine

mysql&gt; show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.02 sec)


Now, it is running !!!

On your remote server perform this query:

SHOW CREATE TABLE C2\G


This will show you the create table statement for C2.

Now just append the URL of the remote table usign the CONNECTION clause.

According to the MySQL Documentation

For that link


  The format of the connection string is as follows: 
  scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
  Where: 
  
  • scheme: A recognized connection protocol. Only mysql is supported as
  the scheme value at this point. 
  
  • user_name: The user name for the connection. This user must have
  been created on the remote server, and must have suitable privileges
  to perform the required actions (SELECT, INSERT, UPDATE, and so forth)
  on the remote table. 
  
  • password: (Optional) The corresponding password for user_name. 
  
  • host_name: The host name or IP address of the remote server. 
  
  • port_num: (Optional) The port number for the remote server. The
  default is 3306.
  
  • db_name: The name of the database holding the
  remote table. 
  
  • tbl_name: The name of the remote table. The name of the local and
  the remote table do not have to match.


Sample connection strings: 

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


Suppose that table C2 in the mydb database looks like this:

CREATE TABLE C2
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(32),
    PRIMARY KEY (id)
)
ENGINE=MyISAM;


Just append the CONNECTION clause and run it on the Local Server:

use mydb
CREATE TABLE C
(
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(32),
    PRIMARY KEY (id)
)
ENGINE=FEDERATED
CONNECTION='mysql://username:password@hostname/mydb/C2';


Finally, in MySQL on your local machine, you should be able to access the C2 table like this:

mysql&gt; SELECT * FROM mydb.C;