从 MySQL 到 Redshift 的最快迁移

一则或许对你有用的小广告

欢迎加入小哈的星球 ,你将获得:专属的项目实战 / Java 学习路线 / 一对一提问 / 学习打卡/ 赠书活动

目前, 星球 内第2个项目《仿小红书(微服务架构)》正在更新中。第1个项目:全栈前后端分离博客项目已经完结,演示地址:http://116.62.199.48/。采用技术栈 Spring Boot + Mybatis Plus + Vue 3.x + Vite 4手把手,前端 + 后端全栈开发,从 0 到 1 讲解每个功能点开发步骤,1v1 答疑,陪伴式直到项目上线,目前已更新了 255 小节,累计 39w+ 字,讲解图:1716 张,还在持续爆肝中,后续还会上新更多项目,目标是将 Java 领域典型的项目都整上,如秒杀系统、在线商城、IM 即时通讯、权限管理等等,已有 1300+ 小伙伴加入,欢迎点击围观

您可能已经知道 ,Redshift 因其“随用随付”定价模式而迅速获得广泛认可,尤其是在 MySQL 和 PostgreSQL 等免费软件的消费者中。然而,同样的定价模式仍然可以使它变得非常昂贵。并非所有查询都需要针对 Redshift 实例本身进行,事实证明,不直接在您的 Redshift 实例上运行这些查询可以为您节省很多钱。尽管如此,许多熟悉 MySQL 的 DBA 很快将需要一个快速有效的迁移到 Redshift 的途径。

迁移看起来像这样:

  1. 从 MySQL 中提取(转储)数据;
  2. 将数据加载到 Redshift 中。
  3. 享受!
  4. 冲洗、起泡沫、重复(尤其是对于非常大的数据集——您将无法一次性移动所有数据)!

但实际上,还有更多:

  1. 从 MySQL 中提取数据转储后, 在将其导入 Redshift 之前我应该​​将其存储在哪里?
  2. 模式和类型兼容性问题又如何呢? 这两种数据库类型共享一些(但不是全部)相同的数据类型。更重要的是,虽然 Redshift 可以被认为是 Postgres 的 PB 级版本(尽管有重大变化),但 MySQL 有自己的 MPP 数据库,称为 ShardQuery,它在基本方式上的工作方式完全不同。
  3. 我怎样才能使这个过程自动化? 您很可能不会一次性完成所有数据库迁移。

所以这就是事情。传统思维,你可能需要做这样的事情:

  1. 创建一个红移集群。
  2. 使用导出查询导出 MySQL 数据,并将它们拆分为多个文件。
  3. 将加载文件上传到 Amazon S3。
  4. 运行 COPY 命令(可能在多次迭代中)以加载表。
  5. 验证数据是否已正确加载。

正如您可能想象的那样,当您谈论分布在多个 MySQL 表中的数十亿行数据时,这是大量的 COPY 命令和容易出错的上传!当然,您还最好确保您的导出查询设置得恰到好处——试图找出到底是哪个文件出了问题是许多 DBA 转行的原因。

有一个更简单的方法。

您是否知道可以通过将 Treasure Data 作为数据湖和 Redshift Ingestion 漏斗置于中间来解决所有这些问题?你现在做。

  1. Treasure Data 可以存储来自 MySQL 的数据库转储: 使用 Treasure Data 的数据连接器,您可以轻松转储和存储您的 MySQL 快照。我们每天摄取多达 600 亿行!此外,连接器支持 SSL,因此您知道您的数据是安全的。
  2. Treasure Data 是读取模式: 您可以轻松转储任何强类型数据库快照,而不必担心兼容性问题。
  3. Treasure Data 可以安排作业: 您可以自动化并安排从 MySQL 中提取数据以及将数据推送到 Redshift。

迁移:MySQL -> Treasure Data -> Redshift


我们假设如下:

  1. 您有一个在 Amazon RDS 上运行的 MySQL 数据库。您的数据库应该至少有一个可从 Treasure Data API 服务器访问的端口。您可以配置您的安全组来执行此操作。
  2. 您有一个正在运行的 Redshift 实例,也可以从 Treasure Data 访问。
  3. 您已经在系统上安装并配置了 Treasure Data Toolbelt。

让我们开始吧。注意:所有命令行内容都可以使用系统库在您选择的 shell 脚本、bash 脚本或脚本语言中自动执行。我们还将在下面向您展示如何安排。此外,为了使这些说明尽可能通用,我们提供了一些可能不清楚的示例值,或者变量名以 your_ 开头。请在评论部分将您的建议发送给我们。

  1. 创建 seed.yml。我们将包括 SSL 设置。这应该包含有关您将从 提取数据的主机和数据库的信息:
    
     config:
      in:
        type:  mysql
        host:   your_mysql_host_name
        port:  3306
        user:  your_test_user
        password:  your_test_password
        options:
          requreSSL: true
          useSSL: true
          verifyServerCertificate: false
        database:  your_test_database
        table:  your_test_table
        select: “*”    #this selects everything
      out: 
        mode:  replace
    

  • “猜”数据。 $ td connector:guess seed.yml -o load.yml
  • 在将数据加载到 Treasure Data 之前预览数据。 (单击图像放大。)$ td connector:preview load.yml
  • 在 Treasure Data 上创建数据库和表。这是在将数据加载到 Redshift 之前从 MySQL 转储数据的地方。 $ td db:create your_treasure_data_database && td table:create your_treasure_data_database your_treasure_data_table
  • 将数据从 MySQL 加载到您的 Treasure Data 表中。请注意,如果没有时间列,您需要指定一个。 $ td connector:issue td-bulkload.yml –database your_treasure_data_database –table your_treasure_data_table –time-column your_timestamp_column
  • 现在是将数据查询为要导出到 Redshift 的格式的步骤。转到 console.treasuredata.com,例如,键入查询以按值对前 20 个项目进行排序。
    
     config:
      in:
        type:  mysql
        host:   your_mysql_host_name
        port:  3306
        user:  your_test_user
        password:  your_test_password
        options:
          requreSSL: true
          useSSL: true
          verifyServerCertificate: false
        database:  your_test_database
        table:  your_test_table
        select: “*”    #this selects everything
      out: 
        mode:  replace
    
    • 将其设置为将查询结果发送到 Redshift。主机:my-amazon_host-rs.c5pqzaoivep8.us-west-2.redshift.amazonaws.com #Example 用户名:your_amazon_redshift_username 密码:your_amazon_redshift_password 数据库:your_amazon_redshift_database 表:your_amazon_redshift_table 模式:附加 方法:插入

  • 运行查询。
  • 在 Redshift 上查看和查询结果。您可以使用 SQL Workbench(或类似工具)连接到您的 Redshift 实例并查询您的 Redshift 数据。
  • 您还可以在 Redshift 上查看您的事务日志。您需要直接在 Redshift 上运行更少的查询和转换,从而节省资金。

  • 您可以使用 td connector:create 命令安排导入。您将需要计划、cron 样式的计划、将存储其数据的数据库和表,以及数据连接器配置文件,如下所示:
    $ td connector:create \ daily_mysql_import \ “10 0 * * *” \ your_treasure_data_database \ your_treasure_data_table \ load.yml
  • 加起来

    Treasure Data 提供了一个很好的解决方案来简化从 MySQL 到 Redshift 的迁移。借助我们的云数据湖,您可以统一您的分析基础架构,并且可以将结果发布到各种目标系统——而不仅仅是 Redshift!


    最初由 Treasure Data 的首席传播者 John Hammink 撰写