Access Remote MySql Data

I was working on a task where I need to write ETL between 2 mysql servers. I was thinking about MSSQL linkedserver nd Oracel DbLink. But  nothing like that exist in Mysql. :(

Mysql offer such connection with Federated Storage option. It has pros and cons and one should use it carefully. Baron Schwartz wrote a worth reading article on this subject.You should read this for in depth understanding.

My objective today is to communicate a step-by-step guide to save your time and to utilize your time more effectively :)

Remote server refers to the instance where I want to read data, Local server is machine where I want to use remote data.


#########################################################
## Enable Federated Storage
#########################################################
Federated storage should be enable on both remote and local server.

# Verify whether Federated engine is enable or not

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | 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         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

As it is not enable. We need to enable it first.

#Enable Federated Storage
Edit your /etc/my.cnf global server configuration file and under [mysqld] stanza section, add the line:

federated

#Verify if it is enable
mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | 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         |
| FEDERATED  | YES      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)


################################################
# CREATE AND POPULATE TABLE ON REMOTE SERVER
################################################
USE test;

CREATE TABLE tbl_remote(
   id INT NOT NULL PRIMARY KEY,
   num INT NOT NULL
);

INSERT INTO tbl_remote
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,2 UNION ALL
SELECT 4,2 UNION ALL
SELECT 5,3 ;

SELECT * FROM tbl_remote;

################################################
# CREATE FEDERATED TABLE ON LOCAL SERVER
################################################
Table creation syntax is same except following. Once you have mentioned those, you can select remote server data.
ENGINE=FEDERATED
CONNECTION='mysql://user:pwd@Remote-IP/remote-db-name/remote-table-name';

USE test;

CREATE TABLE tbl_local (
 id INT NOT NULL PRIMARY KEY,
 num INT NOT NULL
) ENGINE=FEDERATED
 CONNECTION='mysql://user:pwd@Remote-IP/remote-db-name/remote-table-name';

mysql > SELECT * FROM tbl_local;
+----+-----+
| id | num |
+----+-----+
|  1 |   1 |
|  2 |   1 |
|  3 |   2 |
|  4 |   2 |
|  5 |   3 |
+----+-----+
5 rows in set (0.00 sec)

You can join remote table to local table(s) for any operation.

 Enjoy!