Prerequisites
Before you can land or replicate data, make sure to complete the following prerequisites:
-
Install the required driver on the Data Movement gateway server
Information noteThis is only required if you opted to work with Data Movement gateway. For information on the benefits of Data Movement gateway and when it is required, see Qlik Data Gateway - Data Movement.
Note that Data Movement gateway is not available with the Qlik Talend Cloud Starter subscription.
-
Grant the required permissions on the data source
-
Configure the data source connector
Supported editions
The following MySQL editions are supported:
- MySQL Community Edition
- MySQL Standard Edition
- MySQL Enterprise Edition
- MySQL Cluster Carrier Grade Edition
Driver setup
You can install the driver using the driver installation utility (recommended) or manually. Manual installation should only be attempted in the unlikely event that you encounter an issue with the driver installation utility.
Using the driver installation utility to install the driver
This section describes how to install the required driver. The process involves running a script that will automatically download, install and configure the required driver. You can also run scripts to update and uninstall the driver as needed.
Preparing the installation
-
Make sure that Python 3.6 or later is installed on the Data Movement gateway server.
Python comes preinstalled on most Linux distributions. You can check which Python version is installed on your system, by running the following command:
python3 --version
Installing the driver
To download and install the driver:
-
On the Data Movement gateway machine, change the working directory to:
opt/qlik/gateway/movement/drivers/bin
-
Run the following command:
Syntax:
./install mysql
If the driver cannot be downloaded (due to access restrictions or technical issues), a message will be displayed instructing you where to download the driver and where to copy it on the Data Movement gateway machine. Once you have done that, run the install mysql command again.
Otherwise, the EULA for the driver will be displayed.
-
Do one of the following:
- Press [Enter] repeatedly to slowly scroll through the EULA.
- Press the Spacebar repeatedly to quickly scroll through the EULA.
- Press q to quit the license text and be presented with the EULA acceptance options.
-
Do one of the following:
- Type "y" and press [Enter] to accept the EULA and begin the installation.
- Type "n" and press [Enter] to reject the EULA and exit the installation.
-
Type "v" and press [Enter] to view the EULA again.
The driver will be installed.
Updating the driver
Run the update command if you want to uninstall previous versions of the driver before installing the provided driver.
To download and update the driver:
-
On the Data Movement gateway machine, change the working directory to:
opt/qlik/gateway/movement/drivers/bin
-
Run the following command:
Syntax:
./update mysql
If the driver cannot be downloaded (due to access restrictions or technical issues), a message will displayed instructing you where to download the driver and where to copy it on the Data Movement gateway machine. Once you have done that, run the update mysql command again.
Otherwise, the EULA for the driver will be displayed.
-
Do one of the following:
- Press [Enter] repeatedly to slowly scroll through the EULA .
- Press the Spacebar repeatedly to quickly scroll through the EULA.
- Press q to quit the license text and be presented with the EULA acceptance options.
-
Do one of the following:
- Type "y" and press [Enter] to accept the EULA and begin the installation.
- Type "n" and press [Enter] to reject the EULA and exit the installation.
- Type "v" and press [Enter] to review the EULA from the beginning.
The old driver will be uninstalled and the new driver will be installed.
Uninstalling the driver
Run the uninstall command if you want to uninstall the driver.
To uninstall the driver:
-
Stop all tasks configured to use this connector.
-
On the Data Movement gateway machine, change the working directory to:
opt/qlik/gateway/movement/drivers/bin
-
Run the following command:
Syntax:
./uninstall mysql
The driver will be uninstalled.
Installing the driver manually
You should only attempt to install the driver manually if the automated driver installation did not complete successfully.
After Data Movement gateway is installed, download the mysql-connector-odbc-<version>.x86_64.rpm driver. You can find a direct download link to the supported version under binary-artifacts in /opt/qlik/gateway/movement/drivers/manifests/mysql.yaml. Once the download completes, copy the RPM to the Data Movement gateway machine.
On the Data Movement gateway server, open a shell prompt and do the following:
-
Stop the Data Movement gateway service:
sudo systemctl stop repagent
-
Optionally, confirm that the service has stopped:
sudo systemctl status repagent
The status should be as follows:
Active: inactive (dead) since <timestamp> ago
- Install the driver on the Data Movement gateway machine.
-
Change the working directory to <Data Movement gateway-Install-Dir>/bin.
-
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.
-
Optionally, confirm that the driver location was copied:
cat site_arep_login.sh
-
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
-
Start the Data Movement gateway service:
sudo systemctl start repagent
-
Optionally confirm that the service has started:
sudo systemctl status repagent
The status should be as follows:
Active: active (running) since <timestamp> ago
Port
By default, port 3306 needs to be opened for outbound communication on the Data Movement gateway server. You can change this in the connector settings.
General prerequisites
Make sure the following prerequisites have been met:
- A MySQL account with the Required permissions.
- MySQL 8: Standard authentication for capturing changes
-
Certified MySQL versions support the fractional value for temporal data types (introduced in MySQL 5.6). Consequently, moving 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.
Capturing changes from a customer-installed MySQL database
The following section describes the requirements for capturing changes from an on-premises MySQL database.
Enable binary logging
To enable binary logging (required for CDC moving), the following parameters must be configured in MySQL’s my.ini (Windows) or my.cnf (UNIX) files.
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 Move 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.
When using Percona as a source, there is no need to perform the procedures described in this section.
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. |
Capturing changes from Google Cloud for MySQL
Binary logging
Binary logging is required for capturing changes. To check that binary logging is enabled (the default), make sure that the database's binlog_row_image
parameter is set to full
.
Replication
Replication enables data from the primary database server to be copied to one or more secondary servers.
Data can be landed from either a primary server or a secondary server.
To capture changes from a secondary server, the binary logging parameter log_slave_updates
needs to be set to true
(1
).
Further reading: https://cloud.google.com/sql/docs/mysql/replication
Capturing changes from Amazon RDS for MySQL and Amazon Aurora for MySQL
Binary logging
To set up Amazon RDS for MySQL or Amazon Aurora for MySQL for CDC:
-
Follow the instructions in the AWS Help for creating a new Parameter Group (see the Binary Logging Format section).
-
When creating the new Parameter Group, set:
binlog_format=row
- Save the new Parameter Group.
- If you have an existing instance of Amazon RDS for MySQL, edit the instance to use the parameter specified in Step 2 above. If you are provisioning a new instance of Amazon RDS for MySQL, reference the new Parameter Group created in Step 1 above.
- Finally, make sure that automated backup is enabled (the default).
Further reading: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.MySQL.BinaryFormat.html
Replication
Replication enables data from the primary database server to be copied to one or more secondary servers.
Data can be landed from either a primary server or a secondary server.
To capture changes from a secondary server, the binary logging parameter log_slave_updates
needs to be set to true
(1
).
Further reading: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_MySQL.Replication.html
Capturing changes from Microsoft Azure database for MySQL
Binary logging is required for capturing changes. To check that binary logging is enabled (the default), make sure that the database's binlog_row_image
parameter is set to full
.