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> 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> 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> SELECT * FROM mydb.C;
