Migrating databases from Yandex Managed Service for MySQL® to a third-party MySQL® cluster

Written by

Updated at December 5, 2025

Getting started
Transferring data using Yandex Data Transfer
- Required paid resources
- Migrate the database
Transferring data using external replication

Note

To learn about data migration from a third-party MySQL® cluster, see this article: Migrating a database from a third-party MySQL® cluster.

To migrate a database deployed in a Managed Service for MySQL® cluster to a third-party MySQL® cluster:

Transfer data.
Disable data writes to the source database.
Transfer the load to a third-party cluster.

Migration across versions is supported. For example, you can move databases from MySQL® 5.7 to 8. The MySQL® major version on a third-party cluster must be the same or higher than the version in the Managed Service for MySQL® cluster.

There are two ways to migrate data from a Managed Service for MySQL® source cluster to a third-party MySQL® target cluster:

Transferring data using Yandex Data Transfer.

This method allows you to migrate the entire database without interrupting user service.

For more information, see Problems addressed by Yandex Data Transfer.
Transferring data using external replication.

External replication allows you to migrate databases across MySQL® clusters using built-in DBMS tools.

Use this method only if, for some reason, it is not possible to migrate data using Yandex Data Transfer.

Getting started

Prepare the target cluster:

Create a MySQL® database in any suitable configuration.
Make sure that you can connect to the target cluster hosts from the internet.

Additionally, to migrate data using external MySQL® replication:

Make sure all the source cluster's hosts are accessible by a public IP address so that the target cluster can connect to the source. To do this:
- Add hosts with public IP addresses.
- Delete hosts without public IP addresses.
Install Managed Service for MySQL® server SSL certificates on the target cluster's hosts. They are required to connect to the publicly available source cluster.
If you need to, set up a firewall and security groups so you can connect to the source cluster, as well as to each cluster separately, e.g., using the mysql utility, from the target cluster.
Make sure you can connect to the source cluster's hosts from the target cluster's hosts.
Make sure that you can connect to the source cluster and the target cluster via SSL.

Transferring data using Yandex Data Transfer

Required paid resources

Managed Service for MySQL® cluster: computing resources allocated to hosts, size of storage and backups (see Managed Service for MySQL® pricing).
Public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).
Each transfer: use of computing resources and number of transferred data rows (see Data Transfer pricing).

Migrate the database

Prepare the source cluster database.
Prepare the target cluster database.
Set up the endpoints and transfer:
Manually

Terraform
1. Create a source endpoint:
  - Database type: MySQL.
  - Connection settings: Managed Service for MySQL cluster.
    
    Specify the source cluster ID.
2. Create a target endpoint:
  - Database type: MySQL.
  - Connection settings: Custom installation.
    
    Specify the target cluster connection settings.
3. Create a transfer of the Snapshot and increment type that will use the created endpoints.
4. Activate the transfer.
1. If you do not have Terraform yet, install it.
2. Get the authentication credentials. You can add them to environment variables or specify them later in the provider configuration file.
3. Configure and initialize a provider. There is no need to create a provider configuration file manually, you can download it.
4. Place the configuration file in a separate working directory and specify the parameter values. If you did not add the authentication credentials to environment variables, specify them in the configuration file.
5. Download the transfer and the data-transfer-mmy-mysql.tf endpoint configuration file to the same working directory.
6. Specify the following in the configuration file:
  - Source endpoint parameters.
  - Target endpoint parameters.
7. Check that the Terraform configuration files are correct using this command:
  terraform validate
  If there are any errors in the configuration files, Terraform will point them out.
8. Create the required infrastructure:
  1. Run this command to view the planned changes:
    
    terraform plan
    
    If you described the configuration correctly, the terminal will display a list of the resources to update and their parameters. This is a verification step that does not apply changes to your resources.
  2. If everything looks correct, apply the changes:
    
    Run this command:
    
    terraform apply
    
    Confirm updating the resources.
    
    Wait for the operation to complete.
  All the required resources will be created in the specified folder. You can check resource availability and their settings in the management console.
  
  Once created, your transfer will be activated automatically.
Warning

