MySQL connector

You can use this connector both for source and target endpoints.

Source endpoint

  1. Under Connection settings → Connection type, specify the connection properties:

    • The Host IP address or FQDN to connect to.

    • The Port for connection (3306 by default).

    • CA certificate. Click Upload file to upload a certificate file (public key) in PEM format.

  2. Specify the database attributes:

    • Database name associated with the username and password below.

    • User for connection to Transfer.

    • Password for the database user.

  3. Configure the Table filter if you need to transfer specific tables. If you want to transfer all the available tables, skip this section.

    • Included Tables. Transfer will transfer only the data from these tables. Specify the table names together with the database in the database_name.target_table format.

    • Excluded tables. The data from the tables on this list won't be transferred. Specify the table names after the name of the database containing these tables as follows: database_name.excluded_table.

    Tip

    To parse multiple tables in the sections above, use regular expressions as conditions to parse the tables available at the source.

    Collection of regular expression patterns to parse table names
    Pattern Description Example
    abc An explicit series of characters test returns the table names containing test.
    . A character wildcard. Use it to match an expression with defined character positions. t..t returns test, tent, tart etc.
    \ An escape character. Use it to match special characters. \_ returns the table names containing an underscore.
    ? Use it to express that a character (or a group of characters) is optional. c?.n returns can, con, in, on, en, etc.
    + Use it to express that a character (or a group of characters) can appear one and more times. -+ returns the table names containing -, --, --- etc.
    {n} Use it to express that a character (or a group of characters) must appear explicitly n times -{2} returns the table names containing --.
    {n,m} Use it to express that a character (or a group of characters) must appear between n and m times. _{1,3} returns the table names containing _, __ and ___.
    \w An alphanumeric wildcard. Use it to match any alphanumeric characters. The match pattern is case-sensitive. \w+ returns the table names containing letters and/or digits.
    \W A non-alphanumeric wildcard. Use it to match any non-alphanumeric character. The match pattern is case-sensitive. \W+ returns the table names containing characters other than letters or digits.
    \d A digit wildcard. Use it to match any digit characters. The match pattern is case-sensitive. \d+ returns the table names containing digits.
    \D A non-digit wildcard. Use it to match any non-digit characters. The match pattern is case-sensitive. \D+ returns the table names containing any characters other than digits.
    $ Use it to match the position after the table name's last character. metrics$ returns the table names ending with metrics.
    ^ Use it to match the position before the table name's first character. This position is useful to define database names. For example, ^monthly_sales returns all the tables from the monthly_sales database.

    You can add fields to the above sections to function as multiple parsers or filters.

  4. Configure the Transfer schema:

    This section allows you explicitly select the database schema items to migrate and set the specific transfer stage at which to migrate them.

    Warning

    In most cases, the default schema migration settings let you perform a successful transfer. Change the settings for the initial and final stages of the transfer only if necessary.

    The transfer is performed in two stages:

    • Activation stage

      This stage executes on activation of the transfer, before snapshot or replication to create schema on the target.

      You can select parts of the schema to be transferred at this stage.

      By default, the Tables are transferred at start.

    • Deactivation stage

      This stage executes at the end of the transfer upon its deactivation.

      If the transfer is constantly working in replication mode (Replication and Snapshot and replication transfer types), the final stage of the transfer will be performed only after the replication stops. You can choose which parts of the schema to migrate.

      At this stage, it's assumed that when the transfer is deactivated, there is no writing activity on the source. For complete reliability, the source is set to read-only mode. The database schema on the target is brought to a state where it will be consistent with the schema on the source.

    Note

    When the transfer is restarted during the replication phase, the table schemas on the target are preserved. In this case, the service will transfer only the table schemas unavailable at the target after the restart.

  5. Under Advanced settings:

    • Database timezone. Specified as IANA Time Zone Database identifier. The default timezone is Local, it corresponds to the MySQL server timezone.

To create a MySQL source endpoint with API, use the endpoint.MysqlSource model.

Target endpoint

  1. Under Connection settings → Connection type, specify the connection properties:

    • IP or FQDN of the host to connect to.

    • The Port for connection (3306 by default).

    • CA certificate. Click Choose a file to upload a certificate file (public key) in PEM format or provide it as text.

    1. Specify the database attributes:

      • Database name associated with the username and password below.

      • Username for connecting to Transfer.

      • Password for the database user.

  2. Select a Cleanup policy. This policy allows you to select a way to clean up data in the target database when you activate, reactivate or reload the transfer:

    • Don't cleanup: Do not clean. Select this option if you only perform replication without copying data.

    • Drop: Fully delete the tables included in the transfer (default). Use this option to always transfer the latest version of the table schema to the target database from the source.

    • Truncate: Execute the TRUNCATE command for a target table each time you run a transfer.

  3. Specify Advanced settings:

    • Database timezone. Specified as IANA Time Zone Database identifier. You can also set the special Local timezone as a string. This timezone corresponds to the MySQL server timezone. The default timezone is Local.

    • SQL modes enabled on the target server . The delimiter for multiple modes is the comma (,).

      The default modes are the following:

      NO_AUTO_VALUE_ON_ZERO,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION

    • Check Disable constraints checks if you don't need these checks and want to speed up the replication.

    • The Database schema for service tables specifies the database into which to place the tables with the service information.

To create a MySQL target endpoint with API, use the endpoint.MsqlTarget model.

See also

Previous
PostgreSQL
Next
Amazon Redshift