Syncing data from a third-party MySQL® cluster to Yandex Managed Service for MySQL®

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. Prepare your cloud.
2. Create a VM with an online store.
3. Create staging storage.
4. Configure the transfer parameters.
5. Monitor the transfer of changes in the DB in the cloud.

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

Prepare your cloudPrepare your cloud

Sign up for Yandex Cloud and create a billing account:

1. Go to the management console and log in to Yandex Cloud or create an account if you do not have one yet.
2. On the Yandex Cloud Billing page, make sure you have a billing account linked and it has the ACTIVE or TRIAL_ACTIVE status. If you do not have a billing account, create one.

If you have an active billing account, you can go to the cloud page to create or select a folder for your infrastructure to operate in.

Learn more about clouds and folders.

Required paid resourcesRequired paid resources

Infrastructure costs for data transfers include:

1. Fee for a continuously running VM (see Yandex Compute Cloud pricing).
2. Fee for using a dynamic or static external IP address (see Yandex Virtual Private Cloud pricing).
3. Fee for a continuously running Managed Service for MySQL® cluster (see Managed Service for MySQL® pricing).
4. Fee for Data Transfer (see Data Transfer pricing).

Create a VM with an online storeCreate a VM with an online store

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

   CLI

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

   yc compute disk create \
      --name web-store-lab-dataplatform \
      --source-image-id fd8lcf21vlpfdhb84m2s \
      --folder-id <your-yc-folder-id>
   

   Create a virtual machine:

   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
   

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

3. Connect to the VM over SSH:

   ssh yc-user@<VM_public_IP_address>
   

4. Open the hosts (C:\Windows\System32\drivers\etc\hosts) file as an administrator and add this line:

   <ip-address-vm> ya-sample-store.local
   

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

6. You can view the schema of the online store using DBeaver.

Create staging storageCreate staging storage

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

1. In the management console, select the folder where you want to create a DB cluster.

2. Select Managed Service for MySQL® and click Create cluster.

3. Specify a name for the cluster: ya-sample-cloud-mysql.

4. Select the host class: s2.small.

5. Under Storage size:

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

6. Under Database, enter:

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

7. Under Network settings, select the cloud network to host the cluster in and security groups for cluster network traffic.

8. Under Hosts, select the parameters for the DB hosts created with the cluster:

   • Availability zone: ru-central1-d.
   • Subnet: default-ru-central1-d.

9. Click Create cluster.
   For more information about creating clusters, see Getting started with Managed Service for MySQL®.

Configure the transfer parametersConfigure the transfer parameters

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.

2. Select Data Transfer and click Create endpoint.

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

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

5. Select Transfers in the menu and click Create transfer.

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

7. 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 CloudMonitor the transfer of changes to Yandex Cloud

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

   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
   

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

How to delete the resources you createdHow to delete the resources you created

Delete the resources you no longer need to avoid paying for them:

• Delete the magento VM.
• Delete the ya-sample-cloud-mysql cluster.
• If you reserved a public static IP address, delete it.