How to restore MySQL or MariaDB database from backup

Restoring a MySQL or MariaDB database from a backup recovers from accidental deletes, failed upgrades, or storage corruption while keeping recovery repeatable under pressure.

Most logical backups are created with mysqldump, producing a plain-text .sql file containing SQL statements that recreate schema objects and repopulate data. Single-database dumps created without --databases expect the target database to exist, while multi-database dumps often include CREATE DATABASE and USE statements to select targets during import.

Restores apply changes immediately to the selected server and database, so a wrong --host value or restoring into a populated database can overwrite objects or fail with conflicts. Large dumps can also trigger limits such as max_allowed_packet or connection timeouts, so any error output should be treated as a direct clue about what needs adjusting.

1. Connect to the target server using the mysql client.

   $ mysql -u root -p
   Enter password: ********
   mysql>

   Add --host, --port, or --socket when the server is not on the default local endpoint.

2. Create an empty target database for the restore when importing a single-database dump.

   mysql> CREATE DATABASE restored_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   Query OK, 1 row affected (0.01 sec)

   Replace restored_db with the intended target name.

3. Exit the mysql shell.

   mysql> exit
   Bye

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

   $ mysql -u root -p restored_db < /path/to/backup/your_database.sql
   Enter password: ********

   Importing into the wrong database writes immediately and is difficult to undo without another restore, so confirm the target name before running the command.

5. Stream a compressed .sql.gz dump into the target database.

   $ gunzip -c /path/to/backup/your_database.sql.gz | mysql -u root -p restored_db
   Enter password: ********

   Streaming avoids writing a second full-size .sql file to disk during decompression.

6. Import a multi-database dump without specifying a database name.

   $ mysql -u root -p < /path/to/backup/multiple_databases.sql
   Enter password: ********

   Multi-database dumps created with mysqldump –databases or mysqldump –all-databases typically include database selection statements, so importing into a specific database name can produce surprising results.

7. Verify the restored schema by listing tables in the target database.

   $ mysql -u root -p
   Enter password: ********
   mysql> USE restored_db;
   Database changed
   mysql> SHOW TABLES;
   +-----------------------+
   | Tables_in_restored_db |
   +-----------------------+
   | table1                |
   | table2                |
   | table3                |
   +-----------------------+
   3 rows in set (0.00 sec)

   When a known table exists, a sanity query like SELECT COUNT(*) FROM table1; confirms data was imported, not just schema.