Asynchronously replicating data from Yandex Managed Service for MySQL to Yandex Managed Service for YDB

With Data Transfer, you can transfer data from a Managed Service for MySQL source cluster to Managed Service for YDB.

To transfer data:

1. Prepare the source cluster.
2. Prepare and activate the transfer.
3. Test the transfer.

If you no longer need these resources, delete them.

Prepare the infrastructure

Prepare 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. As an example, we'll use a simple table with information transmitted by car sensors.

   Create a table:

   CREATE TABLE measurements (
       device_id varchar(200) NOT NULL,
       datetime timestamp NOT NULL,
       latitude real NOT NULL,
       longitude real NOT NULL,
       altitude real NOT NULL,
       speed real NOT NULL,
       battery_voltage real,
       cabin_temperature real NOT NULL,
       fuel_level real,
       PRIMARY KEY (device_id)
   );
   

   Populate the table with data:

   INSERT INTO measurements VALUES
       ('iv9a94th6rztooxh5ur2', '2022-06-05 17:27:00', 55.70329032, 37.65472196,  427.5,    0, 23.5, 17, NULL),
       ('rhibbh3y08qmz3sdbrbu', '2022-06-06 09:49:54', 55.71294467, 37.66542005, 429.13, 55.5, NULL, 18, 32);
   

Prepare and activate the transfer

1. Create a target endpoint:

   • Database type: YDB.

   • Endpoint parameters:

     • Connection settings → Database: Select a Managed Service for YDB database from the list.
     • Service account ID: Select or create a service account with the ydb.editor role.

2. Create a source endpoint and transfer:

   Manually

   Using Terraform

   1. Create a source endpoint:

      • Database type: MySQL.

      • Endpoint parameters → Connection settings: Managed Service for MySQL cluster.

        Select a source cluster from the list and specify the cluster connection settings.

   2. Create a transfer of the Snapshot and increment type that will use the created endpoints.

   3. Activate it.

   1. Uncomment the following in the data-transfer-mmy-ydb.tf file:

      • The target_endpoint_id variable and set it to the value of the endpoint ID for the target created in the previous step.
      • The yandex_datatransfer_endpoint and yandex_datatransfer_transfer resources.

   2. Make sure the Terraform configuration files are correct using the command:

      terraform validate
      

      If there are errors in the configuration files, Terraform will point to them.

   3. Create the required infrastructure:

      1. Run the command to view planned changes:

         terraform plan
         

         If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.

      2. If you are happy with the planned changes, apply them:

         1. Run the command:

            terraform apply
            

         2. Confirm the update of resources.

         3. Wait for the operation to complete.

      Once created, a transfer is activated automatically.

Test the transfer

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

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

   Management console

   CLI

   1. In the management console, select the folder with the desired DB.
   2. In the list of services, select Managed Service for YDB.
   3. Select the database from the list.
   4. Go to the Navigation tab.
   5. Make sure the Managed Service for YDB database contains the <source cluster DB name>_measurements table with the test data.

   1. Connect to the Managed Service for YDB database.

   2. Make sure the database contains the <source cluster DB name>_measurements table with the test data:

      SELECT *
      FROM <source cluster DB name>_measurements;
      

3. Connect to the Managed Service for MySQL source cluster and add data to the measurements table:

   INSERT INTO measurements VALUES
       ('iv7b74th678tooxh5ur2', '2020-06-08 17:45:00', 53.70987913, 36.62549834, 378.0, 20.5, 5.3, 20, NULL);
   

4. Check that the Managed Service for YDB database shows information about the row added:

   Management console

   CLI

   1. In the management console, select the folder with the desired DB.
   2. In the list of services, select Managed Service for YDB.
   3. Select the database from the list.
   4. Go to the Navigation tab.
   5. Make sure that the new data has been added to the <source cluster DB name>_measurements table.

   1. Connect to the Managed Service for YDB database.

   2. Make sure that the new data has been added to the <source cluster DB name>_measurements table:

      SELECT *
      FROM <source cluster DB name>_measurements;
      

Delete the resources you created

If you no longer need these resources, delete them:

1. Delete the transfer.
2. Delete the target endpoint.
3. If you created the service account along with the target endpoint, delete it.

Delete the other resources, depending on the method used to create them: