Skip to main content

MySQL

MySQL is an open-source relational database management system. It can be used as an upstream or downstream resource in your Turbine data applications using the records or write functions to a select table in a databse.

Meroxa supports self-hosted and Amazon RDS instances of MySQL versions 8.x or later.

Setup

Networking

To add a MySQL database as a resource, it must be accessible by Meroxa. If not publicly accessible, here are some ways to give Meroxa access.

Create a user for Meroxa (Optional)

# Create User 
CREATE USER 'meroxa'@'localhost' IDENTIFIED BY 'supersecret!23';

# Grant Priviledges
GRANT CREATE ON *.* TO 'meroxa' IDENTIFIED BY 'supersecret!23';

# Finalize
FLUSH PRIVILEGES;

Resource Configuration

Use the meroxa resource create command to configure your MySQL resource.

The following example depicts how this command is used to create a MySQL resource named mysqldb with the minimum configuration required.

$ meroxa resource create mysqldb \
--type mysql \
--url "mysql://$MYSQL_USER:$MYSQL_PASS@$MYSQL_URL:$MYSQL_PORT/$MYSQL_DB"

In the example above, replace following variables with valid credentials from your MySQL environment:

  • $MYSQL_USER - MySQL Username
  • $MYSQL_PASS - MySQL Password
  • $MYSQL_URL - MySQL URL
  • $MYSQL_DB - MySQL Database Name
  • $MYSQL_PORT - MySQL Port (e.g., 3306)

Connections

Binary Log (Binlog)

To enable the MySQL Binary Log a restart of the server will be required.

In your MySQL Configuration, update the following options:

log_bin           = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 10

You can also enable the Binary Log on Amazon RDS.

Data Record Format

Data records from MySQL using Binlog events will take on the following format:

{
"schema": {
"type": "struct",
"fields": [
{
"type": "struct",
"fields": [
{
"type": "int32",
"optional": false,
"field": "id"
},
{
"type": "string",
"optional": false,
"field": "category"
},
{
"type": "string",
"optional": false,
"field": "product_type"
},
{
"type": "string",
"optional": false,
"field": "product_name"
},
{
"type": "boolean",
"optional": false,
"field": "stock"
},
{
"type": "int32",
"optional": false,
"field": "product_id"
},
{
"type": "string",
"optional": false,
"field": "shipping_address"
},
{
"type": "string",
"optional": false,
"field": "customer_email"
}
],
"optional": true,
"name": "resource.database.orders.Value",
"field": "before"
},
{
"type": "struct",
"fields": [
{
"type": "int32",
"optional": false,
"field": "id"
},
{
"type": "string",
"optional": false,
"field": "category"
},
{
"type": "string",
"optional": false,
"field": "product_type"
},
{
"type": "string",
"optional": false,
"field": "product_name"
},
{
"type": "boolean",
"optional": false,
"field": "stock"
},
{
"type": "int32",
"optional": false,
"field": "product_id"
},
{
"type": "string",
"optional": false,
"field": "shipping_address"
},
{
"type": "string",
"optional": false,
"field": "customer_email"
}
],
"optional": true,
"name": "resource.database.orders.Value",
"field": "after"
},
{
"type": "struct",
"fields": [
{
"type": "string",
"optional": false,
"field": "version"
},
{
"type": "string",
"optional": false,
"field": "connector"
},
{
"type": "string",
"optional": false,
"field": "name"
},
{
"type": "int64",
"optional": false,
"field": "ts_ms"
},
{
"type": "string",
"optional": true,
"name": "io.debezium.data.Enum",
"version": 1,
"parameters": {
"allowed": "true,last,false"
},
"default": "false",
"field": "snapshot"
},
{
"type": "string",
"optional": false,
"field": "db"
},
{
"type": "string",
"optional": true,
"field": "table"
},
{
"type": "int64",
"optional": false,
"field": "server_id"
},
{
"type": "string",
"optional": true,
"field": "gtid"
},
{
"type": "string",
"optional": false,
"field": "file"
},
{
"type": "int64",
"optional": false,
"field": "pos"
},
{
"type": "int32",
"optional": false,
"field": "row"
},
{
"type": "int64",
"optional": true,
"field": "thread"
},
{
"type": "string",
"optional": true,
"field": "query"
}
],
"optional": false,
"name": "io.debezium.connector.mysql.Source",
"field": "source"
},
{
"type": "string",
"optional": false,
"field": "op"
},
{
"type": "int64",
"optional": true,
"field": "ts_ms"
},
{
"type": "struct",
"fields": [
{
"type": "string",
"optional": false,
"field": "id"
},
{
"type": "int64",
"optional": false,
"field": "total_order"
},
{
"type": "int64",
"optional": false,
"field": "data_collection_order"
}
],
"optional": true,
"field": "transaction"
}
],
"optional": false,
"name": "resource.database.orders.Envelope"
},
"payload": {
"before": null,
"after": {
"id": 1,
"category": "camping",
"product_type": "sleeping-bag",
"product_name": "Forte 35 Sleeping Bag — Womens",
"stock": true,
"product_id": 361632,
"shipping_address": "9718 East Virginia Avenue Silver Spring, MD 20901",
"customer_email": "[email protected]"
},
"source": {
"version": "1.2.5.Final",
"connector": "mysql",
"name": "resource",
"ts_ms": 0,
"snapshot": "true",
"db": "database",
"table": "orders",
"server_id": 0,
"gtid": null,
"file": "binlog.006733",
"pos": 196,
"row": 0,
"thread": null,
"query": null
},
"op": "c",
"ts_ms": 1650418270290,
"transaction": null
}
}