Restoring Data from Dump Files Guide

Data Restoration 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.

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:

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

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

   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:

   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.