MySQL
The source connector for MySQL can extract data change events from a MySQL database system in real-time and stream them to the DataCater platform.
The MySQL source connector is available in two variants:
- The Binlog-based connector implements change data capture by extracting change events from the Binary Log of MySQL. It is based on the Debezium MySQL connector. It can extract INSERTs, UPDATEs, and DELETEs.
- The JDBC-based connector runs queries against the MySQL database to extract change events. It can extract INSERTs and UPDATEs.
The Binlog-based connector uses row-based binary logging, which is available since MySQL version 5.1.5.
Setup user
Create a new MySQL user and assign the required permissions (make sure that you allow the created user to sign in from only the IP address of the machine where DataCater is running):
mysql> CREATE USER '[username]'@'[ip_of_datacater_installation]' IDENTIFIED BY '[password]';
mysql> GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '[username]'@'[ip_of_datacater_installation]' IDENTIFIED BY '[password]';
Enable Binary Log
Add the following lines to the config file of your MySQL installation to enable the Binary Log:
server-id = 42
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 10
# define `binlog_row_image` for MySQL 5.6 or higher, leave it out for earlier releases
binlog_row_image = FULL
Setup user
Create a new MySQL user and assign the required permissions (make sure that you allow the created user to sign in from only the IP address of the machine where DataCater is running):
mysql> CREATE USER '[username]'@'[ip_of_datacater_installation]' IDENTIFIED BY '[password]';
mysql> GRANT SELECT, RELOAD, SHOW DATABASES ON *.* TO '[username]'@'[ip_of_datacater_installation]' IDENTIFIED BY '[password]';
This source connector supports the following configuration options:
General > Hostname or IP
The hostname or IP address of the machine where the MySQL database system is running.
General > Port
The port under which MySQL is available (default:
3306).General > SSL
Whether to use SSL when connecting to the MySQL database or not.
General > Username
The username used for authenticating with MySQL.
General > Password
The password of the user used for authenticating with MySQL.
General > Server timezone
Timezone of the MySQL server (default:
UTC).General > Database name
The name of the database that should be used as a source.
General > Table name
The name of the database table that should be used as a source. You may retrieve the list of tables available in the given MySQL database by clicking on Fetch table names.
General > Automatically detect primary key column
Whether to automatically profile the primary key column using the MySQL table
information_schema.key_column_usage or not.General > Primary key column
Only available if the automated detection of the primary key column is disabled. Name of the attribute that uniquely identifies records. DataCater uses the primary key attribute to detect new records. Please make sure that the column does not hold
NULL values.Change Data Capture > Connector variant
You may choose between the Binlog-based and JDBC-based connector variant.
Change Data Capture > Change Data Capture mode
Only available for the JDBC-based connector.
You may choose one of the following modes for change data capture:
BULK: Fetch all data at each sync.INCREMENTING: Use the primary key column to fetch data that have been inserted since the last sync. This mode does only extract INSERTs, but skips UPDATEs and DELETEs.TIMESTAMP/INCREMENTING: Use the primary key column and the timestamp column, specified using the Timestamp column configuration options, to fetch data that have been inserted or updated since the last sync. This mode does only extract INSERTs and UPDATEs, but skips DELETEs.
Change Data Capture > Timestamp column
Only available for the JDBC-based connector.
DataCater can use a timestamp column, which stores the time of the most recent update of a record, to detect record updates. Specifying the timestamp column is required when using
TIMESTAMP/INCREMENTING as Change Data Capture mode.Change Data Capture > Sync interval
Only available for the JDBC-based connector.
The interval in seconds between synchronizations of the MySQL table and DataCater (default:
60).Last modified 8mo ago