Skip to the content
LeaplogicLeaplogic
  • Home
  • About Us
  • Contact
SIGN IN
  • Home
  • About Us
  • Contact

  • Getting Started
    • Before You Begin
    • Creating an Account
    • Logging into LeapLogic
    • Reset Password
    • Quick Tour of the Web Interface
    • LeapLogic in 15 minutes
      • Prerequisites
      • Step 1. Log into LeapLogic
      • Step 2. Create Assessment and Get Insights
      • Step 3. Create Transformation Pipeline and See Results
      • Step 4. Edit or Optimize the Transformed Code
      • Step 5: Complete the Transformation Lifecycle
  • Introduction to LeapLogic
    • Overview
    • High Level Architecture
    • Supported Legacy and Cloud Platforms
    • Key Features
  • Workload Assessment
    • Overview
    • Value Proposition
    • Creating Assessment
      • Prerequisites
      • Step 1. Provide Primary Inputs
        • Automation Coverage
      • Step 2. Add the Additional Inputs
        • Table Stat Extraction Steps
          • Teradata
          • Oracle
          • Netezza
      • Step 3. Update the Source Configuration
      • Step 4. Configure the Recommendation Settings
    • Assessment Listing
    • Understanding Insights and Recommendations
      • Volumetric Info
      • EDW
        • Oracle
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Vertica
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Snowflake
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Azure Synapse
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • SQL Server
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Teradata
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Netezza
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Google Big Query
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Redshift
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • PostgreSQL
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Duck DB
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • ClickHouse
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • Exasol
          • Highlights
          • Analysis
          • Optimization
          • Lineage
          • Recommendations
          • Downloadable Reports
        • DB2
          • Highlights
          • Analysis
          • Optimization
          • Recommendations
          • Lineage
          • Downloadable Reports
      • ETL
        • Informatica
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • Ab Initio
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • DataStage
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • Talend
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • SSIS
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • Informatica BDM
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • Oracle Data Integrator
          • Highlights
          • Analysis
          • Downloadable Reports
        • Pentaho
          • Highlights
          • Analysis
          • Downloadable Reports
        • Azure Data Factory
          • ARM Template
          • Highlights
          • Analysis
          • Downloadable Reports
        • Matillion
          • Highlights
          • Analysis
          • Downloadable Reports
        • SnapLogic
          • Highlights
          • Analysis
          • Downloadable Reports
      • Orchestration
        • AutoSys
          • Highlights
          • Analysis
          • Downloadable Reports
        • Control-M
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • SQL Server
          • Highlights
          • Analysis
      • BI
        • OBIEE
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • Tableau
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • IBM Cognos
          • Highlights
          • Analysis
          • Downloadable Reports
        • MicroStrategy
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • Power BI
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • SSRS
          • Highlights
          • Analysis
          • Downloadable Reports
        • SAP BO
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
        • WebFOCUS
          • Highlights
          • Analysis
          • Downloadable Reports
      • Analytics
        • SAS
          • Highlight
          • Analysis
          • Lineage
          • Downloadable Reports
        • Alteryx
          • Highlights
          • Analysis
          • Lineage
          • Downloadable Reports
      • Integrated Assessment (EDW, ETL, Orchestration, BI)
        • Highlights
        • Analysis
        • Optimization
        • Lineage
        • Recommendations
    • Managing Assessment Reports
      • Downloading Report
      • Input Report Utility
      • View Configuration
    • Complexity Calculation Logic
    • Key Benefits
    • Ad hoc Query
  • Metadata Management
    • Overview
    • Introduction to Data Catalog
      • Managing Data Catalog
        • Building Data Catalog
        • Insights to Data Catalog
        • Managing the Repository and Data Source
      • Creating Repository (Repo)
      • Creating Data Source
    • Tag Management
    • Key benefits
  • Batch Processing using Pipeline
    • Introduction
    • Designing Pipeline
      • How to create a pipeline
        • Configuring Migration Stage
          • Schema Optimization
        • Configuring Transformation Stage
          • On-premises to Cloud
          • Cloud-to-Cloud
          • LeapLogic Express
        • Configuring Validation Stage
          • Data Validation
            • Table
            • File
            • File and Table
            • Cell-by-cell validation
          • Query Validation
            • Query Validation (When Data is Available)
            • Query Validation (When Data is Not Available)
          • Schema Validation
        • Configuring Execution Stage
        • Configuring ETL Conversion Stage
          • Ab Initio
          • Informatica
          • Informatica BDM
          • Matillion
          • DataStage
          • SSIS
          • IICS
          • Talend
          • Oracle Data Integrator
          • Pentaho
          • SnapLogic
        • Configuring Mainframe Conversion Stage
          • Cobol
          • JCL
        • Configuring Orchestration Stage
          • AutoSys
          • Control-M
        • Configuring BI Conversion Stage
          • OBIEE to Power BI
          • OBIEE to AWS QuickSight
          • Tableau to Amazon QuickSight
          • Tableau to Power BI
          • Tableau to Superset
          • Tableau to Looker
          • IBM Cognos to Power BI
        • Configuring Analytics Conversion Stage
          • SAS
          • Alteryx
        • Configuring Script Conversion Stage
    • Key Features
      • How to schedule a pipeline
      • Configuring Parameters
  • Pipeline Reports
    • Overview of Pipeline Report
    • Pipeline Listing
    • Reports and Insights
      • Migration
      • Transformation
        • On-premises to Cloud
        • Cloud-to-Cloud
        • LeapLogic Express
      • Validation
        • Data
          • File
          • Table
          • File and Table
        • Query
          • Query Validation Report (When Data is Available)
          • Query Validation Report (When Data is not Available)
        • Schema
      • Execution
      • ETL
        • Ab Initio
        • Informatica
        • Informatica BDM
        • Matillion
        • DataStage
        • SSIS
        • IICS
        • Talend
        • Oracle Data Integrator
        • Pentaho
        • SnapLogic
      • Mainframe
        • Cobol
        • JCL
      • Orchestration
        • AutoSys
        • Control-M
      • BI
        • OBIEE to Power BI
        • OBIEE to Amazon QuickSight
        • Tableau to Amazon QuickSight
        • Tableau to Power BI
        • Tableau to Superset
        • Tableau to Looker
        • IBM Cognos to Power BI
      • Analytics
        • SAS
        • Alteryx
      • Shell Script
      • Common Model
    • Automation Level Indicator
      • ETL
        • Informatica
        • Matillion
        • DataStage
        • Informatica BDM
        • SnapLogic
        • IICS
        • Ab Initio
        • SSIS
        • Talend
        • Pentaho
      • Orchestration
        • AutoSys
        • Control-M
      • EDW
      • Analytics
        • SAS
        • Alteryx
      • BI
      • Shell Script
    • Error Specifications & Troubleshooting
  • SQL Transformation
    • Overview
    • Creating and Executing the Online Notebook
      • How to Create and Execute the Notebook
      • Supported Features
    • Configuring the Notebook
      • Transformation
      • Unit Level Validation
      • Script Level Validation
    • Notebook Listing
  • Operationalization
    • Overview
      • Basic
      • Advanced
      • Cron Expression
    • Parallel Run Pipeline Listing
  • Transformation Source
    • Introduction
    • Creating Transformation Source Type
  • Governance
    • Summary of Governance - Roles and Permissions
    • User Creation
      • Creating a new User Account
    • Adding Roles and permissions
      • How to add Roles and Permissions to a new user?
    • Adding Group Accounts
    • Default Quota Limits
    • Product Usage Metrics
  • License
    • EDW
    • ETL
  • Script/ Query Log/ Code Extraction Prerequisites
    • Cloud
      • Azure Data Factory
      • Snowflake
      • Azure Synapse
      • Google BigQuery
      • Redshift
      • Azure SQL Database Hyperscale
      • Aurora PostgreSQL
    • EDW
      • Oracle
      • Netezza
      • Teradata
      • Vertica
      • SQL Server
      • Db2
      • MySQL
      • PostgreSQL
    • ETL
      • DataStage
      • Informatica
      • SSIS
      • Talend
      • ODI
      • IICS
      • DBT
      • Pentaho
      • Matillion
      • SnapLogic
      • Ab Initio
      • SAP BODS
      • TAC
      • WebFOCUS
    • BI
      • IBM Cognos
      • OBIEE
      • Tableau
      • Metabase
      • MicroStrategy
      • PowerBI
      • LeapLogic Utility for SAP BO
      • SAP BO Universe and Web Intelligence
      • SSRS
    • Analytics
      • SAS
      • Alteryx
    • Orchestration
      • AutoSys
      • Control-M
      • SQL Server
    • Mainframe
  • LeapLogic Desktop Version
    • Overview
    • Registration and Installation
    • Getting Started
    • Creating Assessment
      • ETL
      • DML
      • Procedure
      • Analytics
      • Hadoop
    • Reports and Insights
      • Downloadable Reports
      • Reports for Estimation
    • Logging and Troubleshooting
    • Sample Scripts
    • Desktop vs. Web Version
    • Getting Help
  • LeapLogic (Version 4.9) Deployment
    • System Requirements
    • Prerequisites
    • Deployment
      • Extracting Package
      • Placing License Key
      • Executing Deployment Script
      • Accessing LeapLogic
    • Uploading License
    • Appendix
    • Getting Help
  • Removed Features
    • Configuring File Validation Stage
    • Variable Extractor Stage
      • Variable Extractor Report
    • Configuring Meta Diff Stage
      • Meta Diff
    • Configuring Data Load Stage
      • Data Load
    • Configuring Multi Algo Stage
  • FAQs
  • Tutorial Videos
  • Notice
