MySQL中使用FREDATED引擎实现跨数据库服务器、跨实例访问_MySQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL中使用FREDATED引擎实现跨数据库服务器、跨实例访问_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7373字，纯文字阅读大概需要11分钟。

内容图文

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: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引擎表的链接方式

代码如下:

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'

内容总结

以上是互联网集市为您收集整理的MySQL中使用FREDATED引擎实现跨数据库服务器、跨实例访问_MySQL全部内容，希望文章能够帮你解决MySQL中使用FREDATED引擎实现跨数据库服务器、跨实例访问_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端