<返回更多

MySQL数据库如何实现跨服务器访问数据

2023-11-30  微信公众号  数据库干货铺
加入收藏

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

1、如何实现

先说结论:在MySQL数据库中,是可以实现跨实例(跨服务器)访问另一个库中表的。

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

开启FEDERATED存储引擎:

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

[mysqld]
federated

开启后如下:

MySQL数据库如何实现跨服务器访问数据

可见,已经支持FEDERATED存储引擎。

2、具体案例

下面列举具体示例来演示。

(1)具体案例

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

实现步骤:

在服务器A中创建表

mysql> create database  testdb1;
Query OK, 1 row affected (0.00 sec)
mysql> use testdb1;
Database changed
mysql> 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)

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

mysql> create database testdb2;
Query OK, 1 row affected (0.00 sec)
mysql> use testdb2;
Database changed
mysql> 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)其他操作

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

在服务器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)
 

MySQL数据库如何实现跨服务器访问数据

在A服务器上查看数据情况:

mysql> use testdb1;
Database changed
mysql> 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)

MySQL数据库如何实现跨服务器访问数据

其他操作

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 option
mysql> truncate table testdb2_tb1;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from testdb2_tb1;
Empty set (0.00 sec)

可见:增删改查均可以,但是不支持ALTER TABLE操作,可以支持truncate table操作。

3、小结

MySQL数据库使用FEDERATED引擎表表,可以实现库实例(跨服务器)的数据访问及处理,这极大的方便了数据间的关联、对比及数据治理。关于其实现原理及优劣势可以在以后的课程合集中细说,感兴趣的也可以多实验了解。

关键词:MySQL      点击(17)
声明:本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▍相关推荐
更多MySQL相关>>>