mysql的federated引擎添加

如果你还不了解什么是federated不要近，本章主要讲述federated的作用和用处。在我们工作中有这么一个环境，存储的集群已经不能支撑现有的服务了，在扩展已经没有什么意义，但是有些数据还是需要加的，但是不加又不行。在这种情况下会有在查询数据库的时候需要调两个集群库的表，这种跨集群的表如果使用，这里先买个关子，留给后面的文章。这里就说一下两个mysql实例之间如何跨实例查询，但是查询之前需要使用federated引擎。

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| 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)

注意，FEDERATED的Support是NO，如果需要开启，需要在配置文件中my.cnf的mysqld下增加federated，然后重启即可

[root@www.wulaoer.org ~]# cat /etc/my.cnf
[client]
#password   = your_password
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
federated
port        = 3306
socket      = /tmp/mysql.sock
datadir = /usr/local/mysql/var
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
.......................................

重启之后，在查看一下，记得使用show engines;

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | YES      | Federated MySQL storage engine                                 | NO         | NO | NO       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

虽然实例的FEDERATED已经开启了，但是要想让其他的数据同步到本实例还要去连接

申明引擎            连接属性              用户  密码     ip               数据库  表
ENGINE =FEDERATED CONNECTION='mysql://root:wulaoer@10.211.55.32:3306/wulaoer/user';

现在已经创建了连接，但是只是连接还不行，还需要把user表的表结构创建好

CREATE TABLE `user` (
  `id` varchar(255) NOT NULL COMMENT 'id',
  `encryid` varchar(255) DEFAULT NULL COMMENT '加密后的用户id',
  `name` varchar(255) DEFAULT NULL COMMENT '用户名',
  `password` varchar(255) DEFAULT NULL COMMENT '密码',
  `mobile` varchar(40) DEFAULT NULL COMMENT '手机号码',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `sex` int(1) DEFAULT NULL COMMENT '性别（0：男，1：女）',
  `credit` double(11,2) DEFAULT '0.00',
  PRIMARY KEY (`oid`),
  UNIQUE KEY `upk_user_id` (`id`)
)
ENGINE =FEDERATED CONNECTION='mysql://root:wulaoer@10.211.55.32:3306/wulaoer/user';

创建好表结构之后，数据就会自动映射过来，这样在后期的查询中就可以直接操作了。