Use DataX to migrate data from RDS to LindormTSDB - Lindorm

DataX is an open source offline data synchronization tool from Alibaba. Use it to batch-migrate data from a MySQL database to LindormTSDB.

The migration uses two DataX plugins:

MySQL Reader — reads rows from MySQL over Java Database Connectivity (JDBC)
TSDB Writer — writes data points to LindormTSDB over the /api/mput and /api/put HTTP APIs

Prerequisites

Before you begin, ensure that you have:

A Linux machine to run DataX
JDK 1.8 or later — download from Oracle
Python 2.6.x (recommended) — download from python.org
MySQL compatible with version 5.x
A running LindormTSDB instance
Network connectivity from the DataX host to both MySQL (JDBC port) and LindormTSDB (HTTP port). Missing connectivity causes a Connect Exception error.

Set up DataX

Download and extract

Download the DataX package with plugins pre-configured for this migration:

wget https://tsdbtools.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
tar -xzf datax.tar.gz

Set DATAX_HOME to the extracted directory:

export DATAX_HOME=$(pwd)/datax

Verify the installation

Run the built-in Stream Reader to Stream Writer test job to confirm DataX is working. These plugins have no external dependencies — Stream Reader generates random strings and Stream Writer prints them to the console.

cd ${DATAX_HOME}
python bin/datax.py job/job.json

A successful run ends with a summary similar to:

Task start time           : 2019-04-26 11:18:07
Task end time             : 2019-04-26 11:18:17
Total time                :                 10s
Average traffic           :          253.91KB/s
Record write speed        :          10000rec/s
Total records read        :              100000
Total read/write failures :                   0

Total read/write failures: 0 confirms the job completed without errors. The write speed reflects Stream Writer throughput, not LindormTSDB throughput.

Configure the migration job

Create a configuration file named mysql2tsdb.json. The file defines one reader (MySQL Reader) and one writer (TSDB Writer).

{
  "job": {
    "content": [
      {
        "reader": {
          "name": "mysqlreader",
          "parameter": {
            "column": [
              "name",
              "type",
              "create_time",
              "price",
              "description",
              "available"
            ],
            "connection": [
              {
                "jdbcUrl": [
                  "jdbc:mysql://127.0.0.1:3306/datax"
                ],
                "table": [
                  "book"
                ]
              }
            ],
            "password": "xxx",
            "splitPk": "id",
            "username": "root"
          }
        },
        "writer": {
          "name": "tsdbwriter",
          "parameter": {
            "column": [
              "name",
              "type",
              "create_time",
              "price",
              "description",
              "available"
            ],
            "columnType": [
              "tag",
              "tag",
              "timestamp",
              "field_double",
              "field_string",
              "field_bool"
            ],
            "sourceDbType": "RDB",
            "endpoint": "http://localhost:8242",
            "table": "my_book",
            "multiField": "true",
            "username": "xxx",
            "password": "xxx",
            "ignoreWriteError": "false",
            "database": "default"
          }
        }
      }
    ],
    "setting": {
      "speed": {
        "channel": 1
      }
    }
  }
}

Important

The column array in MySQL Reader and the column array in TSDB Writer must list columns in the same order. DataX maps them positionally — mismatched order corrupts data.

MySQL Reader parameters

Parameter	Type	Required	Description	Default	Example
`jdbcUrl`	String	Yes	JDBC connection string for the MySQL database	—	`jdbc:mysql://127.0.0.1:3306/datax`
`username`	String	Yes	MySQL username	—	`root`
`password`	String	Yes	Password for the specified username	—	`root`
`table`	String	Yes	Table to read from	—	`book`
`column`	Array	Yes	Columns to read, in order. Must match the order of `column` in TSDB Writer.	`[]`	`["name","type","create_time"]`
`splitPk`	String	No	Column used to partition reads for parallel execution	(empty)	`id`

TSDB Writer parameters

Parameter	Type	Required	Description	Default	Example
`sourceDbType`	String	No	Source system type. `RDB` for relational databases (MySQL, Oracle, PostgreSQL, DRDS); `TSDB` for time series databases (OpenTSDB, InfluxDB, Prometheus, TimeScale)	`TSDB`	`RDB`
`column`	Array	Yes	Columns to write, in the same order as MySQL Reader `column`. DataX maps columns positionally.	`[]`	`["name","type","create_time","price"]`
`columnType`	Array	Yes	LindormTSDB field type for each column in `column`. Supported values: `timestamp`, `tag`, `metric_num`, `metric_string`. See the mapping table below.	`[]`	`["tag","tag","timestamp","metric_num"]`
`endpoint`	String	Yes	LindormTSDB endpoint address and port	—	`ld-xxxx:8242`
`multiField`	Bool	Yes	Set to `true` to write data in multi-field mode	`false`	`true`
`table`	String	Yes	Target table name in LindormTSDB	—	`my_book`
`username`	String	No	LindormTSDB username	—	`tsdbuser`
`password`	String	No	LindormTSDB password	—	`tsdbpsw`
`ignoreWriteError`	Bool	Yes	When `true`, skips rows that fail to write instead of stopping the job	`false`	`false`
`database`	String	No	Target database name in LindormTSDB	`default`	`default`

Column type mapping

The columnType array maps each MySQL column to a LindormTSDB field type. Supported values are:

`columnType` value	Description	Typical MySQL column type
`timestamp`	Timestamp field	`DATETIME`, `BIGINT` (epoch ms)
`tag`	Dimension field used to filter and group queries	`VARCHAR`, `CHAR`
`metric_num`	Numeric metric value	`INT`, `FLOAT`, `DOUBLE`, `DECIMAL`
`metric_string`	String metric value	`TEXT`, `VARCHAR`

Run the migration

Place mysql2tsdb.json in the parent directory of the datax/ folder, then run:

cd ${DATAX_HOME}/..
python datax/bin/datax.py mysql2tsdb.json

Your directory layout should look like this before running:

datax/
datax.tar.gz
mysql2tsdb.json

Verify the migration

A successful migration ends with a summary where Total read/write failures is 0:

Task start time           : 2019-05-21 18:25:16
Task end time             : 2019-05-21 18:25:27
Total time                :                 11s
Average traffic           :                3B/s
Record write speed        :              0rec/s
Total records read        :                   3
Total read/write failures :                   0

If Total read/write failures is greater than 0 and ignoreWriteError is false, the job stops and reports the error. Check the console output for the specific row and error message.

FAQ

Can I increase the JVM memory allocated to the migration process?

Pass JVM flags with the -j option:

python datax/bin/datax.py mysql2tsdb.json -j "-Xms4096m -Xmx4096m"

How do I add the DataX host to the LindormTSDB whitelist?

See Configure a whitelist.

The migration fails with a `Connect Exception`. What should I check?

For LindormTSDB: confirm the DataX host can reach the LindormTSDB endpoint on the HTTP port specified in endpoint.
For MySQL: confirm the DataX host can reach the MySQL JDBC port.
If running DataX on an ECS instance, review ECS security group configuration use cases and VPC FAQ.