segment-boneyard/mysql

MySQL Source

This source is no longer actively maintained, and is only available as-is

Segment source for MySQL databases. Syncs your production MySQL database with Segment Objects API.

Schema

A listings table in the products schema that looks like this in your production MySQL...

Id	Listing	Cost
123	1 bedroom	$100
345	2 bedroom	$200
567	3 bedroom	$300

would be queryable in your analytics Redshift or MySQL database like this...

select * from <source-name>.products_listings

Redshift

Id	Listing	Cost
123	1 bedroom	$100
345	2 bedroom	$200
567	3 bedroom	$300

Quick Start

The source process is to be run in a trusted environment that has access to the DB endpoint. This may be either the DB itself, or it could also be a dev box that has access to the DB.

Build and Run

Prerequisites: Go >= 1.7

go get -u github.com/segment-sources/mysql/cmd/source-mysql

The first step is to initialize your schema. You can do so by running source-mysql with --init flag.

source-mysql --init --write-key=ab-200-1alx91kx --hostname=mysql-test.ksdg31bcms.us-west-2.rds.amazonaws.com --port=3306 --username=segment --password=cndgks8102baajls --database=segment

The init step will store the schema of possible tables that the source can sync in schema.json. The query will look for tables across all schemas excluding the ones without a PRIMARY KEY.

In the schema.json example below, our parser found the table public.films where public is the schema name and films the table name with a compound primary key and 6 columns. The values in the primary_keys list have to be present in the columns list. The column list is used to generate SELECT statements, you can filter out some fields that you don't want to sync with Segment by removing them from the list.

{
    "public": {
        "films": {
            "primary_keys": [
                "code",
                "title"
            ],
            "columns": [
                "code",
                "title",
                "did",
                "date_prod",
                "kind",
                "len"
            ]
        }
    }
}

Segment's Objects API requires a unique identifier in order to properly sync your tables, the PRIMARY KEY is used as the identifier. Your tables may also have multiple primary keys, in that case we'll concatenate the values in one string joined with underscores.

Scan

source-mysql --write-key=ab-200-1alx91kx --hostname=mysql-test.ksdg31bcms.us-west-2.rds.amazonaws.com --port=5432 --username=segment --password=cndgks8102baajls --database=segment

Example Run:

INFO[0000] Scan started                                  schema=public table=films
DEBU[0000] Executing query: SELECT "code", "title", "did", "date_prod", "kind", "len" FROM "public"."films"
DEBU[0000] Received Row                                  row=map[did:1 date_prod:<nil> kind:<nil> len:<nil> code:1     title:title] schema=public table=films
INFO[0000] Scan finished                                 schema=public table=films

Usage

Usage:
  source-mysql
    [--debug]
    [--init]
    [--concurrency=<c>]
    --write-key=<segment-write-key>
    --hostname=<hostname>
    --port=<port>
    --username=<username>
    --password=<password>
    --database=<database>
    [-- <extra-driver-options>...]
  source-mysql -h | --help
  source-mysql --version

Options:
  -h --help                   Show this screen
  --version                   Show version
  --write-key=<key>           Segment source write key
  --concurrency=<c>           Number of concurrent table scans [default: 1]
  --hostname=<hostname>       Database instance hostname
  --port=<port>               Database instance port number
  --password=<password>       Database instance password
  --database=<database>       Database instance name

mysql's Issues

can not build it

tsgan@bsd:~/go/src/github.com/segment-sources/mysql % go build

github.com/segmentio/objects-go

../../segmentio/objects-go/client.go:97: cannot use req.Properties (type map[string]interface {}) as type *tableize.Input in argument to tableize.Tableize
../../segmentio/objects-go/client.go:111: cannot use req.Properties (type map[string]interface {}) as type *tableize.Input in argument to tableize.Tableize
tsgan@bsd:~/go/src/github.com/segment-sources/mysql % go version
go version go1.6.2 freebsd/amd64

mysql -> source-mysql

Change default binary name to avoid conflicts

Cannot install code

After installing and configuring Go, get the follow error when trying to install the package:

user@ubuntu:~/gocode$ go get -u github.com/segment-sources/mysql
# github.com/segment-sources/mysql
src/github.com/segment-sources/mysql/main.go:6: cannot use MySQL literal (type *MySQL) as type "github.com/segment-sources/sqlsource/driver".Driver in argument to sqlsource.Run:
	*MySQL does not implement "github.com/segment-sources/sqlsource/driver".Driver (wrong type for Scan method)
		have Scan(*domain.Table) (*sqlx.Rows, error)
		want Scan(*domain.Table, []interface {}) ("github.com/segment-sources/sqlsource/driver".SqlRows, error)

Can not "get" project

It looks that moving this project to the "boneyard" introduced an issue with a module dependency. I know this project is now published "as is", but it might be worth noticing that - in its current state - it's no longer usable.

go get -u github.com/segment-boneyard/mysql/cmd/source-mysql
# github.com/segment-boneyard/mysql/cmd/source-mysql
../../../../go/src/github.com/segment-boneyard/mysql/cmd/source-mysql/main.go:9:16: cannot use &mysql.MySQL literal (type *mysql.MySQL) as type "github.com/segment-boneyard/mysql/vendor/github.com/segment-sources/sqlsource/driver".Driver in argument to sqlsource.Run:
	*mysql.MySQL does not implement "github.com/segment-boneyard/mysql/vendor/github.com/segment-sources/sqlsource/driver".Driver (wrong type for Describe method)
		have Describe() (*"github.com/segment-sources/mysql/vendor/github.com/segment-sources/sqlsource/domain".Description, error)
		want Describe() (*"github.com/segment-boneyard/mysql/vendor/github.com/segment-sources/sqlsource/domain".Description, error)