MySQL存储引擎--------Federated最佳实战

2021-07-12 15:13:24

1. 背景

   * 本地MySQL数据库要访问远程MySQL数据库的表中的数据, 必须通过FEDERATED存储引擎来实现. 

　 * 有点类似Oracle中的数据库链接(DBLINK). 要允许这个存储引擎, 当构建MySQL时使用--with-federated-storage-engine来configure.  

   * 当创建一个FEDERATED表的时候, 服务器在数据库目录创建一个表定义文件. 文件由表的名字开始, 并有一个.frm扩展名. 

   * 无其它文件被创建, 因为实际的数据在一个远程数据库上. 


2. 相关特性

　 * 允许本地访问远程 MySQL 数据库中表的数据

   * 本地不存储任何数据文件

   * 仅支持 MySQL 对 MySQL 的访问

   * 不支持异构数据库的访问

   * MySQL默认不开启Federated存储引擎


3. 环境

     两个MySQL 5.7实例

　  serverA　3306

     serverB    3307

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
[root@MySQL ~]# mysql -S /tmp/mysql.sock1 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.18 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2017, 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.
 
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.18    |
+-----------+
1 row in set (0.00 sec)
 
[root@MySQL ~]# mysql -S /tmp/mysql.sock2 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.18 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2017, 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.
 
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.18    |
+-----------+
1 row in set (0.00 sec)

   

4. Federated存储引擎设置

   * 查看Federated是否开启 [ FEDERATED 中Support状态NO表明引擎未开启 ]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)


   * 配置文件指定开启Federated存储引擎　[ /etc/my.cnf ]

1
2
[mysqld]
federated


   * 重启MySQL 再次查看 [ FEDERATED 中Support状态成YES表明引擎开启成功 ]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)


5. 部署 

   * 在ServerA上有一个数据库dbtestA，在ServerB上有数据库dbtestB，要在ServerB的数据库dbtestB上建立ServerA的数据库dbtestA上的表tabletestA的数据表链接remote_tabletestA，通过普通用户test连接。


   * ServerA创建数据库

1
2
mysql> create database dbtestA;
Query OK, 1 row affected (0.02 sec)


   * ServerA在dbtestA数据库中创建tabletestA表

1
2
3
4
mysql> create table tabletestA(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT
)ENGINE=INNODB;
Query OK, 0 rows affected (0.11 sec)


   * ServerA中创建普通用户并授权dbtestA数据库相关权限

1
2
3
4
5
mysql> create user 'test'@'127.0.0.1' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
 
mysql> grant select on dbtestA.* to 'test'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)


   * ServerB 中创建数据库 dbtestB

1
2
mysql> create database dbtestB;
Query OK, 1 row affected (0.00 sec)


   * ServerB 在dbtestB中创建链接表remote_tabletestA, 使用普通用户test

1
2
3
4
5
6
7
mysql> use dbtestB;
Database changed
mysql> create table remote_tabletestA(
    -> id INT PRIMARY KEY NOT NULL AUTO_INCREMENT
    -> )ENGINE=FEDERATED
    -> CONNECTION='mysql://test:123456@127.0.0.1:3306/dbtestA/tabletestA';
Query OK, 0 rows affected (0.09 sec)


   * 在ServerA dbtestA库tableA表中插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Database changed
mysql> use dbtestA;
Database changed
mysql> insert into tabletestA select NULL;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
mysql> insert into tabletestA select NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
mysql> insert into tabletestA select NULL;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
mysql> select * from tabletestA;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)


   * ServerB dbtestB库链接表remote_tabletestA查看

1
2
3
4
5
6
7
8
9
10
11
mysql> use dbtestB;
Database changed
mysql> select * from remote_tabletestA;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)


   * ServerB服务器中查看链接表remote_tablestestA相关文件

　　 .frm 表定义文件　[ Federated链接库本地不产生数据文件 ]

1
2
3
4
[root@MySQL ~]# ll /data/mysql_data2/dbtestB/
total 16
-rw-r----- 1 mysql mysql   65 Jun 25 10:40 db.opt
-rw-r----- 1 mysql mysql 8556 Jun 25 10:42 remote_tabletestA.frm


6. 总结


以需求驱动技术，技术本身没有优略之分，只有业务之分。





      本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1941801，如需转载请自行联系原作者

上一篇：音频格式G711转PCM的代码


下一篇：C# 如何以参数的形式调用.exe程序