MySQL

Last Updated: 2022-01-25

Overview

Grouparoo's MySQL Integration is both a Source and a Destination that enables you to import and export customer data to and from one or more MySQL databases. MySQL is a relational database management system (RDBMS). You can use Grouparoo to sync data between a MySQL database and other services to operationalize your data.

Sources

Option NameDescription
MySQL Import TableImport or update Records from a MySQL database table.
MySQL Import QueryImport or update Records via a custom MySQL query.

Destinations

Destination NameDescription
MySQL Export RecordExport Records to a MySQL table. Groups will be exported to a secondary table linked by a foreign key.

Setup

To work with the MySQL Plugin, you must first install it in an existing Grouparoo project. Grouparoo supports syncing with MySQL databases stored either locally or remotely, depending on how you configure the MySQL App. Part of configuring the App will be to supply the host and port to establish a connection.

App

To connect MySQL to Grouparoo, you create an App to tell Grouparoo how to connect to MySQL. This specifies the name and location of the database you want to sync data to or from.

App Options

In Grouparoo’s Config UI, you specify the options to connect to MySQL.

MySQL App Options

App OptionDescription
HostDatabase host. If left blank, the default value is ‘localhost’.
PortPort on which to open database connection. If left blank, the default value is ‘3306’.
Database (required)The database name. There is no default value for this field. The name of a database must be supplied.
UserThe username of the account that will establish a connection to the database - e.g. "grouparoo_user". If you are connecting to localhost, this can be left as undefined.
PasswordThe database password - e.g. "P@assword". If you don't have a password, leave this field as undefined.

MySQL Import Table

The MySQL Source is a specific type of Source that we call a Columnar Source, which means it imports data from a column-based mechanism, like a database. MySQL Import Table, more specifically, is a Columnar Table Source. This source reads from a single table in your MySQL database using a primary key. Properties are generated from this table from row values or aggregations of row values.

MySQL Import Table Options

Options

There is one option to set for a MySQL Table Import Source. You need to choose a table from the database to serve as the location for Grouparoo to scan for changes to Properties.

Option NameDescription
Table (required)Name of the table to reference in the MySQL database.

Mapping

Defining Mappings is a critical part of the process. This step tells Grouparoo which Record to associate with the imported data.

If you are using MySQL Import Table as the primary source, choose the MySQL column that uniquely identifies Records in your Model. This will often be the primary key of the table that represents your Model. The Property mapped to this column will be assigned as the Model's Primary Key.

In secondary sources, choose the MySQL column that ties this table to your primary source. This will often be a foreign key in your table.

Schedule

Create a schedule to actively pull new data from this MySQL table. If the recurring option is not selected, you are creating a one-time sync. There are also options to confirm records exist when running a schedule and to enable refresh.

MySQL Import Table Schedule


Options

Option NameDescription
Column (required)Choose which column to scan for changes. This would be a Property with a value that, when updated, indicates there are other updates to the Record. A column named ‘updated_at’ is a good example of the appropriate type of column to select for this field.

Filters

If there is a subset of row data you want to specifically include or exclude from your MySQL table, create a Filter by choosing a Key and Operation pair.

Properties

Properties populate your Records with data from your MySQL table based on table columns. Create a Property to be able to use row data in Grouparoo.

MySQL Import Table Properties


Options

Option NameDescription
Column Name (required)Choose the column that will be the source of data for this Grouparoo Property.
Aggregation Method (required)Aggregation methods determine how to use the row values in the table. MySQL supports the standard set of aggregation methods for table sources.

Filters

If there is a subset of data for a specific primary or foreign key you want to include from your MySQL table, create a Filter.

MySQL Import Query

A Query Source works differently than a table-based source in that there are no any options to choose in setting up the connection to this type of source. A Query Source is a more flexible way to build properties.

This type of Source provides the ability to write custom SQL code to extract data from one or more tables and import the result into Grouparoo.

MySQL Export Record

This Destination enables you to sync your Grouparoo Records to Records in MySQL.

Companies use Grouparoo to sync to their databases in order to ensure that operations run smoothly with the more up-to-date data available across platforms.

To create a Destination, you make choices about what data to sync.

Sync Modes

A Destination’s sync mode determines what to update in a Destination. The following are supported by Intercom:

  • Sync: Add, update, and remove MySQL Records as needed.This option looks for new records, changes to existing records, and deletions.
  • Additive: Add and update MySQL Records as needed, but do not remove anybody. This option looks for new Records to add to MySQL and changes to existing MySQL Records, but does not keep track of deletions.
  • Enrich: Only update those Records that already exist in MySQL. Do not add or remove Records. This option will only update existing Records in MySQL.

Destination Options

To further clarify how MySQL should sync incoming data, choose options for the Destination.

MySQL Export Record Options


Option NameDescription
Table (required)The name of the table which Grouparoo will use to export Records.
Primary Key (required)The primary key for the specified table.
Groups Table (required)Grouparoo also exports Group memberships. This is the table used to export Group data.
Groups Foreign Key (required)The foreign key that the groups table uses to reference the records table.
Groups Column Name (required)The column name used to store the Group name values.

Record Data

Mapping Record Properties to MySQL via Exported Properties is the key to syncing data to MySQL.

MySQL Export Record Properties Data


Required Properties

In a MySQL Export Record Destination, you must choose the Grouparoo Property to map to the column listed as the primary key in the MySQL database table that this Destination syncs to. Therefore, the exported property listed as required will be whatever is designated as the Primary Key in the MySQL table.

Optional Properties

In addition to the required mapping for your table’s primary key, any other columns in the table can also have mappings to Grouparoo properties.

Group Data

You can also use Grouparoo Groups to assign records to a Group in MySQL. This information is recorded in a table that lists the Grouping alongside a foreign key that relates to the associated Record being assigned to that Group in the MySQL database.

Notes

We strive to keep our documentation accurate, easy to use, and up to date. If you notice that something has changed or is not addressed in the process of setting up your Grouparoo connection with MySQL, please let us know by reaching out to help@grouparoo.com.

As always, if you need support using Grouparoo, reach out to community members in our Slack channel. We are always happy to help!

Join us on Slack