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).

On Windows, Miraj Server Manager schedules a full backup of all databases to a chosen folder without any script, keeping the N most recent ones (see 21.10).

18.1 Physical hot backup#

Encrypted data folder (Enterprise and Cluster editions): the copy of an encrypted database stays encrypted and carries the keyring, protected by the passphrase; restoring it requires the same master key. See chapter 24, §24.6.

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:\backups --log

or in miraj_config.xml:

<backup_dir>E:\backups</backup_dir>

The directory is created if it does not exist. It must neither contain nor be located inside the data directory, the vault key directory, the TLS private key directory, the slow query log directory (fixed at startup by an absolute path) or the --secure-file-priv directory (in either direction), and it must not contain the configuration file; otherwise the server refuses to start, naming the two directories. An account with the FILE privilege can therefore neither read the backups with LOAD_FILE, nor drop a forged backup there with SELECT … INTO DUMPFILE. 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 name TO 'folder'
BACKUP DATABASE name1, name2 TO 'folder'
BACKUP ALL DATABASES TO 'folder'

• 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 folder\name. 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';

+----------+-----------------------------------+-------+---------+---------+
| Database | Directory                         | Files | Bytes   | End_lsn |
+----------+-----------------------------------+-------+---------+---------+
| gestion  | E:\sauvegardes\gestion-2026-09-24 |     5 | 1069338 |     208 |
+----------+-----------------------------------+-------+---------+---------+

BACKUP DATABASE gestion, essai TO 'multi';

+----------+------------------------------+-------+---------+---------+
| Database | Directory                    | Files | Bytes   | End_lsn |
+----------+------------------------------+-------+---------+---------+
| gestion  | E:\sauvegardes\multi\gestion |     5 | 1069338 |     208 |
| essai    | E:\sauvegardes\multi\essai   |     2 |  609250 |  608945 |
+----------+------------------------------+-------+---------+---------+

Refusals: non-empty destination directory, error 9018 (Backup directory '…' is not empty); path outside --backup-dir (for example '../x'), error 1290 (The server is running with the --backup-dir option so it cannot execute this statement).

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      backup description, written last
  clients.mrj        one table per .mrj file
  clients.bmrj       table's long BLOBs
  journaux.dmrj      disk table (ENGINE = Aria, MyISAM or DISK), instead of a .mrj
  journaux.dbmrj     long BLOBs of a disk table
  routines.mrp       procedures and functions
  views.mrv          views
  sequences.mrq      sequences (definition and state)
  triggers.mrt       triggers
  events.mre         events
  journal.mrl        log: transactions to replay on the tables at restore

manifest.json indicates:

• the format (2), the source database, the date and the engine version;
• the log positions;
• for each file, its size, its CRC32 checksum (media wear) and its SHA-256 fingerprint (deliberate tampering);
• its signature (signature): HMAC-SHA256 of the manifest, computed with a key derived from the server's account vault key, and the public identifier of that key. The vault key never leaves its directory. A backup made without an open account vault (embedded engine without accounts, offline miraj-backup without access to the keys) is not signed.

All the tables of the database are copied the same way, in memory or on disk: a disk table is backed up, restored and verified (miraj-backup verify) like the others, and the companion table holding the history of a system-versioned table (chapter 11) is part of it. Only the original log, the node's replication state and temporary files are not copied; the log of the copy is rebuilt.

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

18.1.4 RESTORE DATABASE#

RESTORE DATABASE name FROM 'folder' [REPLACE]

1. The name name must be free (error 1007 otherwise, except with REPLACE). The manifest is then read and its signature checked (see below). Each file is copied into a working directory under the root, and its size, checksum and SHA-256 fingerprint are verified along the way. A tampered backup is refused without touching anything.
2. The definers of routines, triggers, events and views are checked (see below).
3. The database is then installed under the name name, then loaded. The log of the copy is replayed, as at a restart.

Signature.

An unsigned backup, or one signed elsewhere, does not prove its origin: only restore backups whose provenance you know, and keep --backup-dir out of reach of FILE accounts (see 18.1.1).

Definers. Routines, triggers, events and views run with the privileges of their definer (DEFINER). As with CREATE DEFINER=…, keeping a definer other than yourself requires SUPER or SET_USER_ID:

• with one of these privileges (for example root), the definers from the backup are kept;
• without them, every object whose definer is another account is reassigned to the account doing the restore (including the CREATE DEFINER=… text of a routine), with one warning 9032 per object ("PROCEDURE p: definer root@localhost replaced by ops@%"). Objects belonging to the restoring account are unchanged.

An account with BACKUP_ADMIN and CREATE therefore cannot, through a backup, install a trigger or a routine that would run with the privileges of root.

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-24';
SHOW WARNINGS;
SELECT id, nom FROM gestion_hier.clients WHERE id = 42;

