点击上方蓝字关注我

在使用MySQL数据库时，很多同学经常会问，我能跨服务器访问另一库的数据么？得到的答案很多时候是让人失望的。那么如果真的需要访问，又不想使用拷贝表及数据的方式，可以实现么，又该如何实现呢？

1. 如何实现

先说结论：在MySQL数据库中，是可以实现跨实例（跨服务器）访问另一个库中表的。

实现方法：MySQL数据库的其中一个优点就是插件式管理，因此，可以使用 FEDERATED 存储引擎来实现来实现。

开启FEDERATED存储引擎：

开启的方式是在配置文件中添加FEDERATED配置，即：

[mysqld]federated

开启后如下：

可见，已经支持FEDERATED存储引擎

2. 具体案例

下面列举具体示例来演示

2.1 具体案例

需求： 假设服务器A实例中的testdb1库里有一张表tb1,现在想在服务器B的testdb2中直接访问testdb1中的tb1表的数据

实现步骤：

1）在服务器A中创建表

mysql> create database  testdb1;Query OK, 1 row affected (0.00 sec)
mysql> use testdb1;Database changedmysql> create table tb1(id int primary key ,c1 varchar(20));Query OK, 0 rows affected (0.01 sec)
mysql> insert into  tb1 values(1,'a');Query OK, 1 row affected (0.01 sec)
mysql> insert into  tb1 values(2,'b'),(3,'ca'),(4,'tc');Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from tb1;+----+------+| id | c1   |+----+------+|  1 | a    ||  2 | b    ||  3 | ca   ||  4 | tc   |+----+------+4 rows in set (0.00 sec)

因为需要远程访问A服务器上的表的权限，因此需创建一个数据库用户用来远程访问

mysql> create user t_user identified by 'Test2023.com';Query OK, 0 rows affected (0.00 sec)
mysql> grant all on testdb1.*  to  t_user;Query OK, 0 rows affected (0.01 sec)

2）在服务器B的数据库testdb2上创建FEDERATED存储引擎表

mysql> create database testdb2;Query OK, 1 row affected (0.00 sec)
mysql> use testdb2;Database changedmysql> create table testdb2_tb1(    -> id INT PRIMARY KEY ,    -> c1 varchar(20)    -> )ENGINE=FEDERATED    -> CONNECTION='mysql://t_user:Test2023.com@127.0.0.1:3306/testdb1/tb1';Query OK, 0 rows affected (0.00 sec)
mysql> show tables;+-------------------+| Tables_in_testdb2 |+-------------------+| testdb2_tb1       |+-------------------+1 row in set (0.00 sec)
mysql> select  * from  testdb2_tb1;+----+------+| id | c1   |+----+------+|  1 | a    ||  2 | b    ||  3 | ca   ||  4 | tc   |+----+------+4 rows in set (0.02 sec)

创建后可以直接访问到A服务器中的tb1表的数据。

2.2 其他操作

除了查询，如果创建FEDERATED引擎表的账号（如本文用的t_user）有增删改的权限，那么也可以通过操作B服务器的testdb2.testdb2_tb1对远程表（服务器A上的testdb.tb1）进行相应的操作，例如：

1）在服务器B上新增数据

mysql> select * from testdb2_tb1;+----+------+| id | c1   |+----+------+|  1 | a    ||  2 | b    ||  3 | ca   ||  4 | tc   |+----+------+4 rows in set (0.00 sec)
mysql> insert into  testdb2_tb1 values(5,'cc'),(6,'ty');Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from testdb2_tb1;+----+------+| id | c1   |+----+------+|  1 | a    ||  2 | b    ||  3 | ca   ||  4 | tc   ||  5 | cc   ||  6 | ty   |+----+------+6 rows in set (0.00 sec)

2）在A服务器上查看数据情况：

mysql> use testdb1;Database changedmysql> select * from tb1;+----+------+| id | c1   |+----+------+|  1 | a    ||  2 | b    ||  3 | ca   ||  4 | tc   |+----+------+4 rows in set (0.00 sec)
mysql> select * from tb1;+----+------+| id | c1   |+----+------+|  1 | a    ||  2 | b    ||  3 | ca   ||  4 | tc   ||  5 | cc   ||  6 | ty   |+----+------+6 rows in set (0.00 sec)

3) 其他操作

mysql> delete from  testdb2_tb1 where id=1;Query OK, 1 row affected (0.01 sec)
mysql> update  testdb2_tb1 set c1='bb' where id=2;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from testdb2_tb1;+----+------+| id | c1   |+----+------+|  2 | bb   ||  3 | ca   ||  4 | tc   ||  5 | cc   ||  6 | ty   |+----+------+5 rows in set (0.00 sec)
mysql> alter table testdb2_tb1 add key idx_c1(c1);ERROR 1031 (HY000): Table storage engine for 'testdb2_tb1' doesn't have this option
mysql> create index idx_c1 on testdb2_tb1(c1);ERROR 1031 (HY000): Table storage engine for 'testdb2_tb1' doesn't have this optionmysql> truncate table testdb2_tb1;Query OK, 0 rows affected (0.03 sec)
mysql> select * from testdb2_tb1;Empty set (0.00 sec)