使用FREDATED引擎實現跨執行個體訪問，fredated引擎跨執行個體

使用FREDATED引擎實現跨執行個體訪問，fredated引擎跨執行個體

    跨資料庫伺服器，跨執行個體訪問是比較常見的一種訪問方式，在Oracle中可以通過DB LINK的方式來實現。對於MySQL而言，有一個FEDERATED儲存引擎與之相對應。同樣也是通過建立一個連結方式的形式來訪問遠程伺服器上的資料。本文簡要描述了FEDERATED儲存引擎，以及示範了基於FEDERATED儲存引擎跨執行個體訪問的樣本。

 

1、FEDERATED儲存引擎的描述
  FEDERATED儲存引擎允許在不使用複製或叢集技術的情況下實現遠端存取資料庫
  建立基於FEDERATED儲存引擎表的時候，伺服器在資料庫目錄僅建立一個表定義檔案，即以表名開頭的.frm檔案。
  FEDERATED儲存引擎表無任何資料存放區到本地，即沒有.myd檔案
  對於遠程伺服器上表的操作與本地表操作一樣，僅僅是資料位元於遠程伺服器
  基本流程如下：    

  

2、安裝與啟用FEDERATED儲存引擎
  源碼安裝MySQL時使用DWITH_FEDERATED_STORAGE_ENGINE來配置
  rpm安裝方式預設情況下已安裝，只需要啟用該功能即可

 

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.csdn.net/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:xxx@192.168.1.131:3406/tempdb/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://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'

 

6、涉及到的相關參考
    MySQL多執行個體配置(一)
    MySQL多執行個體配置(二)
    Linux 下MySQL源碼安裝完整版
    MySQL 使用者與許可權管理