Transferring data to a MySQL® target 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 that the MySQL® major version on the target is not lower than that on the source.

3. Make sure the target uses the MyISAM or InnoDB low-level storage subsystem.

4. Set an SQL Mode matching the source.

5. Create a user to connect to the target and grant them the required privileges:

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

• Endpoint type: mysql-target.

• --cluster-id: ID of the cluster you need to connect to.

• --database — Database name. Leave the field empty if you want to create tables in databases with the same names as on the source. In this case, explicitly specify a DB schema for service tables under additional settings.

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

• connection.mdb_cluster_id: ID of cluster to connect to.

• database — Database name. Leave the field empty if you want to create tables in databases with the same names as on the source. In this case, explicitly specify a DB schema for service tables under additional settings.

• 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_target {
      security_groups = ["<list_of_security_group_IDs>"]
      connection {
        mdb_cluster_id = "<cluster_ID>"
      }
      database = "<migrated_database_name>"
      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 create tables in databases with the same names as on the source. In this case, explicitly specify a DB schema for service tables under additional settings.

• user: Username that Data Transfer will use to connect to the database.

• password.raw: Database user password (in text form).

• Endpoint type: mysql-target.

• --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 create tables in databases with the same names as on the source. In this case, explicitly specify a DB schema for service tables under additional settings.

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

• security_groups: Security groups for network traffic.

  Security group rules apply to a transfer. They allow opening up network access from the transfer VM to the VM with the database. For more information, see Networking in Yandex Data Transfer.

  Security groups must belong to the same network as the subnet_id subnet, if the latter is specified.

  Note

  In Terraform, it is not required to specify a network for security groups.

• 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 create tables in databases with the same names as on the source. In this case, explicitly specify a DB schema for service tables under additional settings.

• 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_target {
      security_groups = ["<list_of_security_group_IDs>"]
      connection {
        on_premise {
          hosts = ["<list_of_hosts>"]
          port  = <port_for_connection>
        }
      }
      database = "<migrated_database_name>"
      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 create tables in databases with the same names as on the source. In this case, explicitly specify a DB schema for service tables under additional settings.

• user: Username that Data Transfer will use to connect to the database.

• password.raw: Database user password (in text form).

• sql_mode: Specify the settings that override the MySQL® standard behavior.

• --skip-constraint-checks: Used to speed up replication: the FOREIGN_KEY_CHECKS and UNIQUE_CHECKS settings are set to 0 (no checks are performed).

  Warning

  Disabling constraint checks will speed up replication, but may affect data integrity when using cascading operations.

• --timezone: Specify the IANA Time Zone Database identifier. Defaults to UTC+0.

• sql_mode: Specify settings to override standard MySQL® behavior. The following list is used by default: NO_AUTO_VALUE_ON_ZERO,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION.

• skip_constraint_checks: Used to speed up replication: the FOREIGN_KEY_CHECKS and the UNIQUE_CHECKS values are set to 0 (no checks are performed).

  Warning

  Disabling constraint checks will speed up replication, but may affect data integrity when using cascading operations.

• timezone: Specify the IANA Time Zone Database identifier. Defaults to UTC+0.

For more information, see the Terraform provider documentation.

• sqlMode: Specify settings to override standard MySQL® behavior.

• skipConstraintChecks: Used to speed up replication: the FOREIGN_KEY_CHECKS and UNIQUE_CHECKS settings are set to 0 (no checks are performed).

  Warning

  Disabling constraint checks will speed up replication, but may affect data integrity when using cascading operations.

• timezone: Specify the IANA Time Zone Database identifier. Defaults to UTC+0.