ApsaraDB ClickHouse allows you to create an ApsaraDB RDS for MySQL analytic instance and synchronize data from the ApsaraDB RDS for MySQL instance to an ApsaraDB ClickHouse instance.

Background information

An ApsaraDB RDS for MySQL analytic instance automatically creates a database that runs the MaterializedMySQL engine in ApsaraDB ClickHouse to synchronize the data in the associated ApsaraDB RDS for MySQL instance to ApsaraDB ClickHouse. The MaterializedMySQL engine provides ultra-high analytical performance. This can resolve issues that are caused by the aggregation of multiple dimensions during real-time analysis.
Note For more information about the MaterializeMySQL engine, see MaterializeMySQL.

Limits

  • Make sure that you are granted the access permissions on the binary logs of your ApsaraDB RDS for MySQL instance and the RELOAD, REPLICATION SLAVE, REPLICATION CLIENT, and SELECT permissions on your ApsaraDB RDS for MySQL databases.
    Note You can execute the GRANT RELOAD, REPLICATION SLAVE, REPLICATION CLIENT, and SELECT ON *.* TO 'your-user-name'; statements to grant the relevant permissions on your ApsaraDB RDS for MySQL databases.
  • When you create a synchronization task, you can specify only an ApsaraDB ClickHouse cluster whose kernel version is V20.8 or later as the destination instance.
  • You can create analytic instances only for the following types of ApsaraDB RDS for MySQL instances:
    • ApsaraDB RDS for MySQL 8.0 of the Enterprise Edition
    • ApsaraDB RDS for MySQL 8.0 of the High-availability Edition with local or standard SSDs
    • ApsaraDB RDS for MySQL 5.7 of the Enterprise Edition
    • ApsaraDB RDS for MySQL 5.7 of the High-availability Edition with local or standard SSDs
    • RDS MySQL 5.6
  • When you create a synchronization task, the source ApsaraDB RDS for MySQL instance and the destination ApsaraDB ClickHouse cluster must be deployed in the same region and connected to the same virtual private cloud (VPC).
  • You cannot configure duplicate synchronization tasks for a database in an ApsaraDB RDS for MySQL instance.

Procedure

  1. Create a synchronization task.
    1. Log on to the ApsaraDB for ClickHouse console.
      Note You can also log on to the ApsaraDB RDS console and then proceed to Substep e in Step 1.
    2. On the Clusters page, find the cluster that you want to manage and click the cluster ID.
    3. In the left-side navigation pane, click MySQL Real-time Synchronization.
    4. The first time you create a synchronization task, click ApsaraDB RDS for MySQL in the message that is displayed on the page that appears. Then, you are redirected to the ApsaraDB RDS console. Create a real-time synchronization task
      Note If a synchronization task is already displayed on the page that appears, log on to the ApsaraDB RDS console to create a synchronization task.
    5. On the Instances page, find the instance that you want to manage, and click the instance ID.
    6. In the Distributed by Instance Role section of the Basic Information page, click Analytic Instance of ApsaraDB for ClickHouse in the Analytic Instance field. Select an instance
  2. The first time you create an analytic instance, you must grant the required permissions to your account. To grant the permissions, perform the following steps:
    Note If your account already has the permissions, you are redirected to the MySQL to ClickHouse analysis instance data synchronization configuration wizard after you click Analytic Instance of ApsaraDB for ClickHouse.
    1. In the Create Service-linked Role dialog box, click AliyunServiceRoleForClickHouse.
    2. Click OK.
  3. Configure data synchronization.
    1. In the Configure Source and Destination Instances step of the MySQL to ClickHouse analysis instance data synchronization configuration wizard, select an ApsaraDB for ClickHouse cluster as the destination instance, and specify the usernames and passwords that are used to log on to the ApsaraDB RDS for MySQL instance and the ApsaraDB for ClickHouse cluster. Configure the source and destination instances
    2. Click Test Connectivity to check whether the ApsaraDB RDS for MySQL instance and the ApsaraDB for ClickHouse cluster are connected.
      • If the connection is established, proceed to the next step.
      • If the connection fails, an error message appears. Troubleshoot the failure based on the error message.
    3. In the Source Objects section, select the tables that you want to synchronize.
      Note
      • By default, Skip Table Schemas that Do not Support Synchronization is selected. We recommend that you keep this check box selected. Tables that do not have primary keys cannot be synchronized.
      • When you specify tables that you want to synchronize, you cannot select only the databases. You must select specific tables in the databases. If only databases are selected, the configuration fails.
    4. Click the Right arrow icon to synchronize the database and table configuration to the Configure Destination Instance section. Database and table configuration
    5. Click Next: Save Task and Start Synchronization.
      • If the synchronization task is created, proceed to the next step.
      • If the synchronization task fails to be created, an error message appears. Troubleshoot the failure based on the error message.
    6. Optional. After the synchronization task is created, the data synchronization task automatically starts. Click View Synchronization Tasks.
Note If the "task inner error" message appears during the synchronization, submit a ticket to contact Alibaba Cloud Technical Support at the earliest opportunity.