MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 

How to create federated table in MySQL?

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

Answer No: 191

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.

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.