酒店设计网站建设方案,卫计局本年度网站建设工作总结,微信商店小程序制作教程,郑州软件app开发公司跨数据库服务器#xff0c;跨实例访问是比较常见的一种访问方式#xff0c;在Oracle中可以通过DB LINK的方式来实现。对于MySQL而言#xff0c;有一个FEDERATED存储引擎与之相对应。同样也是通过创建一个链接方式的形式来访问远程服务器上的数据。本文简要描述了FEDERATED存…跨数据库服务器跨实例访问是比较常见的一种访问方式在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的目的[rootrhel64a ~]# cat /etc/issueRed Hat Enterprise Linux Server release 6.4 (Santiago)--启动3406的实例[rootrhel64a ~]# /u01/app/mysql/bin/mysqld_multi start 3406[rootrhel64a ~]# mysql -uroot -pxxx -P3406 --protocoltcprootlocalhost[(none)] show variables like server_id;----------------------| Variable_name | Value |----------------------| server_id | 3406 |------------------------实例3406的版本号rootlocalhost[tempdb] show variables like version;---------------------------| Variable_name | Value |---------------------------| version | 5.6.12-log |-----------------------------创建数据库rootlocalhost[(none)] create database tempdb;Query OK, 1 row affected (0.00 sec)-- Author : Leshami-- Blog http://blog.csdn.net/leshamirootlocalhost[(none)] use tempdbDatabase changed--创建用于访问的表rootlocalhost[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存储引擎表rootlocalhost[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 ) ENGINEInnoDB DEFAULT CHARSETutf8--创建用于远程访问的账户rootlocalhost[tempdb] grant all privileges on tempdb.* to remote_user192.168.1.131 identified by xxx;Query OK, 0 rows affected (0.00 sec)rootlocalhost[tempdb] flush privileges;Query OK, 0 rows affected (0.00 sec)4、演示FEDERATED存储引擎跨实例访问[rootrhel64a ~]# mysql -uroot -pxxxrootlocalhost[(none)] show variables like version;-----------------------| Variable_name | Value |-----------------------| version | 5.6.21 |-----------------------#查看是否支持FEDERATED引擎rootlocalhost[(none)] select * from information_schema.engines where enginefederated;------------------------------------------------------------------------------------| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |------------------------------------------------------------------------------------| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |------------------------------------------------------------------------------------rootlocalhost[(none)] exit[rootrhel64a ~]# service mysql stopShutting down MySQL..[ OK ]#配置启用FEDERATED引擎[rootrhel64a ~]# vi /etc/my.cnf[rootrhel64a ~]# tail -7 /etc/my.cnf[mysqld]socket /tmp/mysql3306.sockport 3306pid-file /var/lib/mysql/my3306.piduser mysqlserver-id3306/federated #添加该选项[rootrhel64a ~]# service mysql startStarting MySQL.[ OK ][rootrhel64a ~]# mysql -uroot -pxxxrootlocalhost[(none)] select * from information_schema.engines where enginefederated;------------------------------------------------------------------------------------| ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS |------------------------------------------------------------------------------------| FEDERATED | YES | Federated MySQL storage engine | NO | NO | NO |------------------------------------------------------------------------------------rootlocalhost[(none)] use test-- 创建基于FEDERATED引擎的表federated_enginerootlocalhost[test] CREATE TABLE federated_engine (- engine varchar(64) NOT NULL DEFAULT ,- support varchar(8) NOT NULL DEFAULT ,- comment varchar(80) NOT NULL DEFAULT - ) ENGINEFEDERATED DEFAULT CHARSETutf8- CONNECTIONmysql://remote_user:xxx192.168.1.131:3406/tempdb/tb_engine;Query OK, 0 rows affected (0.00 sec)-- 下面是创建后表格式文件rootlocalhost[test] system ls -hltr /var/lib/mysql/testtotal 12K-rw-rw---- 1 mysql mysql 8.5K Oct 24 08:22 federated_engine.frm--查询表federated_enginerootlocalhost[test] select * from federated_engine limit 2;------------------------------------------------------------| engine | support | comment |------------------------------------------------------------| MRG_MYISAM | YES | Collection of identical MyISAM tables || CSV | YES | CSV storage engine |--------------------------------------------------------------更新表federated_enginerootlocalhost[test] update federated_engine set supportNO where engineCSV;Query OK, 1 row affected (0.03 sec)Rows matched: 1 Changed: 1 Warnings: 0--查看更新后的结果rootlocalhost[test] select * from federated_engine where engineCSV;-------------------------------------| engine | support | comment |-------------------------------------| CSV | NO | CSV storage engine |-------------------------------------5、创建FEDERATED引擎表的链接方式scheme://user_name[:password]host_name[:port_num]/db_name/tbl_namescheme: 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.链接示例样本:CONNECTIONmysql://username:passwordhostname:port/database/tablenameCONNECTIONmysql://usernamehostname/database/tablenameCONNECTIONmysql://username:passwordhostname/database/tablename
