8. Engine
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
启用 FEDERATED 引擎, 服务器环境 Ubuntu 13.04
$ sudo vim /etc/mysql/conf.d/federated.cnf [mysqld] federated $ sudo service mysql restart
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
A 服务器
CREATE TABLE `t1` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`sex` ENUM('Y','N') NULL DEFAULT 'Y',
`passwd` VARCHAR(50) NULL DEFAULT NULL,
`ctime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4;
B 服务器
DROP TABLE `users`;
CREATE TABLE `users` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
`sex` ENUM('Y','N') NULL DEFAULT 'Y',
`ctime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
`mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=FEDERATED connection = 'mysql://www:qwer123@192.168.2.1:3306/test/t1';
上面字段描述是你需要的字段，并非所有字段。这里屏蔽了passwd字段
mysql> DROP TABLE `users`;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `users` (
-> `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> `name` VARCHAR(50) NOT NULL,
-> `sex` ENUM('Y','N') NULL DEFAULT 'Y',
-> `ctime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
-> `mtime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`id`)
-> ) ENGINE=FEDERATED connection = 'mysql://www:qwer123@192.168.2.1:3306/test/t1';
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users |
+----------------+
1 row in set (0.00 sec)
mysql> desc users;
+-------+------------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| sex | enum('Y','N') | YES | | Y | |
| ctime | timestamp | NO | | 0000-00-00 00:00:00 | |
| mtime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+------------------+------+-----+---------------------+-----------------------------+
5 rows in set (0.00 sec)
mysql> select * from users;
+----+------+------+---------------------+---------------------+
| id | name | sex | ctime | mtime |
+----+------+------+---------------------+---------------------+
| 1 | neo | Y | 0000-00-00 00:00:00 | 2013-05-17 18:05:09 |
| 2 | zen | Y | 0000-00-00 00:00:00 | 2013-05-17 18:05:11 |
| 3 | lily | N | 0000-00-00 00:00:00 | 2013-05-17 18:05:22 |
+----+------+------+---------------------+---------------------+
3 rows in set (0.01 sec)
