通过mysql的federated插件实现dblink功能

db1：172.26.99.157 3306（源库） db2：172.26.99.157 3310（通过dblink调用源库） [root@node7 lepus]# mm mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 357 Server version: 5.7.29-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, 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. 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         | | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         | | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         | | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         | | MyISAM             | YES     | MyISAM 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.00 sec) mysql> install plugin federated soname 'ha_federated.so';  ERROR 1125 (HY000): Function 'federated' already exists mysql> exit Bye 重启数据库， # mysqladmin -uroot -P3310 -p -h 127.0.0.1 shutdown # mysqld --defaults-file=/mysql/data/my.cnf --user=mysql --datadir=/mysql/data/3310  --basedir=/mysql/app/mysql --pid-file=/mysql/data/mysql3310.pid --socket=/mysql/data/mysql3310.sock --port=3310 &  在my.cnf中添加参数： federated 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         | | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         | | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         | | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         | | MyISAM             | YES     | MyISAM 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) @3306： # mysql -uroot -P3306 -h127.0.0.1 -p mysql> create database testdb; mysql> use testdb; mysql> CREATE TABLE `options` (     ->   `name` varchar(50) DEFAULT NULL,     ->   `value` varchar(255) DEFAULT NULL,     ->   `description` varchar(100) DEFAULT NULL,     ->   KEY `idx_name` (`name`) USING BTREE     -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;      @33310： mysql> use tianlei; mysql> CREATE TABLE `options` (     ->   `name` varchar(50) DEFAULT NULL,     ->   `value` varchar(255) DEFAULT NULL,     ->   `description` varchar(100) DEFAULT NULL,     ->   KEY `idx_name` (`name`) USING BTREE     -> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8     -> CONNECTION='mysql://root:root123@172.26.99.157:3306/testdb/options'; Query OK, 0 rows affected (0.01 sec) mysql> select * from options; Empty set (0.01 sec) @3306: mysql>  mysql> insert into options (name,value ) values ('log','Y'); Query OK, 1 row affected (0.00 sec) mysql> select * from options; +------+-------+-------------+ | name | value | description | +------+-------+-------------+ | log  | Y     | NULL        | +------+-------+-------------+ 1 row in set (0.00 sec) @3310: mysql> select * from options; +------+-------+-------------+ | name | value | description | +------+-------+-------------+ | log  | Y     | NULL        | +------+-------+-------------+ 1 row in set (0.00 sec) mysql> insert into options (name,value ) values ('sql_mode','N'); Query OK, 1 row affected (0.01 sec) mysql> select * from options; +----------+-------+-------------+ | name     | value | description | +----------+-------+-------------+ | log      | Y     | NULL        | | sql_mode | N     | NULL        | +----------+-------+-------------+ 2 rows in set (0.00 sec) mysql> update options set description='abc' where name='log'; Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> delete from options where name ='sql_mode'; Query OK, 1 row affected (0.01 sec) 除了直接使用连接串的方法，还可以先创建server，然后建表时调用server： 还可以使用server的方式将连接串存储起来。 CREATE SERVER dblink FOREIGN DATA WRAPPER mysql OPTIONS (USER 'lepus', HOST '172.26.99.157', PORT 3306, DATABASE 'testdb'); CREATE TABLE `options2` (   `name` varchar(50) DEFAULT NULL,   `value` varchar(255) DEFAULT NULL,   `description` varchar(100) DEFAULT NULL,   KEY `idx_name` (`name`) USING BTREE ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='dblink/options'; mysql> select * from options2; ERROR 1429 (HY000): Unable to connect to foreign data source: Access denied for user 'lepus'@'172.26.99.157' (using password:  mysql> drop server dblink; Query OK, 1 row affected (0.00 sec) CREATE SERVER dblink FOREIGN DATA WRAPPER mysql OPTIONS (USER 'lepus', HOST '172.26.99.157', PORT 3306, DATABASE 'testdb',PASSWORD 'lepus'); mysql> CREATE SERVER dblink     -> FOREIGN DATA WRAPPER mysql     -> OPTIONS (USER 'lepus', HOST '172.26.99.157', PORT 3306, DATABASE 'testdb',PASSWORD 'lepus'); Query OK, 1 row affected (0.01 sec) mysql> select * from options2; +------+-------+-------------+ | name | value | description | +------+-------+-------------+ | log  | Y     | abc         | +------+-------+-------------+ 1 row in set (0.01 sec) mysql> show create table options2\G *************************** 1. row ***************************        Table: options2 Create Table: CREATE TABLE `options2` (   `name` varchar(50) DEFAULT NULL,   `value` varchar(255) DEFAULT NULL,   `description` varchar(100) DEFAULT NULL,   KEY `idx_name` (`name`) USING BTREE ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='dblink/options' 1 row in set (0.00 sec) 在mysql 5.7官方文档中有federated引擎的具体介绍，地址为： https://dev.mysql.com/doc/refman/5.7/en/federated-storage-engine.html 远程服务器中包括表定义和数据，可以是myisam、innodb或其他引擎； 本地服务器中只包括相同表结构的federated引擎表和远程服务器的连接串信息。增删改查操作都需要发到远程服务器中。 数据交互如下： 1.存储引擎查看FEDERATED表拥有的每一列，并构造引用远程表的适当SQL语句 2.语句使用MySQL客户端API发送到远程服务器 3.远程服务器处理语句，本地服务器检索语句产生的所有结果(受影响的行数或结果集) 4.如果语句生成了结果集，则每一列都转换为FEDERATED引擎所期望的内部存储引擎格式，并可用于向发出原始语句的客户端显示结果。 限制： 远端服务器必须是mysql 在调用前，FEDERATED指向的远端表必须存在 可以指向FEDERATED引擎表，注意不要循环嵌套 FEDERATED引擎无法使用索引，如果结果集很大，数据存放在内存中，可能使用大量swap甚至hang住。 FEDERATED引擎表支持insert、update、delete、select、truncate table操作和索引操作，但不支持alter table操作 能接受insert …… on deplicate key update,但不生效，有重复值仍然会报错 不支持事务 可以进行批量插入操作，但注意大小不要超过服务器建能传输的最大包大小 FEDERATED引擎表无法获知远程服务器上表的变化 使用连接串时，密码不能包括@符号 INSERT_id和时间戳选项不会传播到data provider 针对FEDERATED表发出的任何DROP TABLE语句只删除本地表，而不删除远程表 不使用查询缓存 不支持用户定义的分区