MYSQL教程MySQL中使用FREDATED引擎实现跨数据库服务器、跨实例访
发布时间：2020-12-12 02:30:58 所属栏目：MySql教程 来源：网络整理
导读：《MYSQL教程MySQL中使用FREDATED引擎实现跨数据库服务器、跨实例访问》要点： 本文介绍了MYSQL教程MySQL中使用FREDATED引擎实现跨数据库服务器、跨实例访问，希望对您有用。如果有疑问，可以联系我们。 跨数据库服务器,跨实例拜访是比较常见的一种拜访方式,
|
《MYSQL教程MySQL中使用FREDATED引擎实现跨数据库服务器、跨实例访问》要点： 跨数据库服务器,跨实例拜访是比较常见的一种拜访方式,在Oracle中可以通过DB LINK的方式来实现.对于MySQL而言,有一个FEDERATED存储引擎与之相对应.同样也是通过创建一个链接方式的形式来拜访远程服务器上的数据.本文简要描述了FEDERATED存储引擎,以及演示了基于FEDERATED存储引擎跨实例拜访的示例.MYSQL教程 1、FEDERATED存储引擎的描写MYSQL教程 ? FEDERATED存储引擎允许在不使用复制或集群技术的情况下实现远程拜访数据库 ? FEDERATED存储引擎表无任何数据存储到当地,即没有.myd文件
二、安装与启用FEDERATED存储引擎MYSQL教程 ? 源码安装MySQL时使用DWITH_FEDERATED_STORAGE_ENGINE来配置 3、准备长途服务器环境MYSQL教程 代码如下:-- 此演示中远程服务器与本地服务器为同一服务器上的多版本多实例? -- 假定远程服务为：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存储引擎跨实例拜访MYSQL教程 代码如下:[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: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/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引擎表的链接方式MYSQL教程 代码以下: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' 编程之家PHP培训学院每天发布《MYSQL教程MySQL中使用FREDATED引擎实现跨数据库服务器、跨实例访问》等实战技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
