How to restore MySQL or MariaDB database from backup

Restoring a MySQL or MariaDB database from a backup is a crucial task when recovering from data loss, corruption, or other failures. Backups created using mysqldump can be easily restored to the original database or to a new database if needed. If the backup was compressed using gzip, the file must be decompressed before or during the restore process.

The restoration process involves loading the SQL file back into the database using the mysql client. For compressed backups, you can directly pipe the gzip file into the mysql command for seamless restoration without manually decompressing the file.

This article provides step-by-step instructions for restoring a MySQL or MariaDB database from both uncompressed and compressed backup files, ensuring data can be recovered efficiently.

1. Access the MySQL or MariaDB shell with administrative privileges.

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

2. If you are restoring to a new database, create the database first.

   mysql> CREATE DATABASE new_database;
   Query OK, 1 row affected (0.01 sec)

   Replace “new_database” with the name of the database where the backup will be restored.

3. Restore the database from an uncompressed SQL backup file.

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

   This command restores the backup to the specified database. Replace “your_database” with the name of the database and adjust the path to the location of the SQL backup file.

4. To restore from a compressed backup, pipe the gzip file directly to mysql.

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

   This command decompresses the file and restores the database in one step. Replace “your_database” with the target database name and adjust the path as needed.

5. If restoring multiple databases from a compressed backup, follow the same procedure.

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

   This command restores multiple databases from a compressed backup. The SQL file contains instructions for restoring each database.

6. Verify that the database has been restored by checking the tables.

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

   Ensure the tables have been restored correctly in the target database.