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> selectdatabase() \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/f[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":0529151704,
"last_altered":0529151704,
"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
}
}
如果觉得《mysql数据库federated存储引擎》对你有帮助，请点赞、收藏，并留下你的观点哦！
