ITPub博客

首页 > 数据库 > MySQL > 通过mysql的federated插件实现dblink功能

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

原创 MySQL 作者:to_be_Dba 时间:2020-07-03 00:13:02 0 删除 编辑


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语句只删除本地表,而不删除远程表

不使用查询缓存

不支持用户定义的分区


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-2702156/,如需转载,请注明出处,否则将追究法律责任。

全部评论

注册时间:2011-11-23

  • 博文量
    157
  • 访问量
    408449