# Restoring Data from Dump Files Guide

This guide explains how to restore your MariaDB data from backup files created with `mariadb-dump`. Learn the basic restoration process using the `mariadb` client and a specific technique for selectively restoring a single table while minimizing data loss on other tables.

It's important to understand that `mariadb-dump` is used for creating backup (dump) files, while the `mariadb` client utility is used for restoring data from these files. The dump file contains SQL statements that, when executed, recreate the database structure and/or data.

### Basic Restoration Process

To restore a dump file, you direct the `mariadb` client to execute the SQL statements contained within the file.

```bash
mariadb --user your_username --password < /path/to/your/backupfile.sql
```

* Replace `your_username` with your MariaDB username and `/path/to/your/backupfile.sql` with the actual path to your dump file.
* You will be prompted for the password for `your_username`.
* The `<` symbol is a standard input (STDIN) redirect, feeding the contents of `backupfile.sql` to the `mariadb` client.
* Often, the dump file itself contains `CREATE DATABASE IF NOT EXISTS` and `USE database_name;` statements, so a specific database doesn't always need to be named on the command line during restore. If your dump file restores to a specific database, ensure that user has permissions to it. If the dump file does *not* specify a database, you might need to create the database first and then run:

  ```bash
  mariadb --user your_username --password your_database_name < /path/to/your/backupfile.sql
  ```

### Important Considerations Before Restoring

* **Data Overwriting:** Restoring a dump file will execute the SQL statements within it. If the dump file contains `DROP TABLE` and `CREATE TABLE` statements (common for full backups), existing tables with the same names will be dropped and recreated, leading to loss of any data added or changed since the backup was made.
* **Backup Age:** If your dump file is several days old, restoring it entirely could revert all data in the affected tables/databases to that older state. This can be disastrous if only a small portion of data was lost and the rest has been actively updated.

Always ensure you understand the contents of the dump file and the potential impact before initiating a restore, especially on a production system. Consider testing the restore on a non-production environment first if possible.

### Restoring a Single Table Selectively

If only one table has been lost or corrupted and your backup file contains an entire database (or multiple tables), a full restore might overwrite recent, valid data in other tables. Here’s a method to restore only a specific table using a temporary user with restricted privileges:

1. **Create a Temporary User:** Create a MariaDB user specifically for this restore operation.
2. **Grant Limited Privileges:**

   * Grant this temporary user the minimal privileges needed for the dump file to execute up to the point of restoring your target table. This might be `SELECT` on all tables in the database if the dump file checks other tables, or simply the ability to `USE` the database.
   * Then, grant `ALL PRIVILEGES` (or specific necessary privileges like `CREATE`, `DROP`, `INSERT`, `SELECT`) *only* on the specific table you want to restore.

   Example SQL to create a temporary user and grant permissions (replace placeholders):

   ```sql
   -- Connect to MariaDB as an administrative user (e.g., root)
   CREATE USER 'admin_restore_temp'@'localhost' IDENTIFIED BY 'its_very_secure_pwd';

   -- Grant general SELECT on the database (might be needed if dump file structure requires it)
   -- Or, if not needed, ensure the user can at least USE the database.
   GRANT SELECT ON your_database_name.* TO 'admin_restore_temp'@'localhost';

   -- Grant full privileges ONLY on the table to be restored
   GRANT ALL PRIVILEGES ON your_database_name.table_to_restore TO 'admin_restore_temp'@'localhost';

   FLUSH PRIVILEGES;
   ```
3. Restore Using the Temporary User and `--force`:

   Use the mariadb client with the temporary user and the --force option. The --force option tells MariaDB to continue executing statements in the dump file even if some SQL errors occur. Errors will occur for operations on tables where admin\_restore\_temp lacks permissions, but operations on table\_to\_restore (where permissions were granted) should succeed.

   Bash

   ```bash
   mariadb --user admin_restore_temp --password --force your_database_name < /path/to/your/fulldumpfile.sql
   ```

   You will be prompted for the password of `admin_restore_temp`.
4. **Verify Restoration:** Check that `table_to_restore` has been correctly restored.
5. **Clean Up:** Drop the temporary user once the restoration is confirmed:

   ```sql
   DROP USER 'admin_restore_temp'@'localhost';
   ```

This method helps to isolate the restore operation to the intended table, protecting other data from being inadvertently reverted to an older state.

{% @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/mariadb-quickstart-guides/mariadb-restore-guide.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.