MySQL - FEDERATED

Why use MySQL FEDERATED ?

Sometime that you need to access the remote server's table at local database, however you don't want to duplicated the data at two databases (remote and local). Therefore, you have another choice - MySQL FEDERATED storage engine.

In addition,  you can grant the specific permission for specific account at remote database for this purpose.
Whatever, create a local table with federated storage engine, it just likes a local proxy table; any SQL statement will be executed at remote table.

How to do ?

• Check the MySQL engine:
  • mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |

| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |

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

| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |

| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |

| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |

| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |

| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

• Enable federated. 
  • Use mysql console to install ha_federated.so
    • mysql>install plugin federated soname 'ha_federated.so';
  • Modify my.cnf. Add ‘federated’ under [mysqld]

[mysqld]

federated

• Configure Remote MySQL Server
  • Grant permission 

mysql> GRANT ALL on DB_NAME.* TO  user@‘local_host' IDENTIFIED BY ‘your password';

For example:

mysql> GRANT ALL on ViewDBTest2.* TO root@'172.16.66.143' IDENTIFIED BY '111111';

• Configure Inbound rule of Remote MySQL Server

shell>  sudo /sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

• Create View Table at Local Database

Create Table `Local_Table_Name` (

      `admin_id` int(11) NOT NULL AUTO_INCREMENT,
      `email` varchar(255) NOT NULL,
      `password` varchar(128) NOT NULL,
      `role` varchar(16) NOT NULL,
      PRIMARY KEY (`admin_id`),
      UNIQUE KEY `UK_jl20d0ecx48g7qwy1dxe2akre` (`email`)
) ENGINE=FEDERATED

DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

CONNECTION='mysql://user:password@remote_mysql_host:port/DB_NAME/Remote_Table_Name';

Note: The columns definition in Local_Table_Name and Remote_Table_Name should be the same!

Something you need to know

• The federated has some performance issue at the Oracle MySQL.

• The data is not existed at the local table; any SQL statement is executing at remote table. 

• The schema changed of remote table would be an issue, because the local table will not be notified.

Reference:

• https://dev.mysql.com/doc/refman/5.7/en/federated-storage-engine.html