MySQL创建远程链接服务器LinkServer步骤

环境准备

• 执行show engines;

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

情况1: 没有FEDERATED这个引擎

• 处理：安装FEDERATED插件

install plugin federated soname 'ha_federated.so';

show plugin;

show engines; 
# 这时候会进入情况2:

情况2: FEDERATED| NO FEDERATED引擎已安装，但未启用(最常见)

• 处理：启用FEDERATED
• 修改my.cnf
• 在[mysqld]这一段里加上FEDERATED
• 示例：

...

[mysqld]
federated
default-time_zone = '+8:00'
character_set_server = utf8mb4
... ...

• 重启mysql服务后执行show engines; 进入情况3

情况3: FEDERATED| YES 已完成准备

• 这种情况就表示环境已就绪，可以创建linkserver了

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

创建链接服务器

• 创建linkserver的语法比较简单，如果是mysql到mysql

CREATE SERVER {servername}
FOREIGN DATA WRAPPER mysql
OPTIONS (HOST '{远程域名或ip}',PORT {端口号},DATABASE ' {dbname}',USER '{用户名}', PASSWORD '{密码}');

# 示例：

CREATE SERVER my02testdb
FOREIGN DATA WRAPPER mysql
OPTIONS (HOST '172.10.2.02',PORT 3308,DATABASE 'testdb',USER 'testdb_reader', PASSWORD 'Abcde.123456');

使用链接服务器

• 需要在本地建一张映射表
• 建议的使用方式如下：

create database dbremote;
use dbremote;

create table t1(`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_no` varchar(50) NOT NULL DEFAULT '' COMMENT '用户工号',
  `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '用户姓名',
    `creation_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) 
)  ENGINE=FEDERATED CONNECTION='my02testdb';

• 注意这里的t1表和远程服务器上的t1表结构最好是相同的，可以少字段，不能多字段

总结

• mysql创建链接服务器需要FEDERATED存储引擎
• 需要在本地创建映射表
• 使用时因为是远程访问，速度不快
• 注意如果CREATE SERVER时的用户有写入权限
• 在映射表的更新会更新远程表