MySQL中應用FREDATED引擎完成跨數據庫辦事器、跨實例拜訪。本站提示廣大學習愛好者：（MySQL中應用FREDATED引擎完成跨數據庫辦事器、跨實例拜訪）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL中應用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/issue
Red 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=tcp
root@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/leshami
root@localhost[(none)]> use tempdb
Database 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_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
--創立用於長途拜訪的賬戶
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 -pxxx
root@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 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 #添加該選項
[root@rhel64a ~]# service mysql start
Starting MySQL.[ OK ]
[root@rhel64a ~]# mysql -uroot -pxxx
root@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_engine
root@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/tempdb/tb_engine';
Query OK, 0 rows affected (0.00 sec)
-- 上面是創立後表格局文件
root@localhost[test]> system ls -hltr /var/lib/mysql/test
total 12K
-rw-rw---- 1 mysql mysql 8.5K Oct 24 08:22 federated_engine.frm
--查詢表federated_engine
root@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_engine
root@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'
