MySQL数据库迁移postgresql

MySQL数据库迁移postgresql解决方案

1. 通过使用mysql_fdw第三方工具实现

首先介绍一下mysql_fdw工具

MySQL Foreign Data Wrapper（mysql_fdw）是一个 PostgreSQL 扩展，它允许在 PostgreSQL 中访问远程 MySQL 数据库中的数据。它允许在 PostgreSQL 数据库中创建外部表，这些表实际上是对远程 MySQL 数据表的引用，并且可以像本地表一样进行查询和操作。
 
使用 mysql_fdw，您可以在 PostgreSQL 中轻松地访问和查询远程 MySQL 数据库中的数据，而无需将数据导入到本地 PostgreSQL 数据库中。这使得 mysql_fdw 成为在 PostgreSQL 中实现数据集成和数据共享的有用工具。
 
mysql_fdw 通过使用 Foreign Data Wrapper（FDW）接口将远程 MySQL 数据库的数据暴露给 PostgreSQL。它使用 MySQL 的 C API 连接到 MySQL 数据库，并将 MySQL 数据库中的数据转换为 PostgreSQL 可以处理的格式。在创建外部表时，您需要指定 MySQL 数据库的连接信息以及要引用的 MySQL 表的名称和列的映射。
 
需要注意的是，使用 mysql_fdw 进行查询时，每次查询都会访问远程 MySQL 数据库，因此请确保网络连接稳定和响应快速。在处理大量数据时，性能可能会受到影响。

2.mysql_fdw源码编译的安装

网上大部分都是使用Git的安装方法，本方法为源码编译安装的方式

首先下载源码软件包https://github.com/EnterpriseDB/mysql_fdw/releases

2.1 配置环境变量

mysql_fdw需要安装在PGserver端，并且因为其需要mysql_config所以还需要在PGserver端安装MySQL的客户端软件

PG用户的环境变量如下：

export MYSQL_HOME=/mysql/app/mysql    ##新加的mysql环境变量
 
export PGHOME=/usr/local/postgresql
export PGDATA=/postgresql/data
export PATH=$PGHOME/bin:$MYSQL_HOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.utf8
export DATE=`date +"%Y-%m-%d %H:%M:%S"`
export LD_LIBRARY_PATH=$PGHOME/lib:$MYSQL_HOME/lib:$LD_LIBRARY_PATH

PG用户下需要能够直接调用pg_config和mysql_config，故此测试

[postgres@linux141 ~]$ which pg_config
/usr/local/postgresql/bin/pg_config
[postgres@linux141 ~]$ which mysql_config
/mysql/app/mysql/bin/mysql_config

将PG用户下的环境变量copy到root用户下一份，因为需要在root用户下编译安装

不然会报错：[root@linux141 mysql_fdw-REL-2_9_0]# make USE_PGXS=1
make: pg_config: Command not found
Makefile:44: *** PostgreSQL 10, 11, 12, 13, 14, or 15 is required to compile this extension. Stop.

2.2 mysql_fdw编译安装

正确编译如下：

