MySQL FEDERATED 存储引擎的使用

傷城~ 2024-03-23 11:07 38阅读 0赞

FEDERATED 存储引擎架构

FEDERATED存储引擎能让你访问远程的MySQL数据库而不使用replication或cluster技术(类似于Oracle的dblink),使用FEDERATED存储引擎的表,本地只存储表的结构信息,数据都存放在远程数据库上,查询时通过建表时指定的连接符去获取远程库的数据返回到本地。

FEDERATED存储引擎默认不启用
        如果是使用的源码，需要使用CMake 加上DWITH_FEDERATED_STORAGE_ENGINE选项。
        如果是二进制包,则在启动MySQL时指定 [--federated] 选项开启或在my.cnf文件中的[mysqld]部分加上federated参数

### FEDERATED 存储引擎架构    ###

1 本地服务器 FEDERATED 存储引擎的表只存放表的.frm结构文件
    2 远程服务器 存放了.frm和数据文件
    3 增删改查操作都是通过建立的连接来访问远程数据库进行操作,把结果返回给本地。
    4 远程数据表的存储引擎为MySQL支持的存储引擎,如MyISAM,InnoDB等

### FEDERATED 存储引擎操作步骤 ###

操作步骤：
        远程库:
            开启 FEDERATED 存储引擎
            建立远程访问用户
            授予访问对象的权限
        本地库：

### 远程库 ###

#### 开启 FEDERATED 存储引擎 ####

\--也可使用show engines查看支持的存储引擎

(root@localhost) \[(none)\]>show engines; --排版问题不贴出执行结果

\--如果support 为NO,则需要在my.cnf中\[mysqld\]增加federated参数,并重启MySQL服务器生效配置

#### 建立远程访问用户并授权 ####

###       本地库   测试登陆远程库是否能成 ###

[root@RHEL6 ~]# mysql -ufed -h172.25.21.10 -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.
    
    fed@172.25.21.10 [(none)]>   --成功通过远程机新建的用户登录上远程数据库
    
    --如果没有连接成功
    　 判断是否防火墙的问题
       查看端口是否正确

创建本地 FEDERATED 表

root@localhost [(none)]>create database test;    --在本地创建一个数据库,也可使用已存在的数据库
    Query OK, 1 row affected (0.01 sec)
    
    root@localhost [(none)]>use test;    --使用新建的数据库
    Database changed
    root@localhost [test]>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@172.25.21.10:3306/employees/employees';   
    Query OK, 0 rows affected (0.00 sec)
    
    connection语法：
    scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
    
    具体语法及含义参考官方文档链接:
    http://dev.mysql.com/doc/refman/5.7/en/federated-create-connection.html
    --创建完成federated存储引擎的表,注意:本地表employees_fed的结构要和远程表employees一样,可以提前在远程表中通过show create table table_name来获取表结构并修改或增加标红的语句。

#### 验证是否配置成功 ####

root@localhost [test]>select * from employees_fed limit 10;    --成功获取到远程数据库中的数据

root@localhost [test]>show variables like 'datadir';   --查看数据文件存放目录
    +---------------+-----------------+
    | Variable_name | Value           |
    +---------------+-----------------+
    | datadir       | /var/lib/mysql/ |
    +---------------+-----------------+
    1 row in set (0.00 sec)
    
    root@localhost [test]>system ls -l /var/lib/mysql/test/* 
    -rw-rw----. 1 mysql mysql   61 Mar 30 13:41 /var/lib/mysql/test/db.opt
    -rw-rw----. 1 mysql mysql 8768 Mar 30 13:41 /var/lib/mysql/test/employees_fed.frm   --确定本地只保存了表的结构信息
    
    --配置成功

####       使用 CREATE SERVER 方式创建 FEDERATED表 ####

--创建一个server
    
    root@localhost [test]>CREATE SERVER emp_link
        -> FOREIGN DATA WRAPPER mysql
        -> OPTIONS (USER 'fed', PASSWORD 'fed_test',HOST '172.25.21.10',PORT 3306,DATABASE 'employees');
    
    CREATER SERVER语法：
    CREATE SERVER server_name
    FOREIGN DATA WRAPPER wrapper_name
    OPTIONS (option [, option] ...)
    具体语法及含义参考官方文档链接:
    http://dev.mysql.com/doc/refman/5.7/en/federated-create-server.html
    --查看已创建的server
    root@localhost [test]>select * from mysql.servers\G;
    *************************** 1. row ***************************
    Server_name: emp_link
    Host: 172.25.21.10
    Db: employees
    Username: fed
    Password: fed_test
    Port: 3306
    Socket:
    Wrapper: mysql
    Owner:
    1 row in set (0.00 sec)

#### 创建基于SERVER 的FEDERATED表 ####

root@localhost [test]>CREATE TABLE `employees_link` (
        ->   `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';

#### 验证是否配置成功 ####

root@localhost [test]>select * from employees_link limit 10;
    --配置成功
    --这种方式的好处在于创建本地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语句都只是对本地库的操作,不对远程库有影响