Home   »  Script/ Query Log/ Code Extraction Prerequisites   »  EDW  »  MySQL Extraction Prerequisites

MySQL Extraction Prerequisites

This topic briefs about the prerequisites required for MySQL artifacts extraction.

In This Topic:

  • Introduction
  • Artifact Extraction
  • Getting Help


Introduction

LeapLogic’s Assessment profiles existing inventory, identifies complexity, performs dependency analysis, and provides recommendations for migration to modern data platform.


Artifact Extraction

LeapLogic requires certain artifacts to perform an assessment. As a prerequisite, you need to have a super user privilege to fetch the required data. You can copy them from your LINUX environment, where all the artifacts such as DDL scripts, stored procedures, functions, query execution logs, DML scripts, and other database objects are stored from MySQL. LeapLogic needs all these artifacts in the form of .sql files.

DDL Scripts

Using a simple Shell script as given below, you can extract all the DDL scripts. To leverage this option, first connect with the MySQL-connector module.

mysqldump -h mysql-host -u username -p –no-data –routines –triggers –events databasename –result-file /tmp/databasename_db_output.sql

Modify the details highlighted as per your environment. This script exports all the table DDLs. Please run this script for every database in the MySQL cluster.

Note:

While running the script using the MySQL client, please provide the password if prompted.

