MySQL迁移到PostgreSQL操作指南

1. 迁移前准备和相关说明

1. 数据迁移： 首先，需要将MySQL数据库中的数据迁移到PostgreSQL。有几种方法可以实现这一点：

   • 使用工具： 可以使用一些第三方工具，如pgloader、 py-mysql2pgsql等，这些工具可以帮助将数据从MySQL迁移到PostgreSQL。
   • 导出和导入： 可以使用MySQL的mysqldump命令将数据导出为SQL文件，然后使用PostgreSQL的psql命令将SQL文件导入到PostgreSQL中。

   请注意，MySQL和PostgreSQL之间有一些语法和数据类型的差异，可能需要根据具体情况进行一些调整和转换。

2. 数据库结构迁移： 数据迁移后，还需要迁移MySQL数据库的结构（表、视图、索引等）到PostgreSQL。这包括：

   • 创建PostgreSQL数据库和用户。
   • 将MySQL的表结构转换为PostgreSQL的语法。
   • 转换数据类型，因为MySQL和PostgreSQL支持的数据类型可能有所不同。
   • 调整索引和约束，因为两个数据库的索引和约束语法可能有所不同。

   这部分的迁移可能需要手动进行，根据数据库结构的复杂程度和差异性而定。

3. 应用程序代码更改： 如果的应用程序与数据库直接交互，并使用了特定于MySQL的查询语法或函数，那么需要相应地修改这些代码，以适应PostgreSQL的语法和函数。

   • 替换MySQL特定的查询语法，如LIMIT、OFFSET等，为PostgreSQL等效的语法。
   • 调整应用程序中使用的SQL查询，以适应PostgreSQL的语法和数据类型。
   • 注意PostgreSQL对大小写的敏感性，与MySQL的不同。

   这需要根据的应用程序和代码库的规模和复杂程度进行逐个检查和调整。

4. 测试和验证： 迁移完成后，确保进行充分的测试和验证，以确保数据的一致性和应用程序的正常运行。进行一系列的功能测试、性能测试和数据验证，确保迁移后的系统按预期工作。

2.教程

迁移有两种工具可以选择。

2.1. 使用pgloader进行迁移

pgloader是一个强大的数据迁移工具，专为将不同数据库之间的数据迁移到PostgreSQL而设计。它支持从MySQL到PostgreSQL的迁移，并提供了一种简单且灵活的方式来转移数据。

安装pgloader：

$ sudo apt-get install pgloader

使用pgloader迁移数据：

$ pgloader mysql://username:password@localhost/source_db postgresql://username:password@localhost/destination_db

这将从MySQL数据库（source_db）中提取数据，并将其加载到PostgreSQL数据库（destination_db）中。

你还可以使用pgloader的配置文件来指定更多的选项和转换规则。例如，创建一个名为load.script的配置文件：

LOAD DATABASE
  FROM mysql://username:password@localhost/source_db
  INTO postgresql://username:password@localhost/destination_db
# 配置数据转换规则
CAST column_name as type using conversion_function

然后运行以下命令执行迁移：

$ pgloader load.script

2.2. 使用 py-mysql2pgsql

请按照以下步骤进行操作：

1. 安装依赖：

   • 确保已安装Python 2.7，并安装所需的依赖项：MySQL-python、psycopg2、PyYAML、termcolor和pytz。可以使用以下命令安装它们：

     pip install MySQL-python psycopg2 PyYAML termcolor pytz
     

2. 下载并安装py-mysql2pgsql工具：

   • 使用以下命令从GitHub下载源代码：

     git clone git://github.com/philipsoutham/py-mysql2pgsql.git
     

   • 进入下载的目录：

     cd py-mysql2pgsql
     

   • 使用以下命令安装工具：

     python setup.py install
     

3. 准备配置文件：

   • 在py-mysql2pgsql工具的根目录下，创建一个名为mysql2pgsql.yml的配置文件。可以使用文本编辑器打开该文件。

4. 配置数据库连接信息：

   • 在配置文件中，需要提供MySQL和PostgreSQL数据库的连接信息。以下是一个示例配置文件的结构：

     mysql:
       host: 127.0.0.1
       port: 3306
       username: your_mysql_username
       password: your_mysql_password
       database: your_mysql_database
     
     postgresql:
       host: 127.0.0.1
       port: 5432
       username: your_postgresql_username
       password: your_postgresql_password
       database: your_postgresql_database
     

