# Backup and Restore

Backing up your tables on a regular basis is essential for recovery in the event of system crashes, hardware failure, or data corruption/loss. It's also highly recommended to make backups before upgrading to a new Manticore Search version or running [ALTER TABLE](../Updating_table_schema_and_settings.md#Updating-table-schema-in-RT-mode).

Backing up database systems can be done in two unique ways: logical and physical backups. Each of these methods has its pros and cons, which may vary based on the specific database environment and needs. Here, we'll delve into the distinction between these two types of backups.

### Logical Backups

Logical backups entail exporting the database schema and data as SQL statements or as data formats specific to the database. This backup form is typically readable by humans and can be employed to restore the database on various systems or database engines.

Pros and cons of logical backups:
- ➕ **Portability:** Logical backups are generally more portable than physical backups, as they can be used to restore the database on different hardware or operating systems.
- ➕ **Flexibility:** Logical backups allow you to selectively restore specific tables, indexes, or other database objects.
- ➕ **Compatibility:** Logical backups can be used to migrate data between different database management systems or versions, provided the target system supports the exported format or SQL statements.
- ➖ **Slower Backup and Restore:** Logical backups can be slower than physical backups, as they require the database engine to convert the data into SQL statements or another export format.
- ➖ **Increased System Load:** Creating logical backups can cause higher system load, as the process requires more CPU and memory resources to process and export the data.

