mysql数据库federated存储引擎

1、概述

msyql数据库federated存储引擎是本场端访问、修改远端mysql数据库表数据，与oracle数据库database link类似，但也存在着如下差异：

每个federated表都有连接串，而oracle只创建一个连接

每个federated表都必须明确指定远端数据库表，而oracle没有限制表

每个本地(local)federated表都有与远端数据库表兼容的定义，而oracle不需要定义，直接引用表

存在安全隐患，无论是采用server方式，还是采用connection string方式，连接密码都是明文，而oracle是加密了的

若采用server方式，是全局，在server中的所有schema都可见server及使用它，而oracle可以public和private 

 

---

---

2、remote端建立非federated表

mysql> select database() \G
*************************** 1. row ***************************
database(): stumysql
1 row in set (0.00 sec)

mysql> show create table student \G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `std_no` int(11) DEFAULT NULL,
  `std_name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
 

---

---

3、local端创建federated表

3.1启用federated存储引擎

    在配制文件/etc/my.cnf[mysqld]区中增加一行“federated”，然后重启mysqld，验证结果如下：

mysql> show engines \G
*************************** 1. row ***************************
      Engine: FEDERATED
     Support: YES
     Comment: Federated MySQL storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
  ...

 

3.2创建federated表，有两种方式,是connection string,另一种是通过server

3.2.1 server 方式

mysql> create server db196
    -> foreign data wrapper mysql
    -> options(host '192.168.1.196',user 'hsdss',password '196mySQL.',database 'stumysql') ;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `student` (
    ->   `std_no` int(11) DEFAULT NULL,
    ->   `std_name` varchar(20) DEFAULT NULL,
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=FEDERATED 
    -> connection 'db196/student'
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from student ;
+--------+----------+----+
| std_no | std_name | id |
+--------+----------+----+
|      1 | x        |  1 |
|      2 | b        |  2 |
|      3 | x        |  4 |
|      5 | d        |  6 |
+--------+----------+----+
4 rows in set (0.07 sec)

mysql> 

 

mysql> insert into student values(7,'y',8) ;
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+--------+----------+----+
| std_no | std_name | id |
+--------+----------+----+
|      1 | x        |  1 |
|      2 | b        |  2 |
|      3 | x        |  4 |
|      5 | d        |  6 |
|      7 | y        |  8 |
+--------+----------+----+
5 rows in set (0.00 sec)

mysql> 

 

在本地只有表定义，没有对应的表数据文件，通过分析sdi文件，实际上是一个json文件

[root@idbmaster usrfederated]# ls -ltr
total 4
-rw-r----- 1 mysql mysql 3335 May 29 23:17 student_356.sdi

{
    "mysqld_version_id":80016,
    "dd_version":80016,
    "sdi_version":80016,
    "dd_object_type":"Table",
    "dd_object":{
        "name":"student",
        "mysql_version_id":80016,
        "created":20190529151704,
        "last_altered":20190529151704,
        "hidden":1,
        "options":"avg_row_length=0;connection_string=db196/student;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
        "columns":[
            {
                "name":"std_no",
                "type":4,
                "is_nullable":true,
                "is_zerofill":false,
                "is_unsigned":false,
                "is_auto_increment":false,
                "is_virtual":false,
                "hidden":1,
                "ordinal_position":1,
                "char_length":11,
                "numeric_precision":10,
                "numeric_scale":0,
                "numeric_scale_null":false,
                "datetime_precision":0,
                "datetime_precision_null":1,
                "has_no_default":false,
                "default_value_null":true,
                "srs_id_null":true,
                "srs_id":0,
                "default_value":"",
                "default_value_utf8_null":true,
                "default_value_utf8":"",
                "default_option":"",
                "update_option":"",
                "comment":"",
                "generation_expression":"",
                "generation_expression_utf8":"",
                "options":"interval_count=0;",
                "se_private_data":"",
                "column_key":1,
                "column_type_utf8":"int(11)",
                "elements":[

                ],
                "collation_id":255,
                "is_explicit_collation":false
            },
            {
                "name":"std_name",
                "type":16,
                "is_nullable":true,
                "is_zerofill":false,
                "is_unsigned":false,
                "is_auto_increment":false,
                "is_virtual":false,
                "hidden":1,
                "ordinal_position":2,
                "char_length":80,
                "numeric_precision":0,
                "numeric_scale":0,
                "numeric_scale_null":true,
                "datetime_precision":0,
                "datetime_precision_null":1,
                "has_no_default":false,
                "default_value_null":true,
                "srs_id_null":true,
                "srs_id":0,
                "default_value":"",
                "default_value_utf8_null":true,
                "default_value_utf8":"",
                "default_option":"",
                "update_option":"",
                "comment":"",
                "generation_expression":"",
                "generation_expression_utf8":"",
                "options":"interval_count=0;",
                "se_private_data":"",
                "column_key":1,
                "column_type_utf8":"varchar(20)",
                "elements":[

                ],
                "collation_id":255,
                "is_explicit_collation":false
            },
            {
                "name":"id",
                "type":4,
                "is_nullable":false,
                "is_zerofill":false,
                "is_unsigned":false,
                "is_auto_increment":true,
                "is_virtual":false,
                "hidden":1,
                "ordinal_position":3,
                "char_length":11,
                "numeric_precision":10,
                "numeric_scale":0,
                "numeric_scale_null":false,
                "datetime_precision":0,
                "datetime_precision_null":1,
                "has_no_default":false,
                "default_value_null":false,
                "srs_id_null":true,
                "srs_id":0,
                "default_value":"AAAAAA==",
                "default_value_utf8_null":true,
                "default_value_utf8":"",
                "default_option":"",
                "update_option":"",
                "comment":"",
                "generation_expression":"",
                "generation_expression_utf8":"",
                "options":"interval_count=0;",
                "se_private_data":"",
                "column_key":2,
                "column_type_utf8":"int(11)",
                "elements":[

                ],
                "collation_id":255,
                "is_explicit_collation":false
            }
        ],
        "schema_ref":"usrfederated",
        "se_private_id":18446744073709551615,
        "engine":"FEDERATED",
        "last_checked_for_upgrade_version_id":0,
        "comment":"",
        "se_private_data":"",
        "row_format":2,
        "partition_type":0,
        "partition_expression":"",
        "partition_expression_utf8":"",
        "default_partitioning":0,
        "subpartition_type":0,
        "subpartition_expression":"",
        "subpartition_expression_utf8":"",
        "default_subpartitioning":0,
        "indexes":[
            {
                "name":"PRIMARY",
                "hidden":false,
                "is_generated":false,
                "ordinal_position":1,
                "comment":"",
                "options":"flags=0;",
                "se_private_data":"",
                "type":1,
                "algorithm":1,
                "is_algorithm_explicit":false,
                "is_visible":true,
                "engine":"FEDERATED",
                "elements":[
                    {
                        "ordinal_position":1,
                        "length":4,
                        "order":1,
                        "hidden":false,
                        "column_opx":2
                    }
                ]
            }
        ],
        "foreign_keys":[

        ],
        "check_constraints":[

        ],
        "partitions":[

        ],
        "collation_id":255
    }
}