MYSQL 链接远程的FEDERATED引擎链接表(类似ORACLE的DBLINK)

MYSQL 链接远程的链接表(类似ORACLE的DBLINK)关于链接表
远程表： CREATE TABLE IF NOTEXISTS `category` (   `id` int(11) NOTNULLAUTO_INCREMENT,   `category_id` int(11) NOTNULL,   ` name` varchar(20) NOTNULL,    PRIMARYKEY(`id`) )ENGINE=MYSAIM DEFAULTCHARSET=utf8;

 

本地建立链接表（映射10.10.10.133 app库的category表）的时候：

CREATE TABLE IF NOT EXISTS `remotecategory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
   PRIMARY KEY (`id`)
)ENGINE=FEDERATED DEFAULT CHARSET=utf8 
CONNECTION='mysql://user:passwod@10.10.10.133:3306/app/category'; 注：表的结构一模一样，就是引擎不同，看红字FEDERATED

需要在 my.ini 的 [mysqld] 下面 加上federated 来激活FEDERATED （mysql 5.0.5以上的版本）

 

show engines

查看是否安装FEDERATED引擎，没有的重新编译一下

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DINSTALL_DATADIR=/opt/mnt/mydata -DDEFAULT_CHARSET=utf8

-DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DWIT
H_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DWITH_MYISAM_STORAGE_ENGINE=1

-DWITH_PERFSCHEMA_STORAGE_ENGINE=1

 

 

---------------------------------------------------------------------------------------------------------------

mysql> show engines;   
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                             | NULL         | NULL | NULL       |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

 

mysql> show plugins;
+-----------------------+----------+--------------------+--------------------+---------+
| Name                  | Status   | Type               | Library            | License |
+-----------------------+----------+--------------------+--------------------+---------+
| binlog                | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| mysql_native_password | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| mysql_old_password    | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| MEMORY                | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| CSV                   | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MRG_MYISAM            | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MyISAM                | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| PERFORMANCE_SCHEMA    | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| InnoDB                | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| INNODB_TRX            | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCKS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCK_WAITS     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP            | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_RESET      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM_RESET   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| FEDERATED             | DISABLED | STORAGE ENGINE     | NULL               | GPL     |
| partition             | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| rpl_semi_sync_master  | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
| rpl_semi_sync_slave   | ACTIVE   | REPLICATION        | semisync_slave.so  | GPL     |
+-----------------------+----------+--------------------+--------------------+---------+
20 rows in set (0.01 sec)

 

vim /etc/my.cnf

添加一行

federated

既可

 

------------------------------------------------------------------------------------------------------

federated远程连接表对主从有影响

主：

mysql> select * from lijiwei_48;
Empty set (0.00 sec)

mysql>

从：

mysql> select * from lijiwei_48;
Empty set (0.00 sec)

mysql>

主： 

mysql> insert into lijiwei_48 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from lijiwei_48;
+------+
| id   |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

从：

mysql> select * from lijiwei_48;
+------+
| id   |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

主从两台mysql各自插入一条数据，导致同步数据出错

同步dblink类似数据库要格外小心，同样当心触发器

 

使用FEDERATED建表语句如下：

                CREATE TABLE (......) ENGINE =FEDERATED CONNECTION='mysql://[name]:[pass]@[location]:[port]/[db-name]/[table-name]'

               创建成功后就可直接在本地查询相应的远程表了。

需要注意的几点：

              1. 本地的表结构必须与远程的完全一样。

              2.远程数据库目前仅限MySQL

              3.不支持事务

              4.不支持表结构修改