MySQL

DataCater uses a JDBC-based connector for streaming data change events from data pipelines to MySQL.

When assigning a MySQL sink to a data pipeline, the user must map the schema of the pipeline to the schema of the MySQL index.

Requirements

Please make sure that the MySQL user has been granted the following permissions for the used database and table: SELECT, INSERT, UPDATE, and DELETE.

You may grant these permissions to an existing user with the following SQL query: 

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON [database_name].[table_name] TO '[username]'@'[ip_of_datacater_installation]';

Configuration

This sink connector supports the following configuration options:

Hostname or IP

The hostname or IP address of the machine where MySQL is running.

Port

The port under which MySQL is available (default: 3306).

SSL

Whether to use SSL when connecting to the MySQL database or not.

Username

The username used for authenticating with MySQL.

Password

The password of the user used for authenticating with MySQL.

Server timezone

Timezone of the MySQL server (default: UTC).

Database name

The name of the database that should be used as sink.

Table name

The name of the database table that should be used as sink. You may retrieve the list of tables available in the given MySQL database by clicking on Fetch table names.

Insert mode

INSERT: Only perform INSERT statements, which resembles an append-only mode.

UPSERT: Perform INSERT, UPDATE, and DELETE statements.

Data Types

When retrieving the schema of the MySQL table, DataCater performs the following mapping between MySQL's data types and DataCater's data types:

MySQL data type DataCater data type
BIGINT Long
BINARY String
BIT Boolean
BLOB String
BOOL Boolean
BOOLEAN Boolean
CHAR String
DATE Date
DATETIME Timestamp
DECIMAL Double
DEC Double
DOUBLE Double
DOUBLE PRECISION Double
ENUM String
FIXED Double
FLOAT Float
GEOMETRY String
INT Int
INTEGER Int
JSON String
LINESTRING String
LONGBLOB String
LONGTEXT String
MEDIUMBLOB String
MEDIUMINT Int
MEDIUMTEXT String
NUMERIC Double
POINT String
POLYGON String
REAL Double
SET String
SMALLINT Int
TEXT String
TIME Time
TIMESTAMP Timestamp
TINYBLOB String
TINYINT Int
TINYTEXT String
VARBINARY String
VARCHAR String
YEAR Int
Jump to