Syncing data from a third-party MySQL® cluster to Yandex Managed Service for MySQL® 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:
- Prepare your cloud.
- Create a VM with an online store.
- Create staging storage.
- Configure the transfer parameters.
- Monitor the transfer of changes in the DB in the cloud.
If you no longer need the resources you created, delete them.
Prepare your cloud
Sign up for Yandex Cloud and create a billing account:
- Go to the management console
and log in to Yandex Cloud or create an account if you do not have one yet. - On the Yandex Cloud Billing
page, make sure you have a billing account linked and it has theACTIVE
orTRIAL_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
Learn more about clouds and folders.
Required paid resources
Infrastructure costs for data transfers include:
- Fee for a continuously running VM (see Yandex Compute Cloud pricing).
- Fee for using a dynamic or a static public IP (see Yandex Virtual Private Cloud pricing).
- Fee for a continuously running Managed Service for MySQL® cluster (see Managed Service for MySQL® pricing).
- Fee for Data Transfer (see Data Transfer pricing).
Create a VM with an online store
-
Create a VM with an online store named
web-store-vm
based on MySQL:CLICreate 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-a \ --network-interface subnet-name=default-ru-central1-a,nat-ip-version=ipv4 \ --hostname ya-sample-store \ --use-boot-disk disk-name=web-store-lab-dataplatform \ --ssh-key ~/.ssh/id_ed25519.pub
-
In the security group settings, add permission for incoming and outgoing traffic from ports
80
and443
and from MySQL port3306
. -
Connect to the VM via SSH:
ssh yc-user@<VM's_public_IP_address>
-
Open the
hosts
(C:\Windows\System32\drivers\etc\hosts) file as an administrator and add the line:<ip-address-vm> ya-sample-store.local
-
Connect to the online store at
http://ya-sample-store.local/
. -
You can view the schema of the online store using DBeaver
.
Create staging storage
To replicate tables with information about online store orders, create a Managed Service for MySQL® cluster:
-
In the management console
, select the folder where you want to create a DB cluster. -
Select Managed Service for MySQL® and click Create cluster.
-
Specify a name for the cluster:
ya-sample-cloud-mysql
. -
Select the host class:
s2.small
. -
Under Storage size:
- Select the storage type:
network-ssd
. - Select the volume:
32GB
.
- Select the storage type:
-
Under Database, enter:
- Enter the database name:
magento-cloud
. - Enter the
yc-user
username and12345678
password.
- Enter the database name:
-
Under Network settings, select the cloud network to host the cluster in and security groups for cluster network traffic.
-
Under Hosts, select the parameters for the DB hosts created with the cluster:
- Availability zone:
ru-central1-a
. - Subnet:
default-ru-central1-a
.
- Availability zone:
-
Click Create cluster.
For more information about creating clusters, see Getting started with Managed Service for MySQL®.
Configure 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:
-
In the management console, select the folder where you want to create a configuration for the connection.
-
Select Data Transfer and click Create endpoint.
-
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
) and password (m@gent0
). - In the whitelist, specify the prefixes of the tables to be replicated. For example,
sales_*
. - Click Create.
- Name:
-
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's name (
yc-user
) and password (12345678
). - Select Disable constraint checks.
In this case, if the data transfer sequence is violated, no error messages are returned. - Click Create.
- Name:
-
Select Transfers in the menu and click Create transfer.
-
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.
- Name:
-
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
yc-user
username and12345678
password. - Select the
magento-cloud
database - Click Connect.
The online store's database schema appears in the window.
- Go to the SQL section of the
Monitor the transfer of changes to Yandex Cloud
- Create an order in the online store at
http://ya-sample-store.local/
. - 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
- Make sure that your order data appeared in the database.
How to delete the resources you created
Delete the resources you no longer need to avoid paying for them: