MYSQL Advanced Settings

Updated on 13 Jun 2024
4 Minutes to read

Print
Share
Dark
Light
PDF

Article summary

Did you find this summary helpful?

Thank you for your feedback

Warning:

We do not recommend changing advanced settings unless you are an experienced Panoply user.

For users who have some experience working with their data in Panoply, there are a number of items that can be customized for this data source.

Enable Binary Logs: By default, Panoply fetches all of your MSSQL data on each run. If you want to utilize the MySQL binary logs, check this checkbox.
Once checked, only tables will be available for collection.
Collecting data using binary logs will enable Panoply to automatically identify new, updated and deleted records in your MySQL table and will extract only them. Deleted records will be marked in the Panoply table with the __deleted column equal true.

Requirements:

Server version 5.5. and higher
Binary logs are enabled on the MySQL server (The log is enabled by starting the server with the --log-bin option)
server_id should be set to non-zero value (read more)
binlog_format should be set to ROW (read more)
row_image should be set to FULL(read more)
The following permissions should be set on the connected user:
1. SELECT permission on the database;
2. REPLICATION CLIENT or SUPER permission;
3. REPLICATION SLAVE permission.
Example:
1. CREATE USER bin_log_user IDENTIFIED BY '********';
2. GRANT SELECT ON my_database.* TO 'bin_log_user';
3. GRANT REPLICATION CLIENT ON *.* to 'bin_log_user'; or GRANT SUPER ON *.* to 'bin_log_user';
4. GRANT REPLICATION SLAVE ON *.* to 'bin_log_user';

Warning:

Collecting data using the binary logs will extract all changes made in the selected tables and with no primary key defined, Panoply will create duplicates in its tables.

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.
- Incremental Key configurations
  - If no Incremental Key is configured by the user, by default, Panoply collects all the MySQL data on each run for the MySQL tables or views selected.
  - If the Incremental Key is configured by column name, but not the column value, Panoply collects all data, and then automatically configures the column value at the end of a successful run.
  - If the Incremental Key is configured by column name and the column value (manually or automatically), then on the first collection, Panoply will use that value as the place to begin the collection.
    - The value is updated at the end of a successful collection to the last value collected.
    - In future collections, the new value is used as the starting value. So in future collections Panoply looks for data where the IK value is greater than where the collection ended.
  - When an Incremental Key is configured, Panoply will look for that key in each of the selected tables and views. If the table or view does not have the column indicated as the Incremental Key, it must be collected as a separate instance of the data source.
  - A table or view may have some records that have a ‘null’ value for the incremental key, or they may not capture the incremental key at all. In these situations Panoply omits these records instead of failing the entire data source.

Warning:

If you set an incremental key, you can only collect one table per instance of MySQL.

Destination Schema: This is the name of the target schema to save the data. The default schema is Public. This cannot be changed once a source had been collected.
Destination: The default destination is mysql_<table or view name> , where <table or view name> is a dynamic field. For example, for a table or view name customers, the default destination table is mysql_customers.
Primary Key: The Primary Key is the field or combination of fields that Panoply will 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.

Exclude: The Exclude option allows you to exclude certain data, such as names, addresses, or other personally identifiable information. Enter the column names of the data to exclude.
Parse String: If the data to be collected contains JSON, include the JSON text attributes to be parsed.
Truncate: Truncate deletes all the current data stored in the destination tables, but not the tables themselves. Afterwards Panoply will recollect all the available data for this data source.
Click Save Changes and then 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.

Was this article helpful?

What's Next

PostgreSQL Data Dictionary