sqlbck

Backup MariaDB databases to a single SQLite archive — browse with Datasette, restore when you need to.

Overview

sqlbck exports a MariaDB database into one compressed SQLite file that serves two purposes:

1. Exploration — open the backup in Datasette and query tables in the browser.
2. Restore — rebuild the database on MariaDB from the same file, with row-count verification.

Schema metadata, views, triggers, routines, and restorable table data live inside the SQLite file. There is no separate SQL dump in the default workflow.

Features

• Compatibility check — scans column types and reports what can be round-tripped through SQLite
• Single-artifact backup — {database}.sqlite.zst plus manifest.toml and datasette.yaml
• Lossless restore (for supported types) — original DDL from SHOW CREATE TABLE, typed data encoding, post-restore row-count checks
• Views in metadata — view definitions stored in SQLite _schema
• Skipped tables — unsupported tables (e.g. GEOMETRY) stored as a compressed SQL blob inside _extras (non-strict mode)
• Credential file — MySQL-style [client] option file, same format as mysql --defaults-file
• Legacy restore — older backups with restore.sql.zst still work via --source sql

Requirements

• Python 3.12+
• MariaDB client tools (mariadb / mysql, mariadb-dump / mysqldump) on PATH
• zstd on PATH
• Network access to the source/target MariaDB server

Installation

python -m venv .venv
source .venv/bin/activate
pip install -e ".[serve]"    # includes Datasette for `serve`
# pip install -e ".[dev]"    # adds pytest

With uv:

uv pip install -e ".[serve]"

Quick start

# 1. Check compatibility
sqlbck check -h db.example.com -d myapp -c ./mysql-conf.ini

# 2. Export
sqlbck export -h db.example.com -d myapp -o ./backup-2026-06-19 -c ./mysql-conf.ini --strict

# 3. Browse
sqlbck serve ./backup-2026-06-19 --open

# 4. Restore to a new database
sqlbck restore -f ./backup-2026-06-19 -h db.example.com -d myapp_restored -c ./mysql-conf.ini --drop-database

Authentication

Provide credentials in one of these ways:

Method	Example
Option file (recommended)	-c ./mysql-conf.ini
CLI flags	-u backup --password secret
Environment	export SQLBCK_PASSWORD=secret (requires --user)

User is required — there is no default. Set it via --user or user= in the defaults file.

Password priority: --password → defaults file → SQLBCK_PASSWORD env var.

Example mysql-conf.ini (see mysql-conf.ini.example):

[client]
user=backup_user
password=your_password

CLI flags override values from the defaults file.

Commands

check

Scan the database for SQLite export/restorability. Reports unsupported column types and completeness warnings (procedures, triggers, events).

sqlbck check -h HOST -d DATABASE -c ./mysql-conf.ini
sqlbck check ... --strict          # exit 1 if any table would be skipped
sqlbck check ... -v                # show per-column type mapping

export

Write a backup directory:

backup/
├── manifest.toml
├── datasette.yaml
└── myapp.sqlite.zst

sqlbck export -h HOST -d DATABASE -o ./backup/ -c ./mysql-conf.ini
sqlbck export ... --strict         # fail if unsupported tables exist
sqlbck export ... --zstd-level 10
sqlbck export ... --no-compress-sqlite

Inside the SQLite file:

Table	Purpose
_schema	DDL: tables, views, triggers, procedures, functions, events
_columns	Original MariaDB types and SQLite encoding per column
_import_log	Row counts per table (used for restore verification)
_extras	zstd-compressed SQL dump of skipped tables (if any)
_* tables	Hidden in Datasette

serve

Decompress .sqlite.zst if needed and launch Datasette.

sqlbck serve ./backup/
sqlbck serve ./backup/ --port 8001 --open

restore

Default source is auto: uses SQLite for new backups, falls back to restore.sql.zst for legacy ones.

sqlbck restore -f ./backup/ -h HOST -c ./mysql-conf.ini
sqlbck restore ... -d target_db --drop-database
sqlbck restore ... --source sqlite --strict
sqlbck restore ... --source sql          # legacy backups only

Restore order from SQLite:

1. CREATE TABLE from _schema
2. Insert data (decode from _columns metadata)
3. Apply _extras blob (skipped tables, if present)
4. Create views
5. Create triggers, procedures, functions, events
6. Verify COUNT(*) against _import_log

Strict mode

Use --strict on check and export to refuse backups that would skip tables (e.g. GEOMETRY columns).

Use --strict on restore to refuse partial backups that contain skipped tables.

For production backups where you expect full round-trip fidelity, --strict is the recommended default.

Legacy backups

Older sqlbck backups may include restore.sql.zst instead of (or alongside) the SQLite-only format. Restore them with:

sqlbck restore -f ./old-backup/ --source sql -h HOST -c ./mysql-conf.ini

Development

pip install -e ".[dev]"
pytest

License

MIT — see LICENSE.