MySQL source Connector, version v1.9.6.Final

MySQL

If you already have a publicly accessible deployment of MySQL, you can already Configure and install a connector using the Axual Self-Service UI. Use the configuration instructions below.
If you don’t have one available, follow the next section to deploy a publicly available instance of MySQL.

Deploying a MySQL instance

We’ll deploy a MySQL instance using Google Cloud Services.
If you have a Google account, you can sign up for a free trial of Google cloud.

You can read more about MySQL on Google cloud here.

Let’s get started.

Create a new MySQL instance.
- instance ID: my-axual-mysql-connector-test
  The instance ID is irrelevant for our example: It will only be displayed in your Google overview.
- Password: You can set the password for the root user of the database. We’ll use this password as the database.password property when configuring the Connector-Application later.
  You can reset this password later.
- Database: Select "MySQL 8.0" (likely the default option).
- Choose a configuration to start with: Development
- Region: europe-west1
  The region isn’t very irrelevant for our example, but usually you would select a region geographically closest to the Connect-Cluster.
- Zonal availability: Single zone
- Customize your instance: Click "Show configuration options"
  - Machine type: Click the dropdown menu and select Lightweight. 1vCPU is enough.
  - Storage: go for the least amount of storage.
    
    If available, Use HDDs.
    
    If given the option, do not enable automatic storage increases.
  - Connections: Leave only "Public IP" selected.
    
    Authorized networks. Click "Add network". Use any name and 0.0.0.0/0 as the CIDR notation (and click "Done").
    This will open the database to the entire internet. That’s ok, we’ll delete it shortly anyway.
  - Authentication: Skip this config if it’s present
  - Data protection:
    
    Enable backups. Under "Advanced options", select (single) Region (europe-west1)
    
    If available, enable point-in-time recovery
    
    Disable deletion protection
  - Maintenance: Skip this config
  - Flags and parameters: Skip this config
  - Labels: Skip this config
- Click "Create instance".
While the database server is getting deployed, let’s create a bucket.
- Name your bucket: mysql-init-axual-connect.
  Skip labels.
  Click "Continue".
  The bucket name is irrelevant for our example: It will only be displayed in your Google overview.
- Choose where to store your data:
  - Region: europe-west1
    The region isn’t very irrelevant for our example, but usually you would select a region geographically closest to the Connect-Cluster.
- Choose a default storage class for your data: Standard.
  Click "Continue".
- Choose how to control access to objects: Uniform.
  Click "Continue".
- Choose how to protect object data: None
  - Data encryption: Skip this config
Click "Create" to crete the bucket.
If you get a pop-up with "Public access will be prevented", click "Confirm".
The bucket page will open. Click "Upload files"

Save the following text to a file on your system, and then click "Upload files" to upload it into the bucket:

USE mysql_database_name;

CREATE TABLE HOTEL
(
  hotel_id    INT           NOT NULL AUTO_INCREMENT,
  hotel_name  VARCHAR(100)  NULL,
  PRIMARY KEY (hotel_id)
);

INSERT INTO `HOTEL` VALUES
(1, 'iris'),
(2, 'altair'),
(3, 'turbulence');

You can close the buckets page. Let’s go back to our SQL instance.
Select your instance to open it. Note down the public IP address.
We’ll use this as the database.hostname property when configuring the Connector-Application later.
[Optional] Change the root user’s password:
- On the left-side menu, click "Users".
- Click the dots next to the root user.
- Click "Change password".
- Type a new password and click "OK".
On the left-side menu, click "Databases".
- Click "Create Database".
  Use mysql_database_name as the name, as this is the name used in this example instance-setup (we referenced it in the SQL snippet above, and we’ll do it again in the connector configuration).
  - Click "Create".
On the left-side menu, click "Overview". Click the "Import" tab.
- Source: click "Browse".
  - Double-click your bucket and select the SQL file we saved earlier.
  - Click "SELECT" at the bottom of the page.
- File format: SQL
- Destination: mysql_database_name
- Click "Import"

Configuring a new source Connector

Determine name-resolving properties

Some properties require you to be aware of Axual’s custom name-resolving mechanism.
Consult an axual-platform operator to acquire the following information:

Tenant shortName

Example value:
axual

Instance shortName

Example value:
local

Environment shortName

Example value:
default

Connect-Application shortName

This value is already provided. We’ll use:
my_hotels_app

Resolving pattern for topic-names

Example pattern:
${tenant-shortName}-${instance-shortName}-${environment-shortName}-${topic-name}
Example resolved topic name:
axual-local-default-transactions

Resolving pattern for consumer-groups

Example pattern:
${tenant-shortName}-${instance-shortName}-${environment-shortName}-${consumerGroup-name}
Example resolved consumer group:
axual-local-default-com.axual.transactionProcessor

Create the associated streams in the UI

Follow the Creating streams documentation in order to create 3 (or more) streams, and deploy them onto an environment.
- All streams will have a Key/Value of type String/String (configured at stream creation time).
- All streams will be deployed onto the same environment. This is the only environment we’ll use throughout the entire process.
- When you’ll deploy the streams onto the environment, you will use the following stream-deployment configuration:
  - Partition count: 1
  - Retention Time: 157680000000 (5 years)
  - (Optional advanced property) segment.ms of at least 157680000000 (5 years).
