How to restore MySQL or MariaDB database from backup

Restoring a recent database dump is the fastest way to recover from an accidental delete, a failed migration, or a bad application deploy. A clean restore point turns recovery into a repeatable operation instead of a manual attempt to rebuild tables and rows under pressure.

SQL-format backups created with mysqldump or mariadb-dump are replayed through the regular client, which executes the saved CREATE, INSERT, TRIGGER, and related statements in order. Single-database dumps often expect an already existing target database, while dumps created with --databases or --all-databases include CREATE DATABASE and USE statements that choose the destination during import.

Restore commands write to the selected server immediately, so the safest workflow is an empty target database or an isolated restore host. Recent mariadb-dump output can begin with a sandbox-mode header that should be loaded with the mariadb client, and large imports can still surface runtime limits such as max_allowed_packet or connection timeouts that need to be adjusted before retrying.

1. Preview the start of the dump file to identify its source and format.

   $ head -n 12 /srv/backups/appdb-single.sql
   /*M!999999\- enable the sandbox mode */ 
   -- MariaDB dump 10.19  Distrib 10.11.14-MariaDB, for debian-linux-gnu (aarch64)
   --
   -- Host: localhost    Database: appdb
   -- ------------------------------------------------------
   -- Server version	10.11.14-MariaDB-0ubuntu0.24.04.1
   
   /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
   /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
   /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
   /*!40101 SET NAMES utf8mb4 */

   MariaDB documents that recent mariadb-dump files can start with a sandbox-mode comment that older mysql clients do not understand. When that header is present, restore with mariadb instead of an older MySQL client.

2. Check whether the dump already contains database-selection statements.

   $ grep -nE '^(CREATE DATABASE|USE )' /srv/backups/appdb-multiple.sql
   23:CREATE DATABASE /*!32312 IF NOT EXISTS*/ `appdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
   25:USE `appdb`;

   No output means the dump expects an existing target database. For a compressed file, use gzip --decompress --stdout /srv/backups/appdb-multiple.sql.gz | grep -nE '^(CREATE DATABASE|USE )'.

3. Create an empty target database when the dump does not contain CREATE DATABASE or USE.

   $ mysql --user=root -e "CREATE DATABASE appdb_restore CHARACTER SET utf8mb4;"

   Repeat the same pattern with another empty database name such as appdb_restore_gz before restoring a second test copy from a compressed dump.

   Reuse the source character set and collation when the application depends on a specific default collation.

4. Import an uncompressed .sql dump into the target database.

   $ mysql --user=root appdb_restore < /srv/backups/appdb-single.sql

   The import starts writing immediately. Use the correct host, port, or socket, and point the command at an empty or intentionally disposable database.

   If the dump begins with the sandbox-mode comment from the first step, replace mysql with mariadb in the import commands.

   If the account does not use socket auth, an option file, or a MySQL login path, add --password so the client prompts securely instead of embedding the secret on the command line.

5. Stream a compressed dump directly into an existing empty database.

   $ gzip --decompress --stdout /srv/backups/appdb-single.sql.gz | mysql --user=root appdb_restore_gz

   Streaming avoids writing a second full-size .sql file to disk before the import starts.

6. Import a dump created with --databases or --all-databases without appending a database name to the client command.

   $ mysql --user=root < /srv/backups/appdb-multiple.sql

   Those dumps already contain CREATE DATABASE and USE statements, so adding a database name on the command line can redirect or conflict with the statements inside the file.

   Current MySQL releases reject DROP DATABASE mysql during reload. If an all-databases dump was created with --add-drop-database and includes the mysql system schema, recreate the dump without that option or remove the offending statement before import.

7. Verify that the expected tables and rows exist after the restore.

   $ mysql --user=root -e "SHOW TABLES FROM appdb_restore; SELECT COUNT(*) AS row_count FROM appdb_restore.orders;"
   Tables_in_appdb_restore
   orders
   row_count
   3

   Use a table that should always exist in the application schema, then follow with a targeted sanity query such as SELECT COUNT(*) or SELECT MAX(id) to confirm the restored data is not empty.