MySQL

MySQL

This document describes the MySQL data source. Continue reading to learn more about:

  • Collecting - what should you know about adding the data source.
  • Data Dictionary - what data is available and how it is structured.

Collecting

Before you start

  • Note the host and the port of the MySQL database.
  • Note the username and the password for the user connecting to the MySQL database.

To configure this data source:

  1. If necessary, whitelist Panoply.
    • To ensure data security, MySQL databases with production data are typically not publicly available. To allow Panoply to access your data, see Whitelisting.
  2. Click Data Sources in the navigation menu.
  3. Click the Add Data Source button.
  4. Search for MySQL and select it.
  5. Enter the credentials to connect Panoply to MySQL. If you’re not sure what your connection details are, contact your administrator or open MySQL Workbench and locate the Manage Server Connections dialog. Also, you can connect Panoply to a read replica if you’re concerned about overloading your master database. Most production environments already have a read replica; if yours doesn’t, follow MySQL’s documentation to create one.
    • Host Address - The URL of the MySQL database or the IP address of the host server.
      • URL example: your.server.com or your.server.com:3306
      • IP example: 123.45.67.89 or 123.45.67.89:3306
    • Your MySQL username and password. This user must have permission to access the data. If the permissions are not in place, some of the data will not be available.
      • In MySQL, you can create a Panoply-specific user with read-level and replication permissions to binary logs. Then enter the username and password for this Panoply-specific user. This user must be reserved for Panoply use and unique to your connector. All information entered into Panoply is encrypted to ensure the security of your data. See Data Protection for more information on how Panoply actively provides data security.
    • Examples:
      • A host address based on a URL might look like this: your.server.com:3306, where your.server.com is the URL and 3306 is the port number. The port number is optional. If you do not include a port number, we will use 3306, which is the default for most connections.
      • A host address based on an IP address might look like this: 123.45.67.89:3306 where 123.45.67.89 is the IP address and 3306 is the port number. The IP address should be the IP address of the database host computer or DNS server through which you are accessing your database. The port number is optional. If you do not include a port number, we will use 3306, which is the default for most connections.
  6. Click Next.
  7. Select the MySQL database to connect to. This loads a list of tables.
  8. Select the MySQL tables from which to collect data.
  9. (Optional) Set the Advanced Options.
    • We recommend not changing advanced options unless you are an experienced Panoply user.
    • SSL Certificates - You can optionally add SSL certificates for the certificate authority, client certificate, and client key. If you want to have an SSH connection, please request configuration help from Panoply Support.
  10. Click Collect.
    • The data source appears grayed out while the collection runs.
    • You may add additional data sources while this collection runs.
    • You can monitor this collection from the Jobs page or the Data Sources page.
    • After a successful collection, navigate to the Tables page to review the data results.

Data Dictionary

Because MySQL data comes from a database system, Panoply cannot provide a data dictionary. But Panoply does automate the data schema for the collected data. This section includes useful information about the Panoply automations. You can adjust these settings in your data source under Advanced Options.

  • Destination - Panoply selects a default destination. These are the tables where data is stored. The default name of each destination table in Panoply is the name of the table in your MySQL database. You can add a prefix or postfix to destination table names. For example, to prefix all table names with salesdata_ you would use this syntax: salesdata_{__table_name}. In this case, {__table_name} is a variable that represents the source table’s name.

  • Primary Key - Field(s) to use as the deduplication key when collecting data. Panoply sets the primary key depending on the scenario identified in the following table. To learn more about primary keys in general, see Primary Keys.

MySQL id column Enter a primary key Outcome
yes no Panoply will automatically select the id column and use it as the primary key.
yes yes Not recommended. Panoply will use the id column but will overwrite the original source values.
If you want Panoply to use your database table’s id column, do not enter a value into the Primary Key field.
no no Panoply creates an id column formatted as a GUID, such as 2cd570d1-a11d-4593-9d29-9e2488f0ccc2.
no yes Panoply creates a hashed id column using the primary key values entered, while retaining the source columns. WARNING: Any user-entered primary key will be used across all the MySQL tables selected.
  • Incremental Key - By default, Panoply fetches all of your MySQL data on each run. If you only want to collect some of your data, enter a column name to use as your incremental key. The column must be logically incremental. Panoply will keep track of the maximum value reached during the previous run and will start there on the next run.
    • WARNING: If you set an incremental key, you should only select one table. Otherwise, the collection will use a single set of incremental key & value for all the tables.
  • A column in a table uses the same data type for all values in that column. Panoply automatically chooses the data type for each column based on the available values. This is important to note for this data source. If even one value in a column has text, then the entire column is considered data type Text.

  • The following metadata columns are added to the destination table(s):
    • __updatetime - Formatted as a datetime, such as 2020-04-26T01:26:14.695Z.
    • __senttime - Formatted as a datetime, such as 2020-04-26T01:26:14.695Z.
    • __tablename - The name of the table in MySQL.
Getting started is easy! Get all your data in one place in minutes.
Try Free