How to restore MySQL or MariaDB database from backup

Restoring a MySQL or MariaDB database from a backup is an essential skill for any database administrator or developer. Backups serve as a safety net in case of data loss, server crashes, or other unexpected incidents. It's important to create regular backups of your databases and know how to restore them when needed.

Restoring a database involves two main steps: retrieving the backup file and importing it into the target database system. The process may vary slightly depending on the backup method you used, such as mysqldump, mysqlhotcopy, or a third-party tool.

This guide will outline the process of restoring a MySQL or MariaDB database using a backup created with the mysqldump command. Follow the steps below to successfully restore your database.

1. Locate your backup file, which should have a .sql extension (e.g., my_database_backup.sql).
2. Log in to your MySQL or MariaDB server using the command-line client.

   mysql -u your_username -p

3. If the target database does not exist, create it using the following command.

   CREATE DATABASE target_database;

4. Select the target database with the following command.

   USE target_database;

5. Exit the MySQL or MariaDB command-line client by typing exit.
6. Import the backup file into the target database using the following command.

   mysql -u your_username -p target_database < my_database_backup.sql

7. Wait for the import process to finish, as it may take some time depending on the size of the backup file.
8. Verify that the data was restored correctly by querying the target database and comparing the results with the original database.