Document toolboxDocument toolbox

MySQL

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

Integration details

Scope

Included

Comments

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

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

 

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

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