SRE文档

深浅模式

参考连接： https://www.percona.com/blog/2018/08/24/postgresql-accessing-mysql-as-a-data-source-using-mysql_fdw/

mysql_fdw 的作用： 用来在PG中快速访问MySQL中的数据

1.编译 mysql_fdw 扩展 ​

# 载入环境变量
export PATH=/usr/local/pgsql-11.5/bin:$PATH

# 如果mysql是编译安装的，这里可能还需要载入mysql的环境变量,类似这样 export PATH=/usr/local/mysql:$PATH（我们mysql使用rpm安装，默认的PATH路径可发现，这里就i不用export了）

# 开始编译扩展
cd /home/postgres/
git clone https://github.com/EnterpriseDB/mysql_fdw.git 
cd mysql_fdw
make USE_PGXS=1
make USE_PGXS=1 install


chown postgres.postgres /usr/local/pgsql-11.5/lib/mysql_fdw.so   # 我这里用root账号编译的，需要改下最终的pg文件夹下的 .so 文件的权限
# 载入环境变量
export PATH=/usr/local/pgsql-11.5/bin:$PATH

# 如果mysql是编译安装的，这里可能还需要载入mysql的环境变量,类似这样 export PATH=/usr/local/mysql:$PATH（我们mysql使用rpm安装，默认的PATH路径可发现，这里就i不用export了）

# 开始编译扩展
cd /home/postgres/
git clone https://github.com/EnterpriseDB/mysql_fdw.git 
cd mysql_fdw
make USE_PGXS=1
make USE_PGXS=1 install


chown postgres.postgres /usr/local/pgsql-11.5/lib/mysql_fdw.so   # 我这里用root账号编译的，需要改下最终的pg文件夹下的 .so 文件的权限

然后，修改pg的配置文件， 加入 mysql_fdw这个功能

shared_preload_libraries = 'mysql_fdw'
shared_preload_libraries = 'mysql_fdw'

登陆pg

\c testdb 
testdb=# \d
           List of relations
 Schema |    Name    | Type  |  Owner  
--------+------------+-------+---------
 public | inno_order | table | repuser
(1 row)


# 下面是参考官方github上面的教程实践的：
testdb=# CREATE EXTENSION mysql_fdw;

testdb=# CREATE SERVER mysql_server_db10 FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '192.168.2.4', port '3306');

testdb=# CREATE USER MAPPING FOR postgres SERVER mysql_server_db10 OPTIONS (username 'dts', password 'dts');

testdb=# CREATE FOREIGN TABLE screens (
  "screenid" bigint  NOT NULL,
  "name" varchar(255) NOT NULL,
  "hsize" int NOT NULL ,
  "vsize" int NOT NULL ,
  "templateid" bigint DEFAULT NULL,
  "userid" bigint  DEFAULT NULL,
  "private" int NOT NULL )
SERVER mysql_server_db10 OPTIONS (dbname 'zabbix', table_name 'screens');
# 注意： PG中有些字段类型与MySQL不一样，需要在PG上建表的时候注意修改适配下。


testdb=# select * from screens limit 5  ;
 screenid |         name         | hsize | vsize | templateid | userid | private 
----------+----------------------+-------+-------+------------+--------+---------
        3 | System performance   |     2 |     3 |      10001 |        |       0
        4 | Zabbix server health |     2 |     3 |      10047 |        |       0
        5 | System performance   |     2 |     2 |      10076 |        |       0
        6 | System performance   |     2 |     2 |      10077 |        |       0
        7 | System performance   |     2 |     2 |      10075 |        |       0
(5 rows)
\c testdb 
testdb=# \d
           List of relations
 Schema |    Name    | Type  |  Owner  
--------+------------+-------+---------
 public | inno_order | table | repuser
(1 row)


# 下面是参考官方github上面的教程实践的：
testdb=# CREATE EXTENSION mysql_fdw;

testdb=# CREATE SERVER mysql_server_db10 FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '192.168.2.4', port '3306');

testdb=# CREATE USER MAPPING FOR postgres SERVER mysql_server_db10 OPTIONS (username 'dts', password 'dts');

testdb=# CREATE FOREIGN TABLE screens (
  "screenid" bigint  NOT NULL,
  "name" varchar(255) NOT NULL,
  "hsize" int NOT NULL ,
  "vsize" int NOT NULL ,
  "templateid" bigint DEFAULT NULL,
  "userid" bigint  DEFAULT NULL,
  "private" int NOT NULL )
SERVER mysql_server_db10 OPTIONS (dbname 'zabbix', table_name 'screens');
# 注意： PG中有些字段类型与MySQL不一样，需要在PG上建表的时候注意修改适配下。


testdb=# select * from screens limit 5  ;
 screenid |         name         | hsize | vsize | templateid | userid | private 
----------+----------------------+-------+-------+------------+--------+---------
        3 | System performance   |     2 |     3 |      10001 |        |       0
        4 | Zabbix server health |     2 |     3 |      10047 |        |       0
        5 | System performance   |     2 |     2 |      10076 |        |       0
        6 | System performance   |     2 |     2 |      10077 |        |       0
        7 | System performance   |     2 |     2 |      10075 |        |       0
(5 rows)

删除扩展 ​

postgres=# drop foreign table screens;
postgres=# drop user mapping for postgres server mysql_server_db10 ;
postgres=# drop server mysql_server_db10 ;
DROP SERVER
postgres=# drop extension mysql_fdw ;
DROP EXTENSION
postgres=# drop foreign table screens;
postgres=# drop user mapping for postgres server mysql_server_db10 ;
postgres=# drop server mysql_server_db10 ;
DROP SERVER
postgres=# drop extension mysql_fdw ;
DROP EXTENSION