Usage

mysql2sqlite transfers MySQL or MariaDB schema and data to a SQLite 3 database file. It reads the source schema from MySQL/MariaDB, creates equivalent SQLite tables, indexes, views, and foreign keys where possible, then transfers table data into the SQLite file.

Prerequisites

  • Python 3.9 or newer, unless you use the Docker image.

  • A reachable MySQL or MariaDB server.

  • A MySQL user that can read the source database and its metadata in information_schema.

  • A writable destination path for the SQLite database file.

See the GitHub Actions CI matrix for the current MySQL and MariaDB versions tested by the project. Very old server versions are more likely to differ in type, default-value, authentication, or metadata behavior.

Installation

Install from PyPI:

pip install mysql-to-sqlite3
mysql2sqlite --help

On macOS, you can also install with Homebrew:

brew install mysql-to-sqlite3
mysql2sqlite --help

Or run the published Docker image:

docker run --rm ghcr.io/techouse/mysql-to-sqlite3:latest --help

Quick start

Use -p / --prompt-mysql-password for interactive password entry. This avoids putting the password in shell history or process listings.

mysql2sqlite \
    --sqlite-file ./app.sqlite3 \
    --mysql-database app_db \
    --mysql-user app_user \
    --prompt-mysql-password \
    --mysql-host 127.0.0.1 \
    --mysql-port 3306

Short options are equivalent:

mysql2sqlite -f ./app.sqlite3 -d app_db -u app_user -p -h 127.0.0.1 -P 3306

For automation, --mysql-password is available, but prefer a secret manager or environment-expanded value rather than typing the password directly into your shell history.

Common recipes

Run with Docker

Use host.docker.internal when the MySQL server is running on the host machine and the Docker container needs to reach it. On Linux Docker Engine, add --add-host=host.docker.internal:host-gateway before the image name if host.docker.internal is not resolvable.

docker run -it \
    --rm \
    --workdir "$PWD" \
    --volume "$PWD:$PWD" \
    ghcr.io/techouse/mysql-to-sqlite3:latest \
    -f ./app.sqlite3 \
    -d app_db \
    -u app_user \
    -p \
    -h host.docker.internal

Files written inside the mounted working directory are written back to the host directory.

Transfer schema only

Create the SQLite tables, indexes, views, and foreign keys without transferring table rows.

mysql2sqlite -f ./schema.sqlite3 -d app_db -u app_user -p --without-data

Transfer data into an existing SQLite schema

--without-tables skips DDL creation and only inserts data. The SQLite tables must already exist.

mysql2sqlite -f ./app.sqlite3 -d app_db -u app_user -p --without-tables

A common two-step flow is:

mysql2sqlite -f ./app.sqlite3 -d app_db -u app_user -p --without-data
mysql2sqlite -f ./app.sqlite3 -d app_db -u app_user -p --without-tables

Transfer only some tables

Table names are space-separated and are consumed until the next CLI option.

mysql2sqlite -f ./subset.sqlite3 -d app_db -u app_user -p --mysql-tables users orders invoices

Transfer everything except selected tables:

mysql2sqlite -f ./subset.sqlite3 -d app_db -u app_user -p --exclude-mysql-tables audit_log temp_imports

Selecting or excluding tables disables foreign key transfer because the referenced tables may not be present.

Sample rows from every table

Transfer at most 100 rows from each table:

mysql2sqlite -f ./sample.sqlite3 -d app_db -u app_user -p --limit-rows 100

Tune large transfers

The CLI fetches and writes rows in batches by default. Use --chunk to tune the batch size. --vacuum repacks the SQLite file after the transfer finishes.

mysql2sqlite -f ./app.sqlite3 -d app_db -u app_user -p --chunk 50000 --vacuum

Use SSL certificates

Verify the server certificate with a CA file:

mysql2sqlite -f ./app.sqlite3 -d app_db -u app_user -p --mysql-ssl-ca /path/to/ca.pem

Use a client certificate and key:

mysql2sqlite \
    -f ./app.sqlite3 \
    -d app_db \
    -u app_user \
    -p \
    --mysql-ssl-ca /path/to/ca.pem \
    --mysql-ssl-cert /path/to/client-cert.pem \
    --mysql-ssl-key /path/to/client-key.pem

Use --skip-ssl only when you explicitly need to disable MySQL connection encryption.

Options at a glance

Option

Purpose

-f, --sqlite-file PATH

Destination SQLite database file. Required.

-d, --mysql-database TEXT

