​

MySQL Database Connector

​

Supported Features

• ✅ Metadata Discovery: Automatic schema, table, and column discovery
• ✅ Data Profiler: Statistical analysis and data profiling
• ✅ Data Quality: Custom quality tests and validations
• ✅ dbt Integration: dbt model and lineage support
• ✅ View Lineage: Table and view relationship tracking
• ✅ Column-level Lineage: Detailed column dependency mapping
• ✅ Query Usage: Query log analysis and usage statistics
• ✅ Sample Data: Data sampling for preview and analysis
• ✅ Auto-Classification: Automated data classification
• ✅ SSL Support: Secure connections with SSL/TLS

​

Limitations

• ❌ Owners: Owner information extraction not supported
• ❌ Tags: Tag metadata extraction not supported
• ❌ Stored Procedures: Stored procedure discovery not supported

​

Prerequisites

​

Database Requirements

• MySQL version 8.0.0 or greater
• User with access to INFORMATION_SCHEMA tables
• For lineage: Access to mysql.general_log table

​

User Permissions

​

Basic Metadata Access

-- Create user (hostname optional, defaults to '%')
CREATE USER '<username>'[@'<hostname>'] IDENTIFIED BY '<password>';

-- Grant select on specific database
GRANT SELECT ON your_database.* TO '<username>';

-- Grant select on specific table
GRANT SELECT ON your_database.your_table TO '<username>';

-- Grant show view to extract DDL
GRANT SHOW VIEW ON your_database.* TO '<username>';

​

Lineage and Usage Extraction

-- Enable query logging
SET GLOBAL general_log='ON';
SET GLOBAL log_output='table';

-- Grant access to log table
GRANT SELECT ON mysql.general_log TO '<username>'@'<host>';

​

Requirements

​

Metadata

-- Create user. If <hostName> is omitted, defaults to '%'
-- More details https://dev.mysql.com/doc/refman/8.0/en/create-user.html
CREATE USER '<username>'[@'<hostName>'] IDENTIFIED BY '<password>';

-- Grant select on a database
GRANT SELECT ON world.* TO '<username>';

-- Grant select on a database
GRANT SELECT ON world.* TO '<username>';

-- Grant select on a specific object
GRANT SELECT ON world.hello TO '<username>';

-- Grant show view to extract ddl
GRANT SHOW VIEW ON world.* to '<username>';

​

Lineage & Usage

-- Enable Logging
SET GLOBAL general_log='ON';
set GLOBAL log_output='table';

-- Grant SELECT on log table
GRANT SELECT ON mysql.general_log TO '<username>'@'<host>';

​

Log Table Management

• Note: We recommend cleaning up log tables only after successful execution of Usage & Lineage workflows to ensure no loss of query data during extraction. Once cleanup occurs, the query history is lost.

​

Create Manual Schedule to rotate logs

-- rotate general logs
CREATE PROCEDURE rotate_general_log()
BEGIN
  -- Step 1: Drop the backup table if it exists
  DROP TABLE IF EXISTS mysql.general_log_backup;

  -- Step 2: Copy current general_log table to backup
  CREATE TABLE mysql.general_log_backup AS SELECT * FROM mysql.general_log;

  -- Step 3: Truncate the general_log table (clears all records)
  TRUNCATE TABLE mysql.general_log;
END;
-- call this procedure
CALL rotate_general_log();

-- rotate slow logs
CREATE PROCEDURE rotate_slow_log()
BEGIN
  DROP TABLE IF EXISTS mysql.slow_log_backup;
  CREATE TABLE mysql.slow_log_backup AS SELECT * FROM mysql.slow_log;
  TRUNCATE TABLE mysql.slow_log;
END
-- call this procedure
CALL rotate_slow_log();

SELECT table_name, round(data_length/1024/1024, 2) AS size_in_mb
FROM information_schema.tables
WHERE table_schema = 'mysql' AND table_name IN ('general_log', 'slow_log', 'general_log_backup', 'slow_log_backup');

​

Create Automatic Event to clear older logs

CREATE EVENT mysql.cleanup_general_log ON SCHEDULE EVERY 7 DAY DO DELETE FROM mysql.general_log WHERE event_time < NOW() - INTERVAL 7 DAY;