Create and deploy the following streams:
1. mysql_nickname
  This stream contains schema-change-events that describe schema-changes that are applied to captured tables in the database.
  This stream is a (<nickname>) for the database.
  You can choose any value for this stream-name: it’s for internal use only, but it has to be unique within the Connect-Cluster.
2. mysql_nickname._schema-changes
  This stream stores the database schema history.
  _This stream name is also completely flexible and independent of the other stream-names, but we chose to call it <nickname>._schema-changes for consistency._
3. mysql_nickname.mysql_database_name.HOTEL
  This stream will contain DB events that happen for the HOTEL table, including (but not limited to) insertions, updates, deletions.
  This stream name is the composition of <nickname>.<database name>.<table name>. This naming pattern is enforced by the Connect-Plugin.
  You would need to create one such stream for every DB table you intend to watch. In our example, we’ll only watch a single table, so we created a single stream.

Create the Connect-Application, configure the Connector-Application

Follow the Configure and install a connector documentation to set up a new Connector-Application.
Let’s call it my_hotels_app.
The plugin name is io.debezium.connector.mysql.MySqlConnector.

Use the following application ID: mysql_nickname-dbhistory.
This value isn’t enforced. We chose to use <nickname>-dbhistory for consistency with other naming schemes.

For advanced configuration, see the official source connector documentation.

Provide the following configuration to set up connectivity to the previously configured MySQL instance.

database.hostname
You noted down this IP address after creating the MySQL instance.

Example value:
123.123.123.123

database.port

3306

database.user

root

database.password
You noted down this password while creating the MySQL instance

Example value:
1234abcdEFG

database.server.name
This is the <nickname> we used as a prefix for all the streams we created.

mysql_nickname

database.server.id
This has to be a unique number within the MySQL Server. This ID isn’t relevant for our example use-case, but it must be set to any value.

184054

database.include.list

mysql_database_name

table.include.list
The name of the table you created, prefixed by the DB name

Example value:
mysql_database_name.HOTEL

database.history.kafka.bootstrap.servers

Example values:
- For local development (helm platform deployment):
platform.local:31757 - For Axual-Cloud, rokin cluster:
bootstrap-c1-ams01-azure.axual.cloud:9094 - For SAAS trial:
oberon-kafka-bootstrap.axual.cloud:9094

database.history.kafka.topic
This is the topic we created using the following naming convention:
<nickname>._schema-changes

This topic name has to be resolved! Information required for this step was acquired at "Determine name-resolving properties"

Example resolved value:
axual-local-default-mysql_nickname._schema-changes

Provide advanced(custom) configuration to the connector

Edit the Connector-Application configuration (my_hotels_app). We need to supply additional (custom) properties, with keys which are not automatically available in the self-service UI.

Many configuration values are of the form:
keyvault:connectors/${tenant}/${instance-shortName}/${environment-shortName}/${application-shortName}:${property.name}

Resolved example:
keyvault:connectors/axual/local/default/my_hotels_app:property.name

Please update the values which are of this form, in the right column of the table, using the values you previously determined. The dollar sign and the curly brackets are part of the value, do not remove them!

auto.create.topics.enable

false

database.history.consumer.group.id This is the application ID used for the my_hotels_app Connect-Application

ATTENTION! This consumer-group name has to be resolved! Information required for this step was previously determined

Example resolved value:
axual-local-default-mysql_nickname-dbhistory

database.history.consumer.security.protocol

SSL

database.history.consumer.ssl.key.password

${keyvault:connectors/axual/local/default/my_hotels_app:ssl.key.password}

database.history.consumer.ssl.keystore.location

${keyvault:connectors/axual/local/default/my_hotels_app:ssl.keystore.location}

database.history.consumer.ssl.keystore.password

${keyvault:connectors/axual/local/default/my_hotels_app:ssl.keystore.password}

database.history.consumer.ssl.truststore.location

${keyvault:connectors/axual/local/default/my_hotels_app:ssl.truststore.location}

database.history.consumer.ssl.truststore.password

${keyvault:connectors/axual/local/default/my_hotels_app:ssl.truststore.password}

database.history.producer.security.protocol

SSL

database.history.producer.ssl.key.password

${keyvault:connectors/axual/local/default/my_hotels_app:ssl.key.password}

database.history.producer.ssl.keystore.location

${keyvault:connectors/axual/local/default/my_hotels_app:ssl.keystore.location}

database.history.producer.ssl.keystore.password

${keyvault:connectors/axual/local/default/my_hotels_app:ssl.keystore.password}

database.history.producer.ssl.truststore.location

${keyvault:connectors/axual/local/default/my_hotels_app:ssl.truststore.location}

database.history.producer.ssl.truststore.password

${keyvault:connectors/axual/local/default/my_hotels_app:ssl.truststore.password}

Authorize the Connector-Application and start producing

Authorize the my_hotels_app source Connector-Application to produce to and consume from all 3 streams we just created:
- mysql_nickname
- mysql_nickname._schema-changes
- mysql_nickname.mysql_database_name.HOTEL
You can now start the Connector-Application.

Cleanup

Once you are done, stop the Connector-Application and cleanup the unused axual resources.
In case you deployed your resources via Google Cloud, don’t forget to delete your bucket and your SQL instance once you are done.