Skip to main content

MySQL Dump Format

Description​

MySQL Dump format is the output format of the mysqldump utility. It contains SQL statements for creating tables and inserting data. The implementation parses INSERT statements and extracts data rows from the dump file.

File Extensions​

  • .sql - SQL dump files
  • .mysqldump - MySQL dump files (alias)

Implementation Details​

Reading​

The MySQL Dump implementation:

  • Parses SQL INSERT statements
  • Extracts table names and data values
  • Handles multi-line INSERT statements
  • Converts INSERT values to dictionaries
  • Supports filtering by table name

Writing​

Writing support:

  • Emits INSERT INTO statements
  • Set _table on each record to choose the table name

Key Features​

  • SQL parsing: Parses SQL INSERT statements
  • Table extraction: Extracts data from specific tables
  • Multi-line support: Handles multi-line INSERT statements
  • Totals support: Can count total rows
  • Flat data: Extracts tabular data from INSERT statements

Usage​

from iterable import open_iterable

# Basic reading (all tables)
with open_iterable('dump.sql') as source:
    for row in source:
        print(row)  # Contains _table and column values

# Filter by table name
source = open_iterable('dump.sql', iterableargs={
    'table_name': 'users'
})

# Writing
with open_iterable('output.sql', mode='w') as dest:
    dest.write({'_table': 'users', 'id': 1, 'name': 'Ada'})

Parameters​

  • table_name (str): Optional - Filter by specific table name
  • encoding (str): File encoding (default: utf8)

Limitations​

  1. SQL parsing: Only parses INSERT statements
  2. Format-specific: Must follow mysqldump format
  3. Table filtering: Can filter by table name
  4. Value parsing: Complex SQL values may require manual handling

Compression Support​

MySQL Dump files can be compressed with all supported codecs:

  • GZip (.sql.gz)
  • BZip2 (.sql.bz2)
  • LZMA (.sql.xz)
  • LZ4 (.sql.lz4)
  • ZIP (.sql.zip)
  • Brotli (.sql.br)
  • ZStandard (.sql.zst)

Use Cases​

  • Database migration: Migrating MySQL data
  • Backup processing: Processing database backups
  • Data extraction: Extracting data from SQL dumps
  • ETL pipelines: Data transformation from SQL dumps

Error Handling​

  • Missing dependency: optional libraries raise ImportError with an install hint (pip install 'iterabledata[<extra>]' when an extra exists).
  • Write mode: read-only formats raise WriteNotSupportedError or ValueError when opened with mode="w".
  • Bad or unsupported input: may raise ValueError, OSError, or library-specific errors.
  • See Troubleshooting for decoding, detection, and engine issues.