Static MariaDB CDC with Gluesync: Setup

Source data from MariaDB

Prerequisites

To have Gluesync working on your MariaDB instance you will need to have:

  • Valid user credentials with permission to read from source tables;

  • Valid user credentials with permission to create tables (and write) within the GLUESYNC schema (used as a temporary staging area, schema name is configurable).

Setup via Web UI

  • Hostname / IP Address: DNS record or IP Address of your server;

  • Port: Optional, defaults to 3306;

  • Database name: Name of your target database;

  • Username: Username with read & write access to the target tables;

  • Password: Password belonging to the given username;

  • Max connections count: Maximum number of connections the pool can instantiate;

  • Enable TLS: Optional, defaults to false;

  • Certificate path: Optional, path to the certificate file.

Setup under AWS RDS / DBaaS

AWS RDS, like any other DBaaS, allows database connections only via SSL. Flag Use SSL to true during the agent’s setup phase.

Setup via Rest APIs

Here following an example of calling the Core Hub’s Rest API via curl to setup the connection for this Agent.

Connect the agent

curl --location --request PUT 'http://core-hub-ip-address:1717/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
        "hostCredentials": {
        "connectionName": "myAgentNickName",
        "host": "host-address",
        "port": 3306,
        "databaseName": "db_name",
        "username": "",
        "password": "",
        "maxConnectionsCount": 100,
        "enableTls": true,
        "certificatePath": "/myPath/cert.pem"
      }
}'

Setting up for CDC

Follow the below steps to set up Bin logs redaction on your MariaDB instance to let Gluesync capture its transaction logs.

MariaDB Bin log

Edit the MariaDB config file, usually located at /etc/mysql/mariadb.conf.d/50-server.cnf then place or edit the following parameter:

[mariadb]
server_id=300
log_bin=mariadb-bin
binlog_format=ROW

#### gtid mode ####
# note: this is not available on all versions of MariaDB, check your instance's documentation
binlog_gtid_index=OFF
#### gtid mode ####

binlog_cache_size=1M
binlog_stmt_cache_size=1M
max_binlog_size=128M
sync_binlog=0
expire_logs_days=5

[client]
default-character-set=utf8

Once done create a new user that will be then given to Gluesync to connect and grab the transaction logs. In the example below we’re going to create a user called gluesync:

CREATE USER 'gluesync'@'host' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'gluesync'@'%' WITH GRANT OPTION;

Then, don’t forget to restart your MariaDB instance by issuing (command may vary depending on your OS):

sudo service restart mariadb

Working with Before & After images

Before & after images are a feature that allows Gluesync to track the changes that have occurred in your database and comparing them with their previous values. This enables Gluesync to apply only the changes that have occurred, saving bandwidth and processing time.

This feature is automatically enabled and available for use in this agent.

Troubleshooting

Here is the list of common errors.

ALLOW_INVALID_DATES flag

This flag is used to allow the storage of invalid dates within MariaDB. Values stored in this way can be converted to NULL or rounded to the nearest closest value which is 0001-01-01 by playing with the zeroDateTimeBehavior parameter that can be set at connection string level.

Supported values are:

  • EXCEPTION (the MariaDB JDBC’s driver default), which throws an SQLException with an SQLState of S1009.

  • CONVERT_TO_NULL (the Gluesync’s default), which returns NULL instead of the date.

  • ROUND (the Gluesync’s default), which rounds the date to the nearest closest value which is 0001-01-01.

For any further information, please refer to the MySQL documentation.