MySQL

Replicate into MySQL tables, including what SQL Flow creates, the privileges its user needs, and its encryption setting.

A MySQL target keeps a replica of the captured tables in a MySQL database: the initial load copies the rows, and every captured change is applied by primary key, in source order. MariaDB is supported too: choose MySQL in the wizard.

Requirements

Encryption works as it does for a MySQL source. At Require (default), a server without TLS, or with MySQL's self-signed certificate, fails Test connection with TLS handshake failed. Trust a private certificate authority on the agent host, or name it with the connection option sslRootCertificate. Prefer encrypts when the server offers TLS and doesn't check the certificate.

Permissions

SQL Flow doesn't check the user's privileges up front. A missing privilege appears as MySQL's own error when the statement that needs it runs.

The user needs, on the target database:

• SELECT, INSERT, UPDATE and DELETE, to apply changes.
• CREATE and ALTER, to create the tables that hold the committed position, and when SQL Flow creates the tables, adds a missing key or applies a schema change.
• DROP, because MySQL requires it for TRUNCATE TABLE, which a reload and a captured TRUNCATE use.

Replace <agent_host>, <password> and <database_name>.

CREATE USER 'sqlflow_apply'@'<agent_host>' IDENTIFIED BY '<password>' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP ON <database_name>.* TO 'sqlflow_apply'@'<agent_host>';

Tables from a source schema other than the default land in a MySQL database of that schema's name, so grant the same privileges on those databases, and CREATE at the server level if SQL Flow is to create them.

What SQL Flow creates on the target

Besides the replicated tables, SQL Flow keeps the pipeline's committed position on the target, so another agent can take over and resume from what the target holds. See High availability for agents.

SQL Flow creates target tables only when you ask it to: with Prepare target schema on the wizard's Prepare step, or the same action when you add a target or change tables. Show SQL lists exactly the statements that will run, and Copy SQL copies them for a DBA.

Each missing table is created with a primary key on the source table's key columns; for a table from a non-default source schema, the database of that name is created first if needed. An existing table without a matching key gets the primary key, or, if it already has a different primary key, a unique index named ux_<table>_<columns>.

Tables from the source's default schema land in the database you entered in Database. Names are otherwise unchanged; see Naming conventions per target engine.

The tables SQL Flow creates use a general type mapping, chosen from each column's value type rather than its exact source type:

A decimal with more than 10 digits after the point, a decimal with more than 28 digits before it, or a time zone offset, doesn't survive this mapping. If you need exact types, create the tables yourself before the pipeline starts.

Options

Apply stops if one source transaction exceeds either transaction limit, which keeps the transaction intact for recovery rather than splitting it.

MySQL targets apply every change in source order, so the wizard shows no Collapse row versions (faster) box for them.

Limitations

• Every target table needs a primary key or a unique constraint on the source key. Otherwise apply stops with Tracked table '<table>' (pipeline '<id>', target engine 'mysql') has no primary key or unique key resolved on the target, and no key columns were configured for it.
• The tables SQL Flow creates use the general type mapping above.
• MySQL allows identifiers of up to 64 characters. SQL Flow doesn't shorten longer source names for MySQL.

See also

• MySQL source
• Prepare
• Data type mapping
• Initial load