Transferring data from a MySQL® source endpoint

1. Make sure the settings for the network hosting the cluster allow public connections from IP addresses used by Data Transfer.

2. Make sure the source uses the MyISAM or InnoDB low-level storage subsystem. If you use other subsystems, the transfer may fail.

3. Enable full binary logging on the source by setting the binlog_row_image parameter to FULL or NOBLOB.

4. Specify row format for the binary log on the source by setting the binlog_format parameter to ROW.

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

6. (Optional) Set a limit on the size of data chunks to send using the max_allowed_packet parameter.

7. Create a user to connect to the source and grant them the required privileges:

   CREATE USER '<username>'@'%' IDENTIFIED BY '<password>';
   GRANT ALL PRIVILEGES ON <database_name>.* TO '<username>'@'%';
   GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO '<username>'@'%';
   

8. For Replication and Snapshot and increment transfers, tables without primary keys are not transferred. To make sure the transfer is running properly when migrating a database with such tables:

   • Do not transfer tables without primary keys. For this purpose, add them to the list of excluded tables in source endpoint settings.

   • Create PRIMARY KEYS in those tables to be migrated where there are no keys.

     1. To get a list of tables without a primary key, run the query:

        SELECT
            tab.table_schema AS database_name,
            tab.table_name AS table_name,
            tab.table_rows AS table_rows
        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
                    AND tco.constraint_type = 'PRIMARY KEY')
        WHERE
            tab.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
            AND tco.constraint_type IS NULL
            AND tab.table_type = 'BASE TABLE';
        

     2. Study the structure of tables without a primary key that need to be transferred to the target:

        SHOW CREATE TABLE <database_name>.<table_name>;
        

     3. Add a simple or complex primary key to the tables that need to be transferred to the target:

        ALTER TABLE <table_name> ADD PRIMARY KEY (<column_or_column_group>);
        

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

        ALTER TABLE <table_name> ADD id BIGINT PRIMARY KEY AUTO_INCREMENT;
        

   Note

   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 parameter value in the DBMS settings.

9. 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 additional endpoint settings for the MySQL® source.

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

• 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:

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 the Terraform provider documentation.

• 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).

• 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 — If the transmitted data needs to be encrypted, for example, to meet the requirements of PCI DSS.

• --subnet-id: ID of the subnet the host resides in.

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

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

• on_premise.subnet_id: ID of the subnet where the host is located.

• 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:

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 the Terraform provider documentation.

• 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, for example, to meet PCI DSS requirements.
    • disabled: Disabled.
    • enabled: Enabled.
      • caCertificate: CA certificate.

  • subnetId: ID of the subnet the host resides in.

• 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).

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

• --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 ID. Defaults to UTC+0.

• Schema transfer settings:

  • --transfer-before-data: When activating a transfer.
  • --transfer-after-data: When deactivating a transfer.

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

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

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

• 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 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).