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
}
}