[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for MySQL®](../index.md) > [Tutorials](index.md) > Migrating a database from Managed Service for MySQL® to MySQL®

# Migrating a database from Managed Service for MySQL® to a third-party MySQL® cluster

# Migrating a database from Managed Service for MySQL® to a third-party MySQL® cluster

{% note info %}

To learn about migrating data from a third-party MySQL® cluster, see [this tutorial](data-migration/index.md).

{% endnote %}

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

1. Transfer the data.
1. Disable data writes to the source database.
1. Switch all traffic to the third-party cluster.

You can migrate your databases from one MySQL® version to another, e.g., from MySQL® 5.7 to MySQL® 8. Note that the third-party cluster must run a major MySQL® version not lower than that on your 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_:

* [Transfer data using Yandex Data Transfer](#data-transfer).

    This method enables you to migrate the entire database without downtime for users.

    Learn more in [Problems addressed by Yandex Data Transfer](../../data-transfer/concepts/use-cases.md).

* [Transfer data using external replication](#binlog-replication).

    [_External replication_](https://dev.mysql.com/doc/refman/8.0/en/replication-configuration.html) enables you to migrate databases between MySQL® clusters using native DBMS tools.

    Use this approach only if, for some reason, transferring data with Yandex Data Transfer is not an option.

## Getting started {#before-you-begin}

Set up the target cluster:

* [Create a MySQL® database](https://dev.mysql.com/doc/refman/8.0/en/creating-database.html) with any suitable configuration.
* Make sure you can connect to the target cluster hosts from the internet.

Additionally, to migrate data using external MySQL® replication:

* Check that all source cluster hosts are accessible via public IP addresses to make sure the target cluster can connect to the source cluster. Proceed as follows:
   * [Add hosts](../operations/hosts.md#add) with public IP addresses.
   * [Delete hosts](../operations/hosts.md#remove) without public IP addresses.
* Install the [Managed Service for MySQL® server SSL certificates](../operations/connect/index.md#get-ssl-cert) on the target cluster hosts. These certificates are required to connect to a publicly accessible source cluster.
* If required, set up a firewall and configure [security groups](../operations/connect/index.md#configuring-security-groups) to enable connections from the target cluster to the source cluster and to each cluster individually, e.g., using [mysql](https://dev.mysql.com/doc/refman/8.0/en/mysql.html).
* Make sure the target cluster hosts can connect to the source cluster hosts.
* Make sure you can [connect](../operations/connect/index.md) to both the source cluster and the target cluster using SSL.

## Migrating data via Yandex Data Transfer {#data-transfer}


### Required paid resources {#paid-resources-with-data-transfer}

* Managed Service for MySQL® cluster, which includes 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 [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)).


### Migrate the database {#database-migration}


# Migrating data via Yandex Data Transfer {#data-transfer}

1. [Set up the source cluster database](../../data-transfer/operations/prepare.md#source-my).
1. [Set up the target cluster database](../../data-transfer/operations/prepare.md#target-my).
1. Create endpoints and a transfer:

    {% list tabs group=instructions %}

    - Manually {#manual}

        1. [Create a source endpoint](../../data-transfer/operations/endpoint/index.md#create):

            * **Database type**: `MySQL`.
            * **Connection settings**: `Managed Service for MySQL cluster`.

                Specify the source cluster ID.

        1. [Create a target endpoint](../../data-transfer/operations/endpoint/index.md#create):

            * **Database type**: `MySQL`.
            * **Connection settings**: `Custom installation`.
                
                Configure the target cluster connection settings.

        1. [Create a transfer](../../data-transfer/operations/transfer.md#create) of the _**Snapshot and increment**_-type that will use the new endpoints.

        1. [Activate the transfer](../../data-transfer/operations/transfer.md#activate).

    - Terraform {#tf}

        1. If you do not have Terraform yet, [install it](../../tutorials/infrastructure-management/terraform-quickstart.md#install-terraform).
        1. [Get the authentication credentials](../../tutorials/infrastructure-management/terraform-quickstart.md#get-credentials). You can add them to environment variables or specify them later in the provider configuration file.
        1. [Configure and initialize a provider](../../tutorials/infrastructure-management/terraform-quickstart.md#configure-provider). There is no need to create a provider configuration file manually, you can [download it](https://github.com/yandex-cloud-examples/yc-terraform-provider-settings/blob/main/provider.tf).
        1. Place the configuration file in a separate working directory and [specify the parameter values](../../tutorials/infrastructure-management/terraform-quickstart.md#configure-provider). If you did not add the authentication credentials to environment variables, specify them in the configuration file.

        1. Download the [data-transfer-mmy-mysql.tf](https://github.com/yandex-cloud-examples/yc-data-transfer-from-cloud-mysql-to-on-premise/blob/main/data-transfer-mmy-mysql.tf) file with the transfer and endpoint configuration file to the same working directory.

        1. Specify the following in the configuration file:

            * [Source endpoint parameters](../../data-transfer/operations/endpoint/source/mysql.md#managed-service).
            * [Target endpoint parameters](../../data-transfer/operations/endpoint/target/mysql.md#on-premise).

        1. Validate your Terraform configuration files using this command:

            ```bash
            terraform validate
            ```

            Terraform will display any configuration errors detected in your files.

        1. Create the required infrastructure:

            1. Run this command to view the planned changes:
            
               ```bash
               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.
            
            1. If everything looks correct, apply the changes:
               1. Run this command:
            
                  ```bash
                  terraform apply
                  ```
            
               1. Confirm updating the resources.
               1. 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](https://console.yandex.cloud).

            The transfer will be activated automatically as soon as it is created.

    {% endlist %}

    {% note warning %}

    Avoid changing the data schema in either the source or target cluster while the transfer is in progress. For more information, see [Working with databases during transfer](../../data-transfer/operations/db-actions.md).

    {% endnote %}

1. Wait for the transfer status to change to **Replicating**.
1. Switch the source cluster to "read-only" mode and move the workload to the target cluster.
1. On the [transfer monitoring](../../data-transfer/operations/monitoring.md) page, wait until the **Maximum data transfer delay** value drops to zero. This indicates that the target cluster now contains all changes made in the source cluster after the data copy completed.
1. [Deactivate](../../data-transfer/operations/transfer.md#deactivate-transfer) the transfer and wait for its status to change to **Stopped**.

    For more information about transfer statuses, see [Transfer lifecycle](../../data-transfer/concepts/transfer-lifecycle.md#statuses).

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

    {% list tabs group=instructions %}

    - Manually {#manual}

      1. [Ensure the transfer is stopped, then delete it](../../data-transfer/operations/transfer.md#delete).
      1. [Delete the source and target endpoints](../../data-transfer/operations/endpoint/index.md#delete).

    - Terraform {#tf}

      1. In the terminal window, go to the directory containing the infrastructure plan.
      
          {% note 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.
      
          {% endnote %}
      
      1. Delete resources:
      
          1. Run this command:
      
              ```bash
              terraform destroy
              ```
      
          1. Confirm deleting the resources and wait for the operation to complete.
      
          All the resources described in the Terraform manifests will be deleted.

    {% endlist %}

## Transferring data using external replication {#binlog-replication}

1. [Transfer the logical database dump](#migrate-schema).
1. [Configure the user in the source cluster to manage replication](#configure-user).
1. [Start replication in the target cluster](#start-replica).
1. [Monitor the migration process](#monitor-migration) until it is complete.
1. [Complete your migration](#finish-migration).


### Required paid resources {#paid-resources-binlog-replication}

* Managed Service for MySQL® cluster, which includes 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 [Virtual Private Cloud pricing](../../vpc/pricing.md)).


### Transfer the logical database dump {#migrate-schema}

A _logical dump_ is a file with a sequence of commands that can restore the database state. You can create it with [mysqldump](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html). Before creating a logical dump, pause database writes to ensure its completeness.

{% note warning %}

If your database contains user-defined stored procedures, [grant](../operations/grant.md#grant-privilege) the database owner the [SHOW ROUTINE](../concepts/settings-list.md#setting-administrative-privileges) administrative privilege to perform a logical dump.

{% endnote %}

1. Get the current binary log position to ensure consistency when restoring the logical dump:

    ```sql
    SHOW MASTER STATUS;
    ```

    ```text
    +-------------------------+----------+--------------+------------------+-----------------------------+
    | 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)
    ```

    Save the `File` and `Position` values, as you will need them to start replication.

1. Create a dump of the source cluster database:

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

   {% note tip %}
   
   Use a [special FQDN](../concepts/network.md#fqdn-master) that always points to the current master host of Managed Service for MySQL® clusters.
   
   {% endnote %}

1. Restore the database from the dump in the target cluster:

    {% list tabs group=connection %}

    - Connecting with SSL {#with-ssl}

       ```bash
       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>
       ```

    - Connecting without SSL {#without-ssl}

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

    {% endlist %}

1. Create a user in the target cluster with full permissions for the database being migrated:

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

### Configure the user in the source cluster to manage replication {#configure-user}

MySQL® uses the <q>master-replica</q> model for replication: the target cluster copies the source cluster's binary log changes to its own relay log. The replica host applies these changes to its data.

To capture binary log changes and manage replication in the source cluster:
1. [Create a user](../operations/cluster-users.md#adduser).
1. [Assign](../operations/grant.md) it the `ALL_PRIVILEGES` role for the source cluster database.
1. [Grant](../operations/cluster-users.md#update-settings) the user the `REPLICATION CLIENT` and `REPLICATION SLAVE` global privileges as well.

The target cluster will connect to the source cluster as this user.

### Start replication in the target cluster {#start-replica}

1. Edit the `/etc/mysql/my.cnf` file containing the target cluster configuration to start replication:

   ```bash
   [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 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 enabled.

1. Restart `mysql`:

    ```bash
    sudo systemctl restart mysql
    ```

1. Connect to the target cluster as the user with full access to the database you want to migrate.
1. Enable replication for this database and disable it for system databases, since they replicate by default:

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

1. To assign a master for the target cluster, specify the parameters of the master host in the source cluster:

   {% note tip %}
   
   Use a [special FQDN](../concepts/network.md#fqdn-master) that always points to the current master host of Managed Service for MySQL® clusters.
   
   {% endnote %}

   ```sql
   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_query>',
         MASTER_LOG_POS = <Position_value_from_binary_log_position_query>,
         MASTER_SSL_CA = '<path_to_SSL_certificate>',
         MASTER_SSL_VERIFY_SERVER_CERT = 0,
         MASTER_SSL = 1;
   ```

1. Start applying the relay log:

   ```sql
   START SLAVE;
   ```

   This initiates data migration from the source cluster database to the target cluster database.

1. After successfully starting the replication, run this command once:

   ```sql
   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. Read more about the setting in [this MySQL® guide](https://dev.mysql.com/doc/refman/8.0/en/change-master-to.html).

### Monitor your data migration {#monitor-migration}

Run the command that returns the *replication status*:

```sql
SHOW SLAVE STATUS\G
```

```text
*************************** 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 [this MySQL® guide](https://dev.mysql.com/doc/refman/8.0/en/replication-administration-status.html).

### Complete your migration {#finish-migration}

1. Stop all writes to the source cluster and make sure the application does not write data to its database. You can do this by [updating the `MAX_UPDATES_PER_HOUR` user-defined setting of the source cluster](../operations/cluster-users.md#update-settings) to `1`.
1. Wait for the `Seconds_Behind_Master` value to decrease to zero. This means that all changes that occurred in the source cluster after creating the logical dump have been transferred to the target cluster.
1. Stop replication in the target cluster:

   ```sql
   STOP SLAVE;
   ```

1. Switch the load to the target cluster.
1. [Delete the user](../operations/cluster-users.md#removeuser) managing replication in the source cluster.
1. Delete the user with full access to the migrated database in the target cluster if this user is no longer needed.