基于PolarDB和AnalyticDB构建实时数据仓库

前提条件

• AnalyticDB for MySQL集群与PolarDB for MySQL集群位于同一地域且所属同一VPC。

• AnalyticDB for MySQL集群的存储空间须大于PolarDB MySQL版集群占用的存储空间。

示例数据

本文示例的PolarDB for MySQL数据库名为DB1，并在该库中创建了一个名为sales_range_columns的分区表。该表以create_date作为分区键，共包含12个分区，12条数据。

-- 创建数据库
CREATE DATABASE IF NOT EXISTS DB1;

-- 创建分区表
CREATE TABLE sales_range_columns (
    dept_no INT(11) NOT NULL AUTO_INCREMENT,
    part_no INT(11) DEFAULT NULL,
    country VARCHAR(20) DEFAULT NULL,
    create_date DATE NOT NULL,
    amount INT(11) DEFAULT NULL,
    PRIMARY KEY (dept_no, create_date)
)
ENGINE=InnoDB
AUTO_INCREMENT=1000001
DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(create_date) (
    PARTITION p1 VALUES LESS THAN ('2024-01-01') ENGINE = InnoDB,
    PARTITION p2 VALUES LESS THAN ('2024-02-01') ENGINE = InnoDB,
    PARTITION p3 VALUES LESS THAN ('2024-03-01') ENGINE = InnoDB,
    PARTITION p4 VALUES LESS THAN ('2024-04-01') ENGINE = InnoDB,
    PARTITION p5 VALUES LESS THAN ('2024-05-01') ENGINE = InnoDB,
    PARTITION p6 VALUES LESS THAN ('2024-06-01') ENGINE = InnoDB,
    PARTITION p7 VALUES LESS THAN ('2024-07-01') ENGINE = InnoDB,
    PARTITION p8 VALUES LESS THAN ('2024-08-01') ENGINE = InnoDB,
    PARTITION p9 VALUES LESS THAN ('2024-09-01') ENGINE = InnoDB,
    PARTITION p10 VALUES LESS THAN ('2024-10-01') ENGINE = InnoDB,
    PARTITION p11 VALUES LESS THAN ('2024-11-01') ENGINE = InnoDB,
    PARTITION p12 VALUES LESS THAN ('2024-12-01') ENGINE = InnoDB
);

-- 插入12条数据
INSERT INTO sales_range_columns (part_no, country, create_date, amount) VALUES
(101, 'USA', '2024-01-15', 500),
(102, 'UK', '2024-02-20', 700),
(103, 'Germany', '2024-03-10', 600),
(104, 'France', '2024-04-05', 800),
(105, 'Japan', '2024-05-25', 900),
(106, 'China', '2024-06-15', 1000),
(107, 'India', '2024-07-05', 1100),
(108, 'Brazil', '2024-08-20', 1200),
(109, 'Canada', '2024-09-10', 1300),
(110, 'Australia', '2024-10-25', 1400),
(111, 'Italy', '2024-11-15', 1500),
(112, 'Spain', '2024-11-30', 1600);

步骤一：开启PolarDB for MySQL集群的Binlog功能

1. 登录PolarDB控制台，在左侧导航栏单击集群列表，选择集群所在地域，并单击目标集群ID进入集群详情页。

2. 您可以通过以下两种方式中的任意一种来开启Binlog：

   • 方式一：

     1. 进入集群的Binlog管理页面。

        

     2. 单击立即开启按钮。

     3. 在开启Binlog对话框中，选择生效方式为立即生效或定时生效。

        若选择定时生效，请指定具体的生效时间。

        

     4. 单击确定。

   • 方式二：通过设置参数loose_polar_log_bin的值来开启Binlog。

     1. 在左侧导航栏中选择配置与管理 > 参数配置。

     2. 找到目标参数loose_polar_log_bin，修改参数值。具体操作请参见修改参数值。

        说明

        • 若您的PolarDB MySQL版集群版本为MySQL 5.6，当前值修改为ON_WITH_GTID；

        • 若您的PolarDB MySQL版集群版本为MySQL 5.7或MySQL 8.0，当前值修改为ON。

步骤二：数据同步

您可以选择以下任意一种方式，将PolarDB for MySQL集群的库表结构、全量数据、增量数据同步至AnalyticDB for MySQL。

步骤三：验证数据一致性

当同步链路的状态为运行中时，可以验证数据一致性。

步骤四：指定冷热分离存储策略

为保证近期数据的查询性能并降低历史数据的存储成本，请按以下步骤对AnalyticDB for MySQL中的表设置冷热分离：

1. 指定存储策略。执行以下SQL语句，设置表的存储策略为mixed（冷热混合存储），并将最近6个月数据保留在热存储中，其余历史数据迁移至冷存储。

   -- 修改存储策略
   ALTER TABLE sales_range_columns storage_policy = 'mixed', hot_partition_count = 6;

   变更冷热分层存储策略后，需要该表再次触发并完成BUILD任务后，新的存储策略才能生效。

2. 监控迁移进度。执行以下SQL语句，查看冷热分离任务状态。

   -- 查看迁移进度
   SELECT 
       table_name,                -- 表名
       source_storage_policy,     -- 原始存储策略
       dest_storage_policy,       -- 目标存储策略
       progress,                  -- 进度（百分比）
       status                     -- 当前任务状态
   FROM 
       information_schema.storage_policy_modify_progress;

   任务状态（status）：

   • INIT：任务初始化，表示冷热分离存储策略已提交但尚未开始执行。

   • RUNNING：任务执行中，表示数据正在从热存储向冷存储迁移。

   • FINISH：任务完成，表示冷热分离存储策略已成功应用，数据分布调整完毕。

3. 验证存储策略生效。执行以下SQL语句，查看表当前存储策略是否生效。

   -- 查看最新表结构
   SHOW CREATE TABLE sales_range_columns;

   查询结果显示STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=6，表明冷热分离存储策略已成功生效。