使用FREDATED引擎实现跨实例访问

跨数据库服务器，跨实例访问是比较常见的一种访问方式，在Oracle中可以通过DB LINK的方式来实现。对于MySQL而言，有一个FEDERATED存储引擎与之相对应。同样也是通过创建一个链接方式的形式来访问远程服务器上的数据。本文简要描述了FEDERATED存储引擎，以及

跨数据库服务器，跨实例访问是比较常见的一种访问方式，在Oracle中可以通过DB LINK的方式来实现。对于MySQL而言，有一个FEDERATED存储引擎与之相对应。同样也是通过创建一个链接方式的形式来访问远程服务器上的数据。本文简要描述了FEDERATED存储引擎，以及演示了基于FEDERATED存储引擎跨实例访问的示例。

1、FEDERATED存储引擎的描述

2、安装与启用FEDERATED存储引擎

3、准备远程服务器环境

-- 此演示中远程服务器与本地服务器为同一服务器上的多版本多实例-- 假定远程服务为：5.6.12(实例3406)-- 假定本地服务器：5.6.21(实例3306)   -- 基于实例3306创建FEDERATED存储引擎表test.federated_engine以到达访问实例3406数据库tempdb.tb_engine的目的[root@rhel64a ~]# cat /etc/issueRed Hat Enterprise Linux Server release 6.4 (Santiago) --启动3406的实例[root@rhel64a ~]# /u01/app/mysql/bin/mysqld_multi start 3406[root@rhel64a ~]# mysql -uroot -pxxx -P3406 --protocol=tcproot@localhost[(none)]> show variables like 'server_id';+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 3406  |+---------------+-------+--实例3406的版本号root@localhost[tempdb]> show variables like 'version';+---------------+------------+| Variable_name | Value      |+---------------+------------+| version       | 5.6.12-log |+---------------+------------+--创建数据库root@localhost[(none)]> create database tempdb;Query OK, 1 row affected (0.00 sec)-- Author : Leshami-- Blog   ：http://blog.gaodaima.com/leshamiroot@localhost[(none)]> use tempdbDatabase changed--创建用于访问的表root@localhost[tempdb]> create table tb_engine as     -> select engine,support,comment from information_schema.engines;Query OK, 9 rows affected (0.10 sec)Records: 9  Duplicates: 0  Warnings: 0--提取表的SQL语句用于创建为FEDERATED存储引擎表root@localhost[tempdb]> show create table tb_engine \G*************************** 1. row ***************************       Table: tb_engineCreate Table: CREATE TABLE `tb_engine` (  `engine` varchar(64) NOT NULL DEFAULT '',  `support` varchar(8) NOT NULL DEFAULT '',  `comment` varchar(80) NOT NULL DEFAULT '') ENGINE=InnoDB DEFAULT CHARSET=utf8--创建用于远程访问的账户root@localhost[tempdb]> grant all privileges on tempdb.* to 'remote_user'@'192.168.1.131' identified by 'xxx';Query OK, 0 rows affected (0.00 sec)root@localhost[tempdb]> flush privileges;Query OK, 0 rows affected (0.00 sec)

4、演示FEDERATED存储引擎跨实例访问

[root@rhel64a ~]# mysql -uroot -pxxxroot@localhost[(none)]> show variables like 'version';+---------------+--------+| Variable_name | Value  |+---------------+--------+| version       | 5.6.21 |+---------------+--------+#查看是否支持FEDERATED引擎root@localhost[(none)]> select * from information_schema.engines where engine='federated';+-----------+---------+--------------------------------+--------------+------+------------+| ENGINE    | SUPPORT | COMMENT                        | TRANSACTIONS | XA   | SAVEPOINTS |+-----------+---------+--------------------------------+--------------+------+------------+| FEDERATED | NO      | Federated MySQL storage engine | NULL         | NULL | NULL       |+-----------+---------+--------------------------------+--------------+------+------------+root@localhost[(none)]> exit[root@rhel64a ~]# service mysql stopShutting down MySQL..[  OK  ]#配置启用FEDERATED引擎[root@rhel64a ~]# vi /etc/my.cnf[root@rhel64a ~]# tail -7 /etc/my.cnf[mysqld]socket = /tmp/mysql3306.sockport = 3306pid-file = /var/lib/mysql/my3306.piduser = mysqlserver-id=3306/federated         #添加该选项[root@rhel64a ~]# service mysql startStarting MySQL.[  OK  ][root@rhel64a ~]# mysql -uroot -pxxxroot@localhost[(none)]> select * from information_schema.engines where engine='federated';+-----------+---------+--------------------------------+--------------+------+------------+| ENGINE    | SUPPORT | COMMENT                        | TRANSACTIONS | XA   | SAVEPOINTS |+-----------+---------+--------------------------------+--------------+------+------------+| FEDERATED | YES     | Federated MySQL storage engine | NO           | NO   | NO         |+-----------+---------+--------------------------------+--------------+------+------------+root@localhost[(none)]> use test-- 创建基于FEDERATED引擎的表federated_engineroot@localhost[test]> CREATE TABLE `federated_engine` (    ->   `engine` varchar(64) NOT NULL DEFAULT '',    ->   `support` varchar(8) NOT NULL DEFAULT '',    ->   `comment` varchar(80) NOT NULL DEFAULT ''    -> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8    -> CONNECTION='mysql://remote_user:[email protected]:3406/t<div>本文来源gaodai.ma#com搞#代!码网_</div>empdb/tb_engine';Query OK, 0 rows affected (0.00 sec)-- 下面是创建后表格式文件root@localhost[test]> system ls -hltr /var/lib/mysql/testtotal 12K-rw-rw---- 1 mysql mysql 8.5K Oct 24 08:22 federated_engine.frm--查询表federated_engineroot@localhost[test]> select * from federated_engine limit 2;+------------+---------+---------------------------------------+| engine     | support | comment                               |+------------+---------+---------------------------------------+| MRG_MYISAM | YES     | Collection of identical MyISAM tables || CSV        | YES     | CSV storage engine                    |+------------+---------+---------------------------------------+--更新表federated_engineroot@localhost[test]> update federated_engine set support='NO' where engine='CSV';Query OK, 1 row affected (0.03 sec)Rows matched: 1  Changed: 1  Warnings: 0--查看更新后的结果root@localhost[test]> select * from federated_engine where engine='CSV';+--------+---------+--------------------+| engine | support | comment            |+--------+---------+--------------------+| CSV    | NO      | CSV storage engine |+--------+---------+--------------------+

5、创建FEDERATED引擎表的链接方式

scheme: A recognized connection protocol. Only mysql is supported as the scheme value at this point.

链接示例样本:

6、涉及到的相关参考

喜欢 (0)赏

[搞代码]

分享 (0)