bitsCN.com
1. source 端创建测试表 create table s select * from mysql.user ;
2. source 端查看测试表的建表语句 show create table s ;
<CREATE TABLE `s` ( `Host` char(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT &#39;&#39;, `User` char(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT &#39;&#39;, `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT &#39;&#39;, `Select_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Insert_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Update_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Delete_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Create_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Drop_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Reload_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Shutdown_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Process_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `File_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Grant_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `References_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Index_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Alter_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Show_db_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Super_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Create_tmp_table_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Lock_tables_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Execute_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Repl_slave_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Repl_client_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Create_view_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Show_view_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Create_routine_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Alter_routine_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Create_user_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Event_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Trigger_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Create_tablespace_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `ssl_type` enum(&#39;&#39;,&#39;ANY&#39;,&#39;X509&#39;,&#39;SPECIFIED&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;&#39;, `ssl_cipher` blob NOT NULL, `x509_issuer` blob NOT NULL, `x509_subject` blob NOT NULL, `max_questions` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;, `max_updates` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;, `max_connections` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;, `max_user_connections` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;, `plugin` char(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT &#39;&#39;, `authentication_string` text CHARACTER SET utf8 COLLATE utf8_bin, `password_expired` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39; ) ENGINE=InnoDB DEFAULT CHARSET=latin1
EOF
3. source端创建用户并且授权
GRANT ALL PRIVILEGES ON sources.s TO dex IDENTIFIED BY &#39;xiaojun&#39;; GRANT ALL PRIVILEGES ON sources.s TO dex@192.168.100.42 IDENTIFIED BY &#39;xiaojun&#39;; SHOW GRANTS FOR dex;
mysql> GRANT ALL PRIVILEGES ON sources.s TO dex IDENTIFIED BY &#39;xiaojun&#39;; Query OK, 0 rows affected (0.03 sec)
mysql> GRANT ALL PRIVILEGES ON sources.s TO dex@192.168.100.42 IDENTIFIED BY &#39;xiaojun&#39;; Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR dex; +----------------------------------------------------------------------------------------------------+ | Grants for dex@% | +----------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO &#39;dex&#39;@&#39;%&#39; IDENTIFIED BY PASSWORD &#39;*8FDE30312222738F1CD8AC8AF0EE515A9DB8180E&#39; | | GRANT ALL PRIVILEGES ON `sources`.`s` TO &#39;dex&#39;@&#39;%&#39; | +----------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
4. 查看target端是否安装了FEDERATED存储引擎
mysql> mysql> show engines ; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | 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 | | 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 | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
[root@rhel6Mysql02 ~]# mysqld_safe --federated & [1] 2194 [root@rhel6Mysql02 ~]# 130620 18:12:28 mysqld_safe Logging to &#39;/var/lib/mysql/rhel6Mysql02.err&#39;. 130620 18:12:28 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
mysql> show engines ; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | 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 | | 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 | | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
好的修改一下my.conf文件
federated
重启一下mysql server service mysql restart
恩,已经支持了。 mysql> show engines ; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | 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 | | 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 | | FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
5. 在target端定义基于federated存储引擎的表links
CREATE TABLE `links` ( `Host` char(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT &#39;&#39;, `User` char(16) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT &#39;&#39;, `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT &#39;&#39;, `Select_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Insert_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Update_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Delete_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Create_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Drop_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Reload_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Shutdown_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Process_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `File_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Grant_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `References_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Index_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Alter_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Show_db_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Super_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Create_tmp_table_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Lock_tables_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Execute_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Repl_slave_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Repl_client_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Create_view_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Show_view_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Create_routine_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Alter_routine_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Create_user_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Event_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Trigger_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `Create_tablespace_priv` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39;, `ssl_type` enum(&#39;&#39;,&#39;ANY&#39;,&#39;X509&#39;,&#39;SPECIFIED&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;&#39;, `ssl_cipher` blob NOT NULL, `x509_issuer` blob NOT NULL, `x509_subject` blob NOT NULL, `max_questions` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;, `max_updates` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;, `max_connections` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;, `max_user_connections` int(11) unsigned NOT NULL DEFAULT &#39;0&#39;, `plugin` char(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT &#39;&#39;, `authentication_string` text CHARACTER SET utf8 COLLATE utf8_bin, `password_expired` enum(&#39;N&#39;,&#39;Y&#39;) CHARACTER SET utf8 NOT NULL DEFAULT &#39;N&#39; ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 COnNECTION=&#39;mysql://dex:xiaojun@192.168.100.41:3306/sources/s&#39;;
Query OK, 0 rows affected (1.10 sec)
mysql> select count(*) from links; +----------+ | count(*) | +----------+ | 12 | +----------+ 1 row in set (0.00 sec) bitsCN.com