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:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 143
   Server version: 8.0.44 MySQL Community Server - GPL
   
   Copyright (c) 2000, 2025, Oracle and/or its affiliates.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql>

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

2. Create empty target databases for the restore examples.

   mysql> CREATE DATABASE appdb_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
   Query OK, 1 row affected (0.00 sec)

   Replace appdb_restore 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 appdb_restore < /root/sg-work/backups/mysql/appdb-single-2025-12-24_235042.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 /root/sg-work/backups/mysql/appdb-single-2025-12-24_235042.sql.gz | mysql -u root -p appdb_restore_gz
   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 < /root/sg-work/backups/mysql/restore-databases-2025-12-24_235042.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:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 146
   Server version: 8.0.44 MySQL Community Server - GPL
   ##### snipped #####
   mysql> USE appdb_restore;
   Database changed
   mysql> SHOW TABLES;
   +-------------------------+
   | Tables_in_appdb_restore |
   +-------------------------+
   | audit_log               |
   | customers               |
   | order_items             |
   | orders                  |
   | users                   |
   +-------------------------+
   5 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.