Skip to content

servers

Jump to bottom
xiaoboluo768 edited this page Jun 7, 2020 · 2 revisions
  • 该表提供查询连接组合信息(远程实例的IP、端口、帐号、密码、数据库名称等信息，详见后续示例)，这些连接组合信息通常用于federated引擎（当然也可以作为在数据库中保存连接组合的一种方式，维护也较为方便），该表中的信息需要使用create server方式创建。
  • 在介绍别字段含义之前，先看看dederated引擎的两种创建方式
# 使用create server方式创建的连接组合
Syntax:
CREATE SERVER server_name
    FOREIGN DATA WRAPPER wrapper_name
    OPTIONS (option [, option] ...)

option:
  { HOST character-literal
  | DATABASE character-literal
  | USER character-literal
  | PASSWORD character-literal
  | SOCKET character-literal
  | OWNER character-literal
  | PORT numeric-literal }

# 直接使用CONNECTION选项指定完整的连接组合
CONNECTION=scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
  • 表字段含义
  • Server_name：连接组合唯一标识（即名称，使用drop server删除连接组合记录时，直接指定该表中存在的server_name即可删除组合记录，如：drop server server_name;）
  • Host：连接组合中的远程主机名（IP或域名），对应create server中的HOST，对应CONNECTION连接组合字符串中的host_name
  • Db：连接组合中的远程实例的数据库名称，对应create server中的DATABASE ，对应CONNECTION连接组合字符串中的db_name
  • Username：连接组合的远程实例用户名,对应create server中的USER ，对应CONNECTION连接组合字符串中的user_name
  • Password：连接组合的远程实例用户密码，对应create server中的PASSWORD ，对应CONNECTION连接组合字符串中的password
  • Port：连接组合的远程实例端口，对应create server中的PORT ，对应CONNECTION连接组合字符串中的port_num
  • Socket：连接组合的本地实例的socket路径，对应create server中的SOCKET ，对应CONNECTION连接组合字符串中的host_name
  • Wrapper：类似一个协议名称，对应create server中的WRAPPER ，对应CONNECTION连接组合字符串中的scheme
  • 表记录内容示例
root@localhost Tue Jun 5 01:12:05 2018 01:12:05 [(none)]>CREATE SERVER fedlink_ip
    -> FOREIGN DATA WRAPPER mysql
    -> OPTIONS (USER 'test',PASSWORD 'test', HOST '127.0.0.1', PORT 3306, DATABASE 'test_table',Owner 'test_table1');
Query OK, 1 row affected (0.00 sec)

root@localhost Tue Jun 5 01:12:10 2018 01:12:10 [(none)]>CREATE SERVER fedlink_socket
    -> FOREIGN DATA WRAPPER mysql
    -> OPTIONS (USER 'test',PASSWORD 'test', SOCKET '/data/mysql/mysql3306/data/mysql.sock', PORT 3306, DATABASE 'test_table',Owner 'test_table2');
Query OK, 1 row affected (0.00 sec)

root@localhost Tue Jun 5 01:12:10 2018 01:12:10 [(none)]>CREATE SERVER fedlink_socket_ip
    -> FOREIGN DATA WRAPPER mysql
    -> OPTIONS (USER 'test',PASSWORD 'test', HOST '127.0.0.1',SOCKET '/data/mysql/mysql3306/data/mysql.sock', PORT 3306, DATABASE 'test_table',Owner 'test_table3');
Query OK, 1 row affected (0.00 sec)

root@localhost Tue Jun 5 01:12:10 2018 01:12:10 [(none)]>select * from mysql.servers;
+-------------------+-----------+------------+----------+----------+------+---------------------------------------+---------+-------------+
| Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner |
+-------------------+-----------+------------+----------+----------+------+---------------------------------------+---------+-------------+
| fedlink_socket_ip | 127.0.0.1 | test_table | test | test | 3306 | /data/mysql/mysql3306/data/mysql.sock | mysql | test_table3 |
| fedlink_socket | | test_table | test | test | 3306 | /data/mysql/mysql3306/data/mysql.sock | mysql | test_table2 |
| fedlink_ip | 127.0.0.1 | test_table | test | test | 3306 | | mysql | test_table1 |
+-------------------+-----------+------------+----------+----------+------+---------------------------------------+---------+-------------+
3 rows in set (0.00 sec)

