[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for MySQL®](../index.md) > [Tutorials](index.md) > Syncing data from a third-party MySQL® cluster to Yandex Managed Service for MySQL® using Yandex Data Transfer

# Syncing data from a third-party MySQL® cluster to Yandex Managed Service for MySQL® using Yandex Data Transfer


# Syncing MySQL data using Yandex Data Transfer

In this scenario, you'll learn how to ensure periodic delivery of changes from an external database to the cloud using Data Transfer. To synchronize data in your cloud, you need to create intermediate staging data storage, Managed Service for MySQL®, to replicate tables to. The data is synchronized almost in real time. 

To set up the transfer of changes:

1. [Get your cloud ready](#before-begin).
1. [Create a VM with an online store](#create-vm-mysql).
1. [Create staging storage](#create-staging-dwh).
1. [Configure the transfer parameters](#create-transfer).
1. [Monitor the transfer of changes in the DB in the cloud](#start-sync).

If you no longer need the resources you created, [delete them](#clear-out).

## Get your cloud ready {#before-begin}

Sign up for Yandex Cloud and create a [billing account](../../billing/concepts/billing-account.md):
1. Navigate to the [management console](https://console.yandex.cloud) and log in to Yandex Cloud or create a new account.
1. On the **[Yandex Cloud Billing](https://center.yandex.cloud/billing/accounts)** page, make sure you have a billing account linked and it has the `ACTIVE` or `TRIAL_ACTIVE` [status](../../billing/concepts/billing-account-statuses.md). If you do not have a billing account, [create one](../../billing/quickstart/index.md) and [link](../../billing/operations/pin-cloud.md) a cloud to it.

If you have an active billing account, you can create or select a [folder](../../resource-manager/concepts/resources-hierarchy.md#folder) for your infrastructure on the [cloud page](https://console.yandex.cloud/cloud).

[Learn more about clouds and folders here](../../resource-manager/concepts/resources-hierarchy.md).


### Required paid resources {#paid-resources}

* VM instance: use of computing resources, storage, public IP address, and OS (see [Yandex Compute Cloud pricing](../../compute/pricing.md)).
* Managed Service for MySQL® cluster: computing resources allocated to hosts, storage and backup size (see [Managed Service for MySQL® pricing](../pricing.md)).
* Public IP addresses if public access is enabled for cluster hosts (see [Yandex Virtual Private Cloud pricing](../../vpc/pricing.md)).
* Each transfer: use of computing resources and the number of transferred data rows (see [Data Transfer pricing](../../data-transfer/pricing.md)).


## Create a VM with an online store {#create-vm-mysql}

1. Create a VM with an online store named `web-store-vm` based on MySQL:

   {% list tabs group=instructions %}

   - CLI {#cli}

      Create a disk with a pre-configured public image of the VM with the online store:

      ```bash
      yc compute disk create \
         --name web-store-lab-dataplatform \
         --source-image-id fd8lcf21vlpfdhb84m2s \
         --folder-id <your-yc-folder-id>
      ```
      
      Create a virtual machine:

      ```bash
      yc compute instance create \
         --name magento \
         --zone ru-central1-d \
         --network-interface subnet-name=default-ru-central1-d,nat-ip-version=ipv4 \
         --hostname ya-sample-store \
         --use-boot-disk disk-name=web-store-lab-dataplatform \
         --ssh-key ~/.ssh/id_ed25519.pub
      ```

   {% endlist %}

1. In the security group settings, add the permission for incoming and outgoing traffic from ports `80` and `443` and from MySQL port `3306`.

1. Connect to the VM over SSH:
   ```
   ssh yc-user@<VM_public_IP_address>
   ```   

1. Open the `hosts` (C:\Windows\System32\drivers\etc\hosts) file as an administrator and add this line:
   ```
   <ip-address-vm> ya-sample-store.local
   ```

1. Connect to the online store at `http://ya-sample-store.local/`.

1. You can view the schema of the online store using [DBeaver](https://dbeaver.com/).

## Create staging storage {#create-staging-dwh}

To replicate tables with information about online store orders, create a Managed Service for MySQL® cluster:

1. In the [management console](https://console.yandex.cloud), select the folder where you want to create your database cluster. 
1. Navigate to **Managed Service for&nbsp;MySQL** and click **Create cluster**.
1. Specify a name for the cluster: `ya-sample-cloud-mysql`.
1. Select the host class: `s2.small`.
1. Under **Storage size**:

   * Select the storage type: `network-ssd`.
   * Select the volume: `32GB`.

1. Under **Database**, enter:

   * Enter the database name: `magento-cloud`.
   * Enter the username (`yc-user`) and password (`12345678`).

1. Under **Network settings**, select the cloud network to host the cluster in and security groups for cluster network traffic.
1. Under **Hosts**, select the parameters for the DB hosts created with the cluster:

   * Availability zone: `ru-central1-d`.
   * Subnet: `default-ru-central1-d`.

1. Click **Create cluster**.
For more information about creating clusters, see [Getting started with Managed Service for MySQL®](../quickstart.md#cluster-create.md).

## Configure the transfer parameters {#create-transfer}

To synchronize the order information from the MySQL database of the website with the intermediate data storage hosted in the cloud, configure Data Transfer:

1. In the management console, select the folder where you want to create a configuration for the connection. 
1. Navigate to **Data Transfer** and click **Create endpoint**.
1. Define the parameters of the data source, that is, the VM of the online store with a MySQL instance running on it:

   * **Name**: `magento-source`.
   * Select the `MySQL` DB type from the list.
   * **Host IP**: <VM's_public_IP_address>.
   * **Database name**: `ya_sample_store`. 
   * **Username**: `magento-svc`, password: `m@gent0`.
   * In the whitelist, specify the prefixes of the tables to replicate, e.g., `sales_*`.
   * Click **Create**.

1. Define the parameters of the target database for the data: a managed Managed Service for MySQL® database hosted in the cloud:

   * **Name**: `magento-report-dest`.
   * **Database**: `Managed Service for MySQL`.
   * Select the cluster ID from the list: `ya-sample-cloud-mysql`.
   * **Database name**: `magento-cloud`.
   * **Replication user name**: `yc-user`, password: `12345678`.
   * Select **Disable constraint** checks. 
     In this case, if the data transfer sequence is violated, no error messages are returned.
   * Click **Create**.

1. Select **Transfers** in the menu and click **Create transfer**.
1. Define the transfer parameters:

   * **Name**: `sales-order-sync`.
   * Under **Source**, select the `magento-source` endpoint.
   * Under **Target**, select the `magento-report-dest` endpoint.
   * Under **Transfer type**, select `Copy and replicate`. 
   * Click **Create**.
   * Click ![horizontal-ellipsis](../../_assets/console-icons/ellipsis.svg) in the line with the transfer description and select **Activate**. 
      
   As a result, the initial synchronization of data schemas and other information is performed and, in the future, the data will be automatically synchronized when changes appear in the source database. For the synchronization status and error messages, see **Logs**.

1. Check that the database schemas appear in the staging storage:

   * Go to the **SQL** section of the `ya-sample-cloud-mysql` staging storage.
   * Enter the username (`yc-user`) and password (`12345678`).
   * Select the `magento-cloud` DB.
   * Click **Connect**. 
      
   The online store's database schema appears in the window.

## Monitor the transfer of changes to Yandex Cloud {#start-sync}

1. Create an order in the online store at `http://ya-sample-store.local/`.
1. Make a query to the database in the cloud:

   ```sql
   SELECT so.*, soi.* FROM sales_order_grid so
   INNER JOIN sales_order_item soi ON so.entity_id = soi.order_id
   ORDER BY entity_id DESC 
   LIMIT 10
   ```

1. Make sure that your order data appeared in the database.

## Delete the resources you created {#clear-out}

To minimize resource consumption, delete the resources you no longer need:

1. [Delete the `magento` VM](../../compute/operations/vm-control/vm-delete.md).
1. [Delete the `ya-sample-cloud-mysql` cluster](../operations/cluster-delete.md).
1. If you reserved a public static IP address, [delete it](../../vpc/operations/address-delete.md).