Avoid any changes to the data schema in the source and target clusters during the transfer operation. For more information, see Working with databases during transfer.
Wait for the transfer status to change to Replicating.
Switch the source cluster to "read-only" mode and transfer the load to the target cluster.
On the transfer monitoring page, wait for the Maximum data transfer delay metric to decrease to zero. This means that all changes that occurred in the source cluster after data was copied are transferred to the target cluster.
Deactivate the transfer and wait for its status to change to Stopped.

For more information about transfer statuses, see Transfer lifecycle.
Delete the endpoints and the transfer you created:
Manually

Terraform
If you created your endpoints and transfer manually:
1. Delete the stopped transfer.
2. Delete endpoints for both the source and target.
If you used Terraform to create your endpoints and transfer:
1. In the terminal window, go to the directory containing the infrastructure plan.
  
  Warning
  
  Make sure the directory has no Terraform manifests with the resources you want to keep. Terraform deletes all resources that were created using the manifests in the current directory.
2. Delete resources:
  1. Run this command:
    
    terraform destroy
  2. Confirm deleting the resources and wait for the operation to complete.
  All the resources described in the Terraform manifests will be deleted.

Transferring data using external replication

Transfer a logical dump of the database.
Configure the user in the source cluster to manage replication.
Start replication in the target cluster.
Monitor the migration process until it is complete.
Complete your migration.

Required paid resources

Managed Service for MySQL® cluster: computing resources allocated to hosts, size of storage and backups (see Managed Service for MySQL® pricing).
Public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).

Transfer a logical dump of the database

A logical dump is a file with a set of commands running which one by one you can restore the state of a database. It is created using the mysqldump utility. To ensure that a logical dump is complete, pause data writes to the database before creating it.

Warning

If the database stores custom procedures, grant the database owner the SHOW ROUTINE administrative privilege to perform a logical dump.

Request the current position of the binary log to make sure that restoring the logical dump is consistent:

SHOW MASTER STATUS;

+-------------------------+----------+--------------+------------------+-----------------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set           |
+-------------------------+----------+--------------+------------------+-----------------------------+
| mysql-bin-log-...000224 |  2058567 |              |                  | d827098b-...00b86:1-1575866 |
+-------------------------+----------+--------------+------------------+-----------------------------+
1 row in set (0.00 sec)

Write down the File and Position values. You will need them when starting replication.

Create a dump of the source cluster database:

mysqldump \
    --databases=<DB_name> \
    --routines \
    --host=<master_host_FQDN> \
    --ssl-ca=<path_to_SSL_certificate> \
    --user=<DB_owner_username> > <dump_file>

Tip

Use a special FQDN that always points to the current master host of Managed Service for MySQL® clusters.

Restore the database from the dump on the target cluster:

Connecting with SSL

Connecting without SSL

mysql --host=<master_host_FQDN> \
      --user=<username> \
      --password \
      --port=3306 \
      --ssl-ca=<path_to_SSL_certificate> \
      --ssl-mode=VERIFY_IDENTITY \
      --line-numbers \
      <DB_name> < <dump_file>

mysql --host=<master_host_FQDN> \
      --user=<username> \
      --password \
      --port=3306 \
      --line-numbers \
      <DB_name> < <dump_file>

Create a user with full access rights to the database being migrated in the target cluster:

CREATE USER '<username>'@'%' IDENTIFIED BY '<password>';
GRANT ALL PRIVILEGES ON <DB_name>.* TO '<username>'@'%';

Configure the user in the source cluster to manage replication

MySQL® uses the master-replica model when performing replication: the target cluster replicates the changes of the source cluster's binary log to its relay log. The host replica reproduces the changes from the relay log applying them to its own data.

To get binary log changes and manage the replication flow in the source cluster:

Create a user.
Assign the ALL_PRIVILEGES role for the source cluster database to that user.
Assign the REPLICATION CLIENT and REPLICATION SLAVE global privileges to that user.

The target cluster will connect to the source cluster on behalf of this user.

Start replication in the target cluster

