# Partial Backup and Restore (mariadb-backup)

{% hint style="info" %}
mariadb-backup was previously called mariabackup.
{% endhint %}

When using `mariadb-backup`, you have the option of performing partial backups. Partial backups allow you to choose which databases or tables to backup, as long as the table or partition involved is in an InnoDB file-per-table tablespace.This page documents how to perform partial backups.

{% hint style="info" %}
For a complete list of `mariadb-backup` options, [see this page](https://mariadb.com/docs/server/server-usage/backup-and-restore/mariadb-backup/mariadb-backup-options).

For a detailed description of `mariadb-backup` functionality, [see this page](https://mariadb.com/docs/server/server-usage/backup-and-restore/mariadb-backup/mariadb-backup-overview).
{% endhint %}

### Backing up the Database Server

Just like with full backups, in order to back up the database, you need to run mariadb-backup with the `--backup` option to tell it to perform a backup and with the `--target-dir` option to tell it where to place the backup files. The target directory must be empty or not exist.

For a partial backup, there are a few other arguments that you can provide as well:

* To tell it which databases to backup, you can provide the `--databases` option.
* To tell it which databases to exclude from the backup, you can provide the `--databases-exclude` option.
* To tell it to check a file for the databases to backup, you can provide the `--databases-file` option.
* To tell it which tables to back up, you can use [the `--tables` option](https://mariadb.com/docs/server/server-usage/backup-and-restore/mariadb-backup-options#tables).
* To tell it which tables to exclude from the backup, you can provide the `--tables-exclude` option.
* To tell it to check a file for specific tables to backup, you can provide the `--tables-file` option.

The non-file partial backup options support regex in the database and table names.

For example, to take a backup of any database that starts with the string `app1_` and any table in those databases that start with the string `tab_`, run the following command:

```bash
$ mariadb-backup --backup \
   --target-dir=/var/mariadb/backup/ \
   --databases='app1 app2' --tables='tab_[0-9]+' \
   --user=mariadb-backup --password=mypassword
```

#### Using --history with Partial Backups

You can use the `--history` option with a partial backup to log the operation in the history table for auditing purposes.

```bash
mariadb-backup --backup --databases="db1" \
  --target-dir=/backup --history=partial_db1
```

{% hint style="warning" %}
You cannot use a partial backup as the base for an incremental backup history chain. The `--incremental-history-name` option is incompatible with partial backups because restoring partial incrementals requires specific preparation steps (`--export`) that the history feature does not automate.
{% endhint %}

{% hint style="info" %}
`mariadb-backup` cannot back up a subset of partitions from a partitioned table. Backing up a partitioned table is an all-or-nothing selection. See [MDEV-17132](https://jira.mariadb.org/browse/MDEV-17132) about that. If you need to backup a subset of partitions, one possibility is that instead of using `mariadb-backup`, you can export the file-per-table tablespaces of the partitions.
{% endhint %}

The time the backup takes depends on the size of the databases or tables you're backing up. You can cancel the backup if you need to, as the backup process does not modify the database.

`mariadb-backup` writes the backup files to the target directory. If the target directory doesn't exist, then it creates it. If the target directory exists and contains files, then it raises an error and aborts.

### Preparing the Backup

Just like with full backups, the data files that `mariadb-backup` creates in the target directory are not point-in-time consistent, given that the data files are copied at different times during the backup operation. If you try to restore from these files, InnoDB notices the inconsistencies and crashes to protect you from corruption. In fact, for partial backups, the backup is not even a completely functional MariaDB data directory, so InnoDB would raise more errors than it would for full backups. This point will also be very important to keep in mind during the restore process.

Before you can restore from a backup, you first need to **prepare** it to make the data files consistent. You can do so with the `--prepare` command option.

Partial backups rely on InnoDB's transportable tablespaces. For MariaDB to import tablespaces like these, InnoDB looks for a file with a `.cfg` extension. For `mariadb-backup` to create these files, you also need to add the `--export` option during the prepare step.

For example, you might execute the following command:

```
$ mariadb-backup --prepare --export \
   --target-dir=/var/mariadb/backup/
```

If this operation completes without error, then the backup is ready to be restored.

`mariadb-backup` did not support the `--export` option. See [MDEV-13466](https://jira.mariadb.org/browse/MDEV-13466) about that. This means that `mariadb-backup` could not create `.cfg` files for InnoDB file-per-table tablespaces during the `--prepare` stage. You can still import file-per-table tablespaces without the `.cfg` files in many cases, so it may still be possible in those versions to restore partial backups or to restore individual tables and partitions with just the `.ibd` files. If you have a full backup and you need to create `.cfg` files for InnoDB file-per-table tablespaces, then you can do so by preparing the backup as usual without the `--export` option, and then restoring the backup, and then starting the server. At that point, you can use the server's built-in features to copy the transportable tablespaces.

### Restoring the Backup

The restore process for partial backups is quite different than the process for full backups. A partial backup is not a completely functional data directory. The data dictionary in the InnoDB system tablespace will still contain entries for the databases and tables that were not included in the backup.

Rather than using the `--copy-back` or the `--move-back`, each individual InnoDB file-per-table tablespace file will have to be manually imported into the target server. The process that is used to import the file will depend on whether partitioning is involved.

#### Restoring Individual Non-Partitioned Tables

To restore individual non-partitioned tables from a backup, find the `.ibd` and `.cfg` files for the table in the backup, and then import them using the Importing Transportable Tablespaces for Non-partitioned Tables process.

#### Restoring Individual Partitions and Partitioned Tables

To restore individual partitions or partitioned tables from a backup, find the `.ibd` and `.cfg` files for the partitions in the backup, and then import them using the [Importing Transportable Tablespaces for Partitioned Tables](https://mariadb.com/docs/server/storage-engines/innodb/innodb-tablespaces/innodb-file-per-table-tablespaces#importing-transportable-tablespaces-for-partitioned-tables) process.

#### Restoring Individual Tables with Full-Text Indexes

When restoring a table with a full-text search (FTS) index, InnoDB may throw a schema mismatch error.

In this case, to restore the table, it is recommended to:

* Remove the corresponding `.cfg` file.
* Restore data to a table without any secondary indexes including FTS.
* Add the necessary secondary indexes to the restored table.

For example, to restore table `t1` with FTS index from database `db1`:

1. In the MariaDB Command-Line Client, drop the table you are going to restore:

   ```sql
   DROP TABLE IF EXISTS db1.t1;
   ```
2. Create an empty table for the data being restored:

   ```sql
   CREATE TABLE db1.t1(f1 CHAR(10)) ENGINE=INNODB;
   ```
3. Modify the table to discard the tablespace:

   ```sql
   ALTER TABLE db1.t1 DISCARD TABLESPACE;
   ```
4. In the operating system shell, copy the table files from the backup to the data directory of the corresponding database:

   ```bash
   $ sudo cp /data/backups/part/db1/t1.* /var/lib/mysql/db1
   ```
5. Remove the `.cfg` file from the data directory:

   ```bash
   $ sudo rm /var/lib/mysql/db1/t1.cfg
   ```
6. Change the owner of the newly copied files to the system user running MariaDB Server:

   ```bash
   $ sudo chown mysql:mysql /var/lib/mysql/db1/t1.*
   ```
7. In the MariaDB Command-Line Client, import the copied tablespace:

   ```sql
   ALTER TABLE db1.t1 IMPORT TABLESPACE;
   ```
8. Verify that the data has been successfully restored:

   ```sql
   SELECT * FROM db1.t1;
   ```
9. Add the necessary secondary indexes:

   ```sql
   ALTER TABLE db1.t1 FORCE, ADD FULLTEXT INDEX f_idx(f1);
   ```
10. The table is now fully restored:

    ```sql
    SHOW CREATE TABLE db1.t1\G
    *************************** 1. row ***************************
           Table: t1
    Create Table: CREATE TABLE `t1` (
      `f1` char(10) DEFAULT NULL,
      FULLTEXT KEY `f_idx` (`f1`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
    ```

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/server-usage/backup-and-restore/mariadb-backup/partial-backup-and-restore-with-mariadb-backup.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.