MySQL映射远程数据库（两种方式）

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 语句都只是对本地库的操作，不对远程库有影响

详细信息参考