Change the target cluster's /etc/mysql/my.cnf configuration file to start replication:
```
[mysqld]
...
log_bin = mysql-bin
server_id = 2
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index

gtid-mode = on
enforce-gtid-consistency = on
```
Where:
- log_bin: Binary log name in the target cluster.
- server_id: Target cluster ID. The default value is 1. However, to run replication, make sure that the values of the source and target cluster IDs are different.
- relay-log: Path to the relay log.
- relay-log-index: Path to the relay log index.
Also enable gtid-mode and enforce-gtid-consistency for replication. In Managed Service for MySQL® clusters, they are always activated.
Restart mysql:
```
sudo systemctl restart mysql
```
Connect to the target cluster on behalf of the user that is granted full access rights to the database being migrated.

Enable replication for this database and disable replication for service databases (they are replicated by default):

CHANGE REPLICATION FILTER
    REPLICATE_DO_DB=(
        <target_cluster_DB_name>
    ),
    REPLICATE_IGNORE_DB=(
        sys,
        mysql,
        performance_schema,
        information_schema
    );

To assign a master for the target cluster, specify the parameters of the source cluster's master host:

Tip

Use a special FQDN that always points to the current master host of Managed Service for MySQL® clusters.

CHANGE MASTER TO
      MASTER_HOST = '<master_host_FQDN>',
      MASTER_USER = '<user_for_replication>',
      MASTER_PASSWORD = '<user_password>',
      MASTER_LOG_FILE = '<File_value_from_binary_log_position_request>',
      MASTER_LOG_POS = <Position_value_from_binary_log_position_request>,
      MASTER_SSL_CA = '<path_to_SSL_certificate>',
      MASTER_SSL_VERIFY_SERVER_CERT = 0,
      MASTER_SSL = 1;

Start the relay log's replication:
```
START SLAVE;
```
This starts the process of migrating data from the source cluster's database to the target cluster's database.
After successfully starting the replication, run this command only once:
```
STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
START SLAVE;
```
This is to ensure the replication will be reconfigured to use the new master host if the master host in the source cluster changes. For more information about configurations, see this MySQL® guide.

Track the migration process

Use the command that returns the replication status:

SHOW SLAVE STATUS\G

*************************** 1. row ***************************
           Slave_IO_State: Waiting for master to send event
              Master_Host: rc1a-hxk9audl********.mdb.yandexcloud.net
              Master_User: replica-my
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin-log-rc1a-hxk9audl********-mdb-yandexcloud-net.000225
      Read_Master_Log_Pos: 1702815
           Relay_Log_File: 6b6d647a39b6-relay-bin.000084
            Relay_Log_Pos: 409
            ...

The following fields contain info on the replication status:

Slave_IO_State and Slave_SQL_Running_State: I/O state of the binary log and relay log streams. If replication is successful, both streams are active.
Read_Master_Log_Pos: Last position read from the master host log.
Seconds_Behind_Master: Replica's lag behind the master, in seconds.
Last_IO_Error and Last_SQL_Error: Replication errors.

For more information about replication status, see the MySQL® documentation.

Complete your migration

Remove the load from the source cluster and make sure that the application does not write data to the source cluster database. To do this, update the MAX_UPDATES_PER_HOUR user-defined setting of the source cluster to 1.
Wait for the Seconds_Behind_Master metric value to decrease to zero. This means that all changes that occurred in the source cluster after creating the logical dump are transferred to the target cluster.
Stop replication in the target cluster:
```
STOP SLAVE;
```
Transfer the load to the target cluster.
Remove the user managing replication on the source cluster.
Remove the user with full access rights to the migrated database on the target cluster if you no longer need this user.

Migrating databases from Yandex Managed Service for MySQL® to a third-party MySQL® cluster

Getting startedGetting started

Transferring data using Yandex Data TransferTransferring data using Yandex Data Transfer

Required paid resourcesRequired paid resources

Migrate the databaseMigrate the database

Transferring data using external replicationTransferring data using external replication

Required paid resourcesRequired paid resources

Transfer a logical dump of the databaseTransfer a logical dump of the database

Configure the user in the source cluster to manage replicationConfigure the user in the source cluster to manage replication

Start replication in the target clusterStart replication in the target cluster

Track the migration processTrack the migration process

Complete your migrationComplete your migration

Was the article helpful?

Getting started

Transferring data using Yandex Data Transfer

Required paid resources

Migrate the database

Transferring data using external replication

Required paid resources

Transfer a logical dump of the database

Configure the user in the source cluster to manage replication

Start replication in the target cluster

Track the migration process

Complete your migration