摘要：MySQL的存储引擎之Federated
Federated引擎让我们可以访问远程MySQL数据库中的数据。查询本地Federated表时自动从远程表中提取数据。本地表中不存储任何数据。
是不是和Oracle的dblink很像？我们一起来探究下。
默认情况下，MySQL中未启用Federated引擎，若要启用，必须使用–federed选项启动。
root@database-one 22:25: [gftest]> 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 | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.08 sec)
FEDERATED的Support列值为NO，确实默认没启用。我们来启用下。[mysqld]下增加federated=on参数，重启服务。
[root@database-one /]# cat /etc/my.cnf | grep federated [root@database-one /]# vi /etc/my.cnf [client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysql] prompt="\u@database-one \R:\m:\s [\d]> " no-auto-rehash [mysqld] federated=on ...... "/etc/my.cnf" 156L, 4456C written [root@database-one /]# cat /etc/my.cnf|grep federated federated=on [root@database-one /]# systemctl restart mysqld [root@database-one /]# mysql -uroot -h127.0.0.1 -P3306 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 209 Server version: 5.7.16-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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. root@database-one 22:49: [(none)]> use gftest; Database changed root@database-one 22:49: [gftest]> 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 | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
Federated表由两部分组成：
- 远程MySQL表，表类型可以是远程MySQL支持的任何类型，比如MyISAM或InnoDB。
- 本地存放的表定义.frm文件，表定义包含指向远程表的连接字符串。本地服务器上没有数据文件。
开始实际测试，我们先去远程服务器192.168.64.131上找张表，这里选为employees数据库中的departments
[root@database-one /]# mysql -uroot -P3307 -h192.168.64.131 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 55142300
Server version: 5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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.
root@database-one 23:08: [(none)]> use employees;
Database changed
root@database-one 23:08: [employees]> desc departments;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no | char(4) | NO | PRI | NULL | |
| dept_name | varchar(40) | NO | UNI | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
root@database-one 23:08: [employees]> select count(*) from departments;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.04 sec)
root@database-one 23:09: [employees]> show create table departments \G
*************************** 1. row ***************************
Table: departments
Create Table: CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`),
UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
再回到本地数据库gftest中创建测试表：
[root@database-one /]# mysql -uroot -h127.0.0.1 -P3306 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1077
Server version: 5.7.16-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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.
root@database-one 23:13: [(none)]> use gftest;
Database changed
root@database-one 23:13: [gftest]> create table testfederated(
-> `dept_no` char(4) NOT NULL,
-> `dept_name` varchar(40) NOT NULL,
-> PRIMARY KEY (`dept_no`),
-> UNIQUE KEY `dept_name` (`dept_name`)
-> ) ENGINE=FEDERATED
-> CONNECTION='mysql://root:********@192.168.64.131:3307/employees/departments';
Query OK, 0 rows affected (0.00 sec)
root@database-one 23:18: [gftest]> exit
Bye
[root@database-one /]# cd /home/mysql/gftest/
[root@database-one gftest]# ls -l testfederated*
-rw-r----- 1 mysql mysql 8606 3月 6 23:18 testfederated.frm
CONNECTION字符串用来给出远程表的访问路径，完整格式和说明如下
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
scheme: A recognized connection protocol. Only mysql is supported as the scheme value at this point.
user_name: The user name for the connection. This user must have been created on the remote server, and must have suitable privileges to perform the required actions (SELECT, INSERT, UPDATE, and so forth) on the remote table.
password: (Optional) The corresponding password for user_name.
host_name: The host name or IP address of the remote server.
port_num: (Optional) The port number for the remote server. The default is 3306.
db_name: The name of the database holding the remote table.
tbl_name: The name of the remote table. The name of the local and the remote table do not have to match.
当我们在本地服务器上对Federated表操作时，操作将被发送到远程服务器执行，执行完后远程服务器返回结果。
root@database-one 23:36: [gftest]> select * from testfederated;
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d005 | Development |
| d002 | Finance |
| d003 | Human Resources |
| d001 | Marketing |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
| d007 | Sales |
+---------+--------------------+
9 rows in set (0.02 sec)
root@database-one 23:36: [gftest]> insert into testfederated values('d999','Tech');
Query OK, 1 row affected (0.01 sec)
root@database-one 23:36: [gftest]> update testfederated set dept_name='HR' where dept_no='d003';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@database-one 23:37: [gftest]> select * from testfederated;
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d005 | Development |
| d002 | Finance |
| d003 | HR |
| d001 | Marketing |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
| d007 | Sales |
| d999 | Tech |
+---------+--------------------+
10 rows in set (0.00 sec)
root@database-one 23:38: [gftest]> delete from testfederated where dept_no='d999';
Query OK, 1 row affected (0.05 sec)
root@database-one 23:38: [gftest]> select * from testfederated;
+---------+--------------------+
| dept_no | dept_name |
+---------+--------------------+
| d009 | Customer Service |
| d005 | Development |
| d002 | Finance |
| d003 | HR |
| d001 | Marketing |
| d004 | Production |
| d006 | Quality Management |
| d008 | Research |
| d007 | Sales |
+---------+--------------------+
9 rows in set (0.00 sec)
© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。