MySQL to SQLite3

A Python CLI for transferring MySQL or MariaDB schema and data to a SQLite 3 database file.

mysql2sqlite 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

Agent skill

This repo includes an optional agent skill at skills/mysql-to-sqlite3/ for users who want Codex or another compatible agent to help prepare a safe mysql2sqlite transfer command. The skill is user-facing: it focuses on migration planning, CLI recipes, password-safe defaults, and MySQL/MariaDB caveats.

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.