18. Backup and restore: BACKUP, miraj-backup, miraj-dump

MIRAJ offers two complementary ways to back up a database:

Rule of thumb: back up every day with BACKUP DATABASE (fast, immediate restore) and keep a miraj-dump --users export from time to time (portable, readable, independent of the version).

18.1 Physical hot backup#

18.1.1 Enabling: --backup-dir#

Both statements are refused (error 1290) as long as the server has no backup directory:

miraj-server --root D:\miraj\data --backup-dir E:\sauvegardes --log

or in miraj_config.xml:

<backup_dir>E:\sauvegardes</backup_dir>

The directory is created if it does not exist. It must neither contain nor be located inside the data directory or the vault key directory. All SQL backups and restores take place there: a relative path is relative to it, an absolute path must be located inside it, and .. is refused. Preferably place it on a different disk from the data.

18.1.2 BACKUP DATABASE#

BACKUP DATABASE nom TO 'dossier'
BACKUP DATABASE nom1, nom2 TO 'dossier'
BACKUP ALL DATABASES TO 'dossier'

• The destination directory is created; if it exists, it must be empty.
• With several databases (list or ALL DATABASES), each database has its own subdirectory dossier\nom. ALL DATABASES excludes the system database.
• The result has one row per database: Database, Directory, Files, Bytes, End_lsn (log position reached by the copy).
• Required privilege: BACKUP_ADMIN.

BACKUP DATABASE gestion TO 'gestion-2026-09-24';

What happens during the copy. The backup waits for in-progress file writes to finish, then briefly holds all the tables of the database for reading. During this short moment, on the order of a few milliseconds, writes wait and reads continue. The backup then creates a second link to each file in a working directory and records the log position. Everything is then released. The copy proper takes place without blocking anything, while clients keep writing.

The result is a clean cut: all transactions committed before the frozen phase are in the backup, none of those committed after. A transaction is never half in it. With several databases, each is cut at its own instant: a backup of several databases is not a common snapshot.

Special cases:

• A transaction open in the session that issues BACKUP is committed first, as before a DDL statement.
• BACKUP is refused under the session's own LOCK TABLES and inside a routine (1314). A table held by the LOCK TABLES ... WRITE of another session makes the backup fail (table busy).
• With save_policy = manual (no log), tables are first written to disk, with every other statement suspended for the duration of this write. A transaction open on a table then prevents the backup.
• An incomplete destination directory (server stopped during the copy) has no manifest.json: it cannot be restored and may be deleted. The working directory, if any (<root>\.backup-…), is deleted at the next restart.

18.1.3 Contents of a backup#

gestion-2026-09-24\
  manifest.json      description de la sauvegarde, écrite en dernier
  clients.mrj        une table par fichier .mrj
  clients.bmrj       BLOB longs de la table
  routines.mrp       procédures et fonctions
  views.mrv          vues
  triggers.mrt       déclencheurs
  events.mre         événements
  journal.mrl        journal : transactions à rejouer sur les tables à la restauration

manifest.json indicates:

• the source database, the date and the engine version;
• the log positions;
• for each file, its size and its checksum (CRC32).

The backup is not compressed: compress the directory with the tool of your choice if needed.

18.1.4 RESTORE DATABASE#

RESTORE DATABASE nom FROM 'dossier' [REPLACE]

1. The manifest is read. Each file is copied into a working directory under the root, and its size and checksum are verified along the way. A corrupted backup is refused without touching anything.
2. The database is then installed under the name nom, then loaded. The log of the copy is replayed, as at a restart.

The name may differ from that of the backed-up database: this is how you restore alongside the database in service to compare or recover rows. A warning (SHOW WARNINGS) reminds you of this. References qualified by the old name in views, routines or foreign keys to another database keep the old name.

• Name already taken: error 1007, except with REPLACE, which first drops the existing database, like DROP DATABASE. Rows held by an open transaction prevent the replacement.
• Invalid name or system database: error 1102.
• Required privileges: BACKUP_ADMIN and CREATE, plus DROP with REPLACE.
• Cluster: refused on a secondary node and in a multi-node cluster. A restored database is not transmitted to the secondaries: restore on a standalone node.
• Shutdown during the restore: at restart, the database is either absent or complete, never half installed. The working directory (<root>\.restore-…) is deleted.

RESTORE DATABASE gestion_hier FROM 'gestion-2026-09-23';
SELECT * FROM gestion_hier.clients WHERE id = 42;

18.2 The miraj-backup tool#

miraj-backup drives the same operations from the command line, for example in a scheduled task.

miraj-backup backup  --db <base>[,<base>…] | --all  --to <dossier>  [connexion | --root <dossier de données>]
miraj-backup restore --from <dossier> --db <nom> [--replace]      [connexion | --root <dossier de données>]
miraj-backup verify  --from <dossier> [--deep]
miraj-backup list    --from <dossier>

Connection:

Offline: --root <data directory> and, if the vault key is not at its default location, --key-dir. If a server holds the directory, the tool reports it and recommends network mode.

verify and list read a local backup directory, without a server:

• verify checks the presence, size and checksum of each file, as well as the log;
• verify --deep additionally rereads each table;
• list displays the manifest.

Exit code: 0 if everything succeeded, 1 otherwise.

Example of a daily scheduled task:

miraj-backup backup --all --to quotidien-%DATE:~6,4%%DATE:~3,2%%DATE:~0,2% --password %MIRAJ_PWD%

18.3 The miraj-dump tool: SQL backup#

miraj-dump [export] --db <base>[,<base>…] | --all  [-r <fichier.sql>] [options] [connexion | --root <dossier>]
miraj-dump restore <fichier.sql> [-d <base>] [--force] [connexion | --root <dossier>]

18.3.1 Export#

The script is written to standard output, or to the -r file. The file is first written under a temporary name, then renamed at the end: an interrupted export does not leave a truncated script.

Order of the script:

1. Header (SET NAMES utf8mb4, foreign key checks disabled).
2. Accounts.
3. For each database:
   1. tables (parents before children) with their rows;
   2. views (in the order of their dependencies);
   3. routines;
   4. triggers (after the rows: they do not fire during loading);
   5. events.
4. Account privileges.

Values are written as follows:

• BLOBs and binary columns in hexadecimal (X'…');
• strings escaped;
• dates and times with their fractional seconds;
• numbers as the server returns them.

Consistency: by default, all reading is done within a single transaction. The script therefore reflects a single instant, without blocking other sessions. A DDL statement executed during the export on a table not yet read makes the export fail (1213): rerun it.

18.3.2 Restore#

miraj-dump restore reads the script as a stream, even for several gigabytes, and executes its statements one by one. The bodies of routines, triggers and events are enclosed in DELIMITER ;; in the script, and the tool understands this command.

• -d <base> selects the current database at the start.
• --force continues after an error. Otherwise, the restore stops at the first error, indicating the line of the script.

miraj-cli does not interpret DELIMITER: for a script produced by miraj-dump, use miraj-dump restore.

18.4 Which method to choose?#

In all cases, test your restores regularly: miraj-backup verify --deep checks a physical backup. A restore under another name followed by a few comparisons checks the whole chain.