Migrating data from Yandex Managed Service for MySQL® to Yandex MPP Analytics for PostgreSQL using Yandex Data Transfer

You can set up a data transfer from a Managed Service for MySQL® database to a Yandex MPP Analytics for PostgreSQL one using Data Transfer. Proceed as follows:

1. Prepare your test data.
2. Create a database in the target cluster.
3. Set up and activate the transfer.
4. Test your transfer.

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

Required paid resourcesRequired paid resources

• Managed Service for MySQL® cluster: Computing resources allocated to hosts, storage and backup size (see Managed Service for MySQL® pricing).
• Yandex MPP Analytics for PostgreSQL cluster: Computing resources allocated to hosts, storage and backup size (see Yandex MPP Analytics for PostgreSQL pricing).
• Public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).

Getting startedGetting started

Set up your infrastructure:

Prepare your test dataPrepare your test data

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

2. Create a simple table named table1:

   CREATE TABLE table1 (
   id int NOT NULL,
   name varchar (10),
   PRIMARY KEY (id)
   );
   

3. Populate the table with data:

   INSERT INTO table1 VALUES
   (1, 'Name1'),
   (2, 'Name2'),
   (3, 'Name3');
   

Create a database in the target clusterCreate a database in the target cluster

1. Connect to the postgres system database in the Greenplum® target cluster as mgp_user.

2. Create a database named mgp_db:

   CREATE DATABASE mgp_db;
   

Set up and activate the transferSet up and activate the transfer

1. Create a Greenplum® target endpoint with these cluster connection settings:

   • Connection type: Managed Service for Greenplum cluster.
   • Managed Service for Greenplum cluster: <Greenplum®_target_cluster_name> from the drop-down list.
   • Database: mgp_db.
   • User: mgp_user.
   • Password: <user_password>.

2. Create a source endpoint and transfer:

   Manually

   1. Create a MySQL® source endpoint with these cluster connection settings:

      • Connection type: Managed Service for MySQL cluster.
      • Managed Service for MySQL cluster: <MySQL®_source_cluster_name> from the drop-down list.
      • Database: mmy_db.
      • User: mmy_user.
      • Password: <user_password>.

   2. Create a transfer of the Snapshot and replication-type that will use the endpoints you created.

   3. Activate the transfer and wait for its status to change to Replicating.

Test your transferTest your transfer

To make sure the transfer works correctly, test the copy and replication processes.

Test the copy operationTest the copy operation

1. Connect to the mgp_db database in the Greenplum® target cluster.

2. Run this query:

   SELECT * FROM mmy_db.table1;
   

Test the replication processTest the replication process

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

2. Populate table1 with data:

   INSERT INTO table1 VALUES
   (4, 'Name4');
   

3. Make sure the target database contains the row you added:

   1. Connect to the mgp_db database in the Greenplum® target cluster.

   2. Run this query:

      SELECT * FROM mmy_db.table1;
      

Delete the resources you createdDelete the resources you created

Some resources are not free of charge. Delete the resources you no longer need to avoid paying for them: