MySQL - FEDERATED引擎实现跨服务器查询

1. MySQL插件的安装与卸载

# 查看插件信息
mysql> show plugins;
mysql> select plugin_name,plugin_status,plugin_library,plugin_description from information_schema.plugins;

# 查看插件共享库位置
mysql> show variables like 'plugin_dir';
+-----------------+---------------------------+
| Variable_name   | Value                     |
+-----------------+---------------------------+
| plugin_dir      |/usr/lib64/mysql/plugin/   |
+-----------------+---------------------------+

# 安装插件
install plugin PLUGIN_NAME soname 'SHARTED_LIBRARY_NAME.so'

# 卸载插件
uninstall plugin PLUGIN_NAME

# 开启/关闭插件
$ vim /etc/my.cnf
[mysqld]
PLUGIN_NAME = ON|OFF

 

2. 激活FEDERATED插件，并创建垮服务器数据库表

　　FEDERATED存储引擎访问在远程数据库的表中的数据，而不是本地的表。这个特性给某些开发应用带来了便利，你可以直接在本地构建一个FEDERATED表来连接远程数据表，配置好了之后本地表的数据可以直接跟远程数据表同步。实际上这个引擎里面是不真实存放数据的，所需要的数据都是连接到其他MySQL服务器上。

2.1 查看FEDERATED引擎是否开启

# 方法1：查看插件,ACTIVE为开启，DISABLED为未开启
> show plugins;
+--------------------------------+----------+--------------------+--------------------+---------+
| Name                           | Status   | Type               | Library            | License |
+--------------------------------+----------+--------------------+--------------------+---------+
| binlog                         | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| FEDERATED                      | DISABLED | STORAGE ENGINE     | NULL               | GPL     |
| rpl_semi_sync_master           | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
+--------------------------------+----------+--------------------+--------------------+---------+
# 方法2：查看引擎，YES为开启，NO为未开启
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| 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         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| FEDERATED          | NO      | FederatedX pluggable storage engine                                        | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+

 

2.2 开启FEDERATED插件

# MySQL配置文件中添加federated[=ON]，然后重启MySQL服务
$ vim /etc/my.cnf
[mysqld]
federated=ON

$ systemctl restart mariadb

 

 2.3 创建一张虚拟表，此表链接到另外一台服务器数据库上的表中

# 创建数据库
mysql> CREATE DATABASE DB_NAME DEFAULT CHARSET utf8;

# 创建表
# USER:数据库账号，PASSWD：数据库密码，HOST：远程数据库连接地址，PORT：端口，DB_NAME：数据库名，TB_NAME：表名
mysql> USE DB_NAME;
mysql> CREATE TABLE TABLE_NAME (
    ...
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://USER:PASSWD@HOST:PORT/DB_NAME/TABLE_NAME'

 

2.4 注意事项

　　1. 对本地虚拟表的结构修改，并不会修改远程表的结构 
　　2.truncate 命令，会清除远程表数据 
　　3.  drop命令只会删除虚拟表，并不会删除远程表