MySQL

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

Integration details

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

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