MySQL 两个数据库之间数据交互——Federated Tables
1. 问题描述
有一个虚拟机中的MySQL数据库需要与外部的物理机MySQL数据库，进行数据交互。
因为虚拟机是NAT模式，不是桥接模式，所以只能单向访问，从虚拟机访问物理机的数据库。
2.解决方法
一个通用方法，MySQL复制可以将数据从一个数据库实例（主服务器）复制到一个或多个数据库实例（从服务器）。虽然这不是实时的查询转发，但它可以确保B数据库的数据在A数据库中是可用的。如果仅仅是少数的一两个表，有点复杂。
另外一个方法，MySQL的FEDERATED存储引擎允许创建一个指向远程数据库表的本地表。
操作如下：
(1)检查数据引擎
在 MySQL 8 中，默认情况下 FEDERATED 存储引擎是禁用的。需要手动启用它。
目标数据库A ，访问数据库B 。
检查目标数据库A的数据引擎：
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)可以看到：FEDERATED 没有启动
(2)启动FEDERATED
在目标数据库A的配置文件，在my.cnf文件中添加以下配置：
[mysqld]
federated
然后，重启MySQL服务使配置生效。
(3)增加网络访问用户
在目标数据库A增加用户，确保访问数据库B上的用户具有适当的权限，允许从 A 数据库的 IP 地址进行连接。
-- drop user 'root'@'192.168.36.16';
create user 'root'@'192.168.36.16' identified by 'FEDERATED#2024#';
grant all privileges on *.* to 'root'@'192.168.36.16';
flush privileges;
注意：
由于数据库B使用的是虚拟机，192.168.36.16是虚拟主机的物理主机IP 。
(4)访问数据库上创建federated 表
在访问数据库B上创建federated 表 。
CREATE TABLE federated_TableA (id INT,name VARCHAR(100)
)
ENGINE=FEDERATED
CONNECTION='mysql://root:FEDERATED#2024#@databaseA_IP:3306/DatabaseA/TableA';
关键字：
ENGINE=FEDERATED
执行成功后，就可以在数据库B上select 查询federated_TableA ，实际上是数据库A上的表。
但是实际感觉速度比在A库上查询慢很多。
(5) 遇到问题
- 无法查询
创建federated 表成功，但是查询的时候报错：
1429 - Unable to connect to foreign data source: Access denied for user
就是因为在A数据库上没有B数据库IP用户的访问权限。
在此处，应该是B数据库虚拟机所在物理机的IP ，在此耽误了时间。
- federated 表和源表数据结构是否要一样
B数据库中的FEDERATED表结构不需要与A数据库中的表结构完全一模一样，但推荐保持一致。
B数据库FEDERATED表的字段可以比A数据库表少，但不能多。