• Rotating mysql query logs
• RDS for MySQL database logs
• Aurora for MySQL database logs

• Monitor log table size regularly
• Implement a log rotation schedule
• Consider automating log cleanup after DAG execution
• Keep logging enabled only when needed for lineage extraction

​

Profiler & Data Quality

​

Metadata Ingestion

​

1. Visit the Services Page

​

2. Create a New Service

​

3. Select the Service Type

​

4. Name and Describe your Service

​

Connection Details

• Username: Specify the User to connect to MySQL. It should have enough privileges to read all the metadata.
• Auth Type: Basic Auth or IAM based auth to connect to instances / cloud rds.
  • Basic Auth:
    • Password: Password to connect to MySQL.
  • IAM Based Auth:
    • AWS Access Key ID & AWS Secret Access Key: When you interact with AWS, you specify your AWS security credentials to verify who you are and whether you have permission to access the resources that you are requesting. AWS uses the security credentials to authenticate and authorize your requests (docs).
    Access keys consist of two parts: An access key ID (for example, AKIAIOSFODNN7EXAMPLE), and a secret access key (for example, wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY). You must use both the access key ID and secret access key together to authenticate your requests. You can find further information on how to manage your access keys here.
    • AWS Region: Each AWS Region is a separate geographic area in which AWS clusters data centers (docs).
    As AWS can have instances in multiple regions, we need to know the region the service you want reach belongs to. Note that the AWS Region is the only required parameter when configuring a connection. When connecting to the services programmatically, there are different ways in which we can extract and use the rest of AWS configurations. You can find further information about configuring your credentials here.
    • AWS Session Token (optional): If you are using temporary credentials to access your services, you will need to inform the AWS Access Key ID and AWS Secrets Access Key. Also, these will include an AWS Session Token.
    You can find more information on Using temporary credentials with AWS resources.
    • Endpoint URL (optional): To connect programmatically to an AWS service, you use an endpoint. An endpoint is the URL of the entry point for an AWS web service. The AWS SDKs and the AWS Command Line Interface (AWS CLI) automatically use the default endpoint for each service in an AWS Region. But you can specify an alternate endpoint for your API requests.
    Find more information on AWS service endpoints.
    • Profile Name: A named profile is a collection of settings and credentials that you can apply to a AWS CLI command. When you specify a profile to run a command, the settings and credentials are used to run that command. Multiple named profiles can be stored in the config and credentials files.
    You can inform this field if you’d like to use a profile other than default. Find here more information about Named profiles for the AWS CLI.
    • Assume Role Arn: Typically, you use AssumeRole within your account or for cross-account access. In this field you’ll set the ARN (Amazon Resource Name) of the policy of the other account.
    A user who wants to access a role in a different account must also have permissions that are delegated from the account administrator. The administrator must attach a policy that allows the user to call AssumeRole for the ARN of the role in the other account. This is a required field if you’d like to AssumeRole. Find more information on AssumeRole.
    Assume Role Authentication Requirements

    When using Assume Role authentication, ensure you provide the following details:

    • AWS Region: Specify the AWS region for your deployment.
    • Assume Role Session Name: An identifier for the assumed role session. Use the role session name to uniquely identify a session when the same role is assumed by different principals or for different reasons.
    By default, we’ll use the name CollateSession. Find more information about the Role Session Name.
    • Assume Role Source Identity: The source identity specified by the principal that is calling the AssumeRole operation. You can use source identity information in AWS CloudTrail logs to determine who took actions with a role.
    Find more information about Source Identity.
• Host and Port: Enter the fully qualified hostname and port number for your MySQL deployment in the Host and Port field.
• databaseName: Optional name to give to the database in Collate. If left blank, we will use default as the database name.
• databaseSchema: databaseSchema of the data source. This is optional parameter, if you would like to restrict the metadata reading to a single databaseSchema. When left blank, Collate Ingestion attempts to scan all the databaseSchema.
• caCertificate: Provide the path to ssl ca file.
• sslCertificate: Provide the path to ssl client certificate file (ssl_cert).
• sslKey: Provide the path to ssl client certificate file (ssl_key).

​

Securing MySQL Connection with SSL in Collate

​

Reverse Metadata