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
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