1. source 端创建测试表 create table s select * frommysql.user ; 2. source 端查看测试表的建表语句 show create table s ; eof create table `s` ( `host` char(60) character set utf8 collate utf8_bin not null default '', `user` char(16) characte
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 '',
`user` char(16) character set utf8 collate utf8_bin not null default '',
`password` char(41) character set latin1 collate latin1_bin not null default '',
`select_priv` enum('n','y') character set utf8 not null default 'n',
`insert_priv` enum('n','y') character set utf8 not null default 'n',
`update_priv` enum('n','y') character set utf8 not null default 'n',
`delete_priv` enum('n','y') character set utf8 not null default 'n',
`create_priv` enum('n','y') character set utf8 not null default 'n',
`drop_priv` enum('n','y') character set utf8 not null default 'n',
`reload_priv` enum('n','y') character set utf8 not null default 'n',
`shutdown_priv` enum('n','y') character set utf8 not null default 'n',
`process_priv` enum('n','y') character set utf8 not null default 'n',
`file_priv` enum('n','y') character set utf8 not null default 'n',
`grant_priv` enum('n','y') character set utf8 not null default 'n',
`references_priv` enum('n','y') character set utf8 not null default 'n',
`index_priv` enum('n','y') character set utf8 not null default 'n',
`alter_priv` enum('n','y') character set utf8 not null default 'n',
`show_db_priv` enum('n','y') character set utf8 not null default 'n',
`super_priv` enum('n','y') character set utf8 not null default 'n',
`create_tmp_table_priv` enum('n','y') character set utf8 not null default 'n',
`lock_tables_priv` enum('n','y') character set utf8 not null default 'n',
`execute_priv` enum('n','y') character set utf8 not null default 'n',
`repl_slave_priv` enum('n','y') character set utf8 not null default 'n',
`repl_client_priv` enum('n','y') character set utf8 not null default 'n',
`create_view_priv` enum('n','y') character set utf8 not null default 'n',
`show_view_priv` enum('n','y') character set utf8 not null default 'n',
`create_routine_priv` enum('n','y') character set utf8 not null default 'n',
`alter_routine_priv` enum('n','y') character set utf8 not null default 'n',
`create_user_priv` enum('n','y') character set utf8 not null default 'n',
`event_priv` enum('n','y') character set utf8 not null default 'n',
`trigger_priv` enum('n','y') character set utf8 not null default 'n',
`create_tablespace_priv` enum('n','y') character set utf8 not null default 'n',
`ssl_type` enum('','any','x509','specified') character set utf8 not null default '',
`ssl_cipher` blob not null,
`x509_issuer` blob not null,
`x509_subject` blob not null,
`max_questions` int(11) unsigned not null default '0',
`max_updates` int(11) unsigned not null default '0',
`max_connections` int(11) unsigned not null default '0',
`max_user_connections` int(11) unsigned not null default '0',
`plugin` char(64) character set utf8 collate utf8_bin default '',
`authentication_string` text character set utf8 collate utf8_bin,
`password_expired` enum('n','y') character set utf8 not null default 'n'
) engine=innodb default charset=latin1
eof
3. source端创建用户并且授权
grant all privileges on sources.s to dex identified by 'xiaojun';
grant all privileges on sources.s to dex@192.168.100.42 identified by 'xiaojun';
show grants for dex;
mysql> grant all privileges on sources.s to dex identified by 'xiaojun';
query ok, 0 rows affected (0.03 sec)
mysql> grant all privileges on sources.s to dex@192.168.100.42 identified by 'xiaojun';
query ok, 0 rows affected (0.00 sec)
mysql> show grants for dex;
+----------------------------------------------------------------------------------------------------+
| grants for dex@% |
+----------------------------------------------------------------------------------------------------+
| grant usage on *.* to 'dex'@'%' identified by password '*8fde30312222738f1cd8ac8af0ee515a9db8180e' |
| grant all privileges on `sources`.`s` to 'dex'@'%' |
+----------------------------------------------------------------------------------------------------+
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)
看到没有安装federated存储引擎（目标端可以是任何的存储引擎比如说myisam或者innodb）
4.1 先来安装federated存储引擎
install plugin federated soname 'ha_federated.so';
mysql> install plugin federated soname 'ha_federated.so';
error 1125 (hy000): function 'federated' already exists
已经安装好了，只是没有启用
测试一下是否可以
[root@rhel6mysql02 ~]# mysqld_safe --federated &
[1] 2194
[root@rhel6mysql02 ~]# 130620 18:12:28 mysqld_safe logging to '/var/lib/mysql/rhel6mysql02.err'.
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 '',
`user` char(16) character set utf8 collate utf8_bin not null default '',
`password` char(41) character set latin1 collate latin1_bin not null default '',
`select_priv` enum('n','y') character set utf8 not null default 'n',
`insert_priv` enum('n','y') character set utf8 not null default 'n',
`update_priv` enum('n','y') character set utf8 not null default 'n',
`delete_priv` enum('n','y') character set utf8 not null default 'n',
`create_priv` enum('n','y') character set utf8 not null default 'n',
`drop_priv` enum('n','y') character set utf8 not null default 'n',
`reload_priv` enum('n','y') character set utf8 not null default 'n',
`shutdown_priv` enum('n','y') character set utf8 not null default 'n',
`process_priv` enum('n','y') character set utf8 not null default 'n',
`file_priv` enum('n','y') character set utf8 not null default 'n',
`grant_priv` enum('n','y') character set utf8 not null default 'n',
`references_priv` enum('n','y') character set utf8 not null default 'n',
`index_priv` enum('n','y') character set utf8 not null default 'n',
`alter_priv` enum('n','y') character set utf8 not null default 'n',
`show_db_priv` enum('n','y') character set utf8 not null default 'n',
`super_priv` enum('n','y') character set utf8 not null default 'n',
`create_tmp_table_priv` enum('n','y') character set utf8 not null default 'n',
`lock_tables_priv` enum('n','y') character set utf8 not null default 'n',
`execute_priv` enum('n','y') character set utf8 not null default 'n',
`repl_slave_priv` enum('n','y') character set utf8 not null default 'n',
`repl_client_priv` enum('n','y') character set utf8 not null default 'n',
`create_view_priv` enum('n','y') character set utf8 not null default 'n',
`show_view_priv` enum('n','y') character set utf8 not null default 'n',
`create_routine_priv` enum('n','y') character set utf8 not null default 'n',
`alter_routine_priv` enum('n','y') character set utf8 not null default 'n',
`create_user_priv` enum('n','y') character set utf8 not null default 'n',
`event_priv` enum('n','y') character set utf8 not null default 'n',
`trigger_priv` enum('n','y') character set utf8 not null default 'n',
`create_tablespace_priv` enum('n','y') character set utf8 not null default 'n',
`ssl_type` enum('','any','x509','specified') character set utf8 not null default '',
`ssl_cipher` blob not null,
`x509_issuer` blob not null,
`x509_subject` blob not null,
`max_questions` int(11) unsigned not null default '0',
`max_updates` int(11) unsigned not null default '0',
`max_connections` int(11) unsigned not null default '0',
`max_user_connections` int(11) unsigned not null default '0',
`plugin` char(64) character set utf8 collate utf8_bin default '',
`authentication_string` text character set utf8 collate utf8_bin,
`password_expired` enum('n','y') character set utf8 not null default 'n'
)
engine=federated
default charset=latin1
connection='mysql://dex:xiaojun@192.168.100.41:3306/sources/s';
query ok, 0 rows affected (1.10 sec)
mysql> select count(*) from links;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
，