mysql使用federated实现dblink远程表访问-php教程

mysql使用federated实现dblink远程表访问

1. source 端创建测试表

create table s select * from mysql.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) 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 ;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

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 |

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 ;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

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 ;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

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` (