5. 运行数据迁移：

   • 使用以下命令运行数据迁移：

     py-mysql2pgsql
     

   • 工具将读取配置文件中的数据库连接信息，并将MySQL数据库中的数据转换并导入到PostgreSQL数据库中。

2.3. 使用mysql_fdw插件读写MySQL数据

这种方式不推荐，只是一种折中办法、

本教程将指导如何使用mysql_fdw插件在RDS PostgreSQL中读取和写入MySQL数据。请按照以下步骤操作：

前提条件：

• 需要一个RDS PostgreSQL 10实例。
• PostgreSQL和MySQL数据库之间需要网络互通。

步骤：

1. 登录到RDS PostgreSQL数据库。详细的登录方法，请参考连接PostgreSQL实例的文档。

2. 创建mysql_fdw插件扩展：
   在数据库中执行以下命令：

   CREATE EXTENSION mysql_fdw;
   

3. 创建MySQL服务器定义：
   执行以下命令创建MySQL服务器定义，替换<连接地址>和<连接端口>为的MySQL数据库的连接地址和端口号：

   CREATE SERVER <server名称>
   FOREIGN DATA WRAPPER mysql_fdw
   OPTIONS (host '<连接地址>', port '<连接端口>');
   

   示例：

   CREATE SERVER mysql_server
   FOREIGN DATA WRAPPER mysql_fdw
   OPTIONS (host 'rm-xxx.mysql.rds.aliyuncs.com', port '3306');
   

4. 创建用户映射：
   执行以下命令创建用户映射，将MySQL服务器定义映射到PostgreSQL的某个用户上，替换<PostgreSQL用户名>、<MySQL用户名>和<MySQL用户对应密码>为相应的值：

   CREATE USER MAPPING FOR <PostgreSQL用户名>
   SERVER <server名称>
   OPTIONS (username '<MySQL用户名>', password '<MySQL用户对应密码>');
   

   示例：

   CREATE USER MAPPING FOR pgtest
   SERVER mysql_server
   OPTIONS (username 'mysqltest', password 'Test1234!');
   

5. 创建MySQL的外部表：
   使用上一步骤中的PostgreSQL用户创建MySQL的外部表。确保外部表的字段名与MySQL数据库中表的字段名相同，并且可以选择仅创建想要查询的字段。执行以下命令，替换<表名>、<字段名>、<数据类型>、<MySQL数据库名>和<MySQL表名>为相应的值：

   CREATE FOREIGN TABLE <表名> (<字段名> <数据类型>,<字段名> <数据类型>...)
   SERVER <server名称>
   OPTIONS (dbname '<MySQL数据库名>', table_name '<MySQL表名>');
   

   示例：

   CREATE FOREIGN TABLE ft_test (id1 int, name1 text)
   SERVER mysql_server
   OPTIONS (dbname 'test123', table_name 'test');
   

6. 测试读写：
   可以通过外部表读写MySQL数据。请注意，MySQL对应的表必须具有主键才能写入数据，否则会出错。执行以下命令进行测试：

   • 读取数据：

     SELECT * FROM <表名>;
     

     示例：

     SELECT * FROM ft_test;
     

   • 插入数据：

     INSERT INTO <表名> VALUES (<值1>, <值2>, ...);
     

     示例：

     INSERT INTO ft_test VALUES (2, 'abc');
     

   • 批量插入数据：

     INSERT INTO <表名> SELECT <查询语句>;
     

     示例：

     INSERT INTO ft_test SELECT generate_series(3, 100), 'abc';
     

   • 统计数据行数：

     SELECT COUNT(*) FROM <表名>;
     

     示例：

     SELECT COUNT(*) FROM ft_test;
     

7. 检查执行计划：
   可以使用以下命令检查执行计划，即查看PostgreSQL查询MySQL数据的请求在MySQL中的执行计划：

   EXPLAIN VERBOSE SELECT COUNT(*) FROM <表名>;
   

   示例：

   EXPLAIN VERBOSE SELECT COUNT(*) FROM ft_test;
   

完成上述步骤后，就可以使用mysql_fdw插件在RDS PostgreSQL中读取和写入MySQL数据了。请根据实际需求和具体情况进行配置和操作。