Migrating a database from MySQL® to ClickHouse® using Yandex Data Transfer

With Data Transfer, you can migrate your database from a MySQL® source cluster to ClickHouse®.

To transfer data:

1. Prepare the source cluster.
2. Set up and activate the transfer.
3. Test your transfer.
4. Select the data from ClickHouse®.

If you no longer need the resources you created, delete them.

Required paid resourcesRequired paid resources

The support cost for this solution includes:

• Managed Service for MySQL® cluster fee: Covers the use of computational resources allocated to hosts and disk storage (see Managed Service for MySQL® pricing).
• Managed Service for ClickHouse® cluster fee: Covers the use of computational resources allocated to hosts (including ZooKeeper hosts) and disk storage (see Managed Service for ClickHouse® pricing).
• Fee for public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).
• Transfer fee: Based on computational resource consumption and the total number of data rows transferred (see Data Transfer pricing).

Getting startedGetting started

Set up the infrastructure:

Prepare the source clusterPrepare the source cluster

1. If you created the infrastructure manually, prepare the source cluster.

2. Connect to the Managed Service for MySQL® source cluster.

3. Add test data to the database.

   1. Create a table named x_tab:

   CREATE TABLE x_tab
   (
       id INT,
       name TEXT,
       PRIMARY KEY (id)
   );
   

   1. Populate the table with data:

   INSERT INTO x_tab (id, name) VALUES
       (40, 'User1'),
       (41, 'User2'),
       (42, 'User3'),
       (43, 'User4'),
       (44, 'User5');
   

Set up and activate the transferSet up and activate the transfer

Test your transferTest your transfer

1. Wait for the transfer status to change to Replicating.

2. Make sure the data from the source Managed Service for MySQL® cluster has been moved to the Managed Service for ClickHouse® database:

   1. Connect to the cluster using clickhouse-client.

   2. Run this query:

      SELECT * FROM <ClickHouse®_database_name>.x_tab
      

      Result:

      ┌─id─┬─name──┬─__data_transfer_commit_time─┬─__data_transfer_delete_time─┐
      │ 40 │ User1 │         1661952756538347180 │                           0 │
      │ 41 │ User2 │         1661952756538347180 │                           0 │
      │ 42 │ User3 │         1661952756538347180 │                           0 │
      │ 43 │ User4 │         1661952756538347180 │                           0 │
      │ 44 │ User5 │         1661952756538347180 │                           0 │
      └────┴───────┴─────────────────────────────┴─────────────────────────────┘
      

      The table also contains columns with timestamps: __data_transfer_commit_time and __data_transfer_delete_time.

3. In the x_tab table of the source MySQL® database, delete the row where id equals 41 and update the one where id equals 42:

   1. Connect to the Managed Service for MySQL® source cluster.

   2. Run the following requests:

      DELETE FROM x_tab WHERE id = 41;
      UPDATE x_tab SET name = 'Key3' WHERE id = 42;
      

4. Check the changes in the x_tab table of the ClickHouse® target:

   SELECT * FROM <ClickHouse®_database_name>.x_tab WHERE id in (41,42);
   

   Result:

   ┌─id─┬─name──┬─__data_transfer_commit_time─┬─__data_transfer_delete_time─┐
   │ 41 │ User2 │         1661952756538347180 │                           0 │
   │ 42 │ User3 │         1661952756538347180 │                           0 │
   └────┴───────┴─────────────────────────────┴─────────────────────────────┘
   ┌─id─┬─name─┬─__data_transfer_commit_time─┬─__data_transfer_delete_time─┐
   │ 41 │ ᴺᵁᴸᴸ │         1661953256000000000 │         1661953256000000000 │
   └────┴──────┴─────────────────────────────┴─────────────────────────────┘
   ┌─id─┬─name─┬─__data_transfer_commit_time─┬─__data_transfer_delete_time─┐
   │ 42 │ Key3 │         1661953280000000000 │                           0 │
   └────┴──────┴─────────────────────────────┴─────────────────────────────┘
   

Select the data from ClickHouse®Select the data from ClickHouse®

For table recovery, the ClickHouse® target with replication enabled uses the ReplicatedReplacingMergeTree and ReplacingMergeTree engines. The following columns are added automatically to each table:

• __data_transfer_commit_time: Time when the was row updated to this value, in TIMESTAMP format.

• __data_transfer_delete_time: Row deletion time, in TIMESTAMP format, if the row was deleted in the source. If the row was not deleted, the value is set to 0.

  The __data_transfer_commit_time column is required for the ReplicatedReplacedMergeTree engine to work. If a record is deleted or updated, a new row is inserted with a value in this column. A query by a single primary key returns multiple records with different __data_transfer_commit_time values.

With the Replicating transfer status, the source data can be added or deleted. To ensure standard behavior of SQL commands when a primary key points to a single record, provide a clause to filter data by __data_transfer_delete_time when querying tables transferred to ClickHouse®. Here is an example of a query to the x_tab table:

SELECT * FROM <ClickHouse®_database_name>.x_tab FINAL
WHERE __data_transfer_delete_time = 0;

To simplify the SELECT queries, create a view with filtering by __data_transfer_delete_time and use it for querying. Here is an example of a query to the x_tab table:

CREATE VIEW x_tab_view AS SELECT * FROM <ClickHouse®_database_name>.x_tab FINAL
WHERE __data_transfer_delete_time == 0;

Delete the resources you createdDelete the resources you created

Some resources incur charges. To avoid paying for them, delete the resources you no longer need:

ClickHouse® is a registered trademark of ClickHouse, Inc.