# 如果要删除连接组合记录，可以使用如下语句
root@localhost Tue Jun 5 01:10:41 2018 01:10:41 [(none)]>drop SERVER fedlink;
Query OK, 1 row affected (0.00 sec)

root@localhost Tue Jun 5 01:11:30 2018 01:11:30 [(none)]>drop SERVER fedlink_socket ;
Query OK, 1 row affected (0.00 sec)

root@localhost Tue Jun 5 01:11:55 2018 01:11:55 [(none)]>drop SERVER fedlink_socket_ip;
Query OK, 1 row affected (0.00 sec)
  • federated引擎的两种使用方式读写远程实例数据示例
# 创建远程实例用户
root@localhost Tue Jun 5 00:23:45 2018 00:23:45 [(none)]>grant all on *.* to test@'%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

# 创建用于存放远程实例表的库
root@localhost Tue Jun 5 00:24:06 2018 00:24:06 [(none)]>create database test_table;
Query OK, 1 row affected (0.00 sec)

root@localhost Tue Jun 5 00:30:50 2018 00:30:50 [(none)]>use test_table
Database changed

# 创建远程实例表test_table1和test_table2
root@localhost Tue Jun 5 00:31:03 2018 00:31:03 [test_table]>CREATE TABLE test_table1 (
    -> id INT(20) NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(32) NOT NULL DEFAULT '',
    -> other INT(20) NOT NULL DEFAULT '0',
    -> PRIMARY KEY (id),
    -> INDEX name (name),
    -> INDEX other_key (other)
    -> );
Query OK, 0 rows affected (0.06 sec)

root@localhost Tue Jun 5 00:31:09 2018 00:31:09 [test_table]>CREATE TABLE test_table2 (
    -> id INT(20) NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(32) NOT NULL DEFAULT '',
    -> other INT(20) NOT NULL DEFAULT '0',
    -> PRIMARY KEY (id),
    -> INDEX name (name),
    -> INDEX other_key (other)
    -> );
Query OK, 0 rows affected (0.00 sec)

# 创建存放federated引擎表的库
root@localhost Tue Jun 5 00:31:16 2018 00:31:16 [test_table]>create database federated;
Query OK, 1 row affected (0.00 sec)

root@localhost Tue Jun 5 00:31:22 2018 00:31:22 [test_table]>use federated
Database changed

# 使用create server方式创建一个连接字符串组合，该记录会保存到mysql.servers表中
root@localhost Tue Jun 5 00:31:25 2018 00:31:25 [federated]>CREATE SERVER fedlink
    -> FOREIGN DATA WRAPPER mysql
    -> OPTIONS (USER 'test',PASSWORD 'test', HOST '127.0.0.1', PORT 3306, DATABASE 'test_table');
Query OK, 1 row affected (0.03 sec)

# 查看mysql.servers表中的记录
root@localhost Tue Jun 5 00:31:37 2018 00:31:37 [federated]>select * from mysql.servers;
+-------------+-----------+------------+----------+----------+------+--------+---------+-------+
| Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner |
+-------------+-----------+------------+----------+----------+------+--------+---------+-------+
| fedlink | 127.0.0.1 | test_table | test | test | 3306 | | mysql | |
+-------------+-----------+------------+----------+----------+------+--------+---------+-------+
1 row in set (0.00 sec)

# 使用create server连接字符串组合方式，创建federated引擎表
root@localhost Tue Jun 5 00:32:12 2018 00:32:12 [federated]>CREATE TABLE federated1 (
    -> id INT(20) NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(32) NOT NULL DEFAULT '',
    -> other INT(20) NOT NULL DEFAULT '0',
    -> PRIMARY KEY (id),
    -> INDEX name (name),
    -> INDEX other_key (other)
    -> )
    -> ENGINE=FEDERATED
    -> CONNECTION='fedlink/test_table1';
