mysql-to-sqlite3 - GitLite

KO | EN

gitlite — search

#javascript #ai #typescript #llm #deep-learning #linux #macos #awesome #cli #kubernetes #awesome-list #pytorch #security #openai

mysql-to-sqlite3

(★ 273)

Transfer data from MySQL to SQLite

File Explorer

Download Latest Version (.zip)

- - bug_report.md
  - feature_request.md
- - docker.yml
  - docs.yml
  - publish.yml
  - test.yml
- copilot-instructions.md
- dependabot.yml
- FUNDING.yml
- pull_request_template.md
- conf.py
- index.rst
- make.bat
- Makefile
- modules.rst
- mysql_to_sqlite3.rst
- README.rst
- requirements.txt
- - SKILL.md
- - __init__.py
  - cli.py
  - click_utils.py
  - debug_info.py
  - mysql_utils.py
  - py.typed
  - sqlite_utils.py
  - transporter.py
  - types.py
- - __init__.py
  - mysql_to_sqlite3_test.py
  - test_cli.py
- - __init__.py
  - mysql_to_sqlite3_test.py
  - test_build_create_table_sql_sqlglot_identifiers.py
  - test_cli_error_paths.py
  - test_cli_views_flag.py
  - test_click_utils.py
  - test_collation_sqlglot_augmented.py
  - test_create_and_transfer_reconnect.py
  - test_create_view_reconnect_and_errors.py
  - test_debug_info.py
  - test_defaults_sqlglot_enhanced.py
  - test_indices_prefix_and_uniqueness.py
  - test_mysql_utils.py
  - test_sqlite_utils.py
  - test_transporter.py
  - test_types_and_defaults_extra.py
  - test_types_fallback.py
  - test_types_sqlglot_augmented.py
  - test_views_build_paths_extra.py
  - test_views_create_view.py
  - test_views_sqlglot.py
- __init__.py
- conftest.py
- database.py
- factories.py
- faker_providers.py
- models.py
.bandit.yml
.flake8
.gitignore
AGENTS.md
CHANGELOG.md
CODE-OF-CONDUCT.md
CONTRIBUTING.md
Dockerfile
LICENSE
pyproject.toml
README.md
requirements_dev.txt
SECURITY.md
tox.ini

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

[![PyPI](https://img.shields.io/pypi/v/mysql-to-sqlite3?logo=pypi)](https://pypi.org/project/mysql-to-sqlite3/)
[![PyPI - Downloads](https://img.shields.io/pypi/dm/mysql-to-sqlite3?logo=pypi&label=PyPI%20downloads)](https://pypistats.org/packages/mysql-to-sqlite3)
[![Homebrew Formula Downloads](https://img.shields.io/homebrew/installs/dm/mysql-to-sqlite3?logo=homebrew&label=Homebrew%20downloads)](https://formulae.brew.sh/formula/mysql-to-sqlite3)
[![PyPI - Python Version](https://img.shields.io/pypi/pyversions/mysql-to-sqlite3?logo=python)](https://pypi.org/project/mysql-to-sqlite3/)
[![MySQL Support](https://img.shields.io/static/v1?logo=mysql&label=MySQL&message=5.5%7C5.6%7C5.7%7C8.0%7C8.4%7C9.7&color=2b5d80)](https://github.com/techouse/mysql-to-sqlite3/actions/workflows/test.yml)
[![MariaDB Support](https://img.shields.io/static/v1?logo=mariadb&label=MariaDB&message=5.5%7C10.0%7C10.6%7C10.11%7C11.4%7C11.8&color=C0765A)](https://github.com/techouse/mysql-to-sqlite3/actions/workflows/test.yml)
[![GitHub license](https://img.shields.io/github/license/techouse/mysql-to-sqlite3)](https://github.com/techouse/mysql-to-sqlite3/blob/master/LICENSE)
[![Contributor Covenant](https://img.shields.io/badge/Contributor%20Covenant-2.1-4baaaa.svg?logo=contributorcovenant)](https://raw.githubusercontent.com/techouse/mysql-to-sqlite3/master/CODE-OF-CONDUCT.md)
[![PyPI - Format](https://img.shields.io/pypi/format/mysql-to-sqlite3?logo=python)](https://pypi.org/project/mysql-to-sqlite3/)
[![Code style: black](https://img.shields.io/badge/code%20style-black-000000.svg?logo=python)](https://github.com/ambv/black)
[![Codacy Badge](https://api.codacy.com/project/badge/Grade/64aae8e9599746d58d277852b35cc2bd)](https://www.codacy.com/manual/techouse/mysql-to-sqlite3?utm_source=github.com&amp;utm_medium=referral&amp;utm_content=techouse/mysql-to-sqlite3&amp;utm_campaign=Badge_Grade)
[![Test Status](https://github.com/techouse/mysql-to-sqlite3/actions/workflows/test.yml/badge.svg)](https://github.com/techouse/mysql-to-sqlite3/actions/workflows/test.yml)
[![CodeQL Status](https://github.com/techouse/mysql-to-sqlite3/actions/workflows/github-code-scanning/codeql/badge.svg)](https://github.com/techouse/mysql-to-sqlite3/actions/workflows/github-code-scanning/codeql)
[![Publish PyPI Package Status](https://github.com/techouse/mysql-to-sqlite3/actions/workflows/publish.yml/badge.svg)](https://github.com/techouse/mysql-to-sqlite3/actions/workflows/publish.yml)
[![codecov](https://codecov.io/gh/techouse/mysql-to-sqlite3/branch/master/graph/badge.svg)](https://codecov.io/gh/techouse/mysql-to-sqlite3)
[![GitHub Sponsors](https://img.shields.io/github/sponsors/techouse?logo=github)](https://github.com/sponsors/techouse)
[![GitHub stars](https://img.shields.io/github/stars/techouse/mysql-to-sqlite3.svg?style=social&label=Star&maxAge=2592000)](https://github.com/techouse/mysql-to-sqlite3/stargazers)

# 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](https://github.com/techouse/mysql-to-sqlite3/blob/master/.github/workflows/test.yml) 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:

```bash
pip install mysql-to-sqlite3
mysql2sqlite --help
```

On macOS, you can also install with Homebrew:

```bash
brew install mysql-to-sqlite3
mysql2sqlite --help
```

Or run the published Docker image:

```bash
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/`](https://github.com/techouse/mysql-to-sqlite3/tree/master/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.

```bash
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:

```bash
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.

```bash
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.

```bash
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.

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

A common two-step flow is:

```bash
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.

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

Transfer everything except selected tables:

```bash
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:

```bash
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.

```bash
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:

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

Use a client certificate and key:

```bash
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.