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

INFORMATION_SCHEMA.VIEWS
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.COLUMNS
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

KADA Knowledge Library