mysql federated5.7_MySQL下FEDERATED引擎的开启和使用

在实际工作中，我们可能会遇到需要操作其他数据库实例的部分表，但又不想系统连接多库。此时我们就需要用到数据表映射。如同Oracle中的DBlink一般，使用过Oracle DBlink数据库链接的人都知道可以跨实例来进行数据查询，同样的，MySQL自带的FEDERATED引擎完美的帮我们解决了该问题。本篇文章介绍FEDERATED引擎的开启和使用。

1.开启FEDERATED引擎

若需要创建FEDERATED引擎表，则目标端实例要开启FEDERATED引擎。从MySQL5.5开始FEDERATED引擎默认安装 只是没有启用，进入命令行输入  show engines  ;  FEDERATED行状态为NO。

mysql> show engines;

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

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

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

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

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

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

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

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

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

| ARCHIVE | YES | Archive 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 |

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

9 rows in set (0.00 sec)

2.使用CONNECTION创建FEDERATED表

使用CONNECTION创建FEDERATED引擎表通用模型：

CREATE TABLE (....