FEDERATED
存储引擎概述
FEDERATED
存储引擎能让你访问远程的MySQL
数据库而不使用replication
或cluster
技术(类似于Oracle
的dblink
),使用FEDERATED
存储引擎的表,本地只存储表的结构信息,数据都存放在远程数据库上,查询时通过建表时指定的连接符去获取远程库的数据返回到本地。
FEDERATED
存储引擎架构
1、本地服务器 FEDERATED
存储引擎的表只存放表的 .frm
结构文件
2、远程服务器存放了 .frm
和数据文件
3、增删改查操作都是通过建立的连接来访问远程数据库进行操作,把结果返回给本地。
4、远程数据表的存储引擎为 MySQL
支持的存储引擎,如 MyISAM,InnoDB
等
FEDERATED
存储引擎默认不启用
如果是使用的源码,需要使用
CMake
加上DWITH_FEDERATED_STORAGE_ENGINE
选项。
如果是二进制包,则在启动MySQL
时指定[--federated]
选项开启或在my.cnf
文件中的[mysqld]
部分加上federated
参数
FEDERATED
存储引擎操作步骤
操作步骤:
远程库:
开启 `FEDERATED` 存储引擎
建立远程访问用户
授予访问对象的权限
本地库:
测试登陆远程库是否能成
创建 `FEDERATED` 表
查询是否成功
目标:将远程 B
库中的 b
表,映射为本地 A
库中 a
表
远程机器的 IP
地址:192.168.200.100
第一步:远程库 B
、本地库 A
都开启 FEDERATED
存储引擎
1、使用下面的 sql
语句,查看是否开启了 FEDERATED
存储引擎的支持
select engine,support from information_schema.engines where engine='FEDERATED';
+-----------+---------+
| engine | support |
+-----------+---------+
| FEDERATED | NO |
+-----------+---------+
1 row in set (0.00 sec)
也可使用 show engines
查看支持的存储引擎。
2、如果查看到 support
为 NO
,则需要在 my.cnf
中 [mysqld]
增加 federated
参数,并重启 MySQL
服务器,将配置生效
3、再次查询,结果如下
select engine,support from information_schema.engines where engine='FEDERATED';
+-----------+---------+
| engine | support |
+-----------+---------+
| FEDERATED | YES |
+-----------+---------+
1 row in set (0.00 sec)
若查看到 support
为 YES
,则配置生效。
第二步:在远程库 B
建立远程访问用户,并授权
1、查看数据库用户
select user,host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| root | % |
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
3 rows in set (0.00 sec)
2、创建一个 federated
连接的用户
create user 'fed'@'%' identified by 'fed_test';
3、授予创建的 fed
用户访问 employees
数据库所有表的权限
grant all on employees.* to 'fed'@'%';
查看用户信息
select user,host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| fed | % |
| root | % |
| mysql.sys | localhost |
| root | localhost |
+-----------+-----------+
4 rows in set (0.00 sec)
查看用户权限
show grants for 'fed'@'%'; --
+----------------------------------------------------+
| Grants for fed@% |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'fed'@'%' |
| GRANT ALL PRIVILEGES ON `employees`.* TO 'fed'@'%' |
+----------------------------------------------------+
2 rows in set (0.00 sec)
第三步:在本地库 A
所在的机器上,查看是否可以正常访问远程库 B
mysql -ufed -h192.168.200.100 -P3306 -p --在本地服务器上去连接远程库
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
>
如果没有连接成功
判断是否防火墙的问题
查看端口是否正确
第四步:在本地库 A
上,创建本地 FEDERATED
表
方法一:直接创建
1、在本地创建一个数据库,也可使用已存在的数据库
create database test;
2、使用新建的数据库
use test;
3、使用新建的数据库
CREATE TABLE `employees_fed` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=federated DEFAULT CHARSET=utf8mb4
connection='mysql://fed:fed_test@192.168.200.100:3306/employees/employees';
查看创建完成 federated
存储引擎的表
注意:本地表 employees_fed
的结构要和远程表 employees
一样,可以提前在远程表中通过 show create table table_name
来获取表结构。
select * from employees_fed limit 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.36 sec)
查看数据文件存放目录,会发现只形成了 .frm
的文件
方法二:使用 CREATE SERVER
方式创建 FEDERATED
表
1、创建一个 server
CREATE SERVER emp_link
FOREIGN DATA WRAPPER mysql
OPTIONS (
USER 'fed',
PASSWORD 'fed_test',
HOST '192.168.200.100',
PORT 3306,
DATABASE 'employees'
);
2、查看已创建的 server
select * from mysql.servers;
3、创建基于 SERVER
的 FEDERATED
表
CREATE TABLE `employees_fed` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=federated DEFAULT CHARSET=utf8mb4
connection='emp_link/employees';
验证是否配置成功
select * from employees_link limit 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 |
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.35 sec)
这种方式的好处在于创建本地 FEDERATED
表时,在 connection
中直接指定已经创建好的 server link
,不需要每次都配置一个新的连接。并且便于统一管理,只需要修改 server link
即可
第五步:FEDERATED
引擎使用注意事项
1、
FEDERATED
表可能会被复制到其他的slave
数据库,你需要确保slave
服务器也能够使用定义在connection
中或mysql.servers
表中的link
的用户名/密码 连接上远程服务器。
2、远程服务器必须是
MySQL
数据库
3、在访问
FEDERATED
表中定义的远程数据库的表前,远程数据库中必须存在这张表。
4、
FEDERATED
表不支持通常意义的索引,服务器从远程库获取所有的行然后在本地进行过滤,不管是否加了where
条件或limit
限制。
查询可能造成性能下降和网络负载,因为查询返回的数据必须存放在内存中,所以容易造成使用系统的swap分区或挂起。
5、
FEDERATED
表不支持字段的前缀索引
6、
FEDERATED
表不支持ALTER TABLE
语句或者任何DDL
语句
7、
FEDERATED
表不支持事务
8、本地
FEDERATED
表无法知道远程库中表结构的改变
9、任何
drop
语句都只是对本地库的操作,不对远程库有影响