Skip to main content
Skip table of contents

MySQL

This page will walkthrough the setup of MySQL in K using the direct connect method

Integration details

Scope

Included

Comments

Metadata

YES

See below

Lineage

YES

Usage

YES

Sensitive Data Scanner

N/A

Known limitations

  • Supported MySQL versions include 5.7x & 8.0x


Step 1: Setup access to MySQL

Log into your MYSQL instance and create a user with access to the following tables

  1. INFORMATION_SCHEMA.VIEWS

  2. INFORMATION_SCHEMA.TABLES

  3. INFORMATION_SCHEMA.COLUMNS

  4. INFORMATION_SCHEMA.KEY_COLUMN_USAGE

After this step you should have the following information

  • MySQL host details

  • Username

  • Password


Step 2: Enabling logging (if desired)

Enabling logging is important for processing lineage and usage. Skip this step if you only want to load in Metadata

See more information about logging here - https://dev.mysql.com/doc/refman/8.0/en/log-destinations.html

Note this study Impact of General Query Log on MySQL Performance and the performance impact of enabling logging.

Enable global logging

CODE
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

Change logging tables from reading CSV files to MyISAM Engine for performance

CODE
SET @old_log_state = @@GLOBAL.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
SET GLOBAL general_log = @old_log_state;


Step 3: Create the Source in K

Create an MySQL source in K

  • Select Platform Settings in the side bar

  • In the pop-out side panel, under Integrations click on Sources

  • Click Add Source and select MySQL

  • Select Direct Connect and add your MySQL details

    • Name: Give the MySQL source a name in K.

    • Host: Enter a hostname for your MySQL instance

    • Use SSL: Set this ON

  • Add Connection Details and click Save & Next

    • Host: Add the Role from Step 1

    • Username: Add the Key from Step 1

    • Password: Add the Secret from Step 1

  • Test your connection and click Next

  • Click Finish Setup


Step 3: Schedule MySQL source load

  • Select Platform Settings in the side bar

  • In the pop-out side panel, under Integrations click on Sources

  • Locate your new MySQL Source and click on the Schedule Settings (clock) icon to set the schedule


Step 4: Manually run an ad hoc load to test MySQL

  • Next to your new Source, click on the Run manual load icon

  • Confirm how your want the manual run to be completed

  • After the source load is triggered, a pop up bar will appear taking you to the Monitor tab in the Batch Manager page. This is the usual page you visit to view the progress of source loads

A manual source load will also require a manual run of

  • DAILY

  • GATHER_METRICS_AND_STATS

To load all metrics and indexes with the manually loaded metadata. These can be found in the Batch Manager page

 

Troubleshooting failed loads

  • If the job failed at the extraction step

    • Check the error. Contact KADA Support if required.

    • Rerun the source job

  • If the job failed at the load step, the landing folder failed directory will contain the file with issues.

    • Find the bad record and fix the file

    • Rerun the source job

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.