Query OK, 0 rows affected (0.04 sec)

root@localhost Tue Jun 5 00:32:17 2018 00:32:17 [federated]>show create table federated1;
...
| Table | Create Table |
...
| federated1 | CREATE TABLE `federated1` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL DEFAULT '',
  `other` int(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `other_key` (`other`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='fedlink/test_table1' |
...
1 row in set (0.00 sec)

# 往federated引擎表federated1中插入数据，然后可以在federated引擎表和远程实例表中都查询到相同的数据
root@localhost Tue Jun 5 00:32:58 2018 00:32:58 [federated]>insert into federated1(name) values('federated1');
Query OK, 1 row affected (0.00 sec)

root@localhost Tue Jun 5 00:33:42 2018 00:33:42 [federated]>select * from federated1;
+----+------------+-------+
| id | name | other |
+----+------------+-------+
| 1 | federated1 | 0 |
+----+------------+-------+
1 row in set (0.00 sec)

root@localhost Tue Jun 5 00:33:49 2018 00:33:49 [federated]>select * from test_table.test_table1;
+----+------------+-------+
| id | name | other |
+----+------------+-------+
| 1 | federated1 | 0 |
+----+------------+-------+
1 row in set (0.00 sec)

# 使用CONNECTION方式完整的连接字符串创建federated引擎表
root@localhost Tue Jun 5 00:32:32 2018 00:32:32 [federated]>CREATE TABLE federated2 (
    -> id INT(20) NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(32) NOT NULL DEFAULT '',
    -> other INT(20) NOT NULL DEFAULT '0',
    -> PRIMARY KEY (id),
    -> INDEX name (name),
    -> INDEX other_key (other)
    -> )
    -> ENGINE=FEDERATED
    -> CONNECTION='mysql://test:test@127.0.0.1:3306/test_table/test_table2';
Query OK, 0 rows affected (0.00 sec)

# 往federated引擎表federated2中插入数据，然后可以在federated引擎表和远程实例表中都查询到相同的数据
root@localhost Tue Jun 5 00:34:08 2018 00:34:08 [federated]>insert into federated2(name) values('federated2');
Query OK, 1 row affected (0.00 sec)

root@localhost Tue Jun 5 00:34:16 2018 00:34:16 [federated]>select * from test_table.test_table2;
+----+------------+-------+
| id | name | other |
+----+------------+-------+
| 1 | federated2 | 0 |
+----+------------+-------+
1 row in set (0.00 sec)

root@localhost Tue Jun 5 00:34:22 2018 00:34:22 [federated]>select * from federated2;
+----+------------+-------+
| id | name | other |
+----+------------+-------+
| 1 | federated2 | 0 |
+----+------------+-------+
1 row in set (0.00 sec)

root@localhost Tue Jun 5 00:34:28 2018 00:34:28 [federated]>select * from mysql.servers;
+-------------+-----------+------------+----------+----------+------+--------+---------+-------+
| Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner |
+-------------+-----------+------------+----------+----------+------+--------+---------+-------+
| fedlink | 127.0.0.1 | test_table | test | test | 3306 | | mysql | |
+-------------+-----------+------------+----------+----------+------+--------+---------+-------+
1 row in set (0.00 sec)

# 使用socket方式类似，如果使用socket时，create server连接组合创建方式参照"表记录内容示例"

上一篇：time_zone_transition_type表 |下一篇：slave_master_info表

  • 验证、测试、整理：罗小波
  • QQ：309969177
  • 提示：本系列文章的主体结构遵循Oracle MySQL 官方 5.7 手册中，关于information_schema、mysql schema、performance_schema、sys schema的章节结构体系，并额外添加了一些验证、测试数据。鉴于本人精力和能力有限，难免出现一些纰漏，欢迎大家踊跃指正！
Clone this wiki locally