01_Matrixdb通过数据联邦mysql_fdw访问mysql

matrixdb视频教程 - 01Matrixdb通过数据联邦mysql_fdw访问mysql

作者

shidb

日期

2021-05-08

标签

matrixdb视频教程 - 01Matrixdb通过数据联邦mysql_fdw访问mysql

一、MySQL端
1，mysql登陆
mysql -h sdw5 -uroot -p’Matrixdb@123’

2，建表
create database mysql_fdw;
create table warehouse (warehouse_id int primary key, warehouse_name text, warehouse_created timestamp);
insert into warehouse values(1,‘warehouse1’,‘2021-01-01’);
insert into warehouse values(2,‘warehouse2’,‘2021-01-02’);

二、Matrixdb端
1，创建插件
create extension mysql_fdw ;

2，创建fdw_server
CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host ‘sdw5’, port ‘3306’);

3，创建FDW User mapping
CREATE USER MAPPING FOR shidb SERVER mysql_server OPTIONS (username ‘root’, password ‘Matrixdb@123’);

4，创建FDW Foreign Table
postgres=# CREATE FOREIGN TABLE warehouse (
warehouse_id int,
warehouse_name text,
warehouse_created timestamp
)
SERVER mysql_server
OPTIONS (dbname ‘mysql_fdw’, table_name ‘warehouse’);

5，查询
select * from warehouse;

6，插入
insert into warehouse values(3,‘warehouse3’,‘2020-01-03’);

7，更新
update warehouse set warehouse_name=warehouse21 where warehouse_id=2;

8，删除
delete from warehouse where warehouse_id=1;

如需入群沟通交流，请扫码添加好友