远程表: 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.不支持表结构修改