#####编译
[root@linux141 mysql_fdw-REL-2_9_0]# make USE_PGXS=1        
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -DMAP_HUGETLB=0x40000 -fPIC -I/mysql/app/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/local/postgresql/include/postgresql/server -I/usr/local/postgresql/include/postgresql/internal -I/opt/local/Current/include -D_GNU_SOURCE  -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include  -c -o connection.o connection.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -DMAP_HUGETLB=0x40000 -fPIC -I/mysql/app/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/local/postgresql/include/postgresql/server -I/usr/local/postgresql/include/postgresql/internal -I/opt/local/Current/include -D_GNU_SOURCE  -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include  -c -o option.o option.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -DMAP_HUGETLB=0x40000 -fPIC -I/mysql/app/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/local/postgresql/include/postgresql/server -I/usr/local/postgresql/include/postgresql/internal -I/opt/local/Current/include -D_GNU_SOURCE  -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include  -c -o deparse.o deparse.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -DMAP_HUGETLB=0x40000 -fPIC -I/mysql/app/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/local/postgresql/include/postgresql/server -I/usr/local/postgresql/include/postgresql/internal -I/opt/local/Current/include -D_GNU_SOURCE  -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include  -c -o mysql_query.o mysql_query.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -DMAP_HUGETLB=0x40000 -fPIC -I/mysql/app/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/local/postgresql/include/postgresql/server -I/usr/local/postgresql/include/postgresql/internal -I/opt/local/Current/include -D_GNU_SOURCE  -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include  -c -o mysql_fdw.o mysql_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -DMAP_HUGETLB=0x40000 -fPIC -I/mysql/app/mysql/include -D _MYSQL_LIBNAME=\"libmysqlclient.so\" -I. -I./ -I/usr/local/postgresql/include/postgresql/server -I/usr/local/postgresql/include/postgresql/internal -I/opt/local/Current/include -D_GNU_SOURCE  -I/opt/local/Current/include/libxml2 -I/opt/local/Current/include  -c -o mysql_pushability.o mysql_pushability.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -DMAP_HUGETLB=0x40000 -fPIC -shared -o mysql_fdw.so connection.o option.o deparse.o mysql_query.o mysql_fdw.o mysql_pushability.o -L/usr/local/postgresql/lib    -L/opt/local/Current/lib -Wl,--as-needed -Wl,-rpath,'/usr/local/postgresql/lib',--enable-new-dtags
#####编译安装
[root@linux141 mysql_fdw-REL-2_9_0]# make USE_PGXS=1 install
/bin/mkdir -p '/usr/local/postgresql/lib/postgresql'
/bin/mkdir -p '/usr/local/postgresql/share/postgresql/extension'
/bin/mkdir -p '/usr/local/postgresql/share/postgresql/extension'
/usr/bin/install -c -m 755  mysql_fdw.so '/usr/local/postgresql/lib/postgresql/mysql_fdw.so'
/usr/bin/install -c -m 644 .//mysql_fdw.control '/usr/local/postgresql/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//mysql_fdw--1.0.sql .//mysql_fdw--1.1.sql .//mysql_fdw--1.0--1.1.sql .//mysql_fdw--1.2.sql .//mysql_fdw--1.1--1.2.sql .//mysql_fdw_pushdown.config  '/usr/local/postgresql/share/postgresql/extension/'

编译代码后，把libmysqlclient.so文件拷贝到pg数据库的lib目录下

 cp /mysql/app/mysql/lib/libmysqlclient.so /usr/local/postgresql/lib/

3.将mysql_fdw添加到PG数据库中,然后创建MySQL数据库的映射

postgres=# create EXTENSION mysql_fdw;
CREATE EXTENSION
---创建成功后查询
postgres=# \dx
                            List of installed extensions
   Name    | Version |   Schema   |                   Description                    
-----------+---------+------------+--------------------------------------------------
 mysql_fdw | 1.2     | public     | Foreign data wrapper for querying a MySQL server
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language

在目标MySQL主机上创建账号和授权：

create user pguser@'%' identified by "111111";
grant select on riskdev.* to pguser@'%';

在PG上创建foreign server

create server mysql_server foreign data wrapper mysql_fdw options(host '10.10.10.50',port '3306');

创建临时用户模式存储架构信息

create schema mysql_schema;
 

创建user mapping

create user mapping for postgres server mysql_server options(username 'pguser',password '111111');
--postgres  是PG的用户
 

运行 IMPORT FOREIGN SCHEMA 语句将mysql_schema模式用作将存储所有数据库链接的目标模式。在迁移后可以删除它。riskdev为mysql数据库中的database，mysql_schema为PG数据库中模式。

import foreign schema riskdev from server mysql_server into mysql_schema; 
 

查一下mysql 的riskdev数据库下的表明即可看到MySQL数据库中的数据已经作为外部表可以在PG中查询到了

postgres=# select * from mysql_schema.table_name;
 

但这些表不是PG数据库内的数据，只是外部表

可以使用create table字句提供的like关键字，它允许我们复制表的结构并创建一个真实的本地 PostgreSQL 表，如果迁移的表很多我们可以把这个create table语句通过SQL拼接的方式把所有表的create语句写好并存储到一个SQL文件中批量执行。

create table table_name (like mysql_schema.table_name);
select * from table_name;
 

注意：以上只是表数据和结构，所有的约束，索引等，同时像存储过程、触发器和函数都需要人工处理。

总结：个人觉得mysql_fdw只作为一个导数据的工具还是不存错的，但是对表结构、存储过程等的支持还是不够友好的。
本文参考链接：
https://www.modb.pro/db/384984
https://www.ucloud.cn/yun/129400.html