Manticore Search supports [mysqldump](../Securing_and_compacting_a_table/Backup_and_restore.md#Backup-and-restore-with-mysqldump) for logical backups.

### Physical Backups

Physical backups involve copying the raw data files and system files that comprise the database. This type of backup essentially creates a snapshot of the database's physical state at a given point in time.

Pros and cons of physical backups:
- ➕ **Speed:** Physical backups are usually faster than logical backups, as they involve copying raw data files directly from disk.
- ➕ **Consistency:** Physical backups ensure a consistent backup of the entire database, as all related files are copied together.
- ➕ **Lower System Load:** Creating physical backups generally places less load on the system compared to logical backups, as the process does not involve additional data processing.
- ➖ **Portability:** Physical backups are typically less portable than logical backups, as they may be dependent on the specific hardware, operating system, or database engine configuration.
- ➖ **Flexibility:** Physical backups do not allow for the selective restoration of specific database objects, as the backup contains the entire database's raw files.
- ➖ **Compatibility:** Physical backups cannot be used to migrate data between different database management systems or versions, as the raw data files may not be compatible across different platforms or software.

Manticore Search has [manticore-backup](../Securing_and_compacting_a_table/Backup_and_restore.md#Using-manticore-backup-command-line-tool) command line tool for physical backups.

In summary, logical backups provide more flexibility, portability, and compatibility but can be slower and more resource-intensive, while physical backups are faster, more consistent, and less resource-intensive but may be limited in terms of portability and flexibility. The choice between these two backup methods will depend on your specific database environment, hardware, and requirements.

## Using manticore-backup command line tool

The `manticore-backup` tool, included in the official Manticore Search [packages](https://manticoresearch.com/install), automates the process of backing up tables for an instance running in [RT mode](../Read_this_first.md#Real-time-mode-vs-plain-mode).

### Installation

**If you followed [the official installation instructions](https://manticoresearch.com/install/), you should already have everything installed and don't need to worry.** Otherwise, [`manticore-backup`](https://github.com/manticoresoftware/manticoresearch-backup) requires PHP 8.1.10 and [specific modules](https://github.com/manticoresoftware/executor/blob/main/build-linux) or [`manticore-executor`](https://github.com/manticoresoftware/executor), and you need to ensure that one of these is available.

Note that `manticore-backup` is not available for Windows yet.

### How to use

First, make sure you're running `manticore-backup` on the same server where the Manticore instance you are about to back up is running.

Second, we recommend running the tool under the `root` user so the tool can transfer ownership of the files you are backing up. Otherwise, a backup will be also made but with no ownership transfer. In either case, you should make sure that `manticore-backup` has access to the data dir of the Manticore instance.

<!-- example backup1 -->

The only required argument for `manticore-backup` is `--backup-dir`, which specifies the destination for the backup. If you don't provide any additional arguments, `manticore-backup` will:
- locate a Manticore instance running with the default configuration
- create a subdirectory in the `--backup-dir` directory with a timestamped name
- backup all tables found in the instance

<!-- request Example -->
```bash
manticore-backup --config=path/to/manticore.conf --backup-dir=backupdir
```

<!-- response Example -->
```bash
Copyright (c) 2023-2024, Manticore Software LTD (https://manticoresearch.com)

Manticore config file: /etc/manticoresearch/manticore.conf
Tables to backup: all tables
Target dir: /mnt/backup/

Manticore config
  endpoint =  127.0.0.1:9308

Manticore versions:
  manticore: 5.0.2
  columnar: 1.15.4
  secondary: 1.15.4
2022-10-04 17:18:39 [Info] Starting the backup...
2022-10-04 17:18:39 [Info] Backing up config files...
2022-10-04 17:18:39 [Info]   config files - OK
2022-10-04 17:18:39 [Info] Backing up tables...
2022-10-04 17:18:39 [Info]   pq (percolate) [425B]...
2022-10-04 17:18:39 [Info]    OK
2022-10-04 17:18:39 [Info]   products (rt) [512B]...
2022-10-04 17:18:39 [Info]    OK
2022-10-04 17:18:39 [Info] Running sync
2022-10-04 17:18:42 [Info]  OK
2022-10-04 17:18:42 [Info] You can find backup here: /mnt/backup/backup-20221004171839
2022-10-04 17:18:42 [Info] Elapsed time: 2.76s
2022-10-04 17:18:42 [Info] Done
```
<!-- end -->

<!-- example backup2 -->
To back up specific tables only, use the `--tables` flag followed by a comma-separated list of tables, for example `--tables=tbl1,tbl2`. This will only backup the specified tables and ignore the rest.

<!-- request Example -->
```bash
manticore-backup --backup-dir=/mnt/backup/ --tables=products
```

<!-- response Example -->
```bash
Copyright (c) 2023-2024, Manticore Software LTD (https://manticoresearch.com)

Manticore config file: /etc/manticoresearch/manticore.conf
Tables to backup: products
Target dir: /mnt/backup/

Manticore config
  endpoint =  127.0.0.1:9308

Manticore versions:
  manticore: 5.0.3
  columnar: 1.16.1
  secondary: 0.0.0
2022-10-04 17:25:02 [Info] Starting the backup...
2022-10-04 17:25:02 [Info] Backing up config files...
2022-10-04 17:25:02 [Info]   config files - OK
2022-10-04 17:25:02 [Info] Backing up tables...
2022-10-04 17:25:02 [Info]   products (rt) [512B]...
2022-10-04 17:25:02 [Info]    OK
2022-10-04 17:25:02 [Info] Running sync
2022-10-04 17:25:06 [Info]  OK
2022-10-04 17:25:06 [Info] You can find backup here: /mnt/backup/backup-20221004172502
2022-10-04 17:25:06 [Info] Elapsed time: 4.82s
2022-10-04 17:25:06 [Info] Done
```
<!-- end -->

### Arguments

| Argument | Description |
|-|-|
| `--backup-dir=path` | Path to the backup directory where the backup will be stored. The directory must already exist. This argument is required and has no default value. On each backup run, `manticore-backup` will create a subdirectory in the provided directory with a timestamp in the name (`backup-[datetime]`), and will copy all required tables to it. So the `--backup-dir` is a container for all your backups, and it's safe to run the script multiple times. Supports S3 URLs in the format `s3://bucket/prefix` — see [S3 storage support](../Securing_and_compacting_a_table/Backup_and_restore.md#S3-storage-support) for details.|
| `--restore[=backup]` | Restore from `--backup-dir`. Just `--restore` lists available backups. `--restore=backup` will restore from `<--backup-dir>/backup`. Works with both local paths and S3 URLs. |
| `--force` | Skip versions check on restore and gracefully restore the backup. |
| `--disable-telemetry` | Pass this flag in case you want to disable sending anonymized metrics  to Manticore. You can also use environment variable TELEMETRY=0 |
| `--config=/path/to/manticore.conf` | Path to the Manticore configuration. Optional. If not provided, a default configuration for your operating system will be used. Used to determine the host and port for communication with the Manticore daemon. The `manticore-backup` tool supports [dynamic configuration](../Server_settings/Scripted_configuration.md) files. You can specify the `--config` option multiple times if your configuration is spread across multiple files. |
| `--tables=tbl1,tbl2, ...` | Semicolon-separated list of tables that you want to back up. To back up all tables, omit this argument. All the provided tables must exist in the Manticore instance you are backing up from, or the backup will fail. |
| `--compress` | Whether the backed up files should be compressed. Not enabled by default. | optional |
| `--unlock` | In rare cases when something goes wrong, tables can be left in a locked state. Use this argument to unlock them. |
| `--version` | Show the current version. |
| `--help` | Show this help. |


## S3 storage support

`manticore-backup` supports storing and restoring backups directly to/from S3-compatible storage, including AWS S3, MinIO, Wasabi, Cloudflare R2, and others. Simply pass an `s3://bucket/prefix` URL as `--backup-dir`.

### Configuration

Set the following environment variables before running `manticore-backup`:

| Variable | Required | Description |
|----------|----------|-------------|
| `AWS_ACCESS_KEY_ID` | Yes | AWS access key (or `AWS_ACCESS_KEY`) |
| `AWS_SECRET_ACCESS_KEY` | Yes | AWS secret key (or `AWS_SECRET_KEY`) |
| `AWS_REGION` | No | AWS region (default: `us-east-1`) |
| `AWS_ENDPOINT_URL` | No | Custom S3-compatible endpoint — **server URL only, no bucket name** (e.g., `http://localhost:9000`) |
| `AWS_S3_ENCRYPTION` | No | Enable SSE-S3 server-side encryption (default: `1` for AWS S3; set to `0` for MinIO, R2, or other custom endpoints) |

> ⚠️ `AWS_ENDPOINT_URL` must not include the bucket name. The bucket is taken from the `s3://bucket/prefix` argument. Including the bucket in the endpoint URL causes it to be doubled in every request and results in errors.
>
> ```
> # Wrong — bucket "mybucket" duplicated in URL path
> AWS_ENDPOINT_URL=https://account.r2.cloudflarestorage.com/mybucket
>
> # Correct — endpoint is the server only
> AWS_ENDPOINT_URL=https://account.r2.cloudflarestorage.com
> ```

### Usage examples

```bash
# Backup to AWS S3 (SSE-S3 encryption enabled by default)
export AWS_ACCESS_KEY_ID=your_key
export AWS_SECRET_ACCESS_KEY=your_secret
manticore-backup --backup-dir=s3://my-bucket/backups

# Restore from S3
manticore-backup --restore --backup-dir=s3://my-bucket/backups
manticore-backup --restore=backup-20221004171839 --backup-dir=s3://my-bucket/backups

# Use with MinIO (disable encryption)
export AWS_ACCESS_KEY_ID=minioadmin
export AWS_SECRET_ACCESS_KEY=minioadmin
export AWS_ENDPOINT_URL=http://localhost:9000
export AWS_S3_ENCRYPTION=0
manticore-backup --backup-dir=s3://my-bucket/backups

# Use with Cloudflare R2
export AWS_ACCESS_KEY_ID=your_r2_access_key
export AWS_SECRET_ACCESS_KEY=your_r2_secret_key
export AWS_ENDPOINT_URL=https://<account_id>.r2.cloudflarestorage.com
export AWS_REGION=auto
export AWS_S3_ENCRYPTION=0
manticore-backup --backup-dir=s3://my-bucket/backups
```

### Required S3 permissions

The backup requires `s3:PutObject`, restore requires `s3:GetObject`, and listing available restore points requires `s3:ListBucket`.

## BACKUP SQL command reference

You can also back up your data through SQL by running the simple command `BACKUP TO /path/to/backup`.

> NOTE: `BACKUP` is not supported in Windows. Consider using [mysqldump](../Securing_and_compacting_a_table/Backup_and_restore.md#Backup-and-restore-with-mysqldump) instead.

> NOTE: `BACKUP` requires [Manticore Buddy](../Installation/Manticore_Buddy.md). If it doesn't work, make sure Buddy is installed.

### General syntax of BACKUP

```sql
BACKUP
  [{TABLE | TABLES} a[, b]]
  TO path_to_backup
  [{OPTION | OPTIONS}
    async = {on | off | 1 | 0 | true | false | yes | no}
    [, compress = {on | off | 1 | 0 | true | false | yes | no}]
  ]
```

For instance, to back up tables `a` and `b` to the `/backup` directory, run the following command:

```sql
BACKUP TABLES a, b TO /backup
```

There are options available to control and adjust the backup process, such as:

* `async`: makes the backup non-blocking, allowing you to receive a response with the backup path immediately and run other queries while the backup is ongoing. The default value is `0`.
* `compress`: enables file compression using zstd. The default value is `0`.

For example, to run a backup of all tables in async mode with compression enabled to the `/tmp` directory:

```sql
BACKUP TO /tmp OPTION async = yes, compress = yes
```

### Async backup behavior

When using `async = 1` (or `yes`, `on`, `true`), the backup operation runs in a background task:

* The command returns immediately with the backup path
* You can continue running other queries while the backup is in progress
* The backup task runs in a separate thread managed by Manticore Buddy
* While running, the backup task will appear in `SHOW QUERIES` output and will be removed automatically when complete

**Example of async backup:**

```sql
BACKUP TO /tmp/mybackup OPTION async = 1
```

This will return immediately with output like:
```
+----------------------------------+
| Path                             |
+----------------------------------+
| /tmp/mybackup/backup-20221004... |
+----------------------------------+
```

You can check if the backup is still running by using `SHOW QUERIES`. Once complete, the task will disappear from the queries list and all backup files will be present in the specified directory.

### Important considerations

1. The backup path can contain spaces if enclosed in single quotes, e.g., `BACKUP TO '/path/with spaces'`
2. Paths without spaces don't require quotes: `BACKUP TO /tmp/backup`
3. Windows paths are supported: `BACKUP TO 'C:\path'` or `BACKUP TO C:\windows\backup`
4. Ensure that Manticore Buddy is launched (it is by default)
5. The backup directory must exist and be writable by the Manticore process

### How backup maintains consistency of tables

To ensure consistency of tables during backup, Manticore Search's backup tools use the innovative [FREEZE and UNFREEZE](../Securing_and_compacting_a_table/Freezing_and_locking_a_table.md) commands. Unlike the traditional lock and unlock tables feature of e.g. MySQL, `FREEZE` stops flushing data to disk while still permitting writing (to some extent) and selecting updated data from the table.

However, if your RAM chunk size grows beyond the `rt_mem_limit` threshold during lengthy backup operations involving many inserts, data may be flushed to disk, and write operations will be blocked until flushing is complete. Despite this, the tool maintains a balance between table locking, data consistency, and database write availability while the table is frozen.

When you use `manticore-backup` or the SQL `BACKUP` command, the `FREEZE` command is executed once and freezes all tables you are backing up simultaneously. The backup process subsequently backs up each table one by one, releasing the freeze after successfully backing up each table.

If backup fails or gets interrupted, the tool tries to unfreeze all the tables.

## Restore by using manticore-backup tool

<!-- example restore_list -->
To restore a Manticore instance from a backup, use the `manticore-backup` command with the `--backup-dir` and `--restore` arguments. For example: `manticore-backup --backup-dir=/path/to/backups --restore`. If you don't provide any argument for `--restore`, it will simply list all the backups in the `--backup-dir`.

<!-- request Example -->
```bash
manticore-backup --backup-dir=/mnt/backup/ --restore
```

<!-- response Example -->

```bash
Copyright (c) 2023-2024, Manticore Software LTD (https://manticoresearch.com)

Manticore config file:
Backup dir: /tmp/

Available backups: 3
  backup-20221006144635 (Oct 06 2022 14:46:35)
  backup-20221006145233 (Oct 06 2022 14:52:33)
  backup-20221007104044 (Oct 07 2022 10:40:44)
```

<!-- end -->

<!-- example restore -->

To start a restore job, run `manticore-backup` with the flag `--restore=backup name`, where `backup name` is the name of the backup directory within the `--backup-dir`. Note that:
1. There can't be any Manticore instance running on the same host and port as the one being restored.
2. The old `manticore.json` file must not exist.
3. The old configuration file must not exist.
4. The old data directory must exist and be empty.

If all conditions are met, the restore will proceed. The tool will provide hints, so you don't have to memorize them. It's crucial to avoid overwriting existing files, so make sure to remove them prior to the restore if they still exist. Hence all the conditions.

<!-- request Example -->
```bash
manticore-backup --backup-dir=/mnt/backup/ --restore=backup-20221007104044
```

<!-- response Example -->

```bash
Copyright (c) 2023-2024, Manticore Software LTD (https://manticoresearch.com)

Manticore config file:
Backup dir: /tmp/
2022-10-07 11:17:25 [Info] Starting to restore...

Manticore config
  endpoint =  127.0.0.1:9308
2022-10-07 11:17:25 [Info] Restoring config files...
2022-10-07 11:17:25 [Info]   config files - OK
2022-10-07 11:17:25 [Info] Restoring state files...
2022-10-07 11:17:25 [Info]   config files - OK
2022-10-07 11:17:25 [Info] Restoring data files...
2022-10-07 11:17:25 [Info]   config files - OK
2022-10-07 11:17:25 [Info] The backup '/tmp/backup-20221007104044' was successfully restored.
2022-10-07 11:17:25 [Info] Elapsed time: 0.02s
2022-10-07 11:17:25 [Info] Done
```

<!-- end -->

## Backup and restore with mysqldump

Manticore supports `mysqldump` utility from MySQL up to 26.7 and `mariadb-dump` utility from MariaDB up to 12.3.

<!-- example mysqldump_backup -->

> NOTE: some versions of `mysqldump` / `mariadb-dump` require [Manticore Buddy](../Installation/Manticore_Buddy.md). If the dump isn't working, make sure Buddy is installed.

To create a backup of your Manticore Search database, you can use the `mysqldump` command. We will use the default port and host in the examples.

Note, `mysqldump` is supported only for real-time tables.

<!-- request Basic -->
```bash
mysqldump -h0 -P9306 manticore > manticore_backup.sql
mariadb-dump -h0 -P9306 manticore > manticore_backup.sql
```

Executing this command will produce a backup file named `manticore_backup.sql`. This file will hold all data and table schemas.

<!-- request Replace mode -->
```bash
mysqldump -h0 -P9306 --replace --net-buffer-length=16m -etc manticore tbl > tbl.sql
```

This will produce a backup file `tbl.sql` with `replace` commands instead of `insert`, with column names retained in each batch. Documents will be batched up to 16 megabytes large. There will be no `drop`/`create table` commands. This is useful for full-text reindexation after changing tokenization settings.

<!-- request Replication mode -->
```bash
mysqldump -etc --replace -h0 -P9306 -ucluster manticore --skip-lock-tables cluster:tbl | mysql -P9306 -h0
mariadb-dump -etc --replace -h0 -P9306 -ucluster manticore --skip-lock-tables cluster:tbl | mysql -P9306 -h0
```

In this case, `mysqldump` will generate commands like `REPLACE INTO cluster:table ...`, which will be sent directly to the Manticore instance, resulting in the documents being reinserted.
Use the `cluster` user and the `-t` flag to enable replication mode. See the details in the notes below.

<!-- end -->

To apply changed full-text settings to existing documents, see [Reindexing existing documents after changing FT settings](../Updating_table_schema_and_settings.md#Reindexing-existing-documents-after-changing-FT-settings).

When piping a dump directly into `mysql` to reindex the same table, `--skip-lock-tables` is required. It is not required when exporting to a file and replaying it after `mysqldump` exits.

<!-- example mysqldump_restore -->
### Restore

If you're looking to restore a Manticore Search database from a backup file, the mysql client is your tool of choice.

Note, if you are restoring in [Plain mode](../Read_this_first.md#Real-time-mode-vs-plain-mode), you cannot drop and recreate tables directly. Therefore, you should:
- Use `mysqldump` with the `-t` option to exclude `CREATE TABLE` statements from your backup.
- Manually [TRUNCATE](../Emptying_a_table.md) the tables before proceeding with the restoration.

<!-- request SQL -->
```bash
mysql -h0 -P9306 < manticore_backup.sql
mariadb -h0 -P9306 < manticore_backup.sql
```

This command enables you to restore everything from the `manticore_backup.sql` file.
<!-- end -->

### Additional options

Here are some more settings that can be used with mysqldump to tailor your backup:

- `-t` skips `drop`/`create` table commands. Use it when reindexing a table after changing full-text settings.
- `--no-data`: This setting omits table data from the backup, resulting in a backup file that consists only of table schemas.
- `--ignore-table=[database_name].[table_name]`: This option allows you to bypass a particular table during the backup operation. Note that the database name must be `manticore`.
- `--replace` to perform `replace` instead of `insert`. Use it when reindexing a table after changing full-text settings.
- `--net-buffer-length=16M` to make batches up to 16 megabytes large for faster restoration.
- `-e` to batch up documents. Useful for faster restoration.
- `-c` to keep column names. Useful for reindexation of a table after changing its schema (e.g., changing field order).

For a comprehensive list of settings and their thorough descriptions, kindly refer to the official [MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html) or [MariaDB documentation](https://mariadb.com/kb/en/mariadb-dump/).

### Notes

* To create a dump in replication mode (where the dump includes `INSERT/REPLACE INTO <cluster_name>:<table_name>`):
  - Make sure the table isn't changed while it's being dumped.
  - Use the `cluster` user. For example: `mysqldump -u cluster ...` or `mariadb-dump -u cluster ...`. You can change the username that enables replication mode for `mysqldump` by running `SET GLOBAL cluster_user = new_name`.
  - Use the `-t` flag.
  - Use the `--skip-lock-tables` flag.
  - When specifying a table in replication mode, you need to follow the `cluster_name:table_name` syntax. For example: `mysqldump -P9306 -h0 -t -ucluster manticore cluster:tbl`.
* It's recommended to explicitly specify the `manticore` database when you plan to back up all databases, instead of using the `--all-databases` option.
* Note that `mysqldump` does not support backing up distributed tables and cannot back up tables containing non-stored fields. For such cases, consider using `manticore-backup` or the `BACKUP` SQL command. If you have distributed tables, it is recommended to always specify the tables to be dumped.

<!-- proofread -->
