跨數(shù)據(jù)庫(kù)服務(wù)器，跨實(shí)例訪問(wèn)是比較常見(jiàn)的一種訪問(wèn)方式，在Oracle中可以通過(guò)DB LINK的方式來(lái)實(shí)現(xiàn)。對(duì)于MySQL而言，有一個(gè)FEDERATED存儲(chǔ)引擎與之相對(duì)應(yīng)。同樣也是通過(guò)創(chuàng)建一個(gè)鏈接方式的形式來(lái)訪問(wèn)遠(yuǎn)程服務(wù)器上的數(shù)據(jù)。本文簡(jiǎn)要描述了FEDERATED存儲(chǔ)引擎，以及演示了基于FEDERATED存儲(chǔ)引擎跨實(shí)例訪問(wèn)的示例。

1、FEDERATED存儲(chǔ)引擎的描述

FEDERATED存儲(chǔ)引擎允許在不使用復(fù)制或集群技術(shù)的情況下實(shí)現(xiàn)遠(yuǎn)程訪問(wèn)數(shù)據(jù)庫(kù)

創(chuàng)建基于FEDERATED存儲(chǔ)引擎表的時(shí)候，服務(wù)器在數(shù)據(jù)庫(kù)目錄僅創(chuàng)建一個(gè)表定義文件，即以表名開(kāi)頭的.frm文件。

FEDERATED存儲(chǔ)引擎表無(wú)任何數(shù)據(jù)存儲(chǔ)到本地，即沒(méi)有.myd文件

對(duì)于遠(yuǎn)程服務(wù)器上表的操作與本地表操作一樣，僅僅是數(shù)據(jù)位于遠(yuǎn)程服務(wù)器

基本流程如下：

2、安裝與啟用FEDERATED存儲(chǔ)引擎

源碼安裝MySQL時(shí)使用DWITH_FEDERATED_STORAGE_ENGINE來(lái)配置

rpm安裝方式缺省情況下已安裝，只需要啟用該功能即可

3、準(zhǔn)備遠(yuǎn)程服務(wù)器環(huán)境

代碼如下:

-- 此演示中遠(yuǎn)程服務(wù)器與本地服務(wù)器為同一服務(wù)器上的多版本多實(shí)例

-- 假定遠(yuǎn)程服務(wù)為：5.6.12(實(shí)例3406)

-- 假定本地服務(wù)器：5.6.21(實(shí)例3306)

-- 基于實(shí)例3306創(chuàng)建FEDERATED存儲(chǔ)引擎表test.federated_engine以到達(dá)訪問(wèn)實(shí)例3406數(shù)據(jù)庫(kù)tempdb.tb_engine的目的

[root@rhel64a ~]# cat /etc/issue

Red Hat Enterprise Linux Server release 6.4 (Santiago)

--啟動(dòng)3406的實(shí)例

[root@rhel64a ~]# /u01/app/mysql/bin/mysqld_multi start 3406

[root@rhel64a ~]# mysql -uroot -pxxx -P3406 --protocol=tcp

)]> show variables like 'server_id';

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

| Variable_name | Value |

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

| server_id | 3406 |

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

--實(shí)例3406的版本號(hào)

]> show variables like 'version';

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

| Variable_name | Value |

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

| version | 5.6.12-log |

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

--創(chuàng)建數(shù)據(jù)庫(kù)

)]> create database tempdb;

Query OK, 1 row affected (0.00 sec)

-- Author : Leshami

-- Blog ：

)]> use tempdb

Database changed

--創(chuàng)建用于訪問(wèn)的表

]> 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語(yǔ)句用于創(chuàng)建為FEDERATED存儲(chǔ)引擎表

]> show create table tb_engine \G

*************************** 1. row ***************************

Table: tb_engine

Create 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

--創(chuàng)建用于遠(yuǎn)程訪問(wèn)的賬戶(hù)

]> grant all privileges on tempdb.* to identified by 'xxx';

Query OK, 0 rows affected (0.00 sec)

]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

4、演示FEDERATED存儲(chǔ)引擎跨實(shí)例訪問(wèn)

代碼如下:

[root@rhel64a ~]# mysql -uroot -pxxx

)]> show variables like 'version';

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

| Variable_name | Value |

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

| version | 5.6.21 |

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

#查看是否支持FEDERATED引擎

)]> select * from information_schema.engines where engine='federated';

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

| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |

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

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

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

)]> exit

[root@rhel64a ~]# service mysql stop

Shutting down MySQL..[ OK ]

#配置啟用FEDERATED引擎

[root@rhel64a ~]# vi /etc/my.cnf

[root@rhel64a ~]# tail -7 /etc/my.cnf

[mysqld]

socket = /tmp/mysql3306.sock

port = 3306

pid-file = /var/lib/mysql/my3306.pid

user = mysql

server-id=3306/

federated #添加該選項(xiàng)

[root@rhel64a ~]# service mysql start

Starting MySQL.[ OK ]

[root@rhel64a ~]# mysql -uroot -pxxx

)]> select * from information_schema.engines where engine='federated';

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

| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |

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

| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |

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

)]> use test

-- 創(chuàng)建基于FEDERATED引擎的表federated_engine

]> 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:xxx@192.168.1.131:3406/tempdb/tb_engine';

Query OK, 0 rows affected (0.00 sec)

-- 下面是創(chuàng)建后表格式文件

]> system ls -hltr /var/lib/mysql/test

total 12K

-rw-rw---- 1 mysql mysql 8.5K Oct 24 08:22 federated_engine.frm

--查詢(xún)表federated_engine

]> select * from federated_engine limit 2;

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

| engine | support | comment |

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

| MRG_MYISAM | YES | Collection of identical MyISAM tables |

| CSV | YES | CSV storage engine |

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

--更新表federated_engine

]> update federated_engine set support='NO' where engine='CSV';

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

--查看更新后的結(jié)果

]> select * from federated_engine where engine='CSV';

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

| engine | support | comment |

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

| CSV | NO | CSV storage engine |

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

5、創(chuàng)建FEDERATED引擎表的鏈接方式

代碼如下:

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

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

user_name: The user name for the connection. This user must have been created on the remote server, and must have suitable privileges to perform the required actions (SELECT, INSERT,UPDATE, and so forth) on the remote table.

password: (Optional) The corresponding password for user_name.

host_name: The host name or IP address of the remote server.

port_num: (Optional) The port number for the remote server. The default is 3306.

db_name: The name of the database holding the remote table.

tbl_name: The name of the remote table. The name of the local and the remote table do not have to match.

鏈接示例樣本:

CONNECTION='mysql://username:password@hostname:port/database/tablename'

CONNECTION='mysql://username@hostname/database/tablename'

CONNECTION='mysql://username:password@hostname/database/tablename'

更多信息請(qǐng)查看IT技術(shù)專(zhuān)欄