? ?Federated存儲(chǔ)引擎訪問(wèn)在遠(yuǎn)程數(shù)據(jù)庫(kù)的表中的數(shù)據(jù)，而不是本地的表。創(chuàng)建一個(gè)Federated表的時(shí)候，服務(wù)器在數(shù)據(jù)庫(kù)目錄創(chuàng)建一個(gè)表定義文件。無(wú)其它表被創(chuàng)建，因?yàn)閷?shí)際的數(shù)據(jù)在一個(gè)遠(yuǎn)程數(shù)據(jù)庫(kù)上。這不同于為本地表工作的存儲(chǔ)引擎的方式。

1. 檢查當(dāng)前服務(wù)器配置是否支持Federated存儲(chǔ)引擎：

Show Engines;

查詢(xún)結(jié)果，如果Federated行對(duì)應(yīng)的Support列值為YES，說(shuō)明當(dāng)前服務(wù)器支持Federated存儲(chǔ)引擎。

如果Federated行對(duì)應(yīng)的Support列值為NO,需要我們進(jìn)行配置。找到服務(wù)器安裝目錄下my.ini文件；記事本打開(kāi)文件添加Federated；保存；重啟MySQL服務(wù)器。

2. 在遠(yuǎn)程服務(wù)器上，創(chuàng)建本地表；并導(dǎo)入數(shù)據(jù)。

CREATE TABLE IF NOT EXISTS `departmentweekdata` (`department` varchar(255) NOT NULL,`week` varchar(10) NOT NULL,`interval` tinyint(4) DEFAULT NULL,`number` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;-- -- 插入表數(shù)據(jù) -- INSERT INTO `departmentweekdata` (`department`, `week`, `interval`, `number`) VALUES ('CAO', '2014-12', 1, 1), ('GBG1', '2014-12', 1, 0), ('CAO', '2014-12', 2, 0), ('GBG1', '2014-12', 2, 0), ('CAO', '2014-12', 3, 1), ('GBG1', '2014-12', 3, 0), ('CAO', '2014-11', 1, 2), ('GBG1', '2014-11', 1, 0), ('CAO', '2014-11', 2, 0), ('GBG1', '2014-11', 2, 0), ('CAO', '2014-11', 3, 1), ('GBG1', '2014-11', 3, 0), ('CAO', '2014-10', 1, 1), ('GBG1', '2014-10', 1, 0), ('CAO', '2014-10', 2, 2), ('GBG1', '2014-10', 2, 0), ('CAO', '2014-10', 3, 0), ('GBG1', '2014-10', 3, 0), ('CAO', '2014-09', 1, 1), ('GBG1', '2014-09', 1, 0), ('CAO', '2014-09', 2, 0), ('GBG1', '2014-09', 2, 0), ('CAO', '2014-09', 3, 0), ('GBG1', '2014-09', 3, 0), ('CAO', '2014-08', 1, 2), ('GBG1', '2014-08', 1, 0), ('CAO', '2014-08', 2, 0), ('GBG1', '2014-08', 2, 0), ('CAO', '2014-08', 3, 1), ('GBG1', '2014-08', 3, 0), ('CAO', '2014-07', 1, 1), ('GBG1', '2014-07', 1, 0), ('CAO', '2014-07', 2, 2), ('GBG1', '2014-07', 2, 0), ('CAO', '2014-07', 3, 0), ('GBG1', '2014-07', 3, 0), ('CAO', '2014-06', 1, 0), ('GBG1', '2014-06', 1, 0), ('CAO', '2014-06', 2, 1), ('GBG1', '2014-06', 2, 0), ('CAO', '2014-06', 3, 0), ('GBG1', '2014-06', 3, 0), ('CAO', '2014-05', 1, 0), ('GBG1', '2014-05', 1, 0), ('CAO', '2014-05', 2, 1), ('GBG1', '2014-05', 2, 0), ('CAO', '2014-05', 3, 0), ('GBG1', '2014-05', 3, 0), ('CAO', '2014-04', 1, 1), ('GBG1', '2014-04', 1, 0), ('CAO', '2014-04', 2, 0), ('GBG1', '2014-04', 2, 0), ('CAO', '2014-04', 3, 0), ('GBG1', '2014-04', 3, 0), ('CAO', '2014-03', 1, 0), ('GBG1', '2014-03', 1, 0), ('CAO', '2014-03', 2, 0), ('GBG1', '2014-03', 2, 0), ('CAO', '2014-03', 3, 1), ('GBG1', '2014-03', 3, 0);

3. 在本地服務(wù)器上創(chuàng)建Federated表。

CREATE TABLE `DepartmentWeekData` (`department` VARCHAR(255) NOT NULL,`week` VARCHAR(10) NOT NULL,`interval` TINYINT(4) NULL,`number` INT NULL ) ENGINE=FEDERATED CONNECTION='mysql://USER:PASSWORD@HOST:PORT/DATABASE/TABLE';

其中連接字符串示例：CONNECTION='mysql://root:123@127.0.0.1:3306/dbTest/TestTable';

4. 如果使用MySQL客戶(hù)端工具，可以創(chuàng)建Federated Server:

CREATE SERVER `FServer` FOREIGN DATA WRAPPER mysql OPTIONS (HOST '127.0.0.1',PORT 3306,USER 'root',PASSWORD '123',DATABASE 'DBTest' );

5. 如果創(chuàng)建了Federated Server，創(chuàng)建Federated表可以用下面的方式：

CREATE TABLE `DepartmentWeekData` (`department` VARCHAR(255) NOT NULL,`week` VARCHAR(10) NOT NULL,`interval` TINYINT(4) NULL,`number` INT NULL ) ENGINE=FEDERATED CONNECTION='FServer/TABLE';

6.創(chuàng)建好Federated表以后，就可以訪問(wèn)遠(yuǎn)程數(shù)據(jù)庫(kù)表中的數(shù)據(jù)了。

轉(zhuǎn)載于:https://www.cnblogs.com/ucos/p/Federated.html

總結(jié)

以上是生活随笔為你收集整理的Mysql Federated Server 示例的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。