Skip to main content Skip to complementary content
Qlik Resources

Prerequisites

The following section describes the prerequisites for working with the Qlik Replicate MySQL endpoint.

Supported versions and editions

For information on supported MySQL versions, see the ReplicateSupport Matrix.

The following MySQL editions are supported:

  • MySQL Community Edition
  • MySQL Standard Edition
  • MySQL Enterprise Edition
  • MySQL Cluster Carrier Grade Edition

Driver prerequisites

Replicate on Windows

Install MySQL ODBC Unicode Driver 64-bit version 8.0.32 on the Replicate machine.

Replicate on Linux

  1. Stop the Replicate service and optionally confirm that it has stopped as described in Replicate server procedures.

  2. Install MySQL ODBC Unicode Driver 64-bit version 8.0.32 on the Replicate machine.

  3. Change the working directory to <Replicate-Install-Dir>/bin.

  4. Copy the driver location to the site_arep_login.sh file:

    echo "export LD_LIBRARY_PATH=\$LD_LIBRARY_PATH:/usr/lib64" >> site_arep_login.sh

    This will add the driver to "LD_LIBRARY_PATH" and update the driver location in the site_arep_login.sh file.

  5. Optionally, confirm that the driver location was copied:

    cat site_arep_login.sh

  6. Make sure that the /etc/odbcinst.ini file contains an entry for MySQL, as in the following example:

    [MySQL ODBC 8.0 Unicode Driver]

    Driver = /usr/lib64/libmyodbc8w.so

    UsageCount = 1

  7. Start the Replicate service and optionally confirm that it has started as described in Replicate server procedures.

General prerequisites

Make sure the following prerequisites have been met:

  • A MySQL account with the Required permissions.
  • To be able to capture changes, the user's database account should be set up to use either SHA-256 pluggable authentication (sha256_password) or caching SHA-2 pluggable authentication (caching_sha2_password).

  • Certified MySQL versions support the fractional value for temporal data types (introduced in MySQL 5.6). Consequently, replication of the old temporal data type format is no longer supported.

    Information noteNote that upgrading to MySQL versions that support the fractional value for temporal data types does not upgrade the temporal data type format automatically. This needs to be done separately. This also applies when upgrading from MariaDB 10.1. For instructions on upgrading the temporal data type format, see the vendor's online help.

Enable binary logging

To enable binary logging (required for CDC replication), the following parameters must be configured in MySQL’s my.ini (Windows) or my.cnf (UNIX) files.

Binary logging parameters
Parameter Value

server_id

Any value from 1.

Example:

server_id=1

log-bin=<path>

Path to the binary log file (without an extension).

Example:

log-bin=E:\MySql_Logs\BinLog

binlog_format

Must be:

binlog_format=row

expire_logs_days

To prevent disk space issues, it is strongly recommended not to use the default value (0).

Example:

expire_logs_days=5

binlog_row_image

Must be:

binlog_row_image=full

Cluster prerequisites

To be able to replicate clustered (NDB) tables (by connecting to any of the cluster nodes), the following parameters must be configured in MySQL’s my.ini (Windows) or my.cnf (UNIX) files.

Information note

  • Replication from Galera Cluster for MySQL/MariaDB is also supported. For more information, see Galera Cluster Considerations below.

  • When using Percona as a source, there is no need to perform the procedures described in this section.
Cluster parameters
Parameter Value

ndb_log_bin

Must be:

ndb_log_bin=on

Ensures that changes in clustered tables will be logged to the binary log.

ndb_log_update_as_write

Must be:

ndb_log_update_as_write=OFF

Prevents UPDATEs being written as INSERTs in the binary log.

ndb_log_updated_only

Must be:

ndb_log_updated_only=OFF

Ensures that the binary log will contain the entire row and not just the changed columns.

Galera cluster considerations

You can replicate data from any active MySQL instance. In the event that an instance to which Replicate is currently connected fails, Replicate will attempt to reconnect several times. If it is unable to reconnect, the task will fail. In this case, you will need to manually change the server instance (by editing the endpoint settings) and then start the task from the timestamp of the first failure. If the exact point of failure is unknown, it can be retrieved from the log. Make sure to specify the timestamp of the first failure (i.e. before Replicate started to try to reconnect) or even a few minutes earlier.

Replicating 4-byte UTF8 emojis

Replication of 4-byte UTF8 emojis to certain targets requires the following preparation:

  • Microsoft SQL Server Target: Transform the emojis from WSTRING(n) to WSTRING(n*2).
  • Amazon Redshift Target: Transform the emojis from WSTRING(n) to WSTRING(n*2).

For information on defining transformations, see Using the Transform tab.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here