Query Execution Logs

Use the MySQL Query Editor to start extracting the query execution logs. To start exporting the required query execution logs, refer to the below script.

Note:

Please remember to change the dates highlighted in the below script.

The total recommended timeframe is of three months for extracting the end-to-end query logs. Next, execute the given script below using the MySQL Query Editor UI and download the output after execution.

SELECT

    “IDWWM” as FILLER,

    “” as username,

    “” as app_name,

    “” as CLIENT_ID,

    sa.first_seen as StartTime,

    sa.last_seen as EndTime,

    “” as TotalIOCount,

    sa.digest as pid,

    sa.query,

    SCHEMA_NAME ,

    sa.MAX_TOTAL_MEMORY

    ,QUERY_SAMPLE_TIMER_WAIT

    ,sa.MAX_CONTROLLED_MEMORY

    ,sew.errors

    ,sew.warnings

    ,sa.DB as database

    ,CPU_latency

    ,esh.ROWS_SENT

    ,EVENT_NAME as `type`

    ,TIMER_WAIT / 1000000000000 AS cpu_time_sec

FROM

    sys.statement_analysis sa

Left JOIN

    performance_schema.events_statements_summary_by_digest esss

ON

    sa.DIGEST = esss.DIGEST

Left join

      sys.statements_with_errors_or_warnings sew

ON

      sew.DIGEST=sa.DIGEST

left join

         performance_schema.events_statements_history esh

ON

      esh.DIGEST=sa.digest

where cast(sa.first_seen as date) between ‘2023-12-01‘ and ‘2024-04-30‘;

