[Yandex Cloud documentation](../../../../index.md) > [Yandex Data Transfer](../../../index.md) > [Step-by-step guides](../../index.md) > [Configuring endpoints](../index.md) > MySQL > Source

# Transferring data from a MySQL® source endpoint

Yandex Data Transfer enables you to migrate data from a MySQL® database and implement various data transfer, processing, and transformation scenarios. To implement a transfer:

1. [Explore possible data transfer scenarios](#scenarios).
1. [Prepare the MySQL®](#prepare) database for the transfer.
1. [Set up a source endpoint](#endpoint-settings) in Yandex Data Transfer.
1. [Set up one of the supported data targets](#supported-targets).
1. [Create](../../transfer.md#create) a transfer and [start](../../transfer.md#activate) it.
1. [Perform the required operations with the database](#db-actions) and [see how the transfer is going](../../monitoring.md).
1. In case of any issues, [use ready-made solutions](#troubleshooting) to resolve them.

## Scenarios for transferring data from MySQL® {#scenarios}

1. Migration: Moving data from one storage to another. Migration often means migrating a database from obsolete local databases to managed cloud ones.

    * [Migrating a MySQL® cluster](../../../tutorials/managed-mysql-to-mysql.md).
    * [Migration with change of storage from MySQL® to YDB](../../../tutorials/managed-mysql-to-ydb.md).
    * [Migration with change of storage from MySQL® to PostgreSQL](../../../tutorials/mmy-to-mpg.md).
    * [Migration with change of storage from MySQL® to Greenplum®](../../../tutorials/mmy-to-mgp.md).

1. Data change capture means tracking changes to a database and delivering those changes to consumers. It is used for applications that are sensitive to real-time data changes.

    * [Capturing changes from MySQL® and delivering to YDS](../../../tutorials/mmy-to-yds.md).
    * [Capturing changes from MySQL® and delivering to Apache Kafka®](../../../tutorials/cdc-mmy.md).

1. Uploading data to data marts is a process of transferring prepared data to storage for subsequent visualization.

    * [Loading data from MySQL® to the ClickHouse® data mart](../../../tutorials/mysql-to-clickhouse.md).

1. Uploading data to scalable Object Storage storage allows you to save on data storage and simplifies the exchange with contractors.

    * [Loading MySQL® data to the Object Storage scalable storage](../../../tutorials/mmy-objs-migration.md).

For a detailed description of possible Yandex Data Transfer scenarios, see [Tutorials](../../../tutorials/index.md).

## Preparing the source database {#prepare}

{% list tabs %}

- Managed Service for MySQL®

    
    1. [Enable full binary logging](../../../../managed-mysql/operations/update.md#change-mysql-config) on the source by setting the [**Binlog row image**](https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_row_image) parameter to `FULL` or `NOBLOB`.
 
    1. Optionally, [set a limit](../../../../managed-mysql/operations/update.md#change-mysql-config) on the size of data chunks to send using the **Max allowed packet** parameter.
    
    1. [Create a user](../../../../managed-mysql/operations/cluster-users.md#adduser) for connecting to the source.
        
        1. [Grant](../../../../managed-mysql/operations/grant.md#grant-privilege) the user `ALL_PRIVILEGES` for the source database.
        
        1. [Grant](../../../../managed-mysql/concepts/settings-list.md#setting-administrative-privileges) the user the `REPLICATION CLIENT` and `REPLICATION SLAVE` administrative privileges.
    
    1. For _**Replication**_ and _**Snapshot and increment**_ transfers, tables without unique indexes are not transferred. 
       
       If a table containing a row has a primary key, only the primary key columns get written to the binary log when the row is modified. If there is no primary key but there is a unique index where all columns are not `NULL`, only these columns get written to the binary log. If there is no primary key or unique index without `NULL` columns, all columns in the row get written to the binary log.
       
       To make sure the transfer works correctly if the database you need to migrate contains tables with no unique indexes:
       
       * Do not transfer such tables. You can add them to the list of excluded tables in [source endpoint settings](mysql.md#additional-settings).
       
       * Create unique indexes, e.g., primary keys (`PRIMARY KEY`), in the tables which do not have them.
       
           1. To get a list of tables without a primary key, run the query:
       
               ```sql
               SELECT
                   tab.table_schema AS database_name,
                   tab.table_name AS table_name,
                   tab.table_rows AS table_rows,
                   tco.*
               FROM information_schema.tables tab
                   LEFT JOIN information_schema.table_constraints tco
                       ON (tab.table_schema = tco.table_schema
                           AND tab.table_name = tco.table_name
               )
               WHERE
                   tab.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
                   AND tco.constraint_type IS NULL
                   AND tab.table_type = 'BASE TABLE';
               ```
       
           1. Study the structure of tables without a primary key that need to be transferred to the target:
       
               ```sql
               SHOW CREATE TABLE <database_name>.<table_name>;
               ```
       
           1. Add a simple or complex primary key to the tables that need to be transferred to the target:
       
               ```sql
               ALTER TABLE <table_name> ADD PRIMARY KEY (<column_or_group_of_columns>);
               ```
       
           1. If the table being transferred to the target has no column or group of columns suitable for the role of the primary key, create a new column:
       
               ```sql
               ALTER TABLE <table_name> ADD id BIGINT PRIMARY KEY AUTO_INCREMENT;
               ```
    
       {% note info %}
    
       If, when creating a primary key, you get an error saying _`Creating index 'PRIMARY' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again`_, [increase](../../../../managed-mysql/operations/update.md#change-mysql-config) the [`Innodb log file size`](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_file_size) parameter value in the DBMS settings.
    
       {% endnote %}
    
    1. Deactivate trigger transfer at the transfer initiation stage and reactivate it at the completion stage (for the _**Replication**_ and the _**Snapshot and increment**_ transfer types). For more information, see the [description of the additional endpoint settings for the MySQL® source](mysql.md#additional-settings).

- MySQL®
    
    1. If not planning to use [Cloud Interconnect](../../../../interconnect/concepts/index.md) or [VPN](https://en.wikipedia.org/wiki/Virtual_private_network) for connections to an external cluster, make such cluster accessible from the Internet from [IP addresses used by Data Transfer](../../../../overview/concepts/public-ips.md#virtual-private-cloud).
       
       For details on linking your network up with external resources, see [this concept](../../../concepts/network.md#source-external).
    
    1. Make sure the source uses the MyISAM or InnoDB low-level storage subsystem. If you use other subsystems, the transfer may fail.
    
    1. [Enable full binary logging](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_row_image) on the source by setting the `binlog_row_image` parameter to `FULL` or `NOBLOB`.

       {% note info %}

       For MariaDB 11.4 or higher, set `binlog_legacy_event_pos = true`.

       {% endnote %}    
    
    1. [Specify row format for the binary log](https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_format) on the source by setting the `binlog_format` parameter to `ROW`.

    1. For the _**Replication**_ and _**Snapshot and increment**_ transfer types:

        * In the `log_bin` parameter, [specify the path to the binary log file](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#option_mysqld_log-bin).

        * Enter the binary log information using the [SHOW MASTER STATUS](https://dev.mysql.com/doc/refman/8.0/en/show-master-status.html) request (for MySQL® 5.7 and 8.0) or the [SHOW BINARY LOG STATUS](https://dev.mysql.com/doc/refman/8.4/en/show-binary-log-status.html) request (for MySQL® 8.4). The request should return a string with the information, not an empty response.

    1. If the replication source is a cluster that is behind the load balancer, enable GTID mode for it (`GTID-MODE = ON`).
    
       If it is not possible to enable GTID mode for any reason, make sure the binary log name template contains the host name.
    
       In both cases, this will allow replication to continue even after changing the master host.
    
    1. Optionally, [set a limit](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_allowed_packet) on the size of outbound data chunks using the `max_allowed_packet` parameter.
    
    1. Create a user to connect to the source and grant them the required privileges:
    
        ```sql
        CREATE USER '<username>'@'%' IDENTIFIED BY '<password>';
        GRANT ALL PRIVILEGES ON <database_name>.* TO '<username>'@'%';
        GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO '<username>'@'%';
        ```

    1. For _**Replication**_ and _**Snapshot and increment**_ transfers, tables without unique indexes are not transferred. 
       
       If a table containing a row has a primary key, only the primary key columns get written to the binary log when the row is modified. If there is no primary key but there is a unique index where all columns are not `NULL`, only these columns get written to the binary log. If there is no primary key or unique index without `NULL` columns, all columns in the row get written to the binary log.
       
       To make sure the transfer works correctly if the database you need to migrate contains tables with no unique indexes:
       
       * Do not transfer such tables. You can add them to the list of excluded tables in [source endpoint settings](mysql.md#additional-settings).
       
       * Create unique indexes, e.g., primary keys (`PRIMARY KEY`), in the tables which do not have them.
       
           1. To get a list of tables without a primary key, run the query:
       
               ```sql
               SELECT
                   tab.table_schema AS database_name,
                   tab.table_name AS table_name,
                   tab.table_rows AS table_rows,
                   tco.*
               FROM information_schema.tables tab
                   LEFT JOIN information_schema.table_constraints tco
                       ON (tab.table_schema = tco.table_schema
                           AND tab.table_name = tco.table_name
               )
               WHERE
                   tab.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
                   AND tco.constraint_type IS NULL
                   AND tab.table_type = 'BASE TABLE';
               ```
       
           1. Study the structure of tables without a primary key that need to be transferred to the target:
       
               ```sql
               SHOW CREATE TABLE <database_name>.<table_name>;
               ```
       
           1. Add a simple or complex primary key to the tables that need to be transferred to the target:
       
               ```sql
               ALTER TABLE <table_name> ADD PRIMARY KEY (<column_or_group_of_columns>);
               ```
       
           1. If the table being transferred to the target has no column or group of columns suitable for the role of the primary key, create a new column:
       
               ```sql
               ALTER TABLE <table_name> ADD id BIGINT PRIMARY KEY AUTO_INCREMENT;
               ```
    
       {% note info %}
    
       If, when creating a primary key, you get an error saying _`Creating index 'PRIMARY' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again`_, increase the [`inno_db_log_file_size`](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_file_size) parameter value in the DBMS settings.
    
       {% endnote %}
    
    1. Deactivate trigger transfer at the transfer initiation stage and reactivate it at the completion stage (for the _**Replication**_ and the _**Snapshot and increment**_ transfer types). For more information, see the [description of the additional endpoint settings for the MySQL® source](mysql.md#additional-settings).

{% endlist %}

## Configuring the MySQL® source endpoint {#endpoint-settings}

When [creating](../index.md#create) or [updating](../index.md#update) an endpoint, you can define:

* [Yandex Managed Service for MySQL® cluster](#managed-service) connection or [custom installation](#on-premise) settings, including those based on Yandex Compute Cloud VMs. These are required parameters.
* [Additional parameters](#additional-settings).

### Managed Service for MySQL® cluster {#managed-service}


{% note warning %}

To create or edit an endpoint of a managed database, you will need the [`managed-mysql.viewer`](../../../../managed-mysql/security/index.md#mmy-viewer) role or the primitive [`viewer`](../../../../iam/roles-reference.md#viewer) role for the folder the cluster of this managed database resides in.

{% endnote %}


Connection to the database with the cluster specified in Yandex Cloud.

{% list tabs group=instructions %}

- Management console {#console}

    * **Connection type**: Select a database connection option:
    
        * **Self-managed**: Allows you to specify connection settings manually.
    
            Select **Managed Service for MySQL cluster** as the installation type and configure the following settings:
    
            * **Managed DB cluster**: Select the cluster to connect to.
            * **Database**: Specify the name of the database in the selected cluster.
            * **User**: Specify the username Data Transfer will use to connect to the database.
            * **Password**: Enter the user's password to the database.
    
        * **Connection Manager**: Enables using a managed database connection using [Yandex Connection Manager](../../../../metadata-hub/quickstart/connection-manager.md):
    
            * Select the folder with the Managed Service for MySQL® cluster.
            * Select **Managed DB cluster** as the installation type and configure the following settings:
    
                * **Managed DB cluster**: Select the cluster to connect to.
                * **Connection**: Specify the managed connection ID in Connection Manager.
                * **Database**: Specify the name of the database in the selected cluster.
    
            {% note warning %}
            
            To use a connection from Connection Manager, the user must have [access permissions](../../../../metadata-hub/operations/connection-access.md) for this connection of `connection-manager.user` or higher.
            
            {% endnote %}
    
    * **Security groups**: Select the cloud network to host the endpoint and security groups for network traffic.
      
      Thus, you will be able to apply the specified security group rules to the VMs and clusters in the selected network without changing the settings of these VMs and clusters. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md).

- CLI {#cli}

    * Endpoint type: `mysql-source`.

    * `--cluster-id`: ID of the cluster you need to connect to.
    * `--database` — Database name. Leave the field empty if you want to transfer tables from multiple databases at the same time.
    * `--user`: Username that Data Transfer will use to connect to the database.
    
    * To set a user password to access the DB, use one of the following parameters:
    
       * `--raw-password`: Password as text.
       * `--password-file`: The path to the password file.

- Terraform {#tf}

    * Endpoint type: `mysql_source`.

    * `connection.mdb_cluster_id`: ID of cluster to connect to.
    * `database` — Database name. Leave the field empty if you want to transfer tables from multiple databases at the same time.
    * `user`: Username that Data Transfer will use to connect to the database.
    * `password.raw`: Password in text form.

    Here is an example of the configuration file structure:

    
    ```hcl
    resource "yandex_datatransfer_endpoint" "<endpoint_name_in_Terraform>" {
      name = "<endpoint_name>"
      settings {
        mysql_source {
          security_groups = ["<list_of_security_group_IDs>"]
          connection {
            mdb_cluster_id = "<cluster_ID>"
          }
          database = "<name_of_database_to_migrate>"
          user     = "<username_for_connection>"
          password {
            raw = "<user_password>"
          }
          <additional_endpoint_settings>
        }
      }
    }
    ```


    For more information, see [this Terraform provider guide](../../../../terraform/resources/datatransfer_endpoint.md).

- API {#api}

    * `mdbClusterId`: ID of the cluster you need to connect to.
    * `database` — Database name. Leave the field empty if you want to transfer tables from multiple databases at the same time.
    * `user`: Username that Data Transfer will use to connect to the database.
    * `password.raw`: Database user password (in text form).

{% endlist %}

### Custom installation {#on-premise}

For OnPremise, all fields are filled in manually.

{% list tabs group=instructions %}

- Management console {#console}

    * **Connection type**: Select a database connection option:
    
        * **Self-managed**: Allows you to specify connection settings manually.
    
            Select **Custom installation** as the installation type and configure the following settings:
    
            * **Host**: Enter the IP address or FQDN of the host you want to connect to.
            * **Port**: Set the number of the port Data Transfer will use for the connection.
            * **Database**: Specify the database name in the custom installation.
            * **User**: Specify the username Data Transfer will use to connect to the database.
            * **Password**: Enter the user's password to the database.
            * **CA certificate**: Upload the [certificate](../../../../managed-mysql/operations/connect/index.md#get-ssl-cert) file or add its contents as text if you need to encrypt the data to transfer, e.g., for compliance with the PCI DSS requirements.
              
              {% note warning %}
              
              If no certificate is added, the transfer may [fail with an error](../../../troubleshooting/index.md#failed-to-connect).
              
              {% endnote %}
            * **Subnet ID**: Select or [create](../../../../vpc/operations/subnet-create.md) a subnet in the appropriate [availability zone](../../../../overview/concepts/geo-scope.md). The transfer will use this subnet to access the source.
              
              If this field has a value specified for both endpoints, both subnets must be hosted in the same availability zone.
    
        * **Connection Manager**: Enables using a managed database connection using [Yandex Connection Manager](../../../../metadata-hub/quickstart/connection-manager.md):
    
            * Select the folder where the Connection Manager managed connection was created.
            * Select **Custom installation** as the installation type and configure the following settings:
    
                * **Connection**: Specify the managed connection ID in Connection Manager.
                * **Database**: Specify the database name in the custom installation.
                * **Subnet ID**: Select or [create](../../../../vpc/operations/subnet-create.md) a subnet in the appropriate [availability zone](../../../../overview/concepts/geo-scope.md). The transfer will use this subnet to access the source.
                  
                  If this field has a value specified for both endpoints, both subnets must be hosted in the same availability zone.
    
            {% note warning %}
            
            To use a connection from Connection Manager, the user must have [access permissions](../../../../metadata-hub/operations/connection-access.md) for this connection of `connection-manager.user` or higher.
            
            {% endnote %}
    
    * **Security groups**: Select the cloud network to host the endpoint and security groups for network traffic.
      
      Thus, you will be able to apply the specified security group rules to the VMs and clusters in the selected network without changing the settings of these VMs and clusters. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md).

- CLI {#cli}

    * Endpoint type: `mysql-source`.

    * `--host`: IP address or FQDN of the master host you want to connect to.
    * `--port`: Number of the port that Data Transfer will use for the connection.
    * `--ca-certificate`: CA certificate if the data to transfer must be encrypted to comply with PCI DSS requirements.
      
      {% note warning %}
      
      If no certificate is added, the transfer may [fail with an error](../../../troubleshooting/index.md#failed-to-connect).
      
      {% endnote %}
    * `--subnet-id`: ID of the subnet the host is in. The transfer will use that subnet to access the host.
    * `--database` — Database name. Leave the field empty if you want to transfer tables from multiple databases at the same time.
    * `--user`: Username that Data Transfer will use to connect to the database.
    
    * To set a user password to access the DB, use one of the following parameters:
    
       * `--raw-password`: Password as text.
       * `--password-file`: The path to the password file.

- Terraform {#tf}

    * Endpoint type: `mysql_source`.

    * `on_premise.hosts`: List of IPs or FQDNs of hosts to connect to. Since only single-item lists are supported, specify the master host address.
    * `on_premise.port`: Port number that Data Transfer will use for connections.
    * `on_premise.tls_mode.enabled.ca_certificate`: CA certificate if the data to transfer must be encrypted to comply with PCI DSS requirements.
      
      {% note warning %}
      
      If no certificate is added, the transfer may [fail with an error](../../../troubleshooting/index.md#failed-to-connect).
      
      {% endnote %}
    * `on_premise.subnet_id`: ID of the [subnet](../../../../vpc/concepts/network.md#subnet) the host is in. The transfer will use that subnet to access the host.
    * `database` — Database name. Leave the field empty if you want to transfer tables from multiple databases at the same time.
    * `user`: Username that Data Transfer will use to connect to the database.
    * `password.raw`: Password in text form.

    Here is an example of the configuration file structure:

    
    ```hcl
    resource "yandex_datatransfer_endpoint" "<endpoint_name_in_Terraform>" {
      name = "<endpoint_name>"
      settings {
        mysql_source {
          security_groups = ["<list_of_security_group_IDs>"]
          connection {
            on_premise {
              hosts = ["<list_of_hosts>"]
              port  = <port_for_connection>
            }
          }
          database = "<name_of_database_to_migrate>"
          user     = "<username_for_connection>"
          password {
            raw = "<user_password>"
          }
          <additional_endpoint_settings>
        }
      }
    }
    ```


    For more information, see [this Terraform provider guide](../../../../terraform/resources/datatransfer_endpoint.md).

- API {#api}

    * `onPremise`: Database connection parameters:
        * `hosts` — IP address or FQDN of the master host to connect to.
        * `port`: The number of the port that Data Transfer will use for the connection.
        * `tlsMode`: Parameters for encrypting transmitted data if it is required, e.g., to meet PCI DSS requirements.
            * `disabled`: Disabled.
            * `enabled`: Enabled.
                * `caCertificate`: CA certificate.
    
                  {% note warning %}
                  
                  If no certificate is added, the transfer may [fail with an error](../../../troubleshooting/index.md#failed-to-connect).
                  
                  {% endnote %}
    
        * `subnetId`: ID of the subnet the host is in. The transfer will use that subnet to access the host.
    * `securityGroups`: Network traffic security groups whose rules apply to VMs and clusters without altering their configurations. For more information, see [Networking in Yandex Data Transfer](../../../concepts/network.md).
    * `database` — Database name. Leave the field empty if you want to transfer tables from multiple databases at the same time.
    * `user`: Username that Data Transfer will use to connect to the database.
    * `password.raw`: Database user password (in text form).

{% endlist %}

### Additional settings {#additional-settings}

{% list tabs group=instructions %}

- Management console {#console}

    * **Table filter**:
        * **Included tables**: Only data from the tables listed here will be transferred. This option is specified using regular expressions.

            When you add new tables when editing an endpoint used in **Snapshot and increment** or **Replication** transfers with the **Replicating** status, the data history for these tables will not get uploaded. To add a table with its historical data, use the **List of objects for transfer** field in the [transfer settings](../../transfer.md#update).

        * **Excluded tables**: Data from the listed tables is not transferred. This option is specified using regular expressions.

        Regular expressions for included and excluded tables must meet the ID naming rules in MySQL®. For more information, see [this MySQL® guide](https://dev.mysql.com/doc/refman/8.0/en/identifiers.html). Escaping double quotes is not required.

        {% note warning %}

        If a table you want to include or exclude has the same name as your database, specify that table in `<DB_name>.<table_name>` format for the filter to work properly.

        {% endnote %}

    * **Transfer schema**: Allows you to select the DB schema elements that will be transferred when activating or deactivating a transfer.

    * **Advanced settings**:

        * **Time zone for connecting to the database**: Specify the [IANA Time Zone Database](https://www.iana.org/time-zones) identifier. By default, the server local time zone is used.

        * **Database for auxiliary tables**: Database for dummy tables (`__tm_keeper` and `__tm_gtid_keeper`). By default, this is the source database the data is transferred from.

- CLI {#cli}

    * `--include-table-regex`: List of included tables. Only the data from the tables listed here will be transferred. This option is specified using regular expressions.

        When you add new tables when editing an endpoint used in **Snapshot and increment** or **Replication** transfers with the **Replicating** status, the data history for these tables will not get uploaded. To add a table with its historical data, use the **List of objects for transfer** field in the [transfer settings](../../transfer.md#update).

    * `--exclude-table-regex`: List of excluded tables. Data from the listed tables will not be transferred. This option is specified using regular expressions.

    * `--timezone`: DB time zone specified as an [IANA Time Zone Database](https://www.iana.org/time-zones) ID. Defaults to UTC+0.

    * Schema transfer settings:
        * `--transfer-before-data`: When activating a transfer.
        * `--transfer-after-data`: When deactivating a transfer.

- Terraform {#tf}

    * `include_table_regex`: List of included tables. Only the data from the tables listed here will be transferred. This option is specified using regular expressions.

        When you add new tables when editing an endpoint used in **Snapshot and increment** or **Replication** transfers with the **Replicating** status, the data history for these tables will not get uploaded. To add a table with its historical data, use the **List of objects for transfer** field in the [transfer settings](../../transfer.md#update).

    * `exclude_table_regex`: List of excluded tables. Data from the listed tables will not be transferred. This option is specified using regular expressions.

    * `timezone`: DB time zone specified as an [IANA Time Zone Database](https://www.iana.org/time-zones) ID. Defaults to UTC+0.

    * `object_transfer_settings`: Schema transfer settings:

        * `view`: Views.
        * `routine`: Procedures and functions.
        * `trigger`: Triggers.

        You can specify one of the following values for each entity:

        * `BEFORE_DATA`: Transferring when activating the transfer.
        * `AFTER_DATA`: Transferring when deactivating the transfer.
        * `NEVER`: No tansfer.

- API {#api}

    * `includeTablesRegex`: List of included tables. Only the data from the tables listed here will be transferred. This option is specified using regular expressions.

        When you add new tables when editing an endpoint used in **Snapshot and increment** or **Replication** transfers with the **Replicating** status, the data history for these tables will not get uploaded. To add a table with its historical data, use the **List of objects for transfer** field in the [transfer settings](../../transfer.md#update).

    * `excludeTablesRegex`: List of excluded tables. Data from the listed tables will not be transferred. This option is specified using regular expressions.

    * `timezone`: DB time zone specified as an [IANA Time Zone Database](https://www.iana.org/time-zones) ID. Defaults to UTC+0.

    * `objectTransferSettings`: Settings for transferring a DB schema when activating and deactivating a transfer (the `BEFORE_DATA` and `AFTER_DATA` values, respectively).

{% endlist %}

#### Settings for transferring a DB schema when enabling and disabling a transfer {#schema-migrations-settings}

During a transfer, the database schema is transferred from the source to the target. The transfer is performed in two stages:

1. At the activation stage.

    This step is performed before copying or replicating data to create a schema on the target. At this stage, you can enable the migration of views and stored procedures, stored functions, and triggers.

1. At the deactivation stage.

    This step is performed at the end of the transfer operation when it is deactivated. If the transfer keeps running in replication mode, the final stage of the transfer will be performed only when replication stops. At this stage, you can enable the migration of views and stored procedures, stored functions, and triggers.

    At the final stage, it is assumed that when the transfer is deactivated, there is no writing load on the source. You can ensure this by switching to read-only mode. At this stage, the database schema on the target is brought to a state where it will be consistent with the schema on the source.

### Known limitations {#known-limitations}

If setting up a transfer from a MySQL® cluster to a ClickHouse® cluster, consider these aspects of transferring [date and time data types](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html):

* `TIME` type data is transferred as strings with the source and target time zones ignored.
* When transferring `TIMESTAMP` type data, the time zone set in the MySQL® source settings or [advanced endpoint settings](#additional-settings) is used. For more information, see [this MySQL® guide](https://dev.mysql.com/doc/refman/8.0/en/datetime.html).
* The source endpoint assigns the UTC+0 time zone to data of the `DATETIME` type.

Transfers from MySQL® to a database of a different type do not support fields of the `DECIMAL` type to prevent loss of data accuracy. There is no such limitation for MySQL®-to-MySQL® transfers.

## Configuring the data target {#supported-targets}

Configure one of the supported data targets:

* [PostgreSQL](../target/postgresql.md)
* [MySQL®](../target/mysql.md)
* [ClickHouse®](../target/clickhouse.md)
* [Greenplum®](../target/greenplum.md)
* [Yandex Managed Service for YDB](../target/yandex-database.md)
* [Yandex Object Storage](../target/object-storage.md)
* [Apache Kafka®](../target/kafka.md)
* [YDS](../target/data-streams.md)
* [YTsaurus](yt.md)
* [OpenSearch](../target/opensearch.md)

For a complete list of supported sources and targets in Yandex Data Transfer, see [Available transfers](../../../transfer-matrix.md).

After configuring the data source and target, [create and start the transfer](../../transfer.md#create).

## Operations with the database during transfer {#db-actions}

* For transfers in the **Copying** status, any changes to the data schema (`ALTER`) on the source or target will interrupt the transfer.

* For transfers in the **Replicating** status, you can change the data schema on the source. All `ALTER` operations recorded in the binary log (binlog) on the source will be automatically applied to the target. This process takes some time, so the transfer may slow down.

## Troubleshooting data transfer issues {#troubleshooting}

Known issues when using a MySQL® endpoint:

* [Single transaction log size exceeds 4 GB](#binlog-size).
* [New tables cannot be added](#no-new-tables).
* [Error when transferring from AWS RDS for MySQL®](#aws-binlog-time).
* [Error when transferring tables without primary keys](#primary-keys).
* [Binary log access error](#binlog-bytes).
* [Unable to get a binary log position](#binlog-position)
* [Error when dropping a table under the Drop cleanup policy](#drop-table-error).
* [Time shift in the DATETIME data type when transferring to ClickHouse®](#timeshift).

For the full list of recommendations, see [Troubleshooting](../../../troubleshooting/index.md).

### The size of a single transaction log exceeds 4 GB {#binlog-size}

Error message:

```text
Last binlog file <file_name>:<file_size> is more than 4GB
```

If the log size of a single transaction exceeds 4 GB, activation of _**Replication**_ or _**Snapshot and increment**_ transfers fails due to MySQL® [internal limits](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_max_binlog_cache_size) on the size of a single transaction's log.

**Solution:** [Reactivate](../../transfer.md#activate) the transfer.

### New tables cannot be added {#no-new-tables}

​No new tables are added to _**Snapshot and increment**_ transfers.

**Solution**:

* [Deactivate](../../transfer.md#deactivate) and [reactivate](../../transfer.md#activate) the transfer.
* Create tables in the target database manually.
* [Create](../../transfer.md#create) a separate transfer of the _**Snapshot**_ type and only add to it the newly created tables. Deactivating the original _**Snapshot and increment**_ transfer is not required.

### Error when transferring from AWS RDS for MySQL® {#aws-binlog-time}

_**Snapshot and increment**_ and _**Replication**_ transfers from the [Amazon RDS for MySQL®](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_MySQL.html) source might experience a table loading error.

Error example:

```text
Failed to execute LoadSnapshot:
Cannot load table "name": unable to read rows and push by chunks:
unable to push changes: unable to execute per table push:
error: err: sql: transaction has already been committed or rolled back
rollback err: sql: transaction has already been committed or rolled back
```

This error is caused by the short MySQL® binary log file retention time in Amazon RDS.

**Solution:**

Increase the binary log retention time by using the command below:

```sql
call mysql.rds_set_configuration('binlog retention hours', <number_of_hours>);
```

The maximum retention time is 168 hours (7 days). Default value is `NULL` (binary log files are not saved). For more information, see [this Amazon RDS guide](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_set_configuration.html).

### Error when transfering tables without primary keys {#primary-keys}

Error message:

```text
Primary key check failed: 14 Tables errors: Table no key columns found
```

For _**Replication**_ and _**Snapshot and increment**_ transfers, tables without primary keys are not transferred.

**Solution:** Prepare the source as described in [Preparing for the transfer](../../prepare.md).

### Binary log access error {#binlog-bytes}

The following error may occur during _**Snapshot and increment**_ transfers:

```text
Warn(replication): failed to run (abstract1 source):
failed to run canal: failed to start binlog sync:
failed to get canal event: ERROR 1236 (HY000): Could not find first log file name in binary log index file
```

The error occurs when the binary log files required for replication are not available. This is usually due to exceeding the maximum allowed file size after adding new changes to the binary log. In this case, some of the old log data is deleted.

**Solution:**

[Increase](../../../../managed-mysql/operations/update.md#change-mysql-config) the maximum allowed size for the binary log files in the MySQL® settings using the [Mdb preserve binlog bytes](../../../../managed-mysql/concepts/settings-list.md#setting-mdb-preserve-binlog-bytes) parameter.

The valid values range from `1073741824` (1 GB) to `107374182400` (100 GB). The default value is `1073741824` (1 GB).

### Unable to get a binary log position {#binlog-position}

Error message:

```text
unable to get binlog position: Storage <source_address> is not master
```

You may get this error when activating _**Replication**_ or _**Snapshot and increment**_ transfers if the data source is a custom MySQL® installation and binary log file position based replication is set up incorrectly.

**Solution**:

Run the following checks in MySQL®:

* Make sure the master is used as the replication source.

* Make sure the [log_bin](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#option_mysqld_log-bin) parameter specifices the correct path to the binary log file location.

* Enter the binary log information using the [SHOW MASTER STATUS](https://dev.mysql.com/doc/refman/8.0/en/show-master-status.html) request (for MySQL® 5.7 and 8.0) or the [SHOW BINARY LOG STATUS](https://dev.mysql.com/doc/refman/8.4/en/show-binary-log-status.html) request (for MySQL® 8.4). The request should return a string with the information, not an empty response.

### Error when dropping a table under the Drop cleanup policy {#drop-table-error}

Error message:

```text
ERROR: cannot drop table <table_name> because other objects depend on it (SQLSTATE 2BP01)
```

With the `Drop` cleanup policy, a transfer drops tables in multiple iterations:

1. The transfer tries to drop all tables one by one. Cascade delete is not used, as it may delete tables that are not included in the transfer. If a table cannot be dropped, for example, due to its links to external keys, an error occurs, but the transfer will continue dropping tables.
1. During the next iteration, the transfer will try to drop the remaining tables. If the blocking child tables were dropped in the previous iteration, the table linked to external keys will also be dropped. In this case, the error is fixed while Data Transfer is running and no other actions are required.
1. If the transfer does not drop any table during another iteration, table dropping stops. In which case:

   * The transfer will continue running if all tables were dropped.
   * The transfer will be aborted with an error if there are any tables left.

**Solution:**

* If child tables are not involved in other transfers and their transfer is not in conflict with the transfer goals, add these tables:

   * To the list of included tables in the source endpoint parameters.
   * To the list of objects to be transferred in the transfer parameters.

* Delete the blocking child tables in the target database manually.
* Use the `Truncate` cleanup policy.
* Recreate the target database.

   {% note warning %}

   This will lead to losing all data in the database.

   {% endnote %}

### Time shift in the DATETIME data type when transferring to ClickHouse® {#timeshift}

A time shift occurs because the source endpoint uses the UTC+0 time zone for `DATETIME` data. For more information, see [Known limitations](mysql.md#known-limitations).

**Solution:** Manually adjust the time zone at the target level.

_Greenplum® and Greenplum Database® are registered trademarks or trademarks of Broadcom Inc. in the United States and/or other countries._

_ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._