MySQL

MySQL is an open-source relational database management system. It can be used as an upstream or downstream resource in your Turbine streaming apps 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.

• Whitelist Meroxa IPs
• SSH Tunneling
• Environment-specific instructions

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