Source MySQL/MariaDB database name. Required.

-u, --mysql-user TEXT

MySQL/MariaDB user. Required.

-p, --prompt-mysql-password

Prompt for the MySQL password. Preferred for interactive use.

--mysql-password TEXT

Provide the MySQL password directly. Useful for automation, but handle carefully.

-h, --mysql-host TEXT

MySQL host. Defaults to localhost.

-P, --mysql-port INTEGER

MySQL port. Defaults to 3306.

-t, --mysql-tables TUPLE

Transfer only the listed tables. Implies no foreign key transfer.

-e, --exclude-mysql-tables TUPLE

Transfer every table except the listed tables. Implies no foreign key transfer.

-T, --mysql-views-as-tables

Materialize MySQL views as SQLite tables instead of creating SQLite views.

-L, --limit-rows INTEGER

Transfer at most this many rows from each table. 0 means no limit.

-C, --collation [BINARY|NOCASE|RTRIM]

Add a SQLite collation to text-affinity columns. Defaults to BINARY.

-K, --prefix-indices

Prefix SQLite index names with their table names.

-X, --without-foreign-keys

Do not create foreign keys in the SQLite schema.

-Z, --without-tables

Skip table/view creation and transfer data only.

-W, --without-data

Create schema only and skip table data.

-M, --strict

Request SQLite STRICT tables; older SQLite versions fall back to non-STRICT tables with a warning.

--mysql-charset TEXT

MySQL database and table character set. Defaults to utf8mb4.

--mysql-collation TEXT

MySQL database and table collation. Must belong to the selected charset.

--mysql-ssl-ca PATH

Path to an SSL CA certificate file.

--mysql-ssl-cert PATH

Path to an SSL client certificate file. Must be paired with --mysql-ssl-key.

--mysql-ssl-key PATH

Path to an SSL client key file. Must be paired with --mysql-ssl-cert.

-S, --skip-ssl

Disable MySQL connection encryption. Cannot be used with SSL certificate options.

-c, --chunk INTEGER

Read and write SQL records in batches. Defaults to 200000.

-l, --log-file PATH

Write logs to a file.

--json-as-text

Force MySQL/MariaDB JSON columns to SQLite TEXT.

-V, --vacuum

Run SQLite VACUUM after transfer.

--use-buffered-cursors

Use buffered MySQL cursors.

-q, --quiet

Show only errors after the initial command banner.

--debug

Re-raise exceptions for debugging instead of printing friendly errors.

--version

Show environment and dependency versions.

--help

Show CLI help.

Combinations and caveats

  • --mysql-tables and --exclude-mysql-tables are mutually exclusive.

  • --mysql-tables or --exclude-mysql-tables automatically disables foreign key transfer.

  • --without-tables and --without-data cannot be used together because there would be nothing to do.

  • --without-tables requires the destination SQLite schema to already exist.

  • --skip-ssl cannot be combined with --mysql-ssl-ca, --mysql-ssl-cert, or --mysql-ssl-key.

  • --mysql-ssl-cert and --mysql-ssl-key must be provided together.

  • --mysql-collation must be valid for the selected --mysql-charset.

  • --limit-rows must be 0 or a positive integer. 0 means no limit.

  • --strict requests SQLite STRICT tables. On SQLite older than 3.37, the tool logs a warning and automatically creates non-STRICT tables instead; rerun with SQLite 3.37 or newer to get STRICT schemas.

  • MySQL views become SQLite views by default. Use --mysql-views-as-tables for the older materialized-table behavior.

MySQL, MariaDB, and SQLite notes

  • MySQL and MariaDB are similar but not identical. Default expressions, generated defaults, authentication plugins, JSON behavior, and metadata returned from information_schema can differ by server family and version.

  • Older legacy servers may not support newer column types such as native JSON.

  • MySQL/MariaDB JSON columns map to SQLite JSON only when this tool detects SQLite JSON1 support. Otherwise they map to TEXT. Use --json-as-text to force TEXT.

  • ENUM, SET, unsupported spatial/network-style types, and unknown types fall back to TEXT.

  • MySQL TIMESTAMP columns are represented as SQLite DATETIME.

  • Unsigned integer types are converted to their signed SQLite-compatible type names.

  • Table names, column names, and index names are quoted for SQLite. Duplicate SQLite index names are made unique, and --prefix-indices can make this behavior explicit.

  • After transfer, verify schema details that are important to your application, especially defaults, collations, JSON columns, views, and foreign keys.