Other Database Objects

For rich insights from the assessment of your environment and workloads, we recommend exporting additional database objects as well. Please refer to the below script to export the data as separate delimited files.

– – – -Database objects: Execute the below queries for every database using Super user credentials

  select db, object_type , count from sys.schema_object_overview soo

– – -Databases:

  select “Databases”, count(object_schema) from   sys.innodb_buffer_stats_by_schema ibsbs

where object_schema not in (“mysql”,”InnoDB System”, “sys”);

– – – -High Data Volume Tables

  SELECT

   table_schema AS `Database`,

   table_name AS `Table`,

   ROUND((data_length + index_length) / 1024 / 1024, 2) AS `Size (MB)`

FROM

   information_schema.TABLES

WHERE

   table_schema NOT IN (‘information_schema’, ‘performance_schema’, ‘mysql’, ‘sys’)

ORDER BY

   (data_length + index_length) DESC;

– – -Data for Partitioning / Bucketing

SELECT

   TABLE_SCHEMA,

   TABLE_NAME,

   PARTITION_NAME,

   PARTITION_ORDINAL_POSITION,

   PARTITION_METHOD,

   SUBPARTITION_NAME,

   SUBPARTITION_ORDINAL_POSITION,

   SUBPARTITION_METHOD,

   PARTITION_EXPRESSION,

   SUBPARTITION_EXPRESSION,

   TABLE_ROWS

FROM

   information_schema.PARTITIONS

 where TABLE_SCHEMA  not in (“mysql”,”InnoDB System”, “sys”,”performance_schema”,”information_schema”) ;

– – -Count of stored procedures and Functions

SELECT ROUTINE_TYPE, COUNT(*) AS count

FROM information_schema.ROUTINES

WHERE ROUTINE_SCHEMA not in (“mysql”,”InnoDB System”, “sys”,”performance_schema”,”information_schema”)

GROUP BY ROUTINE_TYPE;

– – -List of stored procedure

select ROUTINE_SCHEMA ,ROUTINE_NAME , ROUTINE_DEFINITION,SQL_DATA_ACCESS,ROUTINE_TYPE FROM information_schema.ROUTINES where ROUTINE_TYPE =” PROCEDURE” and ROUTINE_SCHEMA not in (“mysql”,”InnoDB System”, “sys”,”performance_schema”,”information_schema”);     

– – -Count of external Tables/Views in MySQL

SELECT TABLE_SCHEMA, TABLE_TYPE, COUNT(*) AS “count”

FROM information_schema.TABLES

WHERE TABLE_SCHEMA not in (“mysql”,”InnoDB System”, “sys”,”performance_schema”,”information_schema”)

GROUP BY TABLE_TYPE,TABLE_SCHEMA;

– – -List of External Tables

SELECT TABLE_SCHEMA, TABLE_NAME ,TABLE_TYPE,TABLE_ROWS

FROM information_schema.TABLES

WHERE TABLE_SCHEMA not in (“mysql”,”InnoDB System”, “sys”,”performance_schema”,”information_schema”)

– – –Total I/O Usage by Days

SELECT

   EVENT_NAME,

   SUM(COUNT_READ) AS total_reads,

   SUM(SUM_TIMER_READ) AS total_read_time,

   SUM(COUNT_WRITE) AS total_writes,

   SUM(SUM_TIMER_WRITE) AS total_write_time

FROM performance_schema.file_summary_by_event_name

GROUP BY EVENT_NAME;

– – – –Database Volume:

SELECT

   table_schema AS ‘Database’,

   ROUND(SUM(data_length) / 1024 / 1024, 2) AS ‘Data Size (MB)’,

   ROUND(SUM(index_length) / 1024 / 1024, 2) AS ‘Index Size (MB)’,

   ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS ‘Total Size (MB)’

FROM information_schema.tables

GROUP BY table_schema;

– – (15day/1month/6months whatever is possible for extraction)


Getting Help

Contact LeapLogic technical support at info@leaplogic.io


To learn more, contact our support team or write to: info@leaplogic.io

Copyright © 2025 Impetus Technologies Inc. All Rights Reserved

  • Terms of Use
  • Privacy Policy
  • License Agreement
To the top ↑ Up ↑