+--------------+-----------------------------------+-------+---------+---------+
| Database     | Directory                         | Files | Bytes   | End_lsn |
+--------------+-----------------------------------+-------+---------+---------+
| gestion_hier | E:\sauvegardes\gestion-2026-09-24 |     5 | 1069338 |     208 |
+--------------+-----------------------------------+-------+---------+---------+
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                   |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | base gestion restaurée sous le nom gestion_hier : les références qualifiées par l'ancien nom (vues, routines, clés étrangères) le gardent |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
+----+--------+
| id | nom    |
+----+--------+
| 42 | Durand |
+----+--------+

Run again, the same restore returns ERREUR 1007 (HY000) : Can't create database 'gestion_hier'; database exists.

18.1.5 Continuous incremental backup (log archiving)#

Enterprise and Cluster editions. In the Express edition, the settings and the WITH ARCHIVE clause are refused (error 9001).

A full backup freezes the database at one instant; anything written afterwards is only protected by the next backup. Continuous log archiving fills that gap: the server copies every committed change into an archive directory as it happens (in under a second). A restore then starts from a full backup and replays the archive, up to the last archived change or up to a chosen instant (point-in-time recovery).

Enabling.

The three variables are set in miraj_config.xml (starting values) or live with SET GLOBAL, with SUPER, SYSTEM_VARIABLES_ADMIN or BACKUP_ADMIN. SET GLOBAL is not persisted: Miraj Server Manager writes the two settings (see 21.10.3).

SET GLOBAL journal_archive_dir = 'D:\\Miraj archive';
SET GLOBAL journal_archive = ON;
SHOW STATUS LIKE 'Miraj_journal_archive%';

Refused: journal_archive = ON without a directory (1290), with the manual backup policy or on a read-only node; a relative or badly placed directory (1231); a directory removed during archiving (1231).

Contents of the archive directory. One subdirectory per database:

<journal_archive_dir>\gestion\
    archive.json                       current lineage and its cuts
    archive-<lineage>.json             previous lineages
    <lineage>-<LSN>.mra                segments (16 MB or 5 minutes at most each)
    base-YYYYMMDD-hhmmss\              full backups taken by the server itself

Each segment carries the log records, preceded by the bytes of the long BLOBs they cite, and time markers. A segment end truncated by a hard stop is removed at restart, and archiving resumes where it stopped.

Lineages. A lineage is an uninterrupted sequence of changes to a database. A new lineage starts, with a new full backup taken immediately by the server in base-…, when continuity can no longer be guaranteed:

• archiving enabled, or re-enabled after a pause;
• database created, dropped or restored;
• records removed from the log before being archived;
• lag beyond journal_archive_max_lag, for example if the archive disk is full or unplugged. The server reports the incident in its log and stops retaining the log, so as not to fill the data disk.

A clean server shutdown does not break the lineage. After a hard stop, it resumes if the log still contains what had not been archived; otherwise, a new lineage starts.

Cuts (DDL). A DDL statement cannot be replayed: CREATE/ALTER/DROP TABLE, RENAME, indexes, routines, triggers, events, views, OPTIMIZE and REPAIR. Each is recorded as a cut at the log point where it runs. A restore from an earlier backup stops before the first cut that follows it, with a warning. About thirty seconds after a DDL (the time for a series of DDL statements to finish), the server takes a new base in base-…, from which a restore goes past the cut.

Extendable backups. A full backup made while archiving is active (BACKUP, scheduled or not, or a base taken by the server) carries in its manifest the lineage and the number of the next cut ("archive", signed with the rest). A backup made without archiving cannot be extended.

Restoring.

RESTORE DATABASE name FROM 'folder'
    WITH ARCHIVE ['archive folder']
    [UNTIL TIME 'YYYY-MM-DD hh:mm:ss' | UNTIL LSN n]
    [REPLACE]

• WITH ARCHIVE: without a folder, the server's (journal_archive_dir). A given folder must be under backup_dir or under journal_archive_dir.
• FROM also accepts a base taken by the server, under journal_archive_dir.
• UNTIL TIME: state of the database at that server-local time, to the second. These are the changes the archive had received at that instant.
• UNTIL LSN: state at that log position. A position beyond the archive is refused.
• Without a bound: up to the last archived change, or up to the first cut.

The result gives the position reached (End_lsn). A warning reports the range replayed and, if applicable, the DDL statement that stopped it.

-- State of yesterday at 3:30 pm, alongside the database in service
RESTORE DATABASE gestion_1530 FROM 'miraj-2026-09-29_020000\gestion'
    WITH ARCHIVE UNTIL TIME '2026-09-29 15:30:00';

Purging. The archive grows as long as nothing purges it. Miraj Server Manager purges it after each scheduled backup: it keeps what extends the backups that are kept. Outside the manager, delete the segments of a lineage whose next segment's starting LSN is lower than the End_lsn of the oldest backup kept. miraj-backup list --from <database archive folder> shows the lineages and their periods.

Limits.

• Restoring is done database by database; there is no common instant across several databases.
• In a cluster, archiving is set on the primary only (refused on a read-only node).
• A data directory replaced while the server is stopped (offline restore with miraj-backup --root) must be followed by disabling and then re-enabling archiving.

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 <database>[,<database>…] | --all  --to <folder>  [connection | --root <data folder>]
miraj-backup restore --from <folder> --db <name> [--replace]      [connection | --root <data folder>]
                     [--with-archive | --archive <folder>] [--until "YYYY-MM-DD hh:mm:ss" | --until-lsn <n>]
miraj-backup verify  --from <folder> [--deep]
miraj-backup list    --from <folder>

Connection:

Password. A password written on the command line (--password=secret, or --password secret as in previous versions) is visible to other users of the machine in the process list; the tool displays a warning. For interactive use, give --password alone: it is prompted on the keyboard without being displayed. In a scheduled task, define MIRAJ_PASSWORD in the task's environment (or in a file readable only by the service account, 0600 permissions on Linux, loaded before the call). A password that starts with a dash is written --password=-mypassword.

TLS encryption. The rows read or written, and with miraj-dump --users the account verifiers, go through this connection:

Away from the server machine, use --ssl-ca: without certificate verification, an intermediary can impersonate the server, relay the connection and read the data.

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, checksum and SHA-256 fingerprint of each file, as well as the log; the signature, which requires the server's vault key, is only verified at restore;
• verify --deep additionally rereads each table;
• list displays the manifest, signature included.

Offline, a backup is signed if the tool has access to the data directory's vault key (same Windows account as the server, --key-dir); an offline restore reports an unsigned backup or one signed by another key. It keeps the definers (the tool acts as administrator of the directory).

Exit code: 0 if everything succeeded, 1 otherwise.

Example of a daily scheduled task (password in the task's MIRAJ_PASSWORD variable, not on the command line):

miraj-backup backup --all --to daily-%DATE:~6,4%%DATE:~3,2%%DATE:~0,2%

18.3 The miraj-dump tool: SQL backup#

miraj-dump [export] --db <database>[,<database>…] | --all  [-r <file.sql>] [options] [connection | --root <folder>]
miraj-dump restore <file.sql> [-d <database>] [--force] [connection | --root <folder>]

The connection is set with the same options as miraj-backup (section 18.2): --host, --port, --user, --password (masked input), MIRAJ_PASSWORD, TLS offered by default (--ssl, --ssl-ca, --ssl-insecure, --no-ssl) and --protocol auto|miraj|postgresql.

Script language: that of the instance read (recognized at connection time, or from its sql_mode offline). On an instance in PostgreSQL language, the script is written in PostgreSQL language and can also be reloaded with psql -f; routines and triggers are written there in PL/pgSQL (translated as best as possible if they were written in Miraj language, otherwise quoted in a comment, like events), and accounts (--users) lose their SCRAM-SHA-256 verifier until their password is redefined: see 29.12. restore splits the script in the language of the target instance. For a --users export from a remote server, require TLS with --ssl-ca: the account verifiers travel over this connection.

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.

Warning — a --users export is a sensitive file: an account's verifier (IDENTIFIED BY PASSWORD '*…') is not the password, but it stands in for it. The authentication protocol only asks the client for a value that this verifier allows computing: anyone who reads it can connect as that account with a modified client, without ever knowing the password. It is also an unsalted double SHA-1, which is quickly broken if the password is weak. Treat such a script like a password file: read permissions reserved to the administration account (chmod 600 on Unix, a restricted ACL on Windows), never in a source repository or a ticket, encrypted transfer, encrypted archive if it leaves the machine, deleted as soon as it has served. After a leak, change the passwords of the accounts concerned.

Order of the script:

1. Header (SET NAMES utf8mb4, foreign key checks disabled).
2. Accounts.
3. For each database:
   1. sequences (before the tables, since a default value may call one): CREATE SEQUENCE then SETVAL to the next unreserved value;
   2. tables (parents before children) with their rows;
   3. views (in the order of their dependencies);
   4. routines, then REST API endpoints (CREATE ENDPOINT, chapter 23);
   5. triggers (after the rows: they do not fire during loading);
   6. 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.

A disk table is exported with its declared ENGINE (SHOW CREATE TABLE). A system-versioned table is exported with its WITH SYSTEM VERSIONING clause and only its current rows: the history is not in the script (use BACKUP, chapter 11).

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.

Duration: miraj-dump (export and restore) and miraj-backup lift, for their own session, the statement duration limit (max_statement_time, 300 s by default, chapter 15): reading a large table, or a long BACKUP or RESTORE, is not interrupted by error 1969.

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 <database> 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.

-r is also written --result-file, -d also --database.

miraj-cli connected to a server also splits the script (DELIMITER included) and stops at the first error (25.7); opened on a data folder (--root), it does not interpret DELIMITER: use miraj-dump restore … --root instead.

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.