Delivering data from Yandex Managed Service for MySQL to Yandex Object Storage

With Data Transfer, you can transfer data from a Managed Service for MySQL source cluster to Object Storage object storage.

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.

Before you begin

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 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: Object Storage.

   • Endpoint parameters:

     • Connection settings:

       • Bucket: Specify the name of the Object Storage bucket.
       • Service account: Select or create a service account with the storage.uploader role.

     • Directory name: measurements.

2. Create a source endpoint and transfer:

   Manually

   Using Terraform

   1. Create a source endpoint:

      • Database type: MySQL.

      • Endpoint parameters:

        • Connection settings:

          • Connection settings: Managed Service for MySQL cluster.

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

        • (Optional) List of included tables, List of excluded tables: Specify regular expressions for tables to be transferred and those that shouldn't be transferred.

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

   3. Activate it.

   1. In the data-transfer-mmy-objs.tf file, specify the variables:

      • target_endpoint_id: ID of the target endpoint.
      • transfer_enabled: Set 1 to enable transfer creation.
      • (Optional) include_tables_regex, exclude_tables_regex: Regular expressions for the tables to be transferred and those not to be transferred.

   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 Completed.

2. Make sure the data has been moved from the Managed Service for MySQL source cluster to the Object Storage bucket:

   1. In the management console, select the folder where the desired bucket is located.
   2. In the list of services, select Object Storage.
   3. Select the bucket from the list.
   4. Go to the Objects tab.
   5. Check that the Object Storage bucket contains the measurements folder with the <source cluster DB name>_measurements object with the test data.

Delete the resources you created

If you no longer need these resources, delete them:

1. Delete the transfer.
2. Delete the target endpoint.

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