mneme-cli

Mneme is a pair of Bash utilities for creating Git-friendly MySQL database snapshots and restoring them later.

• mneme-dump dumps one MySQL database into a stable directory layout: schema files plus one SQL file per table.
• mneme-restore restores a Mneme dump into the original database name or a different target database.

The scripts were designed with Moodle-style MySQL 8+ databases in mind, but they can be used with other mostly-InnoDB MySQL databases as well.

THIS SCRIPT IS PROVIDED ON AN "AS IS" BASIS WITHOUT ANY WARRANTIES OF ANY KIND. BY USING THIS SCRIPT, THE USER CONFIRMS THEY HAVE CONDUCTED THEIR OWN DUE DILIGENCE TO VERIFY ITS SUITABILITY AND SAFETY. THE USER VOLUNTARILY ACCEPTS FULL RISK ASSUMPTION FOR ANY DAMAGES OR DATA LOSS ARISING FROM ITS USE.

Intention

Important: Mneme is a logical Git-friendly snapshot tool, not a full backup strategy.

I created this tool mainly to assist with taking "diff-able" snapshots of databases then committed to git during development, facilitating rolling back to earlier states, examining database differences when debugging if required, and things of that nature.

While not really intended for backups of application databases for non-development purposes, that is a purpose that I'm interested in looking at though I will probably create an entirely separate tool for that purpose rather than expand this tool.

What's a Mneme??

The name Mneme (pronounced like "nee-me") comes from the Greek concept of memory, which suits a tool that facilitates turning a database state into reviewable historical memory.

Why Mneme?

Normal mysqldump output is often awkward to review in Git because one large SQL file can mix unrelated changes from many tables. Multi-row INSERT statements also make diffs noisy because a single row change can alter a very long line.

Mneme aims to make database snapshots more reviewable by:

• writing one data file per table;
• omitting volatile dump timestamps;
• ordering rows by primary key where MySQL can do so;
• using one INSERT statement per row;
• keeping the default dump directory name stable;
• writing simple manifest and diagnostic files.

This makes it easier to review changes such as Moodle configuration changes, plugin upgrade effects, test fixture changes, and pre/post migration database deltas.

Consistency model

A split-table dump is naturally different from a single mysqldump connection.

By default, Mneme dumps each table using its own mysqldump process. Each process uses --single-transaction, which is good for InnoDB table consistency within that process, but separate processes do not automatically share the exact same transaction snapshot.

Recommended modes:

Situation	Recommended approach
Local/dev/staging database that is not being written to	mneme-dump --jobs N
Moodle site placed into maintenance/read-only mode	mneme-dump --jobs N
Live database where writes may continue and a globally consistent split dump is required	mneme-dump --jobs N --lock-for-consistency

--lock-for-consistency holds FLUSH TABLES WITH READ LOCK while dumping. That gives a better global point-in-time split dump, but it blocks writes until the dump completes.

Requirements

• Bash 4.3 or newer.
• MySQL client tools available in PATH:
  • mysql
  • mysqldump
• MySQL 8+ recommended.
• Sufficient database privileges for the selected objects:
  • base tables;
  • triggers, unless --skip-triggers is used;
  • routines, unless --skip-routines is used;
  • events, unless --skip-events is used.

The scripts use coloured labels for INFO, WARN, ERROR, FATAL, OK, and DONE messages.

The scripts also include a small local checkerr-compatible helper so they remain self-contained. checkerr.inc.sh is a bash include script I use in a lot of my bash scripts to help both give feedback to the user on success/failure of operations within the script and stop the script running if a fatal failure occurs that subsequent operations might depend on.

Note that if you want to use the original checkerr.inc.sh (rather than the internal 'simple' checkerr compatible function, you can source it by setting CHECKERR_INC:

export CHECKERR_INC=/path/to/checkerr.inc.sh

Recommended authentication

Prefer mysql_config_editor login paths or protected option files over command-line passwords.

Example login path:

mysql_config_editor set \
  --login-path=moodlebackup \
  --host=localhost \
  --user=moodleuser \
  --password

Then use:

./mneme-dump --login-path=moodlebackup --database moodledb
./mneme-restore --login-path=moodlebackup --input ./mysql-table-dumps/moodledb

Environment-variable password use is supported for convenience:

MYSQL_PWD='secret' ./mneme-dump -u moodleuser -d moodledb

However, --login-path or --defaults-extra-file is preferable.

Dumping a database

Basic Git-friendly dump:

./mneme-dump \
  --login-path=moodlebackup \
  --database moodledb \
  --output ./db-dumps

Parallel dump with six workers:

./mneme-dump \
  --login-path=moodlebackup \
  --database moodledb \
  --output ./db-dumps \
  --jobs 6

Parallel dump with a global read lock:

./mneme-dump \
  --login-path=moodlebackup \
  --database moodledb \
  --output ./db-dumps \
  --jobs 6 \
  --lock-for-consistency

Skip selected tables:

./mneme-dump \
  --login-path=moodlebackup \
  --database moodledb \
  --ignore-table mdl_logstore_standard_log \
  --ignore-table mdl_task_log

By default, Mneme writes to a stable directory:

./mysql-table-dumps/moodledb/

Use --dated only when you intentionally want archival timestamped directories:

./mneme-dump --login-path=moodlebackup -d moodledb --dated

Timestamped directories are less Git-friendly because every run creates a new tree.

Dump layout

A typical dump looks like this:

moodledb/
├── README.md
├── manifest.env
├── tables.tsv
├── diagnostics/
│   └── no_unique_key_tables.txt
├── schema/
│   ├── 00-tables-and-views.sql
│   └── 90-routines-events-triggers.sql
└── tables/
    ├── mdl_config.sql
    ├── mdl_course.sql
    ├── mdl_user.sql
    └── ...

Key files:

Path	Purpose
manifest.env	Basic metadata used by the restore script.
tables.tsv	Maps original table names to table data files.
schema/00-tables-and-views.sql	Table and view definitions.
tables/*.sql	Data-only table dumps, one file per base table.
schema/90-routines-events-triggers.sql	Routines, events, and triggers, when enabled.
diagnostics/no_unique_key_tables.txt	Tables where row order may be less deterministic.

Tables without a primary key or unique key can still produce noisy diffs because MySQL has no stable key for --order-by-primary to use.

Restoring a dump

Restore to the database recorded in manifest.env:

./mneme-restore \
  --login-path=moodlebackup \
  --input ./db-dumps/moodledb

Restore into a clean local development database:

./mneme-restore \
  --login-path=moodlebackup \
  --input ./db-dumps/moodledb \
  --database moodle_dev \
  --drop-database \
  --jobs 6

Restore schema and table data, but skip routines/events/triggers:

./mneme-restore \
  --login-path=moodlebackup \
  --input ./db-dumps/moodledb \
  --database moodle_dev \
  --drop-database \
  --skip-objects

--skip-objects is useful when object restore fails due to DEFINER, routine, trigger, event, or privilege issues.

Restore ordering

Restore runs in this order:

1. create the target database if needed;
2. import schema/00-tables-and-views.sql;
3. import tables/*.sql, optionally in parallel;
4. import schema/90-routines-events-triggers.sql, unless skipped.

Triggers are imported after table data so they do not fire during the table data reload.

Parallel table import disables FOREIGN_KEY_CHECKS and UNIQUE_CHECKS per import session to reduce dependency-order problems and improve loading performance.

Git workflow

A simple workflow:

./mneme-dump --login-path=moodlebackup -d moodledb -o ./db-dumps -j 6
cd ./db-dumps/moodledb
git status
git diff

Suggested .gitignore entries for the repository that stores Mneme output:

# local restore logs
mysql-restore-logs/

# optional compressed/archive files
*.sql.gz
*.sql.bz2
*.tar.gz

# local editor files
.DS_Store
*.swp

Avoid compressing the per-table SQL files if your main goal is readable Git diffs.

Moodle-specific notes

Useful Moodle tables to consider excluding in review-only snapshots may include very large or high-churn logs, depending on the purpose of the dump:

--ignore-table mdl_logstore_standard_log
--ignore-table mdl_task_log

Do not exclude tables blindly for a restore that is meant to reproduce a working Moodle site. Log tables may be optional for many analysis workflows, but other high-volume tables can still be functionally important.

For a Moodle instance in use by more than one user prefer one of these before dumping:

• put Moodle into maintenance mode;
• stop cron and other write-producing integrations temporarily;
• use --lock-for-consistency and accept the write-blocking impact;
• use Mneme only as a secondary diff artifact, while relying on your normal backup system for disaster recovery.

Common issues

mysqldump: Access denied; you need PROCESS privilege

The dump script uses --no-tablespaces to avoid common MySQL 8 tablespace privilege issues. If you still see privilege errors, check the user’s privileges for the database and selected object types.

Routines/events/triggers fail to dump or restore

Try excluding object types on dump:

./mneme-dump --login-path=moodlebackup -d moodledb --skip-routines --skip-events --skip-triggers

Or skip object restore:

./mneme-restore --login-path=moodlebackup -i ./db-dumps/moodledb --skip-objects

Diffs are still noisy

Check:

• whether the table appears in diagnostics/no_unique_key_tables.txt;
• whether the application updates timestamp fields frequently;
• whether the table contains log/session/cache-style data;
• whether volatile tables should be excluded for your review use case.

Restore fails because objects already exist

Restore into a clean database, or use:

./mneme-restore --input ./db-dumps/moodledb --database moodle_dev --drop-database

Safety checklist before first real use

ie. so you don't inadvertently wreck your dev environment off the bat - I know what a pain it can be to have to set things back up again, so please make sure you know what you're doing and perform some tests before working with it on anything important.

1. Run a dump against a disposable development database.
2. Restore into a separate disposable database name.
3. Compare row counts between source and restored databases.
4. Test Moodle (or whatever you're working on) can start against the restored database if that is part of your use case.
5. Review diagnostics/no_unique_key_tables.txt.
6. Commit only the SQL dump files you intentionally want in Git.
7. Do NOT rely on Mneme as your backup plan.

License

This script is licenced under the GNU Affero General Public License v3.0