Home / Table Types or Storage Engines / Federated Engine / Question No: 191

I want to use federated storage engine provided by MySQL. Can you help me to explain, how to create federated type table in MySQL?

The process for creating a federated table varies somewhat from that of other tables types provided by MySQL. The process is best illustrated by example. Suppose a table titled product resides in the corporate database on a remote server (call it "My_Remote_Server"). The table looks like this:

CREATE TABLE product (
             product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
             product_sku CHAR(8) NOT NULL,
             product_name VARCHAR(35) NOT NULL,
             product_price DECIMAL(6,2)
) ENGINE=MyISAM;

Now suppose that you want to access the above table from some other server (call it "My_Local_Server"). To do so, create the same table structure as on "My_Local_Server", with the only difference being that the table engine type should be FEDERATED rather than MyISAM. Additionally, connection parameters must be provided, which allows "My_Local_Server" to communicate with the table on "My_Remote_Server":

CREATE TABLE product (
             product_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
             product_sku CHAR(8) NOT NULL,
             product_name VARCHAR(35) NOT NULL,
             product_price DECIMAL(6,2)
) ENGINE=FEDERATED;
COMMENT='mysql://remoteuser:secret@192.168.1.101/corporate/product';

First, the user identified by username remoteuser and password secret must reside within the MySQL database found on "My_Remote_Server". Second, keep in mind that this information will be transmitted over a possibly unsecured network to "My_Remote_Server", it's possible for anyone to capture not only the authentication variables but also the table data. So you should also make some measurements to escape from this side.

Once it is created, you can access the "My_Remote_Server" product table by accessing the product table on "My_Local_Server". Furthermore, provided the user assigned in the connection string possesses the necessary privileges, it's also possible to add, modify